SQLite and Cloverleaf

[Max Drown (Infor)]

Here are some sample scripts and files demonstrating how to use SQLite with Cloverleaf.

Online documentation: http://www.sqlite.org/docs.html and http://docs.activestate.com/activetcl/8.5/sqlite/doc/sqlite3.html

01) Create a database. Add this code to a file test.sql and then issue this command from the command line “sqlite test.db < test.sql". This will create a database called test.db. You'll see the database file in the directory. You can re-issue this command each time you want to reinitialize the database (empty the database).
— [code]—

-- Max Drown (Infor)

Topic

[Jim Kosloskey]

Thanks Max!

email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.

[Paul Johnston]

Max,

Another recent Clovertech thread discussed SQLite with CL version 6.0 .

I believe it was 5.7 and above when SQLite was bundled with Cloverleaf.

In your sample SQL scripts used with Cloverleaf is it independent of the CL version.  

Thanks

[Max Drown (Infor)]

Paul Johnston wrote:

Max,

Another recent Clovertech thread discussed SQLite with CL version 6.0 .

I believe it was 5.7 and above when SQLite was bundled with Cloverleaf.

In your sample SQL scripts used with Cloverleaf is it independent of the CL version.

-- Max Drown (Infor)

[Paul Johnston]

Excellent Max. !

Thank you .

[Femina Jaffer]

Thank you Max!  This is very helpful.

[Gene Salay]

Thanks Max!

One note – the activestate link doesn’t work as is  – due to a trialing period included in the link.

To use, copy the link into your browser and delete the period.

[Max Drown (Infor)]

Gene Salay wrote:

Thanks Max!

One note – the activestate link doesn’t work as is

-- Max Drown (Infor)

[Mark Thompson]

Hi Max,

Is it possible to connect the new database-inbound or database-outbound protocols to sqlite?  It so, an example of the Database Configurations tab and Configure Database Drivers…  window would be very helpful.

If this is not possible, are there examples somewhere showing how to connect to other types of databases?

- Mark Thompson
HealthPartners

[Max Drown (Infor)]

Hi, Mark. What version of Cloverleaf are you using?

-- Max Drown (Infor)

[Mark Thompson]

6.0.1

- Mark Thompson
HealthPartners

[Max Drown (Infor)]

I’ll upload a BOX. One minute …

-- Max Drown (Infor)

[Max Drown (Infor)]

I’ve attached a BOX with some sqlite and mysql examples to the original post. Please let me know if you have any questions.

-- Max Drown (Infor)

[Max Drown (Infor)]

I can’t get the BOX to upload to the forum. Here’s a download link instead.

https://www.dropbox.com/s/d65vwh80hy10bbc/db_examples_cis60.zip?dl=0

https://www.dropbox.com/s/egxuv6uaql734dc/db_examples_cis612.zip?dl=0

-- Max Drown (Infor)

[Mark Thompson]

Thanks Max.  Excellent examples.

- Mark Thompson
HealthPartners

[James Cobane]

Is there updated documentation regarding the use of the sqlite commands/extensions in lieu of gdbm?  The documentation with 5.8 as well as 6.0 still reference the old gdbm extensions.  In the release notes there is some brief references to the clsqlite package, but there is no information in the actual documentation.

Any info is appreciated.

Thanks,

Jim Cobane

Henry Ford Health

[Max Drown (Infor)]

James, would you please submit that question via Infor Xtreme? They may need to update the docs and your feedback would be helpful.

-- Max Drown (Infor)

[James Cobane]

Will do.

[Max Drown (Infor)]

I added a example database maintenance proc to the original post.

-- Max Drown (Infor)

[Kimberly Drew]

Hello –  We are updating a sqlite db with specific patient MRNs in the ADT process, which is in 1 site, and then we need to read that db to know if that is a valid patient to send the results and also documents to that receiving system. Both the results and document interfaces are in separate sites.  the ADT tclproc will be the only tclproc that will issue the “delete” or “insert” commands, where the other sites will only “select” from the same db to see if that patient exists. Support had mentioned that we could access the db from the other sites by using the full path, but that we needed to be carfeul to program SQLite  db lock handling into the scripts.  We have been successful in accessing the db from the other sites, but the question is about making proper lock handling that is there.  the sqlite databases that we have are only used by that same process, and are updated from a backend command line tcl.  this is new to us and would like to know if the Sqlite experts have any useful ideas.  thanks

[Max Drown (Infor)]

Kim, so far I have tried to keep my sqlite calls in the same process to avoid locking issue. I have been lucky enough to not need to do much lock handling yet. Are you seeing any lock issues in your processes?

-- Max Drown (Infor)

[Kimberly Drew]

No – we haven’t seen any locking issues as of yet, but this is only in our test cloverleaf where there isn’t as much activity as there would be in PROD.   we are trying to be proactive.

[Max Drown (Infor)]

Do you have yer code wrapped in catch statements like I do above?

Also, do you have a line like this?

Code:

DBCMD timeout 10000

-- Max Drown (Infor)

[Kimberly Drew]

no I don’t have the catch or the dbcmd timeout in the code.  I have attached the ADT proc and the results proc that i currently have in TEST for your review.

[Max Drown (Infor)]

I would recommend read through the code in this thread. The procs will provide a good solid foundation for error handling. You can then add on to them if you need more for locking.

-- Max Drown (Infor)

[Kimberly Drew]

ok thanks

[John Frazee]

Max, I get an error when I try to access the box you linked on this thread. Is it available someplace else I can download it, or can you post a new link, please?

[Max Drown (Infor)]

John Frazee wrote:

Max, I get an error when I try to access the box you linked on this thread. Is it available someplace else I can download it, or can you post a new link, please?

I updated the link. Please give it another try.

-- Max Drown (Infor)

[John Frazee]

Thanks, Max. FYI…There is also a post on 2/6/14 that has the erroneous link. I think it only got fixed on the original post.

[Max Drown (Infor)]

Mark Thompson wrote:

Thanks Max.

-- Max Drown (Infor)

[Max Drown (Infor)]

Kimberly Drew wrote:

Hello –

-- Max Drown (Infor)

[Kimberly Drew]

Thanks for the additional update.  Last week when you mentioned to me about using the catch and timeout in the tclscript, I also saw this same document within the sqlite documentation that your referring to for locking handling.

[Max Drown (Infor)]

For what it’s worth, when you get into cross-process db access (concurrency), especially with higher message volumes, you may want to consider using a database built for that, like MySQL or PostgreSQL, for better performance and user/access management. However, even in these cases, consider that SQLite is specifically built similar to Cloverleaf in that it will not lose data even in the case of the system crash, because changes are written to the disk and not to memory (making it potentially slower but safer). I.e., it may be worth the loss in speed to gain an improvement in data recovery.

-- Max Drown (Infor)

[Bob Richardson]

Greetings Max,

Running Integrator 6.1.0 on AIX 6.1 TL9 SP04 and Global Monitor 6.1.0

Question:  is there documentation available on how to access the SMATDB databases from Tcl?  or other language?

We are looking to convert from our current legacy flat files (idx/msg)

to SMATDB.   We would like to crank out a tool to replace a home grown message finder script (combo of ksh/perl/awk).

Please post your response.

And, thank you.

[Terry Kellum]

There is great Doco at:

https://www.sqlite.org/tclsqlite.html

If you want more in-depth information for using SQLite at the command prompt, the tutorial at:

http://www.tutorialspoint.com/sqlite/

is very good.

[Bob Richardson]

Terry,  thank you.

We will check out the links.

[Max Drown (Infor)]

The general SQLite documentation posted above should get you what you need. I have a post stickied and a link in my signature about working with SQLite in general including some example scripts.

If you are encrypting the database, then there are some additional steps that need to be taken in SQLite in order to decrypt the database. PM me or email me, and I’ll provide the details for decryption. I am not at liberty to post this information publicly due to the sensitivity of encrypted data,

-- Max Drown (Infor)

[Max Drown (Infor)]

I got this back from R&D,

There is a command line tool

-- Max Drown (Infor)

[Bob Richardson]

Greetings Max,

Is there a complimentary tool to READ these SMATDB files like the old hcismat for the flat file version?

Or do we need to create TCL scripts and code up such a tool?

Looking to provide a means to find/extract data messages behind the scenes versus IDE or Global Monitor – no save options at this time.

(We have Integrator and Global Monitor 6.1.0).

Thanks.

[Alice Kazin]

We wrote a script to pull out messages from Smat database.

However, the script can’t be used on the current file because Sqllite is not committing changes to smatdb immediately.  You might notice that  x.smatdb-wal file have a more current date than x.smatdb.  When the smatdb-wal gets above a certain size, then the records in smatdb-wal are copied into smatdb.  The Smat Database tool can view all records from smatdb and new records from smatdb-wal.

[Max Drown (Infor)]

From R&D,

Currently user has to use hcismatconvert to convert SMAT DB to flat files then use hcismat

AR12916 has been submitted to create a SMAT DB command line tool similar to hcismat.

-- Max Drown (Infor)

[Alice Kazin]

However, hcismatconvert won’t work if the new records aren’t committed to smatdb and are instead in smatdb-wal.

[Bob Richardson]

Thank you Max.

[Max Drown (Infor)]

Alice, please submit that as a request using Infor Xtreme. That’s definitely some we need to think about.

-- Max Drown (Infor)

[Alice Kazin]

We opened Inclident Ticket 8522462 previously with Infor.  It was my understanding that this is the normal behavior for Sqllite.

[Dustin Sayes]

Hello,

I’d like to initialize my database when my fileset-local thread starts up.

By initialize I mean… drop and create….

#Check if the table Exist and drop if it does

set sqldrop “DROP TABLE IF EXISTS mydatabasename.mytablename;”

#Then recreate the table

set sqlcreate “CREATE TABLE mydatabasename.mytablename ( create some rows );

#then run the commands

DBCMD eval $sqldrop

DBCMD eval $sqlcreate

I’ve added this code to the start mode of at tps, and placed the tps on the protocol directory parse. When the thread starts up, the log has an error, that my db is an unknown database, when doing DBCMD eval $sqlcreate.(while the database does exist, with a valid table, that contains data)

My setup is an inbound fileset-local :: xlate vrl -> sql :: database-outbound.

This thread will only run once a month, and each time it runs, I need the DB to be wiped before any new data is written.

My other option is to call a .sql script from the tps.

Is there a better way I should be going about this?

The db file is in the /exec/processes/myprocess dir, so access or permissions should be good – I am not certain why the log reports “unknown database mydatabase while executing “DBCMD eval $sqlcreate””

[Charlie Bursell]

I don’t see where you set DBCMD in the start=up.  Am I missing something?

I prefer to put code like this in a namespace.  The namespace runs as soon as proc is loaded.  Does not wait for start-up

[Dustin Sayes]

Charlie, what do you mean by name space?

I need this to only run once a month, when the thread timer goes off. The inbound file may contain hundreds of rows, so I don’t want want the DB to be initialized after each message.


switch -exact — $mode {
[code]
switch -exact — $mode {

[Charlie Bursell]

I did not see the open command in previous message.

Your code will work in start-up.  I just expressed a preference for doing it in a namespace.  If placed outside of any procs in the namespace it would only run when the proc is loaded – thread startup.

I don’t understand the error either without more info.  You could simplify by setting db like:

            set db database/mydb.db  

Since you are running in the process directory.  It would be more portable if you changed versions.

If the table you are dropping and recreating is the only table in the DB you could simply delete the file like:

      file delete $db

If file exists it will be deleted if not no error.

You can then open the DB and it will be recreated then add your table.

Wish I knew more and could help more

[Max Drown (Infor)]

Have you read through this thread?

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

Good starting point.

-- Max Drown (Infor)

[Max Drown (Infor)]

Added some information to the original post about auto-populating primary keys. Thank you Van and Dotty at Bay Health for helping working this out!

-- Max Drown (Infor)

[Author]

Replies