No suitable Driver for Microsoft SQL server In ETL Lite(Resolved)

Development
,
1

Hello @ibacher @dkayiwa @druchniewicz @pwargulak . Am trying to come up a setup using ETL for openmrs/cfl instance but using this module.When i try to test extraction of data on openmrs using mysql server url as jdbc:mysql://localhost:3306/openmrs. i get a succes extraction. However when i try to use Microsoft SQL server. using this jdbc url jdbc:sqlserver://localhost:1433/ICEA";. i get a connection failure.

This error is about connectivity on Microsoft SQL server, the error indicates that they are no suitable drivers, however, drivers exist on this line. To me it as if it allows one single parameter url , when you create an instance of second url as a parameter to pass a second jdbc url, then it throws connectivity issues on Mssql.

Looking forward to hear from you.

2

Hi @sharif per our talk on slack, please try to downgrade Microsoft SQL driver and see if that helps. We’ve bundled a 7.4 version, and that might not support very old MS SQL Servers, see: Support matrix - JDBC Driver for SQL Server | Microsoft Learn

1 Like
3

Thanks @pwargulak .Am testing it after downgrading it to 6.2.2.jre8.

4

Hello @pwargulak . Am encountering the same error.

5

Hi @sharif The driver jar added as module dependency doesn’t seem to work. I see two options:

  • you can add driver as dependency to openmrs-core/pom.xml and rebuild it and use your custom WAR.
  • add the driver jar to TOMCAT_HOME/lib directory.
1 Like
6

The third option is to explicitly add a call like:

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

At the earliest point you can detect you’ll need a SQL server driver in the module (you could, for example, do this in the started() method of the module activator). The automatic detection of JDBC drivers doesn’t really work with OpenMRS modules.

Technical explanation:

The Java DriverManager uses the ServiceLoader class to find any registered JDBC drivers the first time a call to getConnection(), getDriver(), or getDrivers() happens. The ServiceLoader scans the current classpath for any JDBC drivers and loads them, at which point, they register themselves with the DriverManager. Once this is done, the ServiceLoader stores the list of classes loaded and will consistently return the same results.

In OpenMRS, we call getConnection() relatively early on in the startup process. At the point where that runs, however, no modules are loaded or available on the classpath, so any JDBC drivers packaged into modules will not be found by the ServiceLoader.

2 Likes
7

Thanks @ibacher for the detailed explanation.

8

Was able to fix this. By doing a docker cp /home/user/OpenMRS/Workspace/openmrs-module-etllite/omod/target/etllite-1.1.11-SNAPSHOT/lib/mssql-jdbc-7.4.1.jre8.jar containerId:/usr/local/tomcat/lib/mssql-jdbc-7.4.1.jre8.jar.

Thanks @ibacher @pwargulak