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

UFT API 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':

PHP mySql table

 

 

 

 

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:

Custom Code for ST DB connection

 

 

 

 

 

 

 

 

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)

6 responses to “Making a DB Connection Using Service Test 11’s Custom Code”

  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.

    • @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

    • @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?

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.

UFT API Cover