Not able to connect SQL server in Groovy

469 views Asked by At

I am trying to connect to a SQL server using an automation tool (Workfusion Studio), which uses selenium and groovy.I am getting an error "No suitable driver found for jdbc:sqlserver:/XXXXXXXXXXXXXX" when I try to create a connection.

The code I am using is below:

<?xml version="1.0" encoding="UTF-8"?>
<config xmlns="http://web-harvest.sourceforge.net/schema/1.0/config" scriptlang="groovy">
<selenium-flow>
<selenium name="seleniumDriver" browser="chrome" close-on-completion="true" start-in-private="true">
<script><![CDATA[
import java.sql.*;
this.class.classLoader.addURL(new URL("http://clojars.org/repo/com/microsoft/sqlserver/sqljdbc4/4.0/sqljdbc4-4.0.jar"));
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
String dbURL = "jdbc:sqlserver://SERVER_NAME:1433;databaseName =DATABASE_NAME;";
String userName = "USER_NAME";
String password = "PASSWORD";
Connection con = DriverManager.getConnection(dbURL, userName, password);
]]></script>
</selenium> 
</selenium-flow>
</config>

Please help resolve the issue.

1

There are 1 answers

0
Andrey On

The JDBC connection is performed through global ClassLoader, so it does not see libs added to local ClassLoader.

You can add the driver jar globally to Control Tower tomcat:

$INSTALL_DIR/apps/webapps/tomcat/lib

For the logic to work in WorkFusion Studio, refer to the Eclipse guides on how to add external jar.

As a workaround (not recommended for production code), the following trick can be performed:

<script><![CDATA[
    try {
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
    } catch (ClassNotFoundException expected) {
        groovy.lang.GroovyShell.class.classLoader.addURL(new URL("http://clojars.org/repo/com/microsoft/sqlserver/sqljdbc4/4.0/sqljdbc4-4.0.jar"));
    }
]]></script>

More efficient way to execute queries is as following (will properly close the DB connection, etc.):

<database connection="jdbc:sqlserver://hostname:6501;DatabaseName=database"
          jdbcclass="com.microsoft.sqlserver.jdbc.SQLServerDriver"
          username="user" password="securepassword">

    select first_name from actor
</database>