How to Connect to External Microsoft SQL Server 2016 Database from Pega 7.1.9

In this example, the Pega 7.1.9 Connector and Metadata Wizard in the Designer Studio is used to create connectivity between a Pega 7 application and an external Microsoft SQL Server database table. This wizard will generate the necessary PRPC rules for mapping to the table’s columns and for browsing, inserting, updating and deleting records.

Related Posts

Summary

  1. Software and Setup used in this Example
  2. Installation of Microsoft SQL Server Developer Edition
  3. Setup of Microsoft SQL Server Management Studio
  4. Setup of Sample Database “FlightInformation
  5. Create User Account for Pega 7 JDBC Connectivity to MS SQL Server
  6. Confirm that the MS SQL Server Accepts JDBC Connections
  7. Configuration of Pega 7.1.9 prconfig.xml for MS SQL Server JDBC Support
  8. Setup of new MS SQL Server Database Instance in Pega 7.1.9
  9. Execution of Pega 7.1.9 Connector and Metadata Wizard for External DB Table
  10. Browse the External SQL Server Table with an Activity

  • Pega 7.1.9 installed as a Personal Virtual Server (PVS) running on Oracle VM VirtualBox as described in the exercise system section of the System Architect Essentials course on Pega Academy. See Pega training courses for how to register and download a Pega 7 PVS.
  • The host operating system (running Pega PVS and Microsoft SQL Server) is Windows 10 64-bit.
  • The virtual machine running Pega 7.1.9 was configured to use a Host-only Adapter and DHCP.
  • In this example, the IP address of the Pega 7 virtual machine is 192.168.56.101.
  • In this example, the IP address of the Windows 10 host OS is 192.168.56.1.

Oracle VirtualBox, Network Host-only

Microsoft SQL Server Download Website

  • It is necessary to create a Microsoft account or to login with an existing account. Once that is done, the Visual Studio Dev Essentials page will show a link to download the Microsoft SQL Server Developer Edition.
  • Visual Studio Dev Essentials can be accessed directly through this link.

Microsoft Visual Dev Studio, Tools Download

  • An installation file, called SQLServer2016-SSEI-Dev.exe, will be downloaded.
  • Execute the installation file to start the installation wizard.
  • For this example, the Basic installation type was used.

MS SQL Server Instllation, Select Basic Installation Type

  • Click on the Accept button to accept the Microsoft SQL Server 2016 License Terms.

MS SQL Server Instllation, Accept License Agreement

  • The next screen allows to specify SQL Server install location. For this example, the default location for Windows C:\Program Files\Microsoft SQL Server was used.

MS SQL Server Instllation, Select Installation Location

  • Click on the Install button to start the installation process.
  • The installer will first download the SQL Server software and then install it.

MS SQL Server Instllation, Downloading Installation Package

  • After the installation completed successfully, a confirmation screen is shown.

MS SQL Server Instllation, Installation Complete

  • Click on the Close button to exit the installation wizard.
  • A computer restart is required to complete the installation.

Microsoft SQL Server Configuration Manager, MSSQLSERVER Service is Running

  • Confirm that the MSSQLSERVER service is running. On Windows, use the SQL Server Configuration Manager.

  • The SSMS provides a graphical user interface for developing and managing SQL Server databases.
  • Download the application from the Microsoft SSMS Download page and run the file SSMS-Setup-ENU.exe and follow the installation wizard’s instructions.

Microsoft SQL Server Management Studio, Installation

  • After the installation, run the application link Microsoft SQL Server Management Studio from the Windows Start menu.

Microsoft SQL Server Management Studio, Initial Screen Connected to Local MS SQL Server Instance

  • The SSMS should automatically connect to the locally running SQL Server instance. If prompted, login with Windows user credentials.

  • A sample database and table was created for holding flight information.
  • The flight information table will be used to access its data from Pega 7.1.9.
  • In SSMS, a new database can created by right-clicking on the MS SQL Server instance and selecting Databases > New Database

Microsoft SQL Server Management Studio, New Database Menu Item

Microsoft SQL Server Management Studio, New Database Options, Partial Containment Type

  • A table can be created by right-clicking on Tables > New > Table.

Microsoft SQL Server Management Studio, Create New Table

  • In this example, a table called dbo.Flights with the following columns was created:
  • id – Auto-generated identifier (…enable Identity Specification for this column)
  • flightNumber – The flight number
  • airline – Name of the airline
  • departureTime – Departure date and time
  • arrivalTime – Arrival date and time
  • fromAirport – The origin airport name
  • toAirport – The destination airport name
  • price – The flight ticket price
  • Use the SQL CREATE TABLE command below to create the Flights table with a DDL statement.
  • Note: I used the char column type because nvarchar and nchar table column mapping did NOT work when running the Connector and Metadata Wizard in Pega 7.1.9.
CREATE TABLE [dbo].[Flights](
    [id] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
    [flightNumber] [char](6) NOT NULL,
    [airline] [char](25) NOT NULL,
    [departureTime] [datetime] NOT NULL,
    [arrivalTime] [datetime] NOT NULL,
    [fromAirport] [char](25) NOT NULL,
    [toAirport] [char](25) NOT NULL,
    [price] [money] NOT NULL,
 CONSTRAINT [PK_Flights] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, 
       STATISTICS_NORECOMPUTE = OFF, 
       IGNORE_DUP_KEY = OFF, 
       ALLOW_ROW_LOCKS = ON, 
       ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY];

Microsoft SQL Server Management Studio, Flights Table Structure

  • 11 records were inserted into the dbo.Flights table as shown in the screen shot below.

Microsoft SQL Server Management Studio, Flights Table Data

  • The below SQL INSERT statements can be used to create the sample data.
INSERT INTO [dbo].[Flights] VALUES ('SA0078', 'Spirit Airlines', '2017-02-07 14:25:00.000', '2017-02-07 21:45:00.000', 'Fort Lauderdale (FLL)', 'Los Angeles (LAX)', 414.69);
INSERT INTO [dbo].[Flights] VALUES ('SA0079', 'Spirit Airlines', '2017-02-07 09:15:00.000', '2017-02-07 14:55:00.000', 'Fort Lauderdale (FLL)', 'Los Angeles (LAX)', 975.99);
INSERT INTO [dbo].[Flights] VALUES ('AA1122', 'American Airlines', '2017-02-10 05:35:00.000', '2017-02-10 08:40:00.000', 'Miami (MIA)', 'New Orleans (MSY)', 328.65);
INSERT INTO [dbo].[Flights] VALUES ('AA1078', 'American Airlines', '2017-02-07 09:38:00.000', '2017-02-07 18:05:00.000', 'West Palm Beach (PBA)', 'New York (JFK)', 1225.49);
INSERT INTO [dbo].[Flights] VALUES ('UA0768', 'United', '2017-03-01 10:00:00.000', '2017-03-01 12:15:00.000', 'Chicago (ORD)', 'Dallas (DFW)', 546);
INSERT INTO [dbo].[Flights] VALUES ('UA0768', 'United', '2017-03-02 10:00:00.000', '2017-03-02 12:15:00.000', 'Chicago (ORD)', 'Dallas (DFW)', 595.9);
INSERT INTO [dbo].[Flights] VALUES ('CA1718', 'Copa Airlines', '2017-02-15 07:55:00.000', '2017-02-15 09:55:00.000', 'San Francisco (SFO)', 'Fresno (FAT)', 316);
INSERT INTO [dbo].[Flights] VALUES ('AC7878', 'Air Canada', '2017-02-05 07:59:00.000', '2017-02-05 10:09:00.000', 'San Francisco (SFO)', 'Seattle (SEA)', 725.78);
INSERT INTO [dbo].[Flights] VALUES ('BA0286', 'British Airways', '2017-03-05 10:52:00.000', '2017-03-06 05:52:00.000', 'San Francisco (SFO)', 'London (LHR)', 3219.45);
INSERT INTO [dbo].[Flights] VALUES ('CH9654', 'China Airlines', '2017-03-05 07:32:00.000', '2017-03-05 07:52:00.000', 'San Francisco (SFO)', 'Shanghai (PVG)', 2199);
INSERT INTO [dbo].[Flights] VALUES ('AA3104', 'Alitalia', '2017-02-26 11:15:00.000', '2017-02-27 10:35:00.000', 'San Francisco (SFO)', 'Milan (MXP)', 4056.99);

  • It is also necessary to create a new database user account for JDBC access from Pega 7.
  • Navigate to Security > Users. Right-click to open the context menu and select New User…

Microsoft SQL Server Management Studio, Create New User Menu

  • In the Database User – New form, select "SQL user with password" for the user type.
  • Enter a user name, in this case it is pega7User and a password, here it is rules.
  • In this example, the Flights table is part of the dbo schema and it should be selected as the default one.

Microsoft SQL Server Management Studio, User Account for Pega 7 JDBC Remote Access

  • In the Database User – New form, navigate to Membership and check the option db_datareader and if needed, db_datawriter for inserting data from Pega 7.

Microsoft SQL Server Management Studio, New User Account Permissions

  • Click on OK to save the user account and close the dialog.

  • By default, the MS SQL Server Developer Edition is configured to NOT allow TCP/IP connections.
  • In order to allow remote JDBC clients to connect to the MS SQL Server instance, the SQL Configuration Manager needs to be used to change TCP/IP network settings.
  • Navigate to SQL Server Network Configuration > Protocols for MSSQLServer and enable the TCP/IP protocol as shown below.

SQL Configuration Manager, Enable TCP/IP Protocols for Remote Access

  • A simple Java program can be used to confirm that the pega7User user account and network configuration changes were done correctly and that the MS SQL Server accepts remote JDBC connections. Copy the below Java code into a file named MSSqlServerDAO.java.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class MSSqlServerDAO {
      
    public Connection getConnection(
            String host, 
            String port,
            String db,
            String user, 
            String pwd) throws SQLException {
        
        String connectionString = "jdbc:sqlserver://" + host + ":" + port 
                + ";databaseName=" + db + ";user=" + user + ";password=" + pwd;

        return DriverManager.getConnection(connectionString);
    }
   
    public static void main(String[] args) throws SQLException {
        // Establish a JDBC connection
        //
        Connection c = new MSSqlServerDAO().getConnection(
            "127.0.0.1", /* or 192.168.56.1 for Virtual Box Host-only adapter IP */
            "1433",
            "FlightInformation",
            "pega7User",
            "rules");

        // Execute a query
        //
        Statement stmt =  c.createStatement();
        ResultSet rs = stmt.executeQuery(
                "SELECT * FROM [dbo].[Flights]");
        
        // Process and print the query results
        //
        while (rs.next()) {            
            int id = rs.getInt("id");
            String flightNumber = rs.getString("flightNumber");
            String airline = rs.getString("airline");
            Float price = rs.getFloat("price");
            
            System.out.printf("%-5d %-9s %-17s   $%.2f\r\n", id, flightNumber, airline, price);
        }
    }
}
  • Download the Microsoft JDBC Driver 6.0 for SQL Server and save it to some folder.
  • Copy the Java file MSSqlServerDAO.java into the same folder.
  • From the command line, compile the program using the syntax shown below.
  • After successful compilation, the folder should contain a file named MSSqlServerDAO.class.
"<JDK_BIN_PATH>\javac" -cp <JDBC_DRIVER_FILE_PATH>\sqljdbc42.jar MSSqlServerDAO.java

Java JDBC Program for MS SQL Server Access

  • Execute the Java class file using the syntax given below. The program will connect to the specified MS SQL Server instance, execute a query and print the first 3 columns of the result set.
"<JRE_BIN_PATH>\java" -cp .;<JDBC_DRIVER_FILE_PATH>\sqljdbc42.jar MSSqlServerDAO

Run Java JDBC Program for MS SQL Server Access

  • If the program fails to connect to the MS SQL Server instance, indicating a TCP/IP communication failure, check the host system’s firewall and make appropriate changes so that TCP/IP communication on port 1443 is allowed.

  • It is necessary to copy the MS SQL Server JDBC driver file to the Pega 7.1.9 PRPC web application and to modify the PRPC prconfig.xml file so that Pega can load the JDBC driver.
  • Use the virtual machine’s command line interface (login is root / install) to get the IP address of the Pega virtual machine. Here, it is:
192.168.56.101

Oracle VirtualBox, Pega 7 Private Virtual Server Login

  • A FTP client such as FileZilla can be used to copy the sqljdbc42.jar JDBC driver file to the PRPC web application’s lib folder. In this case, i.e. Pega 7.1.9 PVS using Tomcat 7, the folder is:
/opt/tomcat/webapps/prweb/WEB-INF/lib
  • On a Pega 7.1.6 PVS exercise system, the folder is:
/usr/share/tomcat7/webapps/prweb/WEB-INF/lib
  • The default SFTP username and password for a Pega 7.1.9 Private Virtual Server (exercise system installation) is root and password.

Use FileZilla to copy sqljdbc driver to Pega 7 Private Virtual Server

  • Using an FTP client, download the prconfig.xml file and open it locally in a text editor.
  • On Pega 7.1.9, the PRPC configuration file is located under:
/opt/tomcat/webapps/prweb/WEB-INF/classes/prconfig.xml
  • On Pega 7.1.6, the PRPC configuration file is located under:
/usr/share/tomcat7/webapps/prweb/WEB-INF/classes/prconfig.xml

Use FileZilla to download Pega 7 prconfig file

  • Modify the prconfig.xml file to add an entry for the MS SQL JDBC Driver Class as shown below.
<env name="database/drivers" value="com.microsoft.sqlserver.jdbc.SQLServerDriver" />

Modify Pega 7 prconfig File for MS SQl Server JDBC Driver Support

  • Upload the prconfig.xml file back to the virtual machine.
  • Access the Tomcat 7 Manager Application through a browser, here the address is:
http://192.168.56.101:9080/manager
  • By default, the username and password is admin and admin.

Use Tomcat 7 Manager Application to Restart Pega 7 prweb Application

  • Reload the PRPC web application using the Reload button for the prweb application.

  • Open the Designer Studio, here, the URL is:
http://192.168.56.101:9080/prweb/PRServlet
  • On Pega 7.1.9 PVS exercise system, the admin login is [email protected] and install.
  • Navigate to Records > Sysadmin > Database to view all instances of external DB connections.
  • Click on the +Create button to add a new external database.

Pega 7 Designer Studio, Create New Database Instance

  • On the next screen, enter a name and description for the new external database instance.
  • Click on Create and open to proceed.

Pega 7 Designer Studio, New Database Instance Name and Description

  • On the Edit Database form select "use JDBC URL listed below" in the first drop-down.
  • In the JDBC definition text field, enter the JDBC URL. In this case, the URL is:
jdbc:sqlserver://192.168.56.1:1433;databaseName=FlightInformation
  • Enter the MS SQL Server user credentials created in section 5. Here the username is pega7User and the password is rules.

Pega 7 Designer Studio, New Database Instance Configuration

  • Test the connection by clicking on the Test Connection button on the bottom of the form.

Pega 7 Designer Studio, New Database Instance, Test Connection Success

ERROR: No Suitable Driver Found

  • Verify that the prconfig.xml file has been modified as shown in section 7 to make the JDBC driver available to PRPC.

Pega 7 Designer Studio, New Database Instance, Test Connection Failure, No Suitable Driver Found

ERROR: TCP/IP connection to the host…has failed

  • Verify that MS SQL Server is configured to accept incoming TCP/IP connections (see section 6).
  • Check MS SQL Server host OS firewall. Make sure TCP/IP connections on port 1443 are allowed.

Pega 7 Designer Studio, New Database Instance, Test Connection, TCP/IP Connection Failure

  • Here, the MS SQL Server is running on Windows 10.
  • For temporary debugging, the Windows Firewall can be turned off as shown below.

Windows 10, Disable Firewall

ERROR: TCP/IP connection to the host…has failed

  • Here, Pega 7 PVS is running on a virtual machine with an IP address of 192.168.56.101.
  • MS SQL Server may not accept connections from this IP address.
  • The SQL Server Configuration Manager can be used to allow incoming IP addresses.

SQL Server Configuration Manager, TCP/IP Settings

For more information on MS SQL Server connection troubleshooting refer to:

  • In the Pega 7 Designer Studio main menu, navigate to Integration > Connectors > Connector and Metadata Wizard >

Pega 7 Designer Studio, Select Connector and Metadata Wizard

1. Choose Purpose

  • Purpose: Generate Connector Rules
  • Metadata Type: SQL
  • Integration rule set name and version: here PGX- and 01-01-01
  • Base class for integration and data model rules: here PGX-
  • Connector Activity Class: container for browse, insert, update and delete activities

Connector and Metadata Wizard Step 1 - Choose Purpose

2a. Process Metadata – Select Database Table

  • Database Name: FlightInformation (…external MS SQL Server DB as configured in section 7)
  • Schema Name: dbo (…this schema contains the table, see section 4)
  • Table Name: Flights (…the table name in MS SQL Server)

Connector and Metadata Wizard Step 2a - Process Metadata, Select Database Table

2b. Process Metadata – Select Table Columns

  • Select the external table columns for which the wizard will generate property rules.
  • As noted earlier, using nvarchar and nchar column types in MS SQL Server caused the mapping to fail (i.e. such columns would not show up on the below form).

Connector and Metadata Wizard Step 2b - Process Metadata, Select Table Columns

2c. Process Metadata – Select SQL Operations

  • Select for which SQL operations the wizard will generate activity rules.

Connector and Metadata Wizard Step 2c - Process Metadata, Select SQL Operations

2d. Process Metadata – Select Browse Criteria

  • When "Browse the database" is selected in the previous step, this step allows to specify the columns used for querying the external table.
  • All columns were selected here using the "Select All" link.

Connector and Metadata Wizard Step 2d - Process Metadata, Select Browse Criteria

2e. Process Metadata – Map Parameter Values

  • This step shows the activities that will be created and the mapping between input parameters and external table columns.
  • The Wizard-generated activity input parameters etc. can be modified later if needed.

Connector and Metadata Wizard Step 2e - Process Metadata, Select Browse Criteria

3. Review and Save

  • The last step shows the class for the external table mapping, here it is PGX-FlightReservation-Int-Flights, and the associated property rules.
  • The Wizard-generated activities will be located in the PGX-FlightReservation-Work class.
  • Click on the Finish button to run the Wizard.

Connector and Metadata Wizard Step 3 - Review and Save

Wizard Summary

  • Once finished, all created rules and the associated rule set name- and version will be shown.
  • Here, the rule set containing the Wizard-generated rules is FlightReservationInt:01-01-01.

Connector and Metadata Wizard Step 4 - Summary

Review Generated Rules

  • The Application Explorer can be used to review and open the generated rules.
  • PGX-FlightReservation-Int contains the generated properties and a stub data transform.

Application Explorer, view generated property rules

  • Technical > Activity under PGX-FlightReservation-Work contains the generated stub activities for accessing the external table.

Application Explorer, view generated activity rules

  • The activity rules can now be used to access the external table using these Pega 7 methods:
Obj-Browse – to query the database table
Obj-Filter – to filter results of Obj-Browse
Obj-Save – to update and save a record
Obj-Delete – to delete loaded record
Obj-Delete-By-Handle – to obtain a record by ID and then delete it
Obj-Save-Cancel – to cancel the most recent uncommitted Obj-Save

  • Open the wizard-generated activity BrowseFlights and click on the Parameters tab.
  • The wizard may create unnecessary parameters, e.g. that only apply to numeric data types, where a range can be provided (e.g. to search for flights with prices between $500 and $1000).
  • Ranges for text types don’t make sense. Unnecessary parameters can be removed as needed.
  • The refactored Parameters page of the BrowseFlights activity is shown below.

Activity BrowseFlights, view parameters

  • By default, there is one entry on the Pages & Classes form. The pyTempListPage page will hold the results of Obj-Browse. Note that pyTempListPage applies to class Code-Pega-List.

Activity BrowseFlights, view Pages and Classes

  • The Steps page shows the 4 wizard-generated steps, including the invocation of Obj-Browse.
  • Step 2 uses the method Show-Page to display the Obj-Browse results as a XML page.
  • For unit testing, uncomment step 2.
  • Step 3 uses Page-Rename to use the targetPage input parameter for the results page (see When rule to check if targetPage parameter is set, if not step is skipped).

Activity BrowseFlights, view Steps and uncomment Show-Page

  • Expand the first step to view the parameters for the Obj-Browse method.
  • The MaxRecords parameter is set to 100 as a best practice. If left blank, up to 10,000 records will be returned by Obj-Browse, creating a potential performance problem.
  • The UseLightWeightList parameter is checked as a best practice, as it may reduce memory requirements during processing. See the PDN article About lightweight lists for more information.

Activity BrowseFlights, Obj-Browse method parameters

  • The Obj-Browse method has a list of query fields that will be used for selecting records. Use the check box to indicate what fields will be used to match records.
  • In the below screen shot, the selection will result in a SQL WHERE clause similar to this:
WHERE (fromAirport = param.fromAirport) AND (price >= param.fromPrice)
AND (price <= param.toPrice) ORDER BY price

Activity BrowseFlights, Browse-Obj query field selection

  • Run the BrowseFlights activity by clicking on Actions > Run in the Designer Studio.
  • A new window will open. It allow to set values for the activity’s input parameters.
  • Here, all flights from San Francisco (SFO) that cost between $500 and $3,000 are selected.

Activity BrowseFlights, Run and show input parameter page

  • Run the BrowseFlights activity by clicking on the Run button.
  • A new window will show the results of the query. In this example, 2 flights matched the query.
  • The SQL prepared statement is shown in the pxSQLStatementPost node in the pagedata XML.

Activity BrowseFlights, Show-Page XML output of query results

  • The node pxResults of type PageList contains the 2 flight records, ordered ascending by price.
  • Each item in pxResults is a single page of PGX-FlightReservation-Int-Flights.

The BrowseFlights activity can now be used in a Pega 7.1.9 application to e.g. populate a data page using its Load Activity. The other activities for inserting, updating and deleting data can be used in a similar fashion. For related examples on how to connect to external MySQL and Oracle databases, see the below posts on this blog.

Related Posts

Leave a Reply