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

PerfectoTopAd

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 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:

'The server name you need to connect to:
strCurrentEnv = "localhost"

'The name of your database:
dbName = "mysqlqtp"

'My SQL connection string. You need to enter your username and password
strConnection = "DRIVER={MySQL ODBC 5.1 Driver}; Server=" & strCurrentEnv & "; DATABASE="& dbName& ";uid=root; pwd="

Set conn = CreateObject("ADODB.Connection")
conn.Open strConnection

'The SQL you want to run
query = "Select * from hptools"

'Runs your SQL
Set rs = conn.Execute(query)
dbResults = rs.GetString
print dbResults

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

 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



26 comments
Greg - September 15, 2011

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

Reply
    Joe Colantonio - September 16, 2011

    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

    Reply
QTP – How to Connect To an Oracle Database — Joe Colantonio -- Test Automation Geek | Bibliomaniac | Software Imagineer - December 17, 2011

[…] a previous post on QTP Database Testing I gave examples on how to connect to MySql, Sql server and Excel databases. Since then I’ve received a few questions on how-to do the same for an Oracle […]

Reply
malay - December 28, 2011

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?

Reply
Ganesh Kodali - March 14, 2012

Thanks its very useful information for Automation Test Engineer

Reply
    Joe Colantonio - March 15, 2012

    Ganesh Kodali » Appreciate it Ganesh!

    Reply
lagunascorpio - May 30, 2012

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.

Reply
    Joe Colantonio - May 31, 2012

    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.

    Reply
George - March 20, 2013

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

Reply
    Joe Colantonio - March 25, 2013

    George » Are you able to connect to the DB without using QTP to see if this is a QTP issue or a permission issue?

    Reply
Narayan Davey - April 26, 2013

Hi,

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

Reply
    Narayan Davey - April 26, 2013

    Is there any workaround for the same as connection to db should take more time ..

    Reply
    Joe Colantonio - May 3, 2013

    Narayan Davey » I’m not aware of any know issues – I use QTP for SQL and Cache DBs without performance issue. Anyone else seen this?

    Reply
Chittranjan - December 4, 2013

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.

Reply
CArlos - February 7, 2014

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

Reply
Abdul - February 13, 2014

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

Reply
loks - April 14, 2014

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…..

Reply
Prashant - September 19, 2014

How do I validate database userid and password are valid before I fetch the data from Oracle?

Reply
[BLOCKED BY STBV] Fix Mysql Connector Odbc Error 10060 Windows XP, Vista, 7, 8 [Solved] - November 22, 2014

[…] QTP Database Testing – DSN Connection String … – 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?… […]

Reply
Rehma - February 25, 2015

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,

Reply
srikar - October 11, 2015

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

Reply
Ishita Chakraborty - September 19, 2016

How do I connect Hive from QTP?

Reply
    Joe Colantonio - September 26, 2016

    I’ve never tried. Does Hive you JDBC drivers? Can you setup an ODBC connection and connect using straight vbscript in QTP?

    Reply
Bibhu - June 15, 2017

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 :)

Reply
Click here to add a comment

Leave a comment: