Inserting HL7 ADT into a SQL database

In your Xlate, in the Configuration, is the Output Record Format set to Database Schema, and the Connection and Table Schema set correctly?

And in the outbound thread, the Protocol is database-outbound, and in the properties, you have selected the same Database Connection and Table Schema? In the same properties, do you have SQL Statement or Stored Procedure selected?

If you restart the process that this outbound thread runs in, do you get these messages:

Dec 18, 2013 1:05:44 AM com.lawson.cloverleaf.dbprotocol.CJDDBProtocolOB doInit

INFO: Running the doInit method in database-outbound protocol

When you send a message and attempt to write to the database, do you get any error messages in the process log?

I’m not sure the error message “Disallowed Gateway Routing” is tied to the database protocol, but I was hoping for a database or jdbc error message in the log.

Sorry to ask basic questions; have you tried testing the Insert SQL in a database tool like Toad, SQL Server Manager Studio, MySQL Workbench or Oracle SQL Developer? i.e. copying the Insert statement as listed in the database-outbound protocol configuration, substitute a set of values that you would get out of the translate and execute that in a database tool/window, just to make sure the SQL is right, the values are right for the columns and the username/password specified on the Connection Properties are authorized to perform the SQL?

And you are using dbo for the schema, and not a specific table? When I use the Database Schema Configurator to create a schema, I hit the import button and it gives me a list of all the tables in that database, and I select one. I don’t use ‘dbo’; in SQL Server at least, that is the default table owner.

When I setup the Database Connection, in the Site Preferences, the schema there is dbo, but in the Database Schema Configurator, I select a table name and it is added to the Table Schema List, and then it is available in the Database Protocol configuration.

Sorry to hear that.

Greetings,

What Release of Cloverleaf Integrator were you running?

Just for the record please.

Thanks.

Out of curiosity, what other product are you moving to?

Going out on a limb here I would say the gateway message may be related to licensing.

Perhaps Cindy’s shop has a thread limit and she has hit it. Since we are not thread limited I don’t know what happens when you hit that limit.

Since she is attempting to us the built-in Database protocol one can surmise she is on 6.0.0 or later as that is the first appearance of this protocol as far as I know.

Anybody have any idea what that error state means – Max Drown do you know?

In a post from 2008, John Hamilton said something about this. But I don’t know what it means.

At the buttom of the thread configuration there are two selections.

External  or Vendor.  

Make sure on the inbound thread where the route is setup you have selected Vendor and the other is External.

https://usspvlclovertch2.infor.com/viewtopic.php?t=2509

Cindy,

Sorry to hear of your dissatisfaction.  We have been using Cloverleaf at Henry Ford Health System for years.  I would be curious to know what issues you experienced that led to your decision to change products.

Jim Cobane

Henry Ford Health

I realize Cloverleaf probably has a lot of a great components, but by the time you find them you are close to your project deadline.

Max, I agree with your assessment that Cindy’s frustration/consternation is based on inexperience with Cloverleaf.

And, I agree completely that this is a great forum with centuries of cumulative experience that it’s members readily share.

I’m not sure it’s a good idea, however, to categorically state that competing products shouldn’t be discussed here.

If, in fact, Cindy and team were able to do in one day with Mirth what they were unable to accomplish with Cloverleaf, that should at least merit discussion.  If it’s really that much easier to build interfaces with Mirth than with Cloverleaf, we should talk about that.  Cloverleaf is a mature product and will eventually die on the vine if it’s too hard for new users to adopt – or even if it’s perceived that it’s too hard…

I think we, in the Cloverleaf community, ought to discuss what other engines do well – with the constructive intent of making Cloverleaf better/easier/stronger, of course.  I don’t think that Cloverleaf has a corner on the good-engine-ideas market, and it’s certainly not perfect.  There are plenty of smart people, with great ideas, working on those competing engines and we should leverage that if we can.

That’s fine as long as it’s constructive and not a sales pitch.

thank you – no harm done here; I get frustrated with our HIS every day.

I’ve only got 2+ years in Cloverleaf, but I like working with the databases and I copied what others had done with ODBC.

So you want to cycle the table, as in you want to make sure you don’t miss any records, and also make sure you don’t double-process records?

Is this happening in the database? So Cloverleaf could be inserting rows all the time, and you want the database process to pick up the next chunk of records? If that is the concern, I would add a column to the table to hold a timestamp, and start each processing cycle by creating a key or timestamp value and update the table – all rows where the timestamp column is null – set the timestamp column to that value. Then loop through all rows having that timestamp value. It skips over any new rows that get inserted, it will get those in the next cycle.

Is that what you are talking about?

Cindy,

If it were me I would require the use of a Stored Procedure and take the SQL stuff out of Cloverleaf. You would still need to invoke the Stored Procedure from Cloverleaf and arrange for a method for the Stored Procedure to tell you it has processed the message you sent and is ready for the next message.

That is the way we do things here with the ODBC drivers available from Cloverleaf (not the DB Protocol) and it works well.

This way the application business rules reside with the receiving system where I believe they belong, and not in the engine.

We use Stored Procedure parameters to provide the message and to receive the ‘acknowledgment’ from the Stored Procedure. If we do not receive a positive ‘acknowledgment’ from the Stored Procedure we throw an Alert using the information provided in the return parameter and stop the flow of messages until the issue is rectified.

The above is a simplified description of what goes on but is essentially accurate.

There are others out there using some semblance of our process and some are using our process as is.

The last person to deploy the process as is had their DB being updated in a few days – that included someone writing the Stored Procedure.

If you would like to discuss this process in more detail email me and I will walk through it with you.

Two things you could consider doing

1) add a column to the database that indicates the column has been processed and have the stored procedure set the column to a 1 when finished processing each row (the query would only pull rows w/o the 1)

2) have the stored procedure delete the row after it’s been processed

ok – I needed to look at your specs again – yes, if you can’t indicate your last ‘high-water-mark’ in the table, then you have to do it somewhere else. It would be nice if you could store the last-used-date in a file and read it in at runtime and use it in your SQL query/stored procedure as your “greater-than’ date, but unfortunately the database inbound protocol does not give you a chance to execute any tcl code before executing the query, so there is no way to change your query at run time.

I put in an enhancement request for ‘global variables’ that could be used in the database protocol query but I don’t know if this has been approved and would not be ready in time anyway.

If I think of the database protocol as just another protocol that gets data, like a fileset ftp, then I would expect that the file is deleted or renamed after I pull the data from it. I would not expect to deal with a file that is always there and usually has the same rows in in it and that I can’t change the rows.

But I guess with this database table that is what you have. You could just read all rows and then use tcl code in your inbound proc to exclude the rows that don’t match the last-used-date, but thats a lot of processing for a (probably) small amount of rows.

The only remaining option is to do more of this logic in the database. If you can get a new table created that only holds the last-used-date, then your stored procedure can read the value, use it for processing, and put in a new value at the end of processing. Your shop may have such a ‘look-up/key value’ table already that you could use to store single values like a last-used-date.

Peter, please put in a feature request for the pre-tcl area, too.

You don’t need to jump through all these hoops to get the latest

Check your DB docs.  There will be a method to select last update time as maintained by the system.  Simply maintain that last time and query for values since.

IN Oracle I think it is something like:  SELECT SCN_TO_TIMESTAMP(MAX(ora_rowscn)) from emp;

But it has been a while

Here’s some feedback from R&D. They have a question. If you would please try to answer, I’ll pass the info back to R&D.

1. About the error message “Disallowed Gateway Routing”, it

Cloverleaf is a rock-solid tool for performing integrations, but as any tool, it has it’s strengths and weaknesses.

Cloverleaf is extremely strong in it’s internals, translation, and I love the TCL language for working with HL7 messages.  There are a lot of tools in the box to keep you from re-inventing the wheel, and allowing you to build custom code to do anything that you want with the messages.

The choice of TCL as the “glue” for the User Points Of Control allow you to use a mature List-rich scripting tool to tear apart messages down to a very fine level and to manipulate data in any way possible.  TCL Has many fine add-on tools such as mysqltcl that work wonderfully within the engine.

The use of PDLs for Input and Output allow customized code to work around problems.

Where it is weak (IMHO) is in the connections to other systems.  There are other tools that have 10 simple questions (more or less) in about 7 or 8 classes of connections that hand you a great connection over the whole space of commonly used integrations.  MLLP over TCPIP, FTP, SFTP, HTTP, SSL, WebServices, SOAP are all configurable from the GUI and work well with a minimum of fuss.  If I had a problem connection that I needed to fuss with byte streams and uncommon delimiters, then Cloverleaf is the tool that I would use.  If I want to get quickly past the connection process without any special training, then Cloverleaf can be a bit daunting.

Cloverleaf is a enterprise-level tool.  The comparison to Oracle is very apt.  The problem with that is that there are many other databases out there that are handling jobs as big as Oracle with different strength/limitation sets.  The trick is to serve both sides of the market, both the deep and the shallow.

Terry, I would like to see Cloverleaf improve in that area, too. If you have specific suggestions to help R&D, please send them to me and I will forward them on to R&D. I would be specifically interested in use-cases and comparisons to other tools. Screenshots will be helpful.

For what it’s worth, R&D is working hard to streamline web services connections. You will be seeing improvements rolling out in new releases.

What about using a counter set to a key value in the SQL file?

CtrInitCounter CTR

where initial value is the next key in the file.

The next time you ran, you would do  CtrNextValue CTR to be handed the integer for the next key.

I have never tried this, but a question would be if the “create” command would overwrite the existing counter without complaining…

The ctr commands add a lot of extra I/O, especially for high volume interfaces and sites.