How to configure external DB connections from Designer Studio
This article is about configuring the external DB connections (using JDBC URL) from Designer Studio, without making any changes in the Application Server.
Prerequisites
- Ensure the connectivity between Pega App Server and the external DB is allowed. Verify the connectivity from the App Server(Example: telnet <hostname/IP> <port>)
- If it is a licensed version DB, then get the corresponding DB driver from the vendor or download the driver, if it is a freeware.
- Application needs to be restarted while performing the configuration. So plan a downtime.
Configuration
1. Create a new DB connection from Records > SysAdmin > Database. In connection method (How to connect option), you can use “configuration in preferences”, if the corresponding data source is already defined in the Application Server (Example: context.xml in tomcat). In such cases, we do not define any such data sources in the Application Server, hence using the JDBC URL option as shown below.
2. Perform a Test Connectivity. We are trying to connect to Postgres DB server over port 5432. The corresponding DB driver is not installed on this PRPC server and therefore, we see the below error.
3. Import the driver file from the Import Wizard
4. The connectivity still fails with the same error message.
5. Though the driver file is imported into DB, it is not specified in the default class path or in the list of default DB drivers. So, create a DSS as given below. The value “org.Postgresql.Driver” is the corresponding class name of postgres DB driver. This needs to be configured based on the DB type we are connecting to.
5. Now, perform the application restart and try testing the connectivity again. This time PRPC is able to connect to the external DB successfully.
Connecting to multiple and different DB’s
We may want to connect to the external DB’s of different type (oracle, MySQL, MS SQL, etc.). In this case, we already have an external DB connection for a postgres DB and going to configure one more DB connection for MySQL DB.
1. Repeat the steps 1 to 4 with the connection parameters of MySQL DB.
2. Modify the existing DSS by adding the class name (semicolon separated) of new DB type which is MySQL.
3. Perform the application restart and test the connectivity.
Pegasystems Inc.
IN
Hi ,
We have to include following string in prconfig.xml in the application server after importing sqljdbc jar file
<env name="database/drivers" value="com.microsoft.sqlserver.jdbc.SQLServerDriver;oracle.jdbc.OracleDriver" />
Once above changes made , we have to restart the application server.
Pegasystems Inc.
US
Hi Manju,
There is no need to update prconfig.xml file to add the driver name. The same value needs to be added in DDS as given below.
DSS parameter-prconfig/database/drivers/default
value=com.microsoft.sqlserver.jdbc.SQLServerDriver;oracle.jdbc.OracleDriver
Pegasystems Inc.
JP
I have done it by following steps for the "use jdbc connection pool" case:
1) Define data source like below in $(tomcat_home)/conf/context.xml
<Resource
name="jdbc/ExternalMSSQL"
auth="Container"
type="javax.sql.DataSource"
maxTotal="100"
maxIdle="30"
maxWaitMillis="10000"
validationQuery="SELECT 3"
validationQueryTimeout="10"
driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
url="jdbc:sqlserver://localhost:1433;database=GoldenMaster;SelectMethod=cursor;SendStringParametersasUnicode=false"
username="sa"
password="Admin9999"/>
2) save proper jdbc driver into $(tomcat_home)/lib/
mssql-jdbc-6.2.2.jre8.jar
3) Declare jdbc driver class in prconfig.xml
<env name="database/drivers" value="com.microsoft.jdbc.sqlserver.SQLServerDriver"/>
4) Define external DB instance:
Toyota System
JP
Hi Billy,
Did you know this error ?
I declare jdbc driver class in DSS：Pega-Engine • prconfig/database/drivers/default
Value : com.microsoft.sqlserver.jdbc.SQLServerDriver
Thanks 
Updated: 24 Jan 2022 5:46 EST
Pegasystems Inc.
JP
@LANH（ラン）san could you share the screen shot of the contents of your tomcat/lib folder [and WEB-INF/Lib]. It could be possible that there are conflicting jars. The error looks like referring to tomcat-dbcp.jar shouldnt we be using tomcat-jdbc.jar instead?
Maybe this article provide some insight in the issue
https://collaborate.pega.com/question/commons-dbcp-vs-tomcat-jdbc-pool
Best regards,
Tarun Sipani
Updated: 6 Apr 2022 14:08 EDT
Saltech Consulting
GB
A similar issue was resolved by adding
factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
to the resource in the context.xml for tomcat.
for example:
<Resource name="jdbc/TestDB" auth="Container" type="javax.sql.DataSource" factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" driverClassName="oracle.jdbc.OracleDriver" url="jdbc:oracle:thin:@XXXXXX" username="user" password="password" maxTotal="10" maxIdle="5" maxWaitMillis="5000" />