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

1 Like
9

Hey @dkayiwa @ibacher @pwargulak @druchniewicz .

Am trying to do an ETL to loading patients from a certain db into specifically openmrs patient Table basing on this module ETL,

i was able to fix all the configurations and finished the Load velocity function, but a its still bit my understanding why am not able to see my data into openmrs Patient table , Perhaps patient details arent lfetched collectively,

Here is my velocity Load function which saves patient in openmrs #set($patientClass = "org.openmrs.Patient")#set($personNameClass = "org.openmr - Pastebin.com. What could be hindering it. Thanks

10

I figured this out. :yum:

11

It helps to share the solution. :slight_smile:

1 Like
12

So basically, i needed to update my Transform and Load velocity function with the axact parameters from the table,

==> There was an error with Phone number format , it wasnt well formatted, so i have to have somthing like,

#if ( $row.Phone))
#set($code = "+256")
#set($result = $code + $row.Phone)
$out.put("Phone", $result);
#end

Here, am able to get phone number with + 256 extension which is how its required.

Then, i also had to deal with First and Last Name in this format,

#set($patientName.givenName = $row.firstName)

#set($patientName.firstName = $row.firstName)

Initially my velocity function was using userName instead of first and lastName which was not working as per our database structure.

Also had to add patientIdentifierUuid AND correct the PatientService configurations which was missing.

1 Like