Clovertech

query database in xlate

› Clovertech Forums › Read Only Archives › Cloverleaf › Cloverleaf › query database in xlate

  • Creator
    Topic
  • January 31, 2014 at 2:45 pm #54026
    Yamil Velez
    Participant

      Hi All

         I am not sure if this is possible that is why I am posting my question for help.   We are on  Cloverleaf version 6.0,  I know how to connect to a database and how to xlate  HL7 to Database or Database to HL7.   The question that I have is the following, the sending system does not always send the SCL number but it does send booking number.   I connect to a database that has both the booking number an the SCL number.   Is it possible to do a lookup in the xlate  to the database for the missing value and put that in the outbound message?

      Thanks in advance.

    • Creator
      Topic
    Viewing 4 reply threads
    • Author
      Replies
      • January 31, 2014 at 4:14 pm #79906
        Jim Kosloskey
        Participant

          If you have the Data Integrator (ODBC Drivers) add-on from Cloverleaf you can in any release of Cloverleaf the Data Integrator is available.

          Natively that feature is not available via the Cloverleaf built-in JDBC drivers as of yet.

          There are considerations when accessing a DB from inside an Xlate which are different than when connecting either as input or as output.

          Not the least of which is what do you do if you have an error while interacting with the DB? You have a message in flight inside the engine so you need to determine how you will react.

          Also if you expect to maintain a persistent connection with the DB you need to make sure you can detect reliably under all conditions if you need to reconnect.

          I know others have done this with the Data Integrator addon so perhaps they can add additional considerations, tips, etc.

          This is something we have tossed around to do. We have not yet had the need. But if we did we would utilize a provided  Stored Procedure service (provided by the owner of the DB) rather than imbedding the SQL inside the Xlate.

          email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.

        • January 31, 2014 at 4:28 pm #79907
          Yamil Velez
          Participant

            Hi Jim

            Thanks for the quick reply

               We have Cloverleaf version 6.0 with Data Integrator.   I was able to establish a connection with the SQL database, which contains the two fields SCL # and the Booking #.  I would like to first get the inbound msg and then path copy the fields over in the xlate.  In the outbound tab of the outbound receiver, I would like to put a TCL code that would put the booking # from the inbound msg in a variable.  Then I would like to query the SQL database to see if the booking # exist. If it does then I would like to take the SCL from the SQL database and put that in the outbound msg and send the msg to the outbound system. If the booking number does not exist I would like to store the msg to file so users can fix it later.

            Do you think this is possible?   Other way I was thinking if possible,  Can I do a lookup to a database just like a table in a xlate?

            Thanks again for your help

          • January 31, 2014 at 8:08 pm #79908
            Jim Kosloskey
            Participant

              In an Xlate you will need to have some Tcl code to query the DB.

              I prefer the owner of the DB provide a Stored Procedure which will give me a return code as well as the data required rather than doing all the SQL myself. But you could do all the heavy lifting yourself.

              Just make sure you allow for how you will handle loss of connectivity to the DB and the case where there is an error interacting with the DB.

              Remember you are inside the Xlate with a message in flight so you need to accomodate what to do with that message should there be a DB error.

              Will you be connecting/disconnecting with each message or will you attempt to connect at thread start, stay connected, and disconne4ct at thread shutdown?

              You need to give consideration to how you will maintain connectivity and what you wil do when connectivity is lost so that you do not lose any messages.

              As for doing the query at the outbound thread – what you outline seems plausible but you still need to know if you will connect/disconnect with each message or try to keep a persistant connection.

              We have not yet done this (query DB while message is in flight) as I said – perhaps others who have can add some insight.

              email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.

            • February 3, 2014 at 3:03 pm #79909
              Yamil Velez
              Participant

                Hi Jim

                   Can you get some more details how you setup the database, so the heavy lifting can be done by a store procedure.    Right now I have an inbound thread call orthosecure thats sends raw to my database thread that is called orthodatabase. From the database thread I go out to my outbound thread called picis ort out also in raw format.   Where do you have your tcl  proc?  and can you give send me a sample tcl proc?

                Thank you for all your help

                Yamil

              • February 3, 2014 at 6:24 pm #79910
                Jim Kosloskey
                Participant

                  Yamil,

                  Email me and we can discuss this off-line.

                  email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.

              • Author
                Replies
              Viewing 4 reply threads
              • The forum ‘Cloverleaf’ is closed to new topics and replies.