- This topic has 9 replies, 4 voices, and was last updated 4 months, 2 weeks ago by .
-
Topic
-
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
- This topic was modified 4 years, 3 months ago by .
- This topic was modified 4 years, 3 months ago by .
- This topic was modified 4 years, 3 months ago by .
- This topic was modified 4 years, 3 months ago by .
- This topic was modified 4 years, 3 months ago by .
- This topic was modified 4 years, 3 months ago by .
- This topic was modified 4 years, 2 months ago by .
- This topic was modified 4 years, 2 months ago by .
- This topic was modified 4 years, 2 months ago by .
-- Max Drown (Infor)
- You must be logged in to reply to this topic.