Submit and vote on feature ideas.

Welcome to the new Parasoft forums! We hope you will enjoy the site and try out some of the new features, like sharing an idea you may have for one of our products or following a category.

Unable to connect to SQLServer through Jython

jason84
jason84 Posts: 6

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 = "........"

Best Answer

Answers

  • jason84
    jason84 Posts: 6

    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
    benken_parasoft Posts: 1,228 ✭✭✭
    edited February 2020

    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
    jason84 Posts: 6

    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
    benken_parasoft Posts: 1,228 ✭✭✭

    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
    jason84 Posts: 6

    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
    benken_parasoft Posts: 1,228 ✭✭✭

    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
    jason84 Posts: 6
    edited February 2020

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

  • benken_parasoft
    benken_parasoft Posts: 1,228 ✭✭✭

    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
    jason84 Posts: 6

    Hi Benken,

    I can connect to DB now. Thank you very much :)