If your UFT testing involves a lot of database checkpoints or output values, then you’ve probably noticed that a lot of “DbTable_#” objects get created in your object repository (OR). The more checkpoints or output values you use, the more cluttered the OR gets.
In the image below, you can see three DbTable objects have been created. Each one connects to the same database (connectionstring), but uses a different SQL query (source). (The dbuniqueid is different for each, but for our purposes, this is irrelevant.)
This can be a real pain when the database information changes because then you have to modify each DbTable object individually. That can be really time consuming and introduce hard-to-find-and-fix errors.
As you can see in the above code, each checkpoint is using the three different DbTable objects. If your database connection string or SQL query changes, you have to manually update each DbTable object with the new information.
I am going to explain how to use only one DbTable object in your testing and be able to use it no matter what database you are connecting to or what query you are running. This method will take a little more time up front when creating your database checkpoints and output values, but it will save a ton of time down the road when your database information changes.
For this example, we will use the HP Flight Application database. In order to use this method, you need to know the connection string for the database you are accessing. Here’s a great way to find out the connection string to your database: http://eyeontesting.com/questions/7403/how-to-get-db-connection-string-to-use-in-uft.html. The connection string for the Flight database should look similar to this:
DSN=QT_Flight32;DBQ=C:\Program Files (x86)\HP\Unified Functional Testing\\samples\flight32.mdb;Driver=C:\Windows\system32\odbcjt32.dll;DriverId=281;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;
Making the DbTable Object
Start by creating a database checkpoint or output value. This will create the DbTable object that we will use for the rest of the test. If you are using the Flight application database, you can just use the technique in the above link to get the connection string and then use “Select * from flights” for the query.
Parameterizing the DbTable Object
Now that the initial DbTable object is in the OR, select it and edit the “source.” Click the “Configure the value” button.
Next, click the “Parameter” radio button and select “Data Table.” I recommend placing this parameter in the “Global sheet.” You can keep the name of “source” or change it to something else that makes sense to you. I like to use SQL_Query.
Now, do the same thing with the “connectionstring” property.
I like to leave the name as “connectionstring.”
Now, both properties have been parameterized, and we can manipulate them from our script as needed.
Using the DbTable Object
At this point, we can set the values for each checkpoint or output value in the data table as needed.
Create each new checkpoint or output value as you normally would, and a new DbTable object will be created in the OR. Just delete it, then modify the object in the checkpoint or output value code to read as DbTable rather than DbTable_#. Add code to update the connectionstring (if needed), and the SQL_Query as required. In this example, I did not need to update the connectionstring, so it is set at the first checkpoint and is retained, unless it is changed later in the script.
Going Forward
After you have the DbTable object and parameters set up, you can now send any connection string or SQL query to the object. As mentioned, there is a little more to do when creating the checkpoint or output value, but going forward, when the database information or SQL query changes for your test, you can just replace the values in the script rather than editing a bunch of DbTable objects in the OR. This also enables you to use multiple connection strings to connect to different databases via the single DbTable object.
If you are using shared object repositories (and you should be!), then you can create this object in its own OR file and associate it in any action that needs to do database queries.
This method enables your tests to have dynamic database connections and run any query, and it keeps your object repositories clean of a bunch of DbTable objects.
