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

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.

If you're not using QTP another great tool to help with database testing is QuerySurge.

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.

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 set up 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 an 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.