Question
Last activity: 28 Jan 2016 11:50 EST
Connecting to external SQL database
Hello,
We are connecting to an external SQL database and seeing weird behavior with the SQL driver.
PRPC is running on oracle database and so for connecting to SQL additional jar file was added. We are using Pega cloud servers.
After few hours of non usage of this external connection somehow the test connection is failing. Usually observed after overnight:
Testing Database connection gives failure.
Then I go and check in SMA for the java class name “com.microsoft.sqlserver.jdbc.SQLServerDriver”, expecting that the class should not be there as the test connection is not working.
Here it finds the class:
And then I again try to test connection to make sure I still see the Test Connection error. But, this time it gets connected successfully.
Hello,
We are connecting to an external SQL database and seeing weird behavior with the SQL driver.
PRPC is running on oracle database and so for connecting to SQL additional jar file was added. We are using Pega cloud servers.
After few hours of non usage of this external connection somehow the test connection is failing. Usually observed after overnight:
Testing Database connection gives failure.
Then I go and check in SMA for the java class name “com.microsoft.sqlserver.jdbc.SQLServerDriver”, expecting that the class should not be there as the test connection is not working.
Here it finds the class:
And then I again try to test connection to make sure I still see the Test Connection error. But, this time it gets connected successfully.
Not sure how just searching for the class name fixes it up.
Has anyone seen this issue? Any suggestions about what may be causing this
We are on 7.1.8
Accepted Solution
Pegasystems Inc.
US
the easier approach is to add the jar to the endorsed directory. SMA lookup and proconfig approach suggested above simply calls for Class.forName and register the driver class. SMA is not feasible as it is manual; prconfig approach also requires manual handling of the the file, not ideal for large clusters (unless you use DSS).
I think the SMA lookup (it is not a search) loaded up the SQL class/jar into the JVM Runtime and thus you get successful test result. This will work fine when you use the DB name record and is only an issue during test connectivity.
Hi Amit,
You need to load the JAR via prconfig.xml:
Edit prconfig.xml to include the mssql driver class and then restart tomcat.
<env name="database/drivers" value="com.microsoft.sqlserver.jdbc.SQLServerDriver" />
Accepted Solution
Pegasystems Inc.
US
the easier approach is to add the jar to the endorsed directory. SMA lookup and proconfig approach suggested above simply calls for Class.forName and register the driver class. SMA is not feasible as it is manual; prconfig approach also requires manual handling of the the file, not ideal for large clusters (unless you use DSS).
Pegasystems Inc.
US
Default location <tomcat home>/endorsed (need to be created) or you can use -Djava.endorsed.dirs. To be clear, we should use datasource as much as possible (i.e., using app server connection pool implementation). In that case, we do not need to pre-load the driver classes.
Pegasystems Inc.
US
Hi Kevin, Richard,
I was challenged with similar problem but having the endorsed folder in tomcat lib didnot help me. I have to add the environment variable for mysql in prconfig.xml. I have mysql jdbc jar in tomcathome/lib. Was just wondering if there is anyother way instead of editing prconfig.xml.
<env name="database/drivers" value="com.mysql.jdbc.Driver"/>
thanks,
Dhananjay
Hi Dhananjay,
You could add a Dynamic System Setting rather than editing prconfig.xml.
Owning RuleSet: Pega-Engine
Puprose: prconfig/database/drivers/default
Value: com.mysql.jdbc.Driver
Restart your app server and test the connection.
Pegasystems Inc.
US
How do I load more than one external driver, say mysql and oracle. The purpose key looks to limit this ‘prconfig/database/drivers/default’ . Would there be any negative impact on my original default driver[Postgres was used to install Pega7]
Dhananjay Ubale | Certified Lead System Architect | Pegasystems Inc.
(617) 510-7266 | [email protected]<mailto:[email protected]> | www.pega.com<http://www.pega.com/>
The driver used to connect to the Pega database is specified in your JDBC connection, so there wouldn't be any negative impact. I believe you can list multiple drivers in the same DSS by separating them with a semi colon, but I have not tried this before. For example:
Owning RuleSet: Pega-Engine
Puprose: prconfig/database/drivers/default
Value: com.mysql.jdbc.Driver;oracle.jdbc.OracleDriver