Cloverleaf 6.0 amd SQL Server Database

[Femina Jaffer]

Hello,

Just wanted to know if we can connect to SQL Database on SQL Server to from version 6.0 via ODBC, JDBC or Jave Gateway connection?  We are on a AIX box.  Can we write directly back to the database?  Do we need to purchase anything extra or can this be done without additional purchases?

Thank you,

fj

Topic

[Jim Kosloskey]

Femina,

There is a Database Protocol available with Cloverleaf 6.0 it uses JDBC.

I have taken a quick look at it and it does not at this time meet our needs but may serve you well.

email: jim.kosloskey@jim-kosloskey.com 30+ years Cloverleaf, 60 years IT – old fart.

[Max Drown (Infor)]

Femina, yes, in CIS 6.0 we added database-inbound and database-outbound protocols. You can also use your own or third-party Java code using the built-in Java driver.

I have been using these protocols with a high level of success, and I am very pleased with them. We will be improving the functionality in future releases. Please submit enhancement requests via Infor Xtreme.

-- Max Drown (Infor)

[Brandon Grudt]

I have recently implemented TclODBC for Windows, but it is also available for Unix, if your company is okay with freeware.  It allows you to query ODBC data sources.  If I were you, I’d play around with it a bit, but the integration was seamless for me.

[Femina Jaffer]

Thank you all for your replies.  I will pursue these avenues and give it a try.

Thanks,

FJ

[Jim Kosloskey]

Femina,

Of course if you have the add-on Data Integrator then you can use the DataDirect (Progress Software) drivers. We use them here as we have Data Integator.

Means again writing your own Tcl. But if you want to reference a DB via Stored Procedures we have procs which are argument driven and possibly can be used out of the box or you can use as a starter.

email: jim.kosloskey@jim-kosloskey.com 30+ years Cloverleaf, 60 years IT – old fart.

[Peter Heggie]

We use the Database Protocol in Cloverleaf v6. We use it for both Inbound (Select statements) and Outbound (Insert or Update statements). We also use it with SQL Server Stored Procedures (for Insert and Update). We use it for real-time and batch. We have previously implemented a Data Integrator solution to use SQL Server as a lookup table, but have decided that we do not want to risk holding up our main HIS input feed if the SQL Server / table should become unavailable, so in the future, for lookup tables that are not Cloverleaf Tables, we will use SQLite locally. Here are two examples of how we used the DB protocol:

1) audit table – we keep a 30 day history of all ADT messages coming out of our HIS – we extract and store the mrn, account number, time stamp, message type, HIS function name, patient class, patient type, patient location, admit date and discharge date in a SQL Server table. We use it for internal troubleshooting only. It saves us a lot of time. We created the table in SQL Server and created a stored procedure to Insert rows into this table. We use the new Cloverleaf schema utility to import this table definition in Cloverleaf and it builds a vrl for this table. Our translator is configured to use this vrl as the output format. The destination thread is congfigured to use the DB Protocol Outbound, and we selected the stored procedure option and typed in the stored procedure name and the parameters. Cloverleaf makes some assumptions here that the stored procedure parameters are made up of (only) the column names found in the table schema. You still have to manually match up the order of the s.p. parameters to the order of the translator output.

So this works well. We had to make sure the table columns that were a datetime type received the string data in the correct format (from the translate) so that data conversion would work. I am making a rough guess that we are a small to medium size shop so even though every ADT message is processed ilke this, it still is not so much that it slows down the system. I don’t know if Cloverleaf keeps the DB Outbound connection open; I hope so.

2) coding and billing charge interface – this interface is in two parts; the first is similar to the above where charge records are stored in a SQL Server table in real-time. A stored procedure is used. If an entry for the account number already exists, it is updated. Once a day, a cron job stops the outbound thread, and starts up a process in a batch site which has an Inbound DB Protocol thread. This is a simple Select statement, not a stored procedure. The entire table is read in; it is configured so that each table row becomes a message. It is also configured such that a successful post-action SQL statement is executed, which is a Delete

so this clears the table after the records are read. The messages are traslated and sent to a file thread. The cron job waits for this to complete and then stops the batch process and starts the online thread. This is working well in test and will go into production next month.

The DB Protocol cannot be used from within TCL or Translates, but I hope this comes in a future enhancement, along with some more GUI functions to build the Inbound or Outbound configuration (stored procedure statement or SQL), and possibly a configuration option to keep the connection open (maybe a timeout parameter).

Peter Heggie
PeterHeggie@crouse.org

[Jim Kosloskey]

Peter,

Using the JDBC (Database) protocol, how do you find out if the Stored Procedure you invoked has worked (besides the SQL_SUCCESS return from the call which simply tells you the Stored Procedure was successfully invoked not that it actually functioned)?

For example if the Stored Procedure is attempting to do an insert and the insert fails (maybe db full) how do you find that out so you don’t send any more transactions?

Once you find out what actions can you take to stop message flow – and how do you do that?

Thanks,

Jim

email: jim.kosloskey@jim-kosloskey.com 30+ years Cloverleaf, 60 years IT – old fart.

[Peter Heggie]

Jim, it is a simple insert so functionally working and successful execution are the same thing. That being said, there does not seem to be any option to reference arguments to the stored procedure that are defined as output or in/out. We do have the post-success and post-fail SQL functions, but these are limited because we can’t pass the values of the output arguments to them, to get a granular response. And we can’t pass them to a post-action procedure (like a Send OK Proc). But we can execute the Send OK Proc and SendFail Proc (I’ve gotten them to fire, based on the results of the stored procedure call, but some of those times (during a fail test), the process went into a panic). It would be nice if the Send OK or Send Fail procs had access to the results of the stored procedure call.

But then what? If the database is unavailable or we have some other fatal error, do we shutdown the outbound thread? Can a tcl proc shutdown the thread it is running in?

Basically.. we want to queue up the messages until the problem is resolved. That is overly simplistic though, right? I guess we have not done anything really complex with the database, especially since it is an endpoint, and does not hold the entire process hostage if something is wrong with it. Maybe I / we need to think about how databases are used and establish the best practise for handling problems. It would be nice to treat a database problem like any other endpoint (ancillary) problem.

Peter Heggie
PeterHeggie@crouse.org

[Jim Kosloskey]

Peter,

I am in a conference this week (interstingly enough talking about how we deploy ODBC in Cloverleaf).

I would be happy to show you how we do things here where I think we can do most of the things you want. We do it with the Cloverleaf bundled Data Direct ODBC drivers and not the dtabase protocol.

I asked the question because I saw that as an omission in the the database protocol fo Cloverleaf 6.0.

If you are interested, email me and we can talk.

email: jim.kosloskey@jim-kosloskey.com 30+ years Cloverleaf, 60 years IT – old fart.

[Femina Jaffer]

Thanks you all for your replies.  We will research what is the best option after reading your comments.

Thanks again!

[Author]

Replies