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 = “MyServernameJOESQL”
‘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.
Joe Colantonio is the founder of TestGuild, an industry-leading platform for automation testing and software testing tools. With over 25 years of hands-on experience, he has worked with top enterprise companies, helped develop early test automation tools and frameworks, and runs the largest online automation testing conference, Automation Guild.
Joe is also the author of Automation Awesomeness: 260 Actionable Affirmations To Improve Your QA & Automation Testing Skills and the host of the TestGuild podcast, which he has released weekly since 2014, making it the longest-running podcast dedicated to automation testing. Over the years, he has interviewed top thought leaders in DevOps, AI-driven test automation, and software quality, shaping the conversation in the industry.
With a reach of over 400,000 across his YouTube channel, LinkedIn, email list, and other social channels, Joe’s insights impact thousands of testers and engineers worldwide.
He has worked with some of the top companies in software testing and automation, including Tricentis, Keysight, Applitools, and BrowserStack, as sponsors and partners, helping them connect with the right audience in the automation testing space.
Follow him on LinkedIn or check out more at TestGuild.com.
Related Posts
Bottom Line: Kobiton is the first real device testing platform I’ve seen that makes AI-powered mobile testing feel like it […]
Look, most of the AI testing tools I cover on the TestGuild Automation Podcast share two things in common: they’re […]
At least one in five people has some kind of impairment, so it’s important to have them in mind when […]
Last Updated: April 18, 2026 By Joe Colantonio — 25+ years in testing, 500+ podcast interviews with tool creators Full […]