Background

In Tutorial #5C, we translated the Financial Code with the function translatefincodessimple. This function translated the codes totally within the script. A problem arises when there is a requirement to add to, or modify the translations. The approach taken in our previous example requires the script to be updated each time.

A better solution is to use a lookup table stored in a database. This allows the translations to be updated without changing the script.

A table listing the value equivalences must be created and populated. This table can be hosted on any database platform that is available through ODBC from the development environment, and accessible to the HL7Connect service. For this tutorial, we will use a Microsoft Access database.

Setting up the translation database for HL7Connect

An Access database (hl7tutorial.mdb) is to be used to translate the financial codes. This database is already created and will be made available during this exercise.

To use this database for the translation, the database must be available at run time. HL7Connect scripts have access to database services through a database connection pool. The database connection pool manages a pool of connections to a database, and provides debugging and performance tracking.

To make the database available to HL7Connect, take the following steps:

  1. Save this file to the directory c:\hl7tutorial - HL7Tutorial.mdb (Left click on the hotspot, then choose Save this file to disk.)

    IMPORTANT:If this does not work then there are two possible solutions:

    1. Reinstall your Internet Explorer OR
    2. The HL7Tutorial.mdb file is installed in the doco folder of the HL7Connect installation. Manually copy this file to c:\hl7tutorial

  2. We will now require a System DSN in ODBC. Create a Microsoft Access DSN called "HL7Tutorial" that points to the file c:\hl7tutorial\hl7tutorial.mdb. This can be done via your control panel. The process varies slightly, depending on what version of Windows you are using. If you have any difficulty here then please seek some expert assistance.

Connecting to the HL7Tutorial Database

To actually use a database, create a database connection pool. Then, ask the pool for a connection. This is an SQL based connection that can be used to interact with the database. The script must return the connection to the pool when it has finished with it. Otherwise the connection will no longer be available, and you will have created a connection leak.

Database Connection Pools can be created as required. Alternatively they may be created when HL7Connect starts, or an interface starts. Since there is considerable overhead in establishing a connection to a database, it is generally better to create the connection once at startup. If the database is shared between interfaces, then it needs to be created at kernelstartup (i.e. when HL7Connect starts), otherwise it should be created when the interface starts. For the purposes of this tutorial however, we will acquire a database connection "on demand".

If the database becomes unavailable while HL7Connect is running, the pool manager will reconnect to the database when it becomes available again.

To connect to the database, you ask the HL7Connect kernel to create a connection to the database like this:

General Syntax:
Kernel.CreateDBDSN(Name, DSN, UserName, Password, ConnCount);

For this example, we will use: Kernel.CreateDBDSN('HL7Tutorial', 'HL7Tutorial', '', '', 2);

Name is the logical connection name that is used throughout HL7Connect and the development environment to identify this database connection. You can use any name except "HL7Connect", as this is the name of the internal connection pool to the primary HL7Connect database.

DSN is the name of the DSN used to point to the appropriate database. This does not need to be the same as the logical Name.

Username & Password are not required for Microsoft Access databases.

ConnCount is the number of connections that this database pool is allowed to create. Connections will be created as they are required, up to this set limit. It is important to set a reasonable limit, to prevent scripts that leak connections from using up all the available connections for a database server.

This connection pool is to be created when the interface starts, and closed when the interfaces stops. Javascript and VBScript functions are supplied to open and close these connections.

Scripts

Select the script you are using then cut/paste the following code into your translate5.vbs OR translate5.js editor window.

Language    Script
VBScript   
    function opendatabase(aEvent)
      Kernel.CreateDBDSN "HL7Tutorial", "HL7Tutorial", "", "", 2
    end function

    function closedatabase(aEvent)
      Kernel.CloseDB "HL7Tutorial"
    end function
   
Javascript   
    function opendatabase(aEvent){
      Kernel.CreateDBDSN('HL7Tutorial', 'HL7Tutorial', '', '', 2);
    }

    function closedatabase(aEvent)
    {
      Kernel.CloseDB('HL7Tutorial');
    }
   

Testing Opening and Closing the Database

New test case files are required to test the script functions opendatabase and closedatabase.

To set this up, take the following steps:

  1. From the menu in the Development Environment, choose Tools .. Databases. You should have a single entry on the left for "HL7Connect".
  2. Create a new Test Case for OnInterfaceStart. Save this as c:\hl7tutorial\interfacestart.tci. Select translate5.js or translate5.vbs for the script, and type "opendatabase" for the Procedure. Since we don't actually reference the interface in the script, we do not need to provide an interface key.
  3. Execute the procedure by clicking on the "Go" button.
  4. Check that you now have a new database connection called "HL7Tutorial". Listed as tables under the database, you should find LISFinCodes and PASFinCodes.

    Screen Shot

    Note: The database window is a view of the central kernel database manager implementation in the Development Environment. Databases created in scripts are visible in the Database window, and databases created in the database window can be used in scripts.

  5. Create a new Test Case for OnInterfaceStop. Save this as c:\HL7Connect\interfacestop.tci. Select translate5.js or translate5.vbs for the script, and type "closedatabase" for the Procedure.
  6. Execute the procedure by clicking on the "Go" button.

The database window should show that the "HL7Tutorial" database has been closed.

Managing database connections in the Development Environment

The opendatabase function defined above connects to the database required for the translation routine. It would be painful to have to run this startup script before the translation routine is executed, as the translation script would typically be executed many times as it is prototyped.

To save having to run the opendatabase function repeatedly, the Development Environment can be configured to automatically create the database connection. This can be done by clicking on "Reconnect at Startup" in the properties window for the HL7Tutorial database. To select properties, right click on the HL7Tutorial database in the database window (run opendatabase again from interfacestart.tci if it is not currently listed in the databases window) and select "Connection Properties". You should then select the "reconnect at startup" check box. It is also a good idea to put in the username and password (not required for Microsoft Access databases) and click on "Remember Password". This way you will not have to login to the HL7Tutorial database each time you start up.

Scripts

Select the script you are using then cut/paste the following code into your translate5.vbs OR translate5.js editor window.

Language    Script
VBScript
  
    function translatefincodedb(aEvent)
      set db = kernel.GetDB("HL7Tutorial")      ' get a handle to the global database pool
      set sql = db.GetConnection("translate")   ' get an actual connection to the database
      db.YieldConnection sql, ""                ' finished; return the connection. always remember to do this
    end function
   
Javascript   
    function translatefincodedb(aEvent){
      var db;    // handle to database connection pool
      var sql;   // actual database connection
      db = kernel.GetDB('HL7Tutorial');       // get a handle to the global database pool
      sql = db.GetConnection('translate');    // get an actual connection to the database
      db.YieldConnection(sql, '');            // finished; return the connection. always remember to do this
    }
   

Running the Script

Select the HL7Tutorial database in the database window. It should appear as follows (Note: the databases do NOT refresh automatically. You need to move off, then back on to the database. Do this by clicking on HL7Connect database then clicking back on HL7Tutorial database.):

Screen Shot

  1. Select the testcase5 test case and change the procedure to translatefincodedb.
  2. Execute the procedure by clicking on the "Go" button.
  3. Select the database window again and look at the status window. It should appear as below. Note the differences to the original status window.

Screen Shot

Running Queries

Once you have the database object (called "sql" in this script), you can interact with the database. Interacting with the database always includes the following steps:

  1. Set the SQL statement.
  2. Prepare the statement.
  3. Bind any Parameters [Optional].
  4. Execute the statement.
  5. Fetch the result set (If a Select Statement) [Optional].
  6. Terminate the statement.

You can repeat the bind/execute/fetch cycle any number of times for a single prepare. You must call the terminate step once for each prepare.

The following script shows how to select the value of the LISCode for the corresponding PASCode:

Scripts

Replace the existing translatefincodedb function with the following code into your translate5.vbs OR translate5.js editor window:

Language    Script
VBScript   
    function translatefincodedb(aEvent)

      fcodein  = uppercase(aEvent.message.hl7.element("PV1-20").AsString)
      fcodeout = fcodein

      set db = kernel.GetDB("HL7Tutorial")      ' get a handle to the global database pool
      set sql = db.GetConnection("translate")   ' get an actual connection to the database

      sql.sql = "select LISCode from PASFinCodes where Code = " & "'" & fcodein  & "'"

      sql.prepare
      sql.execute
      if (sql.fetchnext) then
        fcodeout = sql.ColStringByName("LISCode")
      else
        LastErrorMessage = ""
        On Error Resume Next
        SendEmail "10.10.1.1",                                               +_
                  "HL7Tutorial Interface Error",                             +_
                  "myname@myplace.com",                                      +_
                  "myname@myplace.com",                                      +_
                  "PAS Financial Code " & fcodein & " is unknown"
        if LastErrorMessage <> "" then
           Kernel.WriteToLog DBG_WARNING, "SendEmail Error " & LastErrorMessage
        end if
      end if
      sql.terminate
      aEvent.message.hl7.element("PV1-20").AsString = fcodeout
      db.YieldConnection sql, ""            ' finished; return the connection. always remember to do this
      translatefincodedb = True
    end function
   
Javascript   
    function translatefincodedb(aEvent){
      var fcodein;
      var fcodeout;
      var db;    // handle to database connection pool
      var sql;   // actual database connection

      fcodein  = uppercase(aEvent.message.hl7.element('PV1-20').AsString);
      fcodeout = fcodein;
      db       = kernel.GetDB('HL7Tutorial');       // get a handle to the global database pool
      sql      = db.GetConnection('translate');     // get an actual connection to the database

      sql.sql = 'select LISCode from PASFinCodes where Code = ' + '\'' + fcodein  + '\'';

      sql.prepare;
      sql.execute;
      if (sql.fetchnext)
        fcodeout = sql.ColStringByName('LISCode')
      else
        {
         try
          {
          SendEmail('10.10.1.1',
                    'HL7Tutorial Interface Error',
                    'myname@myplace.com',
                    'myname@myplace.com',
                    'PAS Financial Code "'+fcodein+' " unknown');
          }
        catch(e)
          {
          Kernel.WriteToLog(DBG_WARNING,'SendEmail - SCRIPT EXCEPTION: ' + LastErrorMessage);
          }
        };
      sql.terminate;
      aEvent.message.hl7.element('PV1-20').AsString = fcodeout;
      db.YieldConnection(sql, '');            // finished; return the connection. always remember to do this
      return(true);
    }
   

Testing translatefincodedb

To test the updated translation code take the following steps:

  1. Using testcase5.tci, test the updated translatefincodedb. (You may need to update the HL7 message as the last one used contained errors)
  2. Update the function translate defined in Tutorial #5E to call translatefincodedb rather than translatefincodessimple, and test this works.


© Kestral Computing P/L 2000-2010.