› Clovertech Forums › Read Only Archives › Cloverleaf › Cloverleaf › How to write msgs from thread to SQL Server DB?
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
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.
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
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.
OMG, that’s a lot of code for me to enjoy 😀 I will look into them and your help is very much appreciated!!!
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.
Hello, Jim. Would you mind sendin me a copy of you tcl as well
hosun@tegh.on.ca. Thanks
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
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,
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.
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.
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
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.
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
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
Jim,
Would you be able to send your code to me as well?
Thanks,
fj
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.
You bet! Have fun on your vacation.
Jim,
That is dedication. Checking Clovertech while on vacation!
😀