Need to decide between SQLite and ODBC setup

[Neema Kapadia]

Hi! Everyone,

I am new to Cloverleaf.  At my organization, we have been using eGate and have lots of ELS (Event Linking and Sequencing) setup along with a heavy setup using ODBC (SQL Server).  

We are working on transitioning the eGate interfaces over to Cloverleaf and we are at a point where we need to decide if we should use SQLite or ODBC (SQL Server 2008).

Here is one scenario:

1) Cloverleaf receives an HL7 message (A05) from the Hospital system with empty PID-3 (MRN) – this message needs to be sent out of Cloverleaf as an A04 with PID-18 copied into PID-3 before we send to the other system.  We also write the PID-18 to the SQLite or ODBC (SQL) table.

2) Wait for an A04 with same PID-18 (read the SQLite or ODBC table each time an A04 is received.

3) If match is found we delete the record from the table and we have other additional logic that we have in place.

We will have 100s of A05s with empty PID-3 and 1000s of A04s on a daily basis

Topic

[Max Drown (Infor)]

Personally, I would use SQLite because a) it easy to use, fast, efficient, and written specifically for Tcl, and b) it is local (not a server out on the network).

-- Max Drown (Infor)

[Max Drown (Infor)]

Neema Kapadia wrote:

Will SQLite be able to handle so many transactions?

-- Max Drown (Infor)

[Neema Kapadia]

Max,

Thank you for your response.  

The reason for my post was the fear of the unknown because I am so new to Cloverleaf, however, it sounds like you are very confident with SQLite and its performance.  Your response helps a newbee like me make the right decision.

Thanks again.

Neema

[Max Drown (Infor)]

Very confident!

-- Max Drown (Infor)

[Neema Kapadia]

For SQLite we need to know:

1) If there is a limit on number of records that can be written to in the SQLite database table.

2) Is there a limit on number of columns allowed?

3) Is it possible and advisable to add a column on the fly after the SQLite database table has been in place and has data in it?

    – What options do we have if the answer is No.

4) Is it possible to setup ODBC connection to the SQLite table for ease of running queries?  We have certain ODBC tables we currently use in eGate that has over million records.

Thank in advance for your responses.

[Chris Williams]

Many of the limits depend on values set at compile time. This link discusses most of them. <a href="http://www.sqlite.org/limits.html&#8221; class=”bbcode_url”>http://www.sqlite.org/limits.html

[Peter Heggie]

There are ODBC drivers for SQLite; I’m not sure these are supplied by Cloverleaf. I looked at the ODBC offering for Cloverleaf and I don’t see an entry for SQLite in the odbc.ini file, so I don’t think it is offered out of the box.

To access the SQL Server tables that you currently use with SeeBeyond, you need either the ODBC add-on product from Cloverleaf, called Data Integrator, which allows you to use ODBC functions from anywhere tcl can be used, or you can use the Cloverleaf 6.0 DB Protocol, which allows you to connect to a database as a protocol (like an application) or you can use the Java UPOCs to hold Java code that invokes a JDBC function to access your database. Using the 6.0 DB protocol has limits, in that you can only connect as a protocol (in the ‘beginning’ of a message transaction, basically to get data from the database and that data will be the message sent through Cloverleaf, or in the ‘end’ of a message transaction, basically to insert or update a DB table using the data in the Cloverleaf message).

But it will be complex to access both SQLite and your SeeBeyond database in the same tcl code. It can be done, but there is no direct connection between the SQLite ODBC and the other database ODBC.

So I guess you are comparing one approach where your SQL Server data is migrated to SQLite and then use SQLite functions in Cloverleaf to access the data, or another approach where you keep your data in SQL Server and use ODBC in Cloverleaf to access that data?

Peter Heggie

[Jeff Dinsmore]

You don’t need an ODBC front-end to SQLite – just open the DB and execute your SQL code directly from Tcl.

For access to an external ODBC-compliant DB, we use TclODBC.  We had to compile for Linux, but it’s working great.  If you’re running on Windows, there are pre-compiled installs available online.  You’ll also need a driver for the particular DB flavor you’re connecting to.  We use the FreeTDS ODBC driver for connection to MSSQL DBs.

I echo Max’s confidence in SQLite.  I’ve used it for many years and, properly indexed, it’s very quick.  We have tables with hundreds of thousands of rows that are accessed for every message that passes through many of our interfaces.

The primary thing you need to consider for SQLite is whether you need simultaneous access from more than one DB reader/writer at a time.  If you do, you need to handle lock timeouts/retries yourself.  If you have a need for lots of concurrent access, you’d probably be happier with ODBC and a commercial DB.

Jeff Dinsmore
Chesapeake Regional Healthcare

[Neema Kapadia]

Thanks to all of you for your response.  😀

[Max Drown (Infor)]

Here is some info on Cloverleaf and SQLite to get you started: https://usspvlclovertch2.infor.com/viewtopic.php?t=6686

-- Max Drown (Infor)

[Author]

Replies