Unable to connect to SQLServer through Jython

jason84

Hi,

I am trying to connect SQLServer using extension tool (Jython).
I got an error:
Error during script execution. View Details for more information.
com.microsoft.sqlserver.jdbc.SQLServerDriver cannot be found by
com.parasoft.xtest.libs.base_9.7.7.20180207

I added:

• sqljdbc42.jar in Parasoft/ Preferences/ JDBC driver and
• also tried to copy sqljdbc42.jar to folder com.parasoft.xtest.libs.base_9.7.7.20180207 in C:\Program Files\Parasoft\Test\9.10 for SOAtest & Virtualize\plugins\com.parasoft.xtest.libs.base_9.7.7.20180207

Anyone faced this error? Pls help me solve it. Tks

This is my code:

from java.sql import DriverManager
from java.lang import Class
from soaptest.api import *
from com.parasoft.api import *

def getValues(input, context):
connection = getConnection()
statement = connection.createStatement()
QUERY = "......."
resultSet = statement.executeQuery(QUERY)
while resultSet.next():
resultSet.getInt("id")
def getConnection():
driverName = "........"
Class.forName(driverName)
DB_URL = "......."
DB_USER = "......."
DB_PASSWD = "........"

benken_parasoft

I am trying to connect SQLServer using extension tool (Jython)

Why not use DB Tool instead? No scripting required!

connection = getConnection()

Make sure you use this method which knows how to load classes from the Parasoft JDBC preferences:

com.parasoft.api.SQLUtil.getConnection(String driverName, String url, String user, String password) throws SQLException

jason84

Hi Benken,
Thank you for your response.
I updated SQLUtil.getConnection on my script and I did not get that error anymore. But when I run a simple update command to database, it did not execute, update to the database. That command is running on DB tool.
Could you pls help me this issue? Tks

This is my code
from java.sql import DriverManager
from java.lang import Class
from soaptest.api import *
from com.parasoft.api import *

def getValues(input, context):
connection = getConnection()
statement = connection.createStatement()
QUERY = "update ......'"
resultSet = statement.executeUpdate(QUERY)

def getConnection():
driverName = "....."
DB_URL = "....."
DB_USER = "....."
DB_PASSWD = "......"
SQLUtil.getConnection(driverName, DB_URL, DB_USER, DB_PASSWD)

benken_parasoft

But when I run a simple update command to database, it did not execute, update to the database.

Can you be more specific about "it did not execute"? Are you saying your script throw an Exception on "statement.executeUpdate(QUERY)" or something else?

That command is running on DB tool

I'm not sure I know what you mean by this. Are you referring to SOAtest's "DB Tool" or something else? As I mentioned earlier, I really recommend you using SOAtest's DB Tool instead of SOAtest's Extension Tool. There is little value in running a SQL query like this from a script.

jason84

Hi,

I mean, when I used DB tool and updated data into database, I used QUERY and it was successfully updated. Now I would like to check the same query by using extensions tool (Jython). So I run the code, I got "success" in console window. But when I checked in database, the data was not updated. I wonder did I miss something else.

benken_parasoft

resultSet = statement.executeUpdate(QUERY)

java.sql.Statement.executeUpdate(String) returns an "int", not a result set. So, I would recommend confirming what value is being returned. If your SQL statement updated rows then I'd expect the return value to be the number of rows that were updated. Please also confirm that your script is closing objects. At the end of your script you need to call statement.close() followed by connection.close().

jason84

I changed the code: resultSet = statement.executeUpdate(QUERY)
=> statement.executeUpdate(QUERY)
and added connection.close() at the end of the "def getValues(input, context):"
I run this code => I still got "success" in console window. But, nothing is changed in the database

benken_parasoft

I still got "success" in console window. But, nothing is changed in the database

statement.executeUpdate() returns an integer that indicates how many rows were updated. What is the value of that integer?

jason84

I got message: Extension Tool - success. Test succeeded. I did not get any number.
I see I should get an integer

benken_parasoft

did not get any number. I see I should get an integer

I'm saying you would need change your script to capture the return value and check, like print it out (or something). You can use Application.showMessage(String) to print messages to SOAtest's Console view. For Example:

rowCount = statement.executeUpdate(QUERY)
Application.showMessage(str(rowCount))

jason84

Hi Benken,

I can connect to DB now. Thank you very much