QTP Database Testing DSN Connection String Examples MySQL, SQL Server, Excel, Oracle

QTP/UFT Published on:
Code Hacker

QTP and UFT Database Testing DSN Connection String Examples for MySQL, SQL Server, and Excel

Need to test running queries against a database? Does your test need to insert data into a database table? QTP can do it using DSN.

What is DSN?

DSN stands for Data Source Name.  Setting up a DSN allows you to connect to a database through an ODBC driver. To use you need to first create a DSN with a unique name, and other connection info like username and password in the Windows “ODBC Data Source Administrator.” To use the DSN in QuickTest Professional you must reference the DSN name that you created in the ODBC admin.

[smartads]

The downside to this approach is that for every machine on which you plan to run your QTP test on you will also need to setup a DSN on that machine with the same info that you created earlier. For an example of setting up an ODBC DSN, check out my post entitled “How to create a ODBC DSN Connection.”

What is a DSN-less connection?

The DSN-less approach allows you to specify all the database connection info right in QuickTest Pro. This is the easiest way to connect to a DB because you’ll no longer need to worry about setting up anything on the run machine — the one exception being that all the machines do need to have the correct drivers installed on them.

What is ADO?

ADO stands for ActiveX Data Object and allows you to access a database from inside QTP using VBScript. To use ADO in QTP you’ll simply need to set a connectionstring and a use the Open and Execute methods.

MySQL connection string example

First make sure you have the latest MySQL drivers installed.

My DB info in MySQL:

In QTP enter:

SQL Server 2008


‘The name of my Server

strCurrentEnv = “MyServername\JOESQL”

‘The name of your database:

dbName = “qtpDemo”

‘SQL Server connection string(you need to enter your username and password)

strConnection = “DRIVER={SQL SERVER}; Server=” & strCurrentEnv & “; DATABASE=”& dbName& “;uid=sa; pwd=”

Set conn = CreateObject(“ADODB.Connection”)

conn.Open strConnection

‘The SQL you want to run

query = “Select * from dbo.hpTools”

Set rs = conn.Execute(query)

dbResults = rs.GetString

print dbResults

DSN Excel 2010


strExcelFileName = “C:\test.xlsx”

strConnection = “Provider=Microsoft.ACE.OLEDB.12.0;” & “Data Source=” & strExcelFileName & “;” & ” Extended Properties=Excel 12.0;Persist Security Info=False”

Set conn = createobject(“ADODB.connection”)

conn.Open strConnection

query = “Select * from [hptools$]”

Set rs = conn.Execute(query)

dbResults = rs.GetString

print dbResults

How does it work to get QTP working with a Database?

In general QTP can use database resources already installed on most computers. The most common are ODBC and ADO and both can be used to communicate with a database using VBScript code in QTP.

How to Connect to Oracle using an OLE DB Provider

How to Connect to Oracle using a MS ODBC for Oracle

Unable to connect?

If you have issue always remember that the best person to ask would be your Oracle admin. He/She should be able to help you create a connection string if the two examples above do not work for you.

26 responses to “QTP Database Testing DSN Connection String Examples MySQL, SQL Server, Excel, Oracle”

  1. Is it possible to use your DSN-less approach, using ado – Set conn = CreateObject(“ADODB.Connection”) etc…, without the connection string info not being broadcasted over the network. I would assume some sort of SSL communication between your QTP client and the database it is attempting to interact with?

    thanks,

    Greg

    • Greg » Good question – I’m not sure. I’ll keep looking and let you know if I find a way. Please let me know if you figure it out. Cheers~Joe

  2. I tried connecting to the excel sheet using your code.I could not do it.I ma wondering if this is because the recordset object is not used in the code?

  3. My own problem with this approach is that we’re trying to get functional testers to write scripts at my company. We don’t want to expose our connection particulars to them. Therefore… I intend to write some compiled C# DLLs as a facade to the DB. That way they can just call simple methods to interact with the DB instead of using a bunch of ad hoc SQL statements.

    • lagunascorpio » I would rather create some DB function in QTP. Since it uses ADO just like C# you should be able to get similar functionality. I’ve worked with groups that have created C# frameworks with QTP and it seems the maintenance of scripts, that use this approach, is pretty high.

  4. Hi,

    I am unable to connect to the database,MySQL DB from QTP tool as the following error message is displayed:
    “[MySQL][ODBC 5.2(w) Driver]Can’t connect to MySQL server on ‘194.157.33.182’ (10060)”

    Thanks,
    George

  5. Hi,

    Sometimes my QTP script is taking a lot of time to connect to the database (db2) . Is there any work around for it?

  6. Hi Joe,

    The information provided above is very useful. In my case I using Excel 2003 and when I try to connect using the below statement then it trhows an error that Provider cannot be found and teh driver might not be installed correctly. Can you please help me knowing where I am mistaken.
    “strConnection = “Provider=Microsoft.ACE.OLEDB.12.0;” & “Data Source=” & strExcelFileName & “;” & ” Extended Properties=Excel 12.0;Persist Security Info=False”

    Thanks.

  7. Simple approah to connect to my sql db but I just can’t connect.

    ‘The server name you need to connect to:
    strCurrentEnv = “qamysql01”

    ‘The name of your database:
    dbName = “DB_Name” ‘using my sqlyog I do not use ay databasename so in this case it should be empty

    ‘The user ID
    uID = “UserID”

    ‘Password
    pwd = “Password”

    ‘My SQL connection string. You need to enter your username and password
    strcntn = “DRIVER={MySQL ODBC 5.2 Driver}; Server=” & strCurrentEnv & “; DATABASE=” & dbName & “; uID=” & uID & “; pwd=” & pwd
    print strcntn

    Set cntn = CreateObject(“ADODB.Connection”)
    cntn.Open (strConnection)

    ‘The SQL you want to run
    sqlquery = “Select * from accounts where account_id = 55”

    ‘Runs your SQL
    Set res = cntn.Execute(query)

    dbResults = res.GetString

    print dbResults

  8. Hi Joe,

    That Information was really useful to me.

    In my Own framework i am trying to use a DSN-LESS Connection and the code looks something like this

    connectionString = “Driver={Microsoft ODBC for Oracle};” & “CONNECTSTRING=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=”& Host &”)(PORT=”& Port &”))” & “(CONNECT_DATA=(SID=”& Server &”)));Uid=”& UID &”;Pwd=”& Pwd &”;”

    Do you think, this is the right approach for creating DSN-LESS Connection ? Or am I missing something

    Cheers
    Abdul

  9. Hi Joe,
    I have a Scenario where i have to connect to the Existing Oracle server with Excel macro which will run query at backend and update the excel sheet for user details .just want o know what should be my approach to this…..

  10. Hi,

    When i am trying to connect to Excel database using ADO object from UFT 11.53 i always get “System Resource Exceed” error. Can you please tell me UFT 11.53 is having any issues when using ADO objects because when i was using QTP 11 i was not having any issues. Do i have to install any patches…?

    Thanks,

  11. i am getting error, when i am trying to execute the query in the QTP/UFT, with the same approach u have mentioned above.

    the same query is working fine in SQL

    select distinct a.table_id, b.* from table a, b

    Request you please help

  12. Hi Jeo,

    I really appreciate the information you have shared !!

    I need you support hear I am using excel 2013 and OS 10, I have designed my code for excel 97-2003 ,it is working fine.
    when i am using in same code for excel 2013 and OS 10 not establishing connection with excel 2013 using ADODB.Connection

    Could you please help me what driver other parameters will use soatht i can connect to excel 2013

    Waiting for your response :)

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Code Hacker