How to connect to external SQL server through Workfusion?

Hello Team,

I have a requirement to connect to an SQL server in my bot task. For that, I have to use sqljdbc42.jar to fulfill my requirement. I have put the Jar in s3 server and follow the KB to load the class at run time and try to register com.microsoft.sqlserver.jdbc.SQLServerDriver Driver, but it throws exception ‘no such driver is found to register’. Below is the code for your reference.


				URLClassLoader ucl = new URLClassLoader (new URL("http://localhost:15110/mlrpa/sqljdbc42.jar?X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=admin%2F20190528%2Fus-east-1%2Fs3%2Faws4_request&X-Amz-Date=20190528T135617Z&X-Amz-Expires=604800&X-Amz-SignedHeaders=host&X-Amz-Signature=ed936feab35eb0fd054dd8e55a93e79888554382ffe0e42ddf900009532cf2aa"));

				//System.out.println ("Attempting...");
				String db_username = workfusion_bot_task_service_registry.get(SecretsVault.class).findEntry('sqlserver_db_credentials_amruth').map({ it.getKey() }).map({ RString.of(it) }).orElseThrow({ new SecretsVaultException("Secret Entry with alias 'wfdbuser' not found") })
				String db_password = workfusion_bot_task_service_registry.get(SecretsVault.class).findEntry('sqlserver_db_credentials_amruth').map({ it.getValue() }).map({ RString.of(it) }).orElseThrow({ new SecretsVaultException("Secret Entry with alias 'wfdbuser' not found") })
				//minio_accessKey
				String url = sqlserver_db_url.toString();
				String user = db_username;
				String password = db_password;
				//Driver d = (Driver)Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver", true, cl).newInstance();
		String classname = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
		DriverManager.getConnection(sqlserver_db_url, user, password);

I have find one solution as below but in workfusion as it is using both java and groovy language.

  1. Groovy Cannot implement method from java interface with varargs and generics but for the reason it throws exception : Groovy:Can’t have an abstract method in a non-abstract class. The class ‘DriverShim’ must be declared abstract or the
    method ‘java.util.logging.Logger getParentLogger()’ must be implemented.

Below is the my approach to load the sqlserver driver by implementing driver interface but it is not working in workfusion. could you please look into the issue and let me know if you have any solution to connect wxternal sysqlserver db .

If you’re going to do any sort of database activity in Java, you’ll probably be using JDBC. Like ODBC before it, JDBC is a great way to ensure that your program is free of any ties to the underlying database. Traditionally, the mechanism is that you put the JDBC driver somewhere in the classpath and then use class.forName() to find and load the driver.

One problem with this is that it presumes that your driver is in the classpath. This means either packaging the driver in your jar, or having to stick the driver somewhere (probably unpacking it too), or modifying your classpath.

“But why not use something like URLClassLoader and the overload of class.forName() that lets you specify the ClassLoader ?” Because the DriverManager will refuse to use a driver not loaded by the system ClassLoader . Ouch!

The workaround for this is to create a shim class that implements java.sql.Driver . This shim class will do nothing but call the methods of an instance of a JDBC driver that we loaded dynamically. Something like this:

import java.sql.*; 
public class DriverShim implements Driver {
	private Driver driver;
	DriverShim(Driver d) {
		this.driver = d;
	}
	public boolean acceptsURL(String u) throws SQLException {
		return this.driver.acceptsURL(u);
	}
	public Connection connect(String u, Properties p) throws SQLException {
		return this.driver.connect(u, p);
	}
	public int getMajorVersion() {
		return this.driver.getMajorVersion();
	}
	public int getMinorVersion() {
		return this.driver.getMinorVersion();
	}
	public DriverPropertyInfo[] getPropertyInfo(String u, Properties p) throws SQLException {
		return this.driver.getPropertyInfo(u, p);
	}
	public boolean jdbcCompliant() {
		return this.driver.jdbcCompliant();
	}
}


        System.out.println(this.getClass().getClassLoader().getClass() + " is the class loader");
        System.out.println("Before loading the drivers in driver manager are ");
        listDrivers();
        try {
            Method method = URLClassLoader.class.getDeclaredMethod("addURL", URL.class);
            method.setAccessible(true);
            method.invoke(loader, new File(jar).toURI().toURL());

            Class<?> classToLoad = Class.forName(driverName, true, loader);
            Driver driver = (Driver) classToLoad.newInstance();
            DriverManager.registerDriver(new DriverShim(driver));
            Connection connection = DriverManager.getConnection(dbUrl, userName, password);
            System.out.println("is Connection null " + (connection == null) + ". After loading the drivers in driver manager are ");
            //Do business specific action
            listDrivers();
            DriverManager.deregisterDriver(driver);
        } catch (NoSuchMethodException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (MalformedURLException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }

Thanks in advance.
Diptiranjan panda

Could you please look into the issue

Hi @diptiranjanpanda please check this topic on connecting to SQL database

https://forum-2-new.workfusion.com/t/connecting-to-ms-sql-server/631/2

HEllo No same issue is populating :slight_smile:

<?xml version="1.0" encoding="UTF-8"?>
<export include-original-data="true"></export>
when i am add the jar it is success. below is the screenshot.

when i am removed the jar it throws : No suitable driver found for jdbc:sqlserver:

@diptiranjanpanda have you added the driver jar as advised here?
https://forum-2-new.workfusion.com/t/connecting-to-ms-sql-server/631/5

@ashapkina Hello, is there a way to exit in case there is no data returned from SQl plugin?

Best–

Manoj