Change JDBC Driver from jtds driver to the MS SQL jdbc driver

Document ID : KB000123127
Last Modified Date : 19/12/2018
Show Technical Document Details
Introduction:
Process Automation has been using a 3rd party JDBC Driver for the SQL Server database.  Starting in 4.3 sp3 we are now including the option to use a driver provided by Microsoft during the install or upgrade.

Can we manually make this change from the JTDS Driver to the MS SQL Driver without rerunning the installer?
Instructions:
To manually update Process Automation 4.3 Sp3 to use the MS SQL JDBC Driver:

First verify that the MSSQL JDBC Driver, "mssql-jdbc-6.1.0.jre7.jar" is located in the following 2 directories on the cluster node:

\CA\PAM\server\c2o\ext-lib\
\CA\PAM\server\c2o\.c2orepository\.c2oserverresources\lib\

If they do not exist, locate and copy mssql-jdbc-6.1.0.jre7.jar from the original Node. 


Once the jar is in place open the secondary Node's \pam\server\c2o\.config\OasisConfig.properties file in a text editor. 
There are 11 lines that reference the JTDS driver and will need to be corrected.   Please update the OasisConfig to comment out the incorrect values and replace with MS SQL JDBC Driver details.   

Line numbers shown below are to be used as a guide, the specific line numbers will vary slightly in each OasisConfig.properties file.
If your MS SQL Database uses a Port other than 1433 please ensure that is correct before saving the changes.



    Line 24:
#oasis.database.connectionurl=jdbc:jtds:sqlserver://{MSSQL_Database_Host}:1433;databaseName=
oasis.database.connectionurl=jdbc:sqlserver://{MSSQL_Database_Host}:1433;databaseName=

    Line 32:
#oasis.database.driver=net.sourceforge.jtds.jdbc.Driver
oasis.database.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver

    Line 42:
#oasis.database.datasource.class=net.sourceforge.jtds.jdbcx.JtdsDataSource
oasis.database.datasource.class=com.microsoft.sqlserver.jdbc.SQLServerXADataSource

    Line 46:
#oasis.database.additionalparamurl=;responseBuffering=full;SelectMethod=cursor;socketTimeout=0
oasis.database.additionalparamurl=;responseBuffering=full;SelectMethod=cursor;MultiSubnetFailover=True;

    Line 64:
#oasis.reporting.database.driver=net.sourceforge.jtds.jdbc.Driver
oasis.reporting.database.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver

    Line 72:
#oasis.reporting.database.connectionurl=jdbc:jtds:sqlserver://{MSSQL_Database_Host}:1433;databaseName=
oasis.reporting.database.connectionurl=jdbc:sqlserver://{MSSQL_Database_Host}:1433;databaseName=

    Line 78:
#oasis.reporting.database.additionalparamurl=;responseBuffering=full;SelectMethod=cursor;socketTimeout=0
oasis.reporting.database.additionalparamurl=;responseBuffering=full;SelectMethod=cursor;MultiSubnetFailover=True;

    Line 96:
#oasis.runtime.database.driver=net.sourceforge.jtds.jdbc.Driver
oasis.runtime.database.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver

    Line 106:
#oasis.runtime.database.datasource.class=net.sourceforge.jtds.jdbcx.JtdsDataSource
oasis.runtime.database.datasource.class=com.microsoft.sqlserver.jdbc.SQLServerXADataSource

    Line 110:
#oasis.runtime.database.connectionurl=jdbc:jtds:sqlserver://{MSSQL_Database_Host}:1433;databaseName=
oasis.runtime.database.connectionurl=jdbc:sqlserver://{MSSQL_Database_Host}:1433;databaseName=

    Line 114:
#oasis.runtime.database.additionalparamurl=;responseBuffering=full;SelectMethod=cursor;socketTimeout=0
oasis.runtime.database.additionalparamurl=;responseBuffering=full;SelectMethod=cursor;MultiSubnetFailover=True;


Update ACTIVEMQ message service with new driver information:


Copy the mssql-jdbc-6.1.0.jre7.jar to "PAM\\activemq\lib" folder

Modify the "PAM\activemq\bin\pamactivemq.bat" file to update the CLASSPATH to point at the MSSQL JDBC Driver:

set CLASSPATH="%~dp0..\lib\activemqwrapper-snapshot.jar";"%~dp0..\..\server\c2o\ext-lib\jna-3.4.0.jar";"%~dp0..\lib\optional\commons-dbcp2-2.1.1.jar";"%~dp0..\lib\optional\commons-pool2-2.4.2.jar";"%~dp0..\lib\mssql-jdbc-6.1.0.jre7.jar";"...