Retrieve Patient Information Using an Input Database Communication Point

In this scenario, patient information is stored in a number of tables in the database, and is inserted there by another application. An input database communication point needs to be configured to watch the database, and generate a message every time a new patient is inserted. The generated message will then be processed by a Rhapsody route.

Database Structure

The database that the information will be inserted into for this scenario has four relevant tables. The SQL script for creating these tables are as follows:

CREATE TABLE Address (
 AddressID int identity(1,1) PRIMARY KEY, 
 PatientID int NOT NULL,
 StreetAddress varchar(100) NOT NULL,
 City varchar(50) NOT NULL,
 StateOrProvince varchar(50) NULL,
 Country varchar(50) NOT NULL
)

CREATE TABLE NextOfKin (
 NextOfKinID int identity(1,1) PRIMARY KEY,
 PatientID int NOT NULL,
 FamilyName varchar(50) NOT NULL,
 GivenName varchar(50) NULL,
 Sex varchar(1) NULL,
 Relationship varchar(5) NOT NULL,
 PhoneNumber varchar(20) NULL,
)

CREATE TABLE NextOfKinAddress (
 AddressID int identity(1,1) PRIMARY KEY,
 NextOfKinID int NOT NULL,
 StreetAddress varchar(100) NOT NULL,
 City varchar(50) NOT NULL,
 StateOrProvince varchar(50) NULL,
 Country varchar(50) NOT NULL
)

CREATE TABLE Patient (
 PatientID int identity(1,1) PRIMARY KEY,
 FamilyName varchar(50) NOT NULL,
 GivenName varchar(50) NULL,
 Title varchar(4) NULL,
 Gender varchar(1) NULL,
 DateOfBirth datetime NULL
)

The primary table in this scenario is the Patient table. It has a one-to-many relationship with the Address and NextOfKin tables, and the NextOfKin table has a one-to-many address with the NextOfKinAddress table.

Four statements are required; one to retrieve data from each table.

Due to the table relationships, the Address and NextOfKin statements will be nested under the Patient statement, and the NextOfKinAddress statement will be nested underneath the NextOfKin statement. The generated XML message will reflect this structure.

This gives a configuration outline as follows:

• Statement One: Selects the new patients from the Patient table, ordering them by their generated identifiers.
• Statement Two: This is a child statement of statement one, and retrieves all the addresses for the patient in question.
• Statement Three: This is a child statement of statement one, and retrieves all the next of kins for the patient in question.
• Statement Four: This is a child statement of statement three, and retrieves all the addresses of the next of kin in question.

Creating the Communication Point

Create a new Database communication point in the Rhapsody IDE and give it an appropriate name. Then configure the database connection settings from within the IDE to point the communication point at the database. Once this is done, click the Create Configuration link to launch the Database Configuration Editor.

Configuring Database Polling

Click the Database Polling button in the top-left corner of the Database Configuration Editor to display the polling options. Configure the polling options to use the PatientId column in the Patient table as the key, with an initial key value of 0 (zero), and a polling interval of 30 seconds.

Retrieving Data from the Patient Table

Drag a new Execute SQL object from the toolbox onto the editor. This will be used to retrieve information from the Patient table.

Open the statement and rename it to Patient. Select the Patient table in the Server Explorer tree, and select the Generate SQL button.

The generated SQL needs to be modified to allow the database communication point to select the patient identifier (as it knows the last identifier that was used), and to order the records appropriately. The SQL should be changed to the following:

The reason we refer to the PatientID column as @patientid here, is that it the column we configured previously as the key column for this configuration. That is the column Rhapsody uses to determine when new rows are available.

Finally, give the statement an appropriate summary and close it.

Retrieving Data from the Address Table

Drag a new Execute SQL object onto the main editor, and make it a child of the Patient statement using the green arrow icons.

Open the statement and rename it to Address. Select the Address table in the Server Explorer tree, and click the Generate SQL button.

The generated SQL needs to be modified so it uses the patient identifier retrieved in the previous statement instead of trying to use a message property. Change the SQL to the following:

Finally give the statement an appropriate summary and close it.

Retrieving Data from the NextOfKin Table

Drag a new Execute SQL object onto the main editor, and make it a child of the Patient statement using the green arrow icons.

Open the statement and rename it to NextOfKin. Select the NextOfKin table in the Server Explorer tree, and click the Generate SQL button.

The generated SQL needs to be modified so it uses the patient identifier retrieved in the previous statement instead of trying to use a message property. Change the SQL to the following:

Finally give the statement an appropriate summary and close it.

Retrieving Data from the NextOfKinAddress Table

Drag a new Execute SQL object onto the main editor, and make it a child of the NextOfKin statement using the green arrow icons.

Open the statement and rename it to NextOfKinAddress. Select the NextOfKinAddress table in the Server Explorer tree, and click the Generate SQL Statement button.

The generated SQL needs to be modified so it uses the next of kin identifier retrieved in the previous statement instead of trying to use a message property. Change the SQL to the following:

Finally give the statement an appropriate summary and close it. The final configuration should look like this:

XML Message Generation

The configuration can now be checked in, and the database communication point hooked up to a route. Once this is done, messages will be generated once data is inserted into the tables. The format of the generated XML message will match the structure of our configured statements.

An XML message generated with this configuration is shown below:

<?xml version="1.0"?>
<message>
   <Patient>
      <Gender>M</Gender>
      <Title>Mr</Title>
      <FamilyName>Bloggs</FamilyName>
      <DateOfBirth>1980-04-15 00:00:00.0</DateOfBirth>
      <PatientID>1</PatientID>
      <GivenName>Joe</GivenName>
      <Address>
         <Country>AUS</Country>
         <StateOrProvince>Queensland</StateOrProvince>
         <StreetAddress>34 Brunswick St</StreetAddress>
         <City>Brisbane</City>
         <AddressID>1</AddressID>
      </Address>
      <Address>
         <Country>NZL</Country>
         <StateOrProvince>Auckland</StateOrProvince>
         <StreetAddress>34 Ponsonby Rd</StreetAddress>
         <City>Ponsonby</City>
         <AddressID>2</AddressID>
      </Address>
      <NextOfKin>
         <Sex>F</Sex>
         <PhoneNumber null="yes"></PhoneNumber>
         <FamilyName>DuSHANE</FamilyName>
         <NextOfKinID>1</NextOfKinID>
         <GivenName>HANNA </GivenName>
         <Relationship>21</Relationship>
         <NextOfKinAddress>
            <Country>KNA</Country>
            <StateOrProvince>Samoa</StateOrProvince>
            <StreetAddress>qwer</StreetAddress>
            <City>New York</City>
            <AddressID>1</AddressID>
         </NextOfKinAddress>
         <NextOfKinAddress>
            <Country>CZE</Country>
            <StateOrProvince>North Australia</StateOrProvince>
            <StreetAddress>adsasd</StreetAddress>
            <City>Auckland</City>
            <AddressID>2</AddressID>
         </NextOfKinAddress>
      </NextOfKin>
      <NextOfKin>
         <Sex>A</Sex>
         <PhoneNumber null="yes"></PhoneNumber>
         <FamilyName>SEABORN</FamilyName>
         <NextOfKinID>2</NextOfKinID>
         <GivenName>KRISTINA </GivenName>
         <Relationship>20</Relationship>
         <NextOfKinAddress>
            <Country>AFG</Country>
            <StateOrProvince>Sumatra</StateOrProvince>
            <StreetAddress>blah de blah</StreetAddress>
            <City>Oregon</City>
            <AddressID>3</AddressID>
         </NextOfKinAddress>
         <NextOfKinAddress>
            <Country>SLE</Country>
            <StateOrProvince>North Australia</StateOrProvince>
            <StreetAddress>blah de blah</StreetAddress>
            <City>Italy</City>
            <AddressID>4</AddressID>
         </NextOfKinAddress>
      </NextOfKin>
   </Patient>
   <Patient>
      <Gender>M</Gender>
      <Title>Mr</Title>
      <FamilyName>Smith</FamilyName>
      <DateOfBirth>1980-04-15 00:00:00.0</DateOfBirth>
      <PatientID>2</PatientID>
      <GivenName>John</GivenName>
   </Patient>
</message>