How to execute DB tool in a loop

Sindhu

Hi,

I need to execute DB tool query in a loop based on the count is fetched at run time which depends on the number of array elements in my json payload.
Kindly help me with a solution, Thanks in advance!!

benken_parasoft

In your test suite, define a test variable with some name like "myCount". Elsewhere in your test suite, use a JSON Data Bank to capture "count(/root/myArray/item)" (an XPath that counts the array items) and assign the extraction to your "myCount" variable. Lastly, open your test suite and click Execution Options > Test Flow Logic. From there, you can configure a "While variable" loop your DB Tool based on "myCount".

See Configuring Test Suite Properties.

Sindhu

Thank you!!
I tried the 1st steps and able to store the count. However the last step for configuring the test suite I am using Parasoft Virtualize and I have Responder suite which doesn't have a tab for Execution options. Do we have a alternative for Parasoft Virtualize?

Also my parameters in where clause in the DB tool -> SQL Query will be updated for each iteration, do we have a solution for that?

benken_parasoft

My last answer is applicable for provisioning actions or functional test suites, not virtual asset deployments.

Perhaps you can dynamically construct a single SQL statement based on the data from all the array items? Alternatively, you can also script this entirely, using JDBC API from Extension Tool. From an Extension Tool you can acquire a java.sql.Connection object using "com.parasoft.api.SQLUtil.getConnection(String driverName, String url, String user, String password)". In your script, you can define your own loop where you create and execute your SQL statement.

Sindhu

Thank you, I tried the below code but doesn't seem like the connection is established. I also added the jar to systems properties and JDBC drivers. Any solution to connect to driver successfully??

import com.parasoft.api.*
import java.sql.*

void sqlConnection() {

Connection conn = SQLUtil.getConnection(
"com.microsoft.sqlserver.jdbc.SQLServerDriver", // or whatever JDBC driver class you need
"URL contains database name as well ",
"username", "password")
}

benken_parasoft

I tried the below code but doesn't seem like the connection is established.

Can you be more specific? Are you seeing a SQLException or some other error?

Sindhu

I do not see any error in console, but I am trying to print some message in the console after the DB connection and that message is not printing in console. So, I doubt if connection is established.

import com.parasoft.api.*
import java.sql.*

void sqlConnection() {
Application.showMessage(" Connection to DB") // This message is printing in the console.
Connection conn = SQLUtil.getConnection("com.microsoft.sqlserver.jdbc.SQLServerDriver", "URL contains database name as well ","username", "password")
ResultSet rs = query.executeQuery("select * from table_name")
Application.showMessage(" Connection Established") // This message is not printing in the console.
}

benken_parasoft

You need to monitor your virtual asset to check for any configuration or runtime errors.
See Gaining Visibility into Server Events.

Sindhu

Thank you very much!! I did check the events as well and do not see any errors there as well. For now, I am sending some other hardcoded response and trying to check the DB connection in the extension tool for which I do not see any errors in console or event logs.

Sindhu

Thank you so much for the inputs @benken_parasoft . I was able to resolve the issue and now the connection is established.