CODEWITHSUNDEEP
javasql-serverseleniumgroovyworkfusion
user2335580
user2335580

Reputation: 408

Not able to connect SQL server in Groovy

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.

Upvotes: 2

Views: 493

Answers (1)

Andrey
Andrey

Reputation: 6786

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>

Upvotes: 1

Related Questions