Example for Accessing External MySQL Database from Pega 7 PRPC

This post shows how data can be accessed in Pega 7 from an external MySQL database using the Connector and Metadata Accelerator. This wizard will generate the necessary activity rules for accessing the external MySQL DB table and for mapping input parameters and query results.

  • For this example, the MySQL Community Server 5.7.12 for Windows x64 was used.
  • The MySQL DB Server can be downloaded from the MySQL Community Server website.
  • In addition, the MySQL Workbench, a MySQL development and administration tool was used.
  • It can be downloaded for free from the MySQL Workbench website.

Summary

  1. Pega 7 Setup Used in this Example
  2. MySQL DB Server Configuration and Table Used in this Example
  3. Configuration of Connection to MySQL Server in Pega 7
  4. Creating a new Database Instance in Pega 7 for MySQL JDBC Access
  5. Running the Pega 7 Connector and Metadata Accelerator
  6. Browse the External MySQL Table with an Activity Rule

Related Posts

  • Pega 7.1.6 was used as a Personal Virtual Server (PVS) installation with the VMWare Player as per the Downloading the Exercise System section of the SAE I (7.1) and SSA (7.1) courses provided by Pega Academy.
  • 64-bit Windows 7 was used as the host operating system (running the VMWare Player).
  • The virtual machine’s network adapter was configured to use NAT (used to share the host’s IP address).

VMWarePlayer Virtual Machine Network Settings NAT Share Host IP Address

The MySQL Server is installed on the Windows host operating system. The Pega 7 platform is running on the virtual machine. It is therefore necessary to make sure that the two systems can communicate with each other over the network. The host’s IP address can be looked up by running ipconfig from the Windows command line. In this case the host’s address is 192.168.0.3.

After starting the virtual machine, the command line window of the VMWare Player can be used to lookup the IP address of the virtual machine and to ping the Windows host machine to confirm network connection. Execute:

  • ifconfig – In this case, the virtual machine has the address 192.168.161.129.
  • ping 192.168.0.3 to confirm that the Pega 7 platform can reach the Windows host system on which the MySQL server will run.

VMWare command line ifconfig and ping to MySQL host machine

  • Also, confirm that the Windows host system can reach the virtual machine by executing ping 192.168.161.129 from a Windows command line.

Windows command line ping Pega 7 PRPC virtual machine

For this post, the MySQL Community Server is used to host a database that represents a product catalog. The database has one table called product_catalog and it has the following five columns:

idint(11)unsigned, primary key, not nullable, auto-increment
namevarchar(45)not nullable
category – enum('electronics', 'books', 'hardware')not nullable
unit_price – decimal(6, 2)not nullable
description – varchar(45)nullable

MySQL product_catalog table structure

  • The MySQL database server is installed to run as a Windows service. To access the server from the MySQL Workbench, the following connection parameters are used:
host name: localhost(MySQL Workbench is running on the same Windows host machine)
port: 3306(default MySQL server port)
username: rootnot nullable
password: *****root password was set during MySQL server installation
  • The MySQL Workbench can be used to create the table and to insert records. For this post, the following 9 rows were inserted into the product_catalog table with the MySQL Workbench.

MySQL product_catalog table data

  • In order to connect from Pega 7 PRPC to the MySQL server, it is highly recommended to create a dedicated user. The MySQL Workbench or the command line can be used for this task.
  • In the MySQL Workbench, click on Users and Privileges and then on the Add Account button.
  • Complete the form and specify the username and password.
  • Keep the default values for Authentication Type (Standard) and Limit to Hosts Matching (%).

MySQLWorkbench Add Account under Users and Privileges

  • Navigate to the Schema Privileges tab and click on the button New Entry. In the dialog window, select the correct schema from the dropdown.
  • Alternatively, All Schema (%) can be used to give the user access to all schemas. Click OK to close the Schema Privilege Definition dialog.

MySQLWorkbench Add Account New Schema Privilege Definition

  • The Schema Privileges tab should show the new entry and clicking on the entry allows to set specific Object Rights such as executing SELECT, INSERT and UPDATE.

MySQLWorkbench Add Account Schema Privileges Tab

  • For the example in this post, only SELECT is required. Click on the Apply button to save the changes.

As a prerequisite for running the Connector and Metadata Accelerator, a Database Instance needs to be created in PRPC. The Database instance will contain the JDBC connection information necessary for PRPC to establish a connection. This setup can be divided into 3 steps:

(a) Download the MySQL JDBC Driver

(b) Copy the MySQL JDBC Driver to PRPC System

  • The MySQL JDBC driver JAR file needs to be copied to the PRPC system. FTP can be used.
  • See FTP Connect to Pega 7 System for Private Virtual Server Installations for details.
  • Copy the mysql-connector-java-5.1.38-bin.jar file to the prweb/WEB-INF/lib folder.
  • Here (Pega 7.1.6 installed on VMWare as PDN exercise system), the full path of the folder is:
/usr/share/tomcat7/webapps/prweb/WEB-INF/lib
  • It may be required to change the access permissions of the WEB-INF/lib directory.
  • The VMWare console can be used to do that by executing the command: sudo chmod 777 lib

Use VMWarePlayer command line to run chmod on WEB-INF lib folder

  • Once the JDBC driver has been copied to the WEB-INF/lib directory, it should look like this:

FileZilla prweb WEB-INF lib Folder with MySQL JDBC Driver Present

(c) Configure PRPC with prconfig.xml

  • The prconfig.xml file needs to be modified to make the MySQL JDBC driver available to PRPC.
  • Use the VMWare console to access the file system of the PRPC instance and navigate to:
/usr/share/tomcat7/webapps/prweb/WEB-INF/classes
  • This folder contains the prconfig.xml file. Change the file permissions by executing the command:
sudo chmod 777 prconfig.xml
  • The file needs to be edited to add an entry to specify the MySQL JDBC driver class.
  • Open the file in the vi editor with vi prconfig.xml and add the line:
<env name="database/drivers" value="com.mysql.jdbc.Driver" />
  • …to specify the MySQL JDBC driver class.

Use VMWarePlayer to run vi to edit PRPC prconfig XML file to specify MySQL JDBC driver class

(d) Restart Pega PRPC

After modifying the prconfig.xml file, it is necessary to restart the Pega 7 PRPC application. This can be accomplished by:

Before running the Connector and Metadata Accelerator to configure access to an external MySQL table, it is necessary to create a new Database instance in Pega 7 PRPC that encapsulates the JDBC connection to the external MySQL DB server.

  • In the Designer Studio, navigate to Records > SysAdmin > Database.

Pega PRPC 7 DesignerStudio Navigation to Records SysAdmin Database

  • In the Instances of Database view, click on the +Create button to start the process of creating a new database instance.

Pega PRPC 7 DesignerStudio View Instances Of Database

  • This will open the Create Database form. Enter a description and a name for the database instance and click on the Create and open button.
  • This will save the new database instance and open it in edit mode for further configuration.

Pega PRPC 7 DesignerStudio Create Database

  • On the Database tab of the database instance, specify use JDBC URL listed below in the How To Connect dropdown.
  • Enter the correct MySQL JDBC URL in the JDBC URL text field. In this case, it is:
jdbc:mysql://192.168.0.3:3306/product_catalog
  • Where 192.168.0.3 is the IP address of the Windows host system on which the MySQL server is running and product_catalog is the name of the schema to access.
  • Specify the username and password of the MySQL user account that was created in step 2. In this case the username is prpc.

Pega PRPC 7 DesignerStudio Edit Database Database Tab

  • Click on the Test Connection button to validate the configuration. A new dialog should show a successful connection status message:

Pega PRPC 7 DesignerStudio Edit Database Test Database Connection Success

  • Note: In case of connection errors, refer to earlier sections on network configuration and MySQL server setup. Below are some possible error scenarios and how to approach fixing them.

ERROR: No Suitable Driver Found

  • Make sure that the MySQL JDBC driver file mysql-connector-java-5.1.38-bin.jar is copied to the /WEB-INF/lib folder on the PRPC system and that the prconfig.xml has been modified as described in section 3.

Pega PRPC 7 DesignerStudio Edit Database Test Database Connection No suitable driver found for jdbc:mysql

ERROR: Communications Link Failure

  • This error is caused most likely by a network connection problem. Make sure the IP address of the MySQL server host machine is correct and reachable from the Pega 7 PRPC host system.
  • In this case Pega 7 is running on a virtual machine using the VMWare player. See section 1 for verifying network connectivity between the virtual machine and the MySQL DB Server host.

Pega PRPC 7 DesignerStudio Edit Database Test Database Connection Communications Link Failure The last package sent successfully to the server was 0 milliseconds ago

ERROR: Host… not Allowed to Connect to MySQL Server

  • Refer to section 2 and make sure that the MySQL user account (here prpc) used by Pega 7 is configured properly.
  • Check if it has Limits to host matching set to % to allow all remote hosts to connect to the MySQL server instance. Note that, by default, the root user does not have remote access.

Pega PRPC 7 DesignerStudio Edit Database Test Database Connection Host is Not Allowed To Connect To this MySQL Server

  • After configuring the database instance and successfully testing the connection, save the record and reload the Instances of Database view.

Pega 7 PRPC Instances Of Database MySQL

  • The new external MySQL database instance should now appear in the list as shown in the screen shot above. Here, the database instance is named MySQLProductCatalog.

  • Navigate to Designer Studio > Integration > Connectors > Create Other Integration to open the Pega 7 Connector and Metadata Accelerator.

Pega 7 PRPC DesignerStudio Navigation Integration Connectors Create Other Integration

1. Choose Purpose

  • For connecting to an external database, the Purpose must be set to Generate Connector Rules and the Metadata Type must be set to SQL.
  • Select an appropriate rule set name, ideally an integration rule set, and the rule set version.
  • The Base Class should be a class derived from the Data- class.
  • The Connector Activity Class should be a class that is derived from the Work- class.
  • For more information refer to the Pega 7 Help entry titled About generating connector rules.

Pega 7 PRPC Connector And Metadata Accelerator Choose Purpose

  • Click on the Next button to continue.

2a. Process Metadata – Select Database Table

  • On the Select Database Table form, select the database instance that was created in section 4 and specify the Schema Name.
  • Here, the schema name is product_catalog (see section 2).
  • Once the schema name is entered, the form will show all tables in an auto-complete control.
  • Here, the Table Name is set to products (see section 2).

Pega 7 PRPC Connector And Metadata Accelerator Choose Purpose ProcessMetadata Select Database Table

  • Click on Next to continue.

2b. Process Metadata – Select Table Columns

  • Check-mark the required table columns, in this case all available columns are selected.

Pega 7 PRPC Connector And Metadata Accelerator Choose Purpose ProcessMetadata Select Table Columns

  • Click on Next to continue.

2c. Process Metadata – Select SQL Operations

  • Select the SQL operations that are needed for the specific application use case.
  • In this case, all operations are selected and for each one the wizard will create an activity rule.

Pega 7 PRPC Connector And Metadata Accelerator Choose Purpose ProcessMetadata Select SQL Operations

  • Click on Next to continue.

2d. Process Metadata – Select Browse Criteria

  • If Browse the database was checked in the previous form, the Select Browse Criteria form appears.
  • It is used to select specific columns which PRPC can use to query the external MySQL table.

Pega 7 PRPC Connector And Metadata Accelerator Choose Purpose ProcessMetadata Select Browse Criteria

  • Click on Next to continue.

2e. Process Metadata – Map Parameter Values

  • The Map Parameter Values form does not have to be changed for this example, it allows to specify the input parameters of the activity rules (browse, update etc.) that will be generated for each SQL operation. These input parameters can be changed easiliy later.

Pega 7 PRPC Connector And Metadata Accelerator Choose Purpose ProcessMetadata Map Parameter Values

  • Click on Next to continue.

3. Review and Save

  • The Review and Save form, lists all rules that will be generated and modified when the wizard runs.
  • Note that a data class will be created that derives from Data-, using the table name that was specified earlier.
  • In addition, all activity rules that will be created for the selected SQL operations are listed here.

Pega 7 PRPC Connector And Metadata Accelerator Review And Save

  • Click on Finish to complete the setup and to let the wizard generate the required rules.
  • A final confirmation screen will be shown once the wizard has completed generating the rules.

Pega 7 PRPC Connector And Metadata Accelerator Confirmation

  • Here, 13 rules were generated, including 4 activity rules for browse, insert, update and delete operations and 5 property rules for mapping the columns of the products table.
  • Click on Close to complete the setup process and to close the wizard.

  • The auto-generated Browseproducts activity can now be unit tested to ensure that PRPC can successfully retrieve data from the external MySQL products table.
  • Open the rule and remove unnecessary input parameters. For this example, only browsing by category is required. So, the other input parameters can be deleted from this activity or the required flag can be set to No.
  • Note that the wizard created from and to parameters for each table column. Some parameters may not be applicable and can be removed.

Activity Browseproducts Parameters

  • Defining from and to parameters only makes sense for numeric table columns, where a range is provided to query the table (e.g.: search products where unit price is between $0.00 and $100.00).
  • Here, the list of input parameters of the Browseproducts activity was reduced as shown below.

Activity Browseproducts Parameters Short Parameter List

  • Switch to the Steps tab and expand the first step that calls the Obj-Browse method.
  • Configure the selected fields as needed and set the CONDITION to either Value Only or use Is Equal for a comparison to an input parameter value. Value Only means that the field will not be used for querying but that it will be included in the search results.
  • For testing, remove the comment (//) from the step which calls Show-Page, to Show the temporary list page of query results. When the activity is executed, this will result in a popup window showing the SQL query results in XML format.

Activity Browseproducts Parameters Steps Obj-Browse Method

  • Click on Save to commit the changes to the activity.
  • The activity can be run by clicking on Actions > Run.
  • In the activity execution window, enter the value for the input parameter to query by, in this case the input parameter is category and only table rows where the category matches electronics should be returned (see Is Equal condition on the steps page of the activity).
  • Click on the Execute button to run the browse activity.

Activity Browseproducts Parameters Run Enter Parameters

  • A new dialog window will open and show the query results in XML format.
  • The XML output will contain the total number of rows retrieved from the table, the actual SQL query string that was executed against the external MySQL table and the result data.
  • The node pxResults contains the query results. Each entry is of the type PXC-OrderCat-Data-products.
  • In this case, 4 products, where the category matches electronics, were retrieved from the products table on the MySQL server.

Activity Browseproducts Run pagedata pxResults

  • The activity rule can now be used in a Pega 7 application as a data source for e.g. populating a drop-down control or a repeating grid on a UI section.

21 thoughts on “Example for Accessing External MySQL Database from Pega 7 PRPC

  1. Pega is not able to access the MySql DB in host system.I’m getting Communications link failure. when I entered the command ping ipaddress of the windows in prpc server,the process is not ending.Please suggest any solution.

    • What setup do you have? Are you using VMWare Player or Virtual Box? It sounds like the network setting of your VM is not configured correctly.

    • Ok, I haven’t tried this on Virtual Box. Will give it a try when I have time and post an update if I find out anything. But the network configuration on Virtual Box should be similar to VMWare Player. You can try the different settings: Bridged, Private Network with the Host etc…

  2. Hi,

    Map Parameter Values->Browsing Mapping(Connector and Metadata Accelator)

    What does QueryFrom and QueryTo means,could you please explain about this

    Thanks

    • Hi,

      this would apply if you want to query certain table fields using a range. This would usually only apply to numeric fields such as price for example, i.e. you query based on a certain price range, in SQL: “SELECT * FROM products WHERE price >= param.priceFrom AND price < param.priceTo". Pega auto-generates these activity parameters in the accelerator. If you scroll down, to the screenshot of the steps for activity "Browseproducts", I removed most of the "from" and "to" parameters or made them optional. When you actually implement the browse activity, you would modify the auto-generated "from" or "to" parameters and properly map parameters to the actual table fields. Hope that helps, Regards

  3. hi admin i tried above steps but i am not able to connect with external database.can u please provide steps to configure within the same system rather than virtual system.error displaying like drivers not found.

    • Hi Koushik,

      if you have the MySQL DB server running in the same system, the steps should be very similar, the setup should be even easier. The “drivers not found error” sounds like a problem with locating the MySQL JDBC driver, again, the setup for that is described in the post and should be the same steps for VM vs. same system. If you send me the exact error message and more details of your setup I may be able to provide better help.

      Regards,
      Bruno

  4. Hi Bruno,

    Thanks for posting articles on pega.I am facing an issue I need your help.

    I am trying to connect external db(MySQL) to personal edition but I am getting test connection failed ” Problem encountered when getting a database connection: code: 0 SQLState: 08001 Message: No suitable driver found for jdbc:mysql://localhost:3306/customerinformation”

    I have created customerinformation database in mysql db and I added jar file(sqljdbc4) to the path (tomcat\lib\sqljdbc4).

    jdbc url:jdbc:mysql://localhost:3306/customerinformation

    Is there anything else do I need to change to make connection successful.

    Thanks in advance.Waiting for your reply.

    • Hi Krishna,

      try to copy the JDBC driver JAR file into the tomcat/webapps/prweb/WEB-INF/lib folder. Also, make sure to restart the prweb module or Tomcat if necessary so that the driver file becomes available on the classpath and can be found when configuring the DB in the Pega Designer Studio. Let me know if that worked for you.

      Regards,
      Bruno

      • Hi Bruno,

        Thanks for your reply.

        In personal edition I am able to see the path (Tomcat\lib ) and I have placed the jar in that path only.Even though connection failed.After placing jar file,do I need configure anything?

  5. Hi Bruno,

    A good detailed article. Thanks for this.

    I have one question on the obj-save part, though it is not in scope of your article but any inputs on this will be appreciated.

    In your example of database table, the ID column is an auto increment at database level. How do we say or map in class property saying to consider next value of ID generated by database and save the record database when an obj-save executed (assume all other column data’s are provided except ID value).

    Thanks,
    Ravi

  6. Hi ,

    Thank you for the Post!!! its really helpful!! i Appreciate your work..

    I followed the the same steps, but myself encountered with the below issue.

    Cannot access instance of class ABC-Loan-Data-StatesInfo, as it belongs to a table without a pzInsKey column and key property StateID is not exposed

    Note: Obj-browse and RDB methods are working fine.If i try to save with Obj-Save(write-now) , getting the mentioned issue

    Please help me here.

    Thank you !!!!

Leave a Reply