Basic Tutorial for Using SQLite With Cloverleaf

[Max Drown (Infor)]

Basic Tutorial for Using SQLite With Cloverleaf

This is a basic tutorial on using SQLite with Cloverleaf.  A Cloverleaf BOX (created using CIS 19.1) is attached that demonstrates using SQLite with TPS, XLT, and the Database-Inbound Protocol. The examples included in this tutorial and in the attached BOX are simple and are only meant to be a starting point to help with understanding the basics.

(Note: The forum is stripping out the spacing of the code blocks. I’ve attached the TPS and XLTP scripts as attachments to the post.)

Create a database

Place this code into a file called firefly.sql. The name of the file and the file extension are arbitrary.

-- Usage: sqlite firefly.db < firefly.sql

DROP TABLE IF EXISTS firefly;

CREATE TABLE firefly (
db_id integer primary key,
date_time varchar(14),
id_num varchar(250),
lname varchar(150),
fname varchar(75),
mname varchar(25),
dob varchar(24),
gender varchar(10),
acct_num varchar(250),
ssn varchar(16),
location varchar(40),
room varchar(40),
bed varchar(40),
visit_num varchar(250),
admit_date varchar(24),
discharge_date varchar(24)
);

Create, initialize, or reintialize the database

sqlite firefly.db < firefly.sql

View the database

sqlite firefly.db
.schema
.exit

Populate a database table

Place this code into firefly_insert.sql

insert into firefly (date_time, id_num, lname, fname, mname) values ("201303011101", "1111", "REYNOLDS", "MALCOM", "");
insert into firefly (date_time, id_num, lname, fname, mname) values ("201303011102", "1112", "WASHBURNE", "ZOE", "Z");
insert into firefly (date_time, id_num, lname, fname, mname) values ("201303011103", "1113", "WASHBURNE", "HOBAN", "H");
insert into firefly (date_time, id_num, lname, fname, mname) values ("201303011104", "1114", "SERRA", "INARA", "");
insert into firefly (date_time, id_num, lname, fname, mname) values ("201303011105", "1115", "COBB", "JAYNE", "");
insert into firefly (date_time, id_num, lname, fname, mname) values ("201303011106", "1116", "FRYE", "KAYLEE", "");

Execute the SQL statements against the database

sqlite firefly.db < firefly_insert.sql

View the database

sqlite firefly.db
select * from firefly;
select fname,lname from firefly;
.exit

Use Tcl to query the database

Reinitialize the database

sqlite firefly.db < firefly.sql

Place this data into a file called firefly.dat

9901,Reynolds,Malcolm,,19720621,M,000901,999999999,EME,9,A,83849,20130801,
9902,Washburne,Zoe,,19730701,F,000902,888888888,BAL,8,B,83847,20130802,
9903,Washburne,Hoban,,19740811,M,000903,777777777,FL1,7,B,83846,20130803,20130803
9904,Serra,Inara,,19750913,F,000904,666666666,FL5,6,D,83845,20130804,
9905,Cobb,Jayne,,19761002,M,000905,555555555,BAK,5,A,83844,20130805,
9906,Frye,Kaylee,,19771130,F,000906,444444444,TOP,4,A,83843,20130805,20130817

Insert the data into the database

Place this code into a file called firefly_insert.tcl

#global HciSiteDir
#set db $HciSiteDir/data/db/test.db
set db firefly.db
sqlite DBCMD $db
DBCMD timeout 10000

set fileName firefly.dat
set f [open $fileName r]
fconfigure $f -translation binary
set data [read $f]
close $f

set date_time [clock format [clock scan now] -format %Y%m%d%H%M%S]

foreach line [split $data \n] {
if {$line == ""} {continue}

set line [split $line ,]

set id_num [string trim [lindex $line 0]]
set lname [string trim [lindex $line 1]]
set fname [string trim [lindex $line 2]]
set mname [string trim [lindex $line 3]]
set dob [string trim [lindex $line 4]]
set gender [string trim [lindex $line 5]]
set acct_num [string trim [lindex $line 6]]
set ssn [string trim [lindex $line 7]]
set location [string trim [lindex $line 8]]
set room [string trim [lindex $line 9]]
set bed [string trim [lindex $line 10]]
set visit_num [string trim [lindex $line 11]]
set admit_date [string trim [lindex $line 12]]
set discharge_date [string trim [lindex $line 13]]

if {[catch {DBCMD eval {
insert into firefly (
date_time, id_num, lname, fname, mname, dob, gender,
acct_num, ssn, location, room, bed, visit_num, admit_date, discharge_date)
values (
$date_time, $id_num, $lname, $fname, $mname, $dob, $gender,
$acct_num, $ssn, $location, $room, $bed, $visit_num, $admit_date, $discharge_date)
}} err]} {
echo "Error in database processing: $err"
}
}

Execute the tcl code

tcl firefly_insert.tcl

View the database

sqlite firefly.db
select * from firefly;
select fname,lname from firefly;
.exit

Select data from the database

Place this code into a file called firefly_select.tcl

#global HciSiteDir
#set db $HciSiteDir/data/db/firefly.db
set db firefly.db
sqlite DBCMD $db
DBCMD timeout 10000

set query "select * from firefly"

if {[catch {
DBCMD eval $query {
puts "db_id: $db_id"
puts "date_time: $date_time"
puts "id_num: $id_num"
puts "lname: $lname"
puts "fname: $fname"
puts "mname: $mname"
puts "dob: $dob"
puts "gender: $gender"
puts "acct_num: $acct_num"
puts "ssn: $ssn"
puts "location: $location"
puts "room: $room"
puts "bed: $bed"
puts "visit_num: $visit_num"
puts "admit_date: $admit_date"
puts "discharge_date: $discharge_date"
puts ""
}
} err]} {
echo "Error in database processing: $err"
}

if {[catch { DBCMD close } err]} {
echo "Error closing database: $err"
}

Example using TPS to update the database

proc tps_sqlite_firefly_update { args } {
global HciConnName HciSiteDir
set module "tps_sqlite_firefly_update/$HciConnName"
keylget args MODE mode
set debug 1
set dispList {}

switch -exact -- $mode {
start {}

run {

set mh [keylget args MSGID]
set msg [msgget $mh]
set msg [split $msg ,]

if {$debug} {puts "DEBUG: $module: msg: $msg"}

set date_time [clock format [clock scan now] -format %Y%m%d%H%M%S]
set id_num [lindex $msg 0]
set lname [lindex $msg 1]
set fname [lindex $msg 2]
set mname [lindex $msg 3]
set dob [lindex $msg 4]
set gender [lindex $msg 5]
set acct_num [lindex $msg 6]
set ssn [lindex $msg 7]
set location [lindex $msg 8]
set room [lindex $msg 9]
set bed [lindex $msg 10]
set visit_num [lindex $msg 11]
set admit_date [lindex $msg 12]
set discharge_date [lindex $msg 13]

set db $HciSiteDir/data/db/firefly/firefly.db
sqlite DBCMD $db
DBCMD timeout 10000

# If id_num exists, update the record, else insert the record

set query "select db_id from firefly where id_num=$id_num"
set db_id [DBCMD onecolumn $query]

if {$db_id == ""} {
if {[catch {DBCMD eval {
insert into firefly (
date_time, id_num, lname, fname, mname, dob, gender,
acct_num, ssn, location, room, bed, visit_num, admit_date, discharge_date)
values (
$date_time, $id_num, $lname, $fname, $mname, $dob, $gender,
$acct_num, $ssn, $location, $room, $bed, $visit_num, $admit_date, $discharge_date)
}} err]} {
echo "Error in database processing: $err"
}

if {$debug} {puts "DEBUG: $module: Inserted id_num: <$id_num>"}
} else {
if {[catch {DBCMD eval {
update firefly
set
date_time=$date_time, id_num=$id_num, lname=$lname, fname=$fname, mname=$mname, dob=$dob, gender=$gender,
acct_num=$acct_num, ssn=$ssn, location=$location, room=$room, bed=$bed, visit_num=$visit_num, admit_date=$admit_date, discharge_date=$discharge_date
where db_id=$db_id
}} err]} {
echo "Error in database processing: $err"
}

if {$debug} {puts "DEBUG: $module: Updated id_num: <$id_num>"}
}

if {[catch { DBCMD close } err]} {
echo "Error closing database: $err"
}

lappend dispList "CONTINUE $mh"
}

time {}
shutdown {}
default {}
}

return $dispList
}

Example using XLTP to query the database

proc xltp_sqlite_firefly_query {} {
upvar xlateInVals xlateInVals xlateOutVals xlateOutVals
global HciSiteDir

set db $HciSiteDir/data/db/firefly/firefly.db
sqlite DBCMD $db
DBCMD timeout 10000

set id_num [lindex $xlateInVals 0]

set query "select lname from firefly where id_num=$id_num"
set result [DBCMD onecolumn $query]

set xlateOutVals 


if {[catch {DBCMD close} err]} {
echo Error: Error closing the database: $err
}
}

• This topic was modified 4 years, 7 months ago by Max Drown (Infor).
• This topic was modified 4 years, 7 months ago by Max Drown (Infor).
• This topic was modified 4 years, 7 months ago by Max Drown (Infor).
• This topic was modified 4 years, 6 months ago by clovertech_admin.
• This topic was modified 4 years, 6 months ago by clovertech_admin.
• This topic was modified 4 years, 6 months ago by clovertech_admin.
• This topic was modified 4 years, 6 months ago by Max Drown (Infor).
• This topic was modified 4 years, 6 months ago by Max Drown (Infor).
• This topic was modified 4 years, 6 months ago by Max Drown (Infor).

Attachments:

You must be logged in to view attached files.

-- Max Drown (Infor)

Topic

[Paul Stein]

Max – this is great, thank you! I was able to use this and do a proof of concept on more than one use case referencing this documentation.

Running through this tutorial did bring up a question:

Do the database-inbound/outbound protocol threads have the ability to read/write/update/delete from an encrypted db (non-SMATdb)?

This could be for situations when you are storing the PHI on encrypted sqlite db on the same servers as Cloverleaf. I realize you could do this via tcl PRAGMA key but was curious as to the options of doing in a protocol thread.

[Max Drown (Infor)]

Yes, the db protocols can write to SQL Server, Oracle, SQLite, and you can add your own JDBC drivers for other databases, too.

I might be misunderstanding your question? Please clarify or add more detail.

-- Max Drown (Infor)

[Paul Stein]

I am trying to use db protocol thread with an encrypted sqlite db but when i try to use the DB schema configurator I am getting this error:

 

[SQLITE_NOTADB] File opened that is not a database file (file is encrypted or is not a database)

[Jerry Hendrickson]

Hi Max.  I have written a number of scripts to update and read sqlite databases, but now I need to do a loop.  I need to write a record to the database for ORMs containing PID-3, PV1-19, PV1-44, OBR-2.1, and OBR-4.1 for each OBR segment.  I attached my proc to do a single insert.  How do I loop it to insert for multiple OBRs?

 

Thanks, Jerry.

[Max Drown (Infor)]

Paste the code here, please. I don’t see the attachment.

-- Max Drown (Infor)

[Max Drown (Infor)]

Paste the code here, please. I don’t see the attachment.

-- Max Drown (Infor)

[Jerry Hendrickson]

Hi Max.  Here is the code.

 

proc tps_labcorp_check_db_new { args } {
global HciConnName HciSiteDir ;# Name of thread, site
keylget args MODE mode
set ctx “” ; keylget args CONTEXT ctx
set uargs {} ; keylget args ARGS uargs
set debug 0
set module “tps_labcorp_check_db_new/$HciConnName/$ctx”
set dispList {}

switch -exact — $mode {

start {}
run {
if {$debug} {echo DEBUG: $module: Begin.}

keylget args MSGID mh
set msg [msgget $mh]

set db $HciSiteDir/data/sqlite/labcorp.db
sqlite DBCMD $db
DBCMD timeout 10000 ;#timeout controls db locking parameter

set mrn [hl7get $msg PID 3 0] ;# Database column: mrn
set csn [hl7get $msg PV1 19 0] ;# Database column: csn
set admDate [hl7get $msg PID 18 0] ;# Database column: har
set orderNum [hl7get $msg OBR 2 0] ;# Database column: order
set labTest [hl7get $msg OBR 4 0] ;# Database column: labTest
set query “select admDate, orderNum from labcorp where mrn = ‘$mrn’ and csn = ‘$csn’ and labTest = $labTest”
set record “”

if {[catch {
set record [DBCMD eval $query] ;# Returning a list of 3 elements (mrn, csn, and admDate)
#echo <record: $record>
} err]} {
echo “ERROR: $module: Error in db processing: $err”
}

# If match found, copy admDate value found in sqlite into msg PV1-44
if {[llength $record] > 0 } {
# Get the HL7 encoding characters
set fld [string range $msg 3 3]
set com [string range $msg 4 4]
set rep [string range $msg 5 5]
set esc [string range $msg 6 6]
set sub [string range $msg 7 7]

# Split the message into into segments
set segments [split $msg \r]
#echo segments <$segments>

# Get the segment and split it into fields
set loc [lsearch -regexp $segments {^PV1}] ;# Get the element number of the PV1 segment
set PV1 [lindex $segments $loc] ;# Get the PV1 segment
set PV1 [split $PV1 $fld] ;# Create a list of PV1 fields

# Making additional fields to get to PV1-44. If list length is less than 19 than increment field
for { set i [llength $PV1]} { $i < 45} { incr i} {
lappend PV1 {}
}

# Make the modifications
set PV1 [lreplace $PV1 44 44 [lindex $record 0]]
set PV1 [join $PV1 $fld]
set segments [lreplace $segments $loc $loc $PV1]

# Get the ORCsegment and split it into fields
set loc2 [lsearch -regexp $segments {^ORC}] ;# Get the element number of the ORC segment
set PV1 [lindex $segments $loc] ;# Get the ORC segment
set PV1 [split $PV1 $fld] ;# Create a list of ORC fields

# Rebuild the message
set msg [join $segments \r]
msgset $mh $msg
} else {
#echo “Not modifying the message”
#return “{KILL $mh}”
# What to do if no records found in db?
}

# Disconnect from sqlite db
if {[catch { DBCMD close } err]} {
echo “ERROR: $module: Error closing database: $err”
}

set dispList “{CONTINUE $mh}”
}

time {}
shutdown {}
default {}
}

return $dispList
}

[Max Drown (Infor)]

If I’m understanding the question correctly ….

First grab all of the OBR segments from the message:

set obx_segs [lsearch -all -inline -regexp $msg {^OBX}]

Then, loop across the segments and execute the inserts:

foreach obx $obx_segs {
    # Do the database insert
}

-- Max Drown (Infor)

[Chris]

Hi, have a question about protocol Database-Inbound and unlimited vs. limited max row (per read) config. From related Cloverleaf docs (paraphrasing) —

“The other option enables you to set the maximum row number of query results. If you specify an uncertified DBMS that does not support the max row setting, Max Row Per Read must be set to Unlimited.”

Coming in, I would’ve assumed that limiting rows would use LIMIT and OFFSET to (eventually) work through the entire query result set, but processing appears to return the same X results upon each read attempt, and I’m not seeing those clauses in the processes’ EO SQL statement output.

Is this the expected outcome? If not, is this outcome related to use of an “uncertified DBMS”? If yes, where can I find a list of supported DMBSs?

To add context – I’m using sqlite and running into heap space issues when using unlimited, so trying to determine how best to sequentially step through the full query result return (~150k rows).

[Author]

Replies