Change JDBC Driver from jtds driver to the MS SQL jdbc driver
Article ID: 123127
Updated On: 10-18-2023
Products
CA Process Automation Base
Issue/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?
Can we manually make this change from the JTDS Driver to the MS SQL Driver without rerunning the installer?
Environment
Release: ITPASA99000-4.3-Process Automation-Add On License for-CA Server Automation
Component:
Component:
Resolution
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";"...
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";"...
Feedback
Yes
No
Powered by
