Making a DB Connection Using Service Test 11’s Custom Code

Automation Testing Published on:
UFT API Cover

I've been getting this question a lot lately: “How can I connect to a database from Service Test 11?” Actually, it would be nice if Service Test had a database activity. It doesn't, unfortunately, but the good news is that it’s fairly easy to create one.

There are a few ways to do this in C#, but for this example, I'll be using ODBC.

DSN Setup:

First, make sure you have an ODBC DSN setup under Windows Admin Tools\ODBC. I'm using mySQL for this example:

Service Test 11 DNS

 

 

 

 

 

 

 

Database Info:

I also have mySQL running on my local machine with a database named ‘servicetest' that has a table named ‘customers':

 

 

 

 

 

Custom Code:

Drag a custom code activity onto your main canvas area in Service Test, then go to its events and select ‘create a default handler' from the ExecuteEvent. In the ‘SharedUserCode' area, we need to first import the ODBC and Forms libraries:

using System.Windows.Forms;

using System.Data.Odbc;

 

Next, type the following code to create a connection to the DB and return a record:

//Create a string variable that holds your database connection info.
string strConnect = “DSN=yourDSNName;UID=yourDBUserName;PWD=yourDbPassword;DATABASE=yourDbName”;
//Create a connection object
OdbcConnection dbMySQL = new OdbcConnection(strConnect);
//Open a connection
dbMySQL.Open();
//Create a OdbcCommand that will hold the sql statement to execute
OdbcCommand sqlCommand = dbMySQL.CreateCommand();

sqlCommand.CommandText = “select * from customers”;
//Create a sqlReader object which provides a way to read the data rows returned from the data source
OdbcDataReader sqlReader = sqlCommand.ExecuteReader();
//Loop through all the returned recordsets
while (sqlReader.Read())
{
//Show the second column value in the current row In this example it would return the Name field.
MessageBox.Show(sqlReader.GetString(1));
}

 

The final code in Service Test should look like this:

 

 

 

 

 

 

 

 

 

To learn how to pass data between activities in Service Test 11, check out my blog post on how to use the custom code functionality.

For my fellow bibliomaniacs who may be new to C#, I would also recommend these two books:

  1. Beginning C# 3.0: An Introduction to Object Oriented Programming (Wrox Beginning Guides)
  2. A Tester's Guide to .NET Programming (Expert's Voice)

Leave a Reply

Your email address will not be published.

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

  1. Hi,
    I would like to update that we have a complete support for DB in Service Test.
    you can connect, extract information and change information. you can use extracted information within your flow for data driven testing.
    all that without one line of code.

    1. @Ofer: Hi Ofer – this is great news! Can you please tell me where this functionality is found? I know you can data drive info using Excel or XML but I don’t seen any options to point to a database. I might be missing something but I can’t find this info in the HP ST User guide either. Thanks — Look forward to hearing back from you!

  2. @Joe:
    Hi Joe, the support for DB is part of our next release, coming out quite soon.
    We are using the same concepts as for Excel – you can bring the data in DT, see it like Excel and data drive.
    Also in RT, you can connect to the DB, extract/update data and link it like any other data in the flow.
    The user guide will include all the required information.
    If you need additional help/information on ST 11 and above, feel free to address me.
    Ofer

    1. @Ofer: Awesome – you made my day! I can’t wait to get my hands on the new version. I really like ST11 — you guys did a great job developing it.

  3. Hi joe,

    Currently i have just started to explore ST 11.20. I have a requirement to connect to AS400 database, fetch data from it through multiple SQL Queries and compare the result table that is generated through all queries with the SOA Response nodes and also update the result table to an excel file for reference.

    I have kept all the sql queries in excel file and i am able to parameterize it succssfully using custom code.
    Then i am sending this customcode output property(SQL String) as a input to select data component.

    Here i need to write “After Execute step event” to append the result table items to an Dynamic array or list.But i am not able to access the Reslut Table of Select Data Check Point.

    Could you help me to get this done. Also suggest me other solutions that may be helpful for me?

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
UFT API Cover

Latest Automation Trends: Top 13 Predictions for 2023

Posted on 01/04/2023

Welcome to my annual Latest Automation Trends: Top 13 Predictions for 2023 article. ...

Chrome Selenium Driver in Visual Studio C#

Posted on 12/21/2022

I'm sure you've all heard of Selenium by now. It's a popular tool ...

15 Reasons Why You Should (or shouldn’t) Automate a Test

Posted on 12/20/2022

Just because you can automate tests doesn’t necessarily mean that you should. In ...