Using a Reference Table Query in a Module

This example obtains a bibliography for the specified author using a Reference Table Query inside of a module.

1. Obtain the necessary information about the external database table containing the information you wish to look up.
   • the name of the table containing the data
   • the schema for that table
   • the service to use to connect to it
2. Create the reference table query in your project. When you define a reference in a module, it must always be mapped to a reference in your project which contains the connection information to the database.

   For example, for a table named bibliographies, with a string column, authorName, a string column, bookTitle, and a string column, publisher, that can be accessed by a service named databaseLookup, create a reference table query named getBiblio:

   CREATE REFERENCE getBiblio 
   SCHEMA (authorName string, bookTitle string, publisher string)
   PROPERTIES service='databaseLookup',source='bibliographies',sourceSchema='bookSchema';

3. Create an input stream for order events to enter the project.

   For an event which is an order placed by a customer, consisting of three integers, named orderID, customerID, and itemID, and one string authorName create an input stream named orderStream.

   CREATE INPUT STREAM orderStream
   SCHEMA (orderID integer, customerID integer, itemID integer, authorName string)
   ;

4. Create an output stream named otherBooks with three string columns: authorName, bookTitle, and publisher to display the list of other books by the author.

   CREATE OUTPUT STREAM otherBooks
   SCHEMA (authorName string, bookTitle string, publisher string)
   ;

5. Create a module that uses this table reference query. Note that in the module you only need to specify the name and schema of the reference table query.

   CREATE MODULE listOtherBooks
   IN orderStream
   OUT otherBooks
   REFERENCES getBiblio
   BEGIN
       CREATE REFERENCE getBiblio 
           SCHEMA (authorName string, bookTitle string, publisher string);
       CREATE INPUT STREAM orderStream
           SCHEMA (orderID integer, customerID integer, itemID integer, authorName string);
       CREATE OUTPUT STREAM otherBooks
           SCHEMA (authorName string, bookTitle string, publisher string)
           FROM orderStream, bibliography
           WHERE orderStream.authorName=bibliography.authorName;
   END;

6. Load the module to generate the list of other works by the author of the book that was ordered. Loading the module creates a mapping between the streams in the main project and in the module. The output from the module will be visible in the otherBooks stream in the main project.

   LOAD MODULE listOtherBooks AS listAll
       IN
           orderStream = orderStream
       OUT
           otherBooks = otherBooks
       REFERENCES
           getBiblio = getBiblio
   ;