Inbound Database Protocol

[Daniel Murphy]

Hey!

I have searched for previous topics and scoured through anything related to using databases in Cloverleaf. I made many attempts through various levels of success to get the databases working as I need them to, until this point. So I am open to going about this in a different direction or trying something new. So far, most of what I have done works. But…

I am trying to read from a SQL Server 2012 R2 database and send a message based upon the results read.

I am able to get the messages generated, but I cannot figure out how to get the Read Success Action to trigger or how I am supposed to set that up to tell the stored procedure which record I need to make a write to so that it doesn’t send it again.

Read Action setup:

Action Options Type: Stored Procedure

Content: {call GetHL7MessageToSendSp()}

Scope: “Each row as a message”

Read Success Action:

Action Options Type: Stored Procedure

Content: {call MarkHL7MessageDateSentSp()}

I am not sure if will work here or not. The intention was to see if I could just pass the entire HL7 message back to set it from that.

The short list of fields in the table named [HL7Message]:

Topic

[Peter Heggie]

There does not appear to be any way to get the message id of the message generated by Cloverleaf (which holds the contents of a row returned by the Inbound protocol query), not in the Read Success action anyway.

From the documentation it appears that there is no message or column value available to the Read Success activity/query. The examples are based on the use of a flag, basically, that you can use some column with a fixed/expected value to select your data (flag = Y), and then in your Read Success action, you set that flag = N where that flag = Y, and that you believe that no rows have had that column set to Y in the time between the Read and the Read Success.

You could have a more complex stored procedure that first selects all the eligible rows, marks them (or moves them to a special ‘in-progress’ table), and then returns those rows to the protocol. The Read success must assume that all marked rows are now eligible for completion (whatever that means to you). This at least guarantees that rows are only processed once. But if there is a Read Failure, then the Read Failure stor proc must restore the previous state, and it must assume that ALL rows in the ‘in-progress’ state are eligible for restore.

I have not had a chance to play with the new global variables. But even if a global variable can be used in a Read and Read Success query, there is no TCL UPOC that allows you to increment it, once it is used.

The new version of Cloverleaf introduced variables into some of the other protocols (Fileset-Local, etc.) that would automatically include things like a date stamp or other context value into an output filename. Something similar for the database inbound protocol would solve this problem once and for all. Example: provide a new field that contains tcl, or even just a simple incrementtng variable. You can then guarantee that you provide a unique value in your query that can be used to mark rows, and, assuming that your Read Success query has access to this same variable (before it is changed), it can be used to ‘complete’ or delete the processed rows.

There are other methods that could be used, but require changes to Cloverleaf, all of which basically provide a way for a unique value to be used in both the Read and the Read Success actions, and also provide a way to increment/change it, once the transactions are processed.

Peter Heggie

[Daniel Murphy]

Thank you Peter!

This gives me some insight and at least a new direction from which I can try to get this working.

Just to clarify: I am NOT wanting the MessageId from the messages generated within Cloverleaf itself. MessageId happens to be an Identity Primary Key and Index to my table in SQL Server. The MessageId from THAT table is what I would like to pass around and use to control updates to which messages were sent or not 🙂

Incidentally, I basically did what you suggested prior to my writing the initial post, but within a single execution of the stored procedure. All messages ready to be sent were queried and placed them into a variable table. After-which, it would update the actual table with the date sent and then return the contents of the variable table to Cloverleaf. Peculiarly, the variable table managed to get nothing to return; so no messages were sent but the date sent was applied. I believe there is an order of operations that took place that logically should not have. Either way, that method was flawed, and I knew that while writing it. The issue is that it assumed the messages were sent successfully all of the time, and that may not be the case. I was trying out different ideas, just to see what would and would not work.

I will edit the stored procedure for Read Success and change my backend logic – might need to add a bit flag to avoid making a new table *just* for this purpose. This will mark all unsent messages with the DateSent. Upon failure, nothing changes, and those will show back up in the next run. No clean-up will be needed 🙂

[Daniel Murphy]

I finally got the entire process to work! Thanks again, Peter!

You need to be simple with stored procedures with Cloverleaf. It doesn’t like running a stored procedure that does multiple things before it returns back to Cloverleaf.

I had to basically separate all of my processes that work up to getting the data from the database before sending to Cloverleaf. After the Read is successful, it was pretty straight forward, but you need that pre-cursor setup in place for it.

I’ll make a write-up and post it of how I made this process work for those that come later to try and understand this process. There are a bunch of moving parts, and if someone tried to do this based on the documentation alone, I believe they would not fare well.

[Peter Heggie]

thank you for documenting your steps – I would have hit the same roadblocks (putting everything inside one big stored procedure).

Peter Heggie

[Daniel Murphy]

This is a major step for me. I have several projects that RELY upon being able to utilize this functionality.

I cannot imagine someone else that has less SQL experience or Cloverleaf experience trying to set this up. Thankfully, when I was first starting out in Software Engineering and DBA, he taught me to always think outside of the box; when you finally find a solution, try it over again and see if there is another approach that would be better.

Hopefully, in the next few days, I can put together a document that details out the process and my findings 🙂

[Robbie Parker]

Daniel-

Nice work here and I for one would greatly enjoy seeing any documentation you would be able to present.

[Daniel Murphy]

Thanks Robbie!

What I will be sharing will hopefully be a treat for everyone; as this has been many months in the making as well as some really neat concepts and ideas I came up with just to work within the confines of Cloverleaf. No doubt, it has taken me a LONG time due to interruptions at work and other high priorities that come along. I am glad to finally be in the light outside that long dark tunnel. 🙂

I’m excited to give my work out for everyone to benefit from. It is one solution, and there may be other better ways to go about it. At least it IS a working solution! I will do my best to make it easy to understand and follow.

Looking at my schedule, it looks to be next week before I will be able to write up that document. It will be written, as I need that for work too. It is far easier to read over one’s notes than to figure it all out from scratch – that’s true of the person that makes the notes. Good documentation saves a *lot* of time in the long run 🙂

[Author]

Replies