Connecting to MS SQL server

We have a problem connecting to Microsoft SQL Server.
Right now we try to write working code in WF Eclipse.

Tried two different approaches:
1. Using WebHarvest tag

	<var-def name="result">                  
		<datastore name="GlobalStaffHistoryAll">
			select * from @this;
		</datastore>
	</var-def>

Such usage generates error:
Table “ds_GlobalStaffHistoryAll” not found; SQL statement

in our case GlobalStaffHistoryAll is a name of view.
But even using wrong credentials error still the same.

2. Using groovy script

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

this.getClass().classLoader.addURL(new java.io.File("./libs/sqljdbc42.jar").toURL());
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
driver = Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver").newInstance();
DriverManager.registerDriver(driver);

String url = "jdbc:sqlserver://evbyminsd7547";
Connection conn = DriverManager.getConnection(url, "username", "password");

As a result we recieve following error: No suitable driver found for jdbc:sqlserver://evbyminsd7547

When we tried to check list of drivers that DriverManager is carrying about - there was no our driver SQLServerDriver

Are there any solutions how to connect to MSSQL DB and request view results from there?

  1. datastore plug-in is custom WF plug-in and it uses DataStore functionality from WF (https://kb.workfusion.com/display/WF/Data+Stores). It supports PostgreSQL DB only.
    Probably, your intention was to use standard database plug-in (http://web-harvest.sourceforge.net/manual.php#database)

  2. According to search on SOF could you try to specify full connection name (e.g. http://stackoverflow.com/questions/5616898/java-sql-sqlexception-no-suitable-driver-found-for-jdbcmicrosoftsqlserver)
    jdbc:sqlserver://server:port;DatabaseName=dbname
    As well I don’t think that MS-SQL driver is present in default delivery. Probably you can use it using URL class loader and such jar somewhere in public accessed place, e.g. example for Beanshell
    addClassPath( new URL(“https://public-bucket.s3.amazonaws.com/jdbc-drivers/mssql-xxx.jar”) );
    Or ask administrators to put it somewhere on the server, so it can be used from local file system.

I am working with Maksym on this issue. The mentioned method addClassPath does not work. The machine config in Eclipse throws:

java.lang.ClassNotFoundException: com.microsoft.sqlserver.jdbc.SQLServerDriver

I do not think it is a problem with loading jars dynamically or statically. I think it is a problem with JDBC driver correct initialization and special class loading when creating a Connection.

Maybe somebody has a working example with creating JDBC connections?

I think it is caused by different classloaders in Groovy and Beanshell. So to use JDBC in groovy scripts you need to load library in one of them, e.g.

 <script language="groovy"><![CDATA[
    try {
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
    } catch (ClassNotFoundException expectedOnce) {
	    this.class.classLoader.addURL(new URL("https://public-bucket.s3.amazonaws.com/jdbc-drivers/mssql-xxx.jar"));
    }
  ]]></script>

And after this usual JDBC boilerplate can be used (init/close resources), e.g.

Class.forName(“com.microsoft.sqlserver.jdbc.SQLServerDriver”);
conn = DriverManager.getConnection(DB_URL,USER,PASS);
stmt = conn.createStatement();

Or out-of-the-box<database> plugin:
http://web-harvest.sourceforge.net/manual.php#database

Alternatively the driver jar can be placed to tomcat’s $CATALINA_HOME/lib on the APP server.

Hi There,

I have tried implementing the above in WF eclipse and I’m still getting a java.lang.ClassNotFoundException: com.microsoft.sqlserver.jdbc.SQLServerDriver error.

I have even added the jar file to the java build path with no luck. Is there possibly something I am doing wrong?

Hi,
any idea what could be the cause of below error or where i need to copy the JAR file.

Caused by: java.lang.ClassNotFoundException: com.microsoft.sqlserver.jdbc.SQLServerDriver cannot be found by com.workfusion.groovy-all_2.5.2

import groovy.sql.Sql
import java.sql.*

@CustomScriptAction(

)

def customScript(){

def url = ‘jdbc:sqlserver://XXLIXXVMQADB03:1433:X360’
def user = ‘qaxser’
def password = ‘qaxser$’
def driver = ‘com.microsoft.sqlserver.jdbc.SQLServerDriver’
def sql = Sql.newInstance(url, user, password, driver)
}

Do not think this solution works???

Step name ‘DB_Connect’ has failed. Reason: ’ Config line 6: script block java.lang.NullPointerException: Cannot invoke method addURL() on null object’

@ManojPuri have you added the jar to the lib folder in tomcat?

@ashapkina this is a local installation on Windows 10, I have RPA installed @ location C:\RPAExpress. Where can I find $CATALINA_HOME/lib in the installation folder??
Best–

Manoj