How to write msgs from thread to SQL Server DB?

Clovertech Forums Read Only Archives Cloverleaf Cloverleaf How to write msgs from thread to SQL Server DB?

  • Creator
    Topic
  • #50468
    Rentian Huang
    Participant

      Greetings Cloverleafers!!!

      We are running Qdx 5.2 on AIX 5.1. Our director is planning to build a database warehouse, by sending all ADT, ORDER, etc information from Cloverleaf to a SQL Server DB.

      I have no xp implementing this. I checked Healthvision.com and found Cloverleaf

    Viewing 17 reply threads
    • Author
      Replies
      • #66210
        Jim Kosloskey
        Participant

          Rentian,

          I just completed initial testing of an ODBC (Data Integrator) connection from Cloverleaf(R) to SQL Server 2005.

          What we did was somewhat simpler than what you are planning on doing.

          We are delivering a subset of fields fromn ADT^A04, A05, and A08 messages to a transaction table.

          We convert the HL/7 messages to a VRL (via Xlate) with only the fields of interest.

          We then constructed a reusable Tcl proc that connects to the SQL Server DB and invokes a Stored Procedure. That Stored Procedure’s purpose in life is to populate a Transaction Table. The Transaction Table is then used to do the actual updates to the business Table(s).

          We deploy the proc as an IB Tps proc on an Outbound thread.

          In your case, will you be delivering the HL/7 message to something or interpreting the HL/7 message and doing the SQL statements inside your proc?

          We have taken the postion here not to do SQL statements inside the engine but rather to invoke Stored Procedures whenever possible.

          It is our belief that the SQL statements represent ‘business rules’ and they do not belong inside of our Integration tool but rather belong with the application.

          There are some other caveats.

          There is no protocol setting on a Cloverleaf(R) thread for ODBC (Data Integrator). That has been requested, but has not yet materialized.

          So you need to write a Tcl proc and use it with one of the existing Cloverleaf(R) protocols. In our case stated above, we used TCP/IP for some very good reasons.

          I don’t see why you could not use almost any available Cloverleaf(R) protocol althought there would be pros and cons with each.

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

        • #66211
          Rentian Huang
          Participant

            Hi Jim, thanks for your reply as usual!

            We plan to do the way similar to what you are doing. SQL statement should be on the SQL DB side, I agree!

            I understand the part that you are converting the HL7 to VRL. But not for the reusable Tcl that you are using to connect to SQL Server DB and invoke SP. I thou the “Cloverleaf

          • #66212
            Jim Kosloskey
            Participant

              Rentian,

              Data Integrator provides you with the Data Direct ODBC Drivers and a package Healthvision developed to connect Tcl to the C API for Data Direct.

              You need to construct the Tcl code to invoke the package which in turn invokes the Data Direct ODBC drivers.

              I have sent you the proc we are developing to your email address.

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

            • #66213
              Rentian Huang
              Participant

                OMG, that’s a lot of code for me to enjoy  ðŸ˜€  I will look into them and your help is very much appreciated!!!

              • #66214
                Jim Kosloskey
                Participant

                  Rentian,

                  There is more code because it is a reusable, argument driven proc. The intent is that we will use the same proc for all of our ODBC work regardless of DB, etc. Because we are following an architecture of invoking Stored Procedures rather than imbedding the SQL Statements in the Tcl procs, we can attempt to have a single reusable proc.

                  You can have a ‘skinnier’ proc if you make it non-reusable and then make a copy and modify it for each use.

                  The provided proc should give you some ideas as to what might be needed.

                  We also have taken the approach of using Connect Strings rather than referencing only the DSN in the odbc.ini file in order to connect to the DB.

                  We feel that gives us the most flexibility.

                  We would like to keep the odbc.ini file from needing to be modified and instead define the exceptions to the defaults in the connection string.

                  I also have a Tcl proc for testing ODBC connections which we use like hcitcptest – wherein we test our connectivity before we even begin to build Engine objects – so that the connectivity issues (and there are always connectivity issues) can be resolved without involving the engine.

                  I also have a proc to test execution of Stored Procedures – this proc is really in the early development stages – that we will use like testing connectivity but for testing invocation of Stored Procedures just enough to make sure we can invoke the Stored Procedure.

                  We currently have one ODBC integration in Production for an Oracle DB, one in test for a SQL Server 2005 DB, and one in early development for a DB2 DB. Once those are done, we will have an example for handling all of the institution supported DBs.

                  I have a presentation I put together for our group on ODBC in general and it shows the basics using a text file for tghe example.

                  Healthvision also has an excellent Webinar on the ODBC Drivers/Data Integrator.

                  The DataDirect web site has a lot of information and be sure to get the various books that are included with Data Integrator (on disk). Those give you the particulars for the connection attributes and some insight to the drivers.

                  Also the Help section for ODBC or Data Integrator that comes with Cloverleaf(R) provides some additional information.

                  Don’t forget the Microsoft Support books on ODBC (MS web site) – lot’s of useful information there as well.

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

                • #66215
                  Hongle Sun
                  Participant

                    Hello, Jim. Would you mind sendin me a copy of you tcl as well

                    hosun@tegh.on.ca. Thanks

                  • #66216
                    Rentian Huang
                    Participant

                      Jim, again thanks for your generous information! We are still in the very early stage of this project and are still discussing some primary setup, but didn’t expected to be this complicated thou 🙂  Thou Healthvision handles everything.

                      I will definitely read the information come with the Cloverleaf

                    • #66217
                      Sergey Sevastyanov
                      Participant

                        Hello Jim

                        I already asked you this question before when we talked on the phone but I wonder if anything changed since. Did you by chance figure out how to get OUTPUT parameter from called store procedure?

                        I use TclODBC to extract some data that I need to add into message during translation. As I couldn’t find a way to return data from stored procedure I had to use SQL statements in the engine rather than just calling a storing procedure (which I’d prefer to do).

                        To avoid hardcoding of T-SQL statements in Tcl proc I store SQL queries in gdbm and tcl proc pulls the statement (and parameter types) from gdbm and then runs it. That way I can change SQL query without changing calling tcl code.

                        Unfortunately that also means that I can’t use pre-compiled sql on SQL Server

                        Thanks,

                      • #66218
                        Jim Kosloskey
                        Participant

                          Sebastian,

                          We are still working on getting an OUTPUT parameter returned from SQL Server.

                          It is not a problem with Oracle, but SQL Server is an issue.

                          We do not believe this is related to the Data Integrator but rather it is an SQL Server oddity.

                          Some of our research seems to indicate that all SQl Server Stored Procedures return a Record Set (even if the purpose of the Stored Procedure is not to return a Record Set) and that Record Set – even if empty – must be exhausted prior to getting the OUPUT parameter(s) that have been bound.

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

                        • #66219
                          Jim Kosloskey
                          Participant

                            Sergey,

                            We are still working on getting an OUTPUT parameter returned from SQL Server.

                            It is not a problem with Oracle, but SQL Server is an issue.

                            We do not believe this is related to the Data Integrator but rather it is an SQL Server oddity.

                            Some of our research seems to indicate that all SQl Server Stored Procedures return a Record Set (even if the purpose of the Stored Procedure is not to return a Record Set) and that Record Set – even if empty – must be exhausted prior to getting the OUPUT parameter(s) that have been bound.

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

                          • #66220
                            Sergey Sevastyanov
                            Participant

                              Jim

                              I also don’t think it’s a problem with the engine. Do you use the same odbc to call stored procedure from Oracle?

                              If so can you give me an example how you call store procedure? I don’t know how the OUTPUT parameter should be defined in Tcl – I usually first try it in standalone Tcl before bringing it to Cloverleaf but I can’t figure out how to pass output parameter (variable)

                              Thanks

                              Sergey

                            • #66221
                              Terry Kellum
                              Participant

                                We’ve been doing something similar here for a number of years.  I stuff the whole transaction as a blob into a MySQL database table.  It’s keyed on a msgnum that autoincrements.

                                I have TCL scripts that then run every 5 minutes or so and create an Index table of interesting data from the stored transactions.

                                This allows me to find any transaction or subset of transactions that I need by submitting an SQL query to MySQL.

                              • #66222
                                Chris Arts
                                Participant

                                  Hi all,

                                  Another way to move messages to SQL databases is the Soap Endpoint in SQL-Server (2005). We use a transalation from HL7 to XML, and wrap it in a soap envelope, TCP/IP will deliver it to the Soap Endpoint and from there a Stored Proc will pick it up and handle it. You may even store it as xml strings, if needed.

                                  I think the handling of XML  is a lot better in version 5.6 and upward, though.

                                  yours sincerelly,

                                  Chris Arts

                                  Netherlands

                                • #66223
                                  Wilhelm Pettersson
                                  Participant

                                    Hi,

                                    I am planning to construct a thread that will store the HL7 LAB messages in a SQL database, index against our hospital number and date but also a unique id.

                                    Then want to create query thread that will receive our hospital number and date range via HL7, in return send the HL7 messages related to the select.

                                    Has anyone does this or similar idea with LAB results?

                                    Also is possible to the get a copy of a TCL proc that will allow me to use SQL statements to send data to tables and get data from the tables?

                                    Help would be much appreciated.

                                    Thanks,

                                    Wilhelm

                                  • #66224
                                    Femina Jaffer
                                    Participant

                                      Jim,

                                      Would you be able to send your code to me as well?

                                      Thanks,

                                      fj

                                    • #66225
                                      Jim Kosloskey
                                      Participant

                                        Femina,

                                        I am on vacation and will be kind of out of touch until June 11th. Can you wait until then?

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

                                      • #66226
                                        Femina Jaffer
                                        Participant

                                          You bet!  Have fun on your vacation.

                                        • #66227
                                          Tom Rioux
                                          Participant

                                            Jim,

                                            That is dedication.  Checking Clovertech while on vacation!

                                            😀

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