Please Advise – How do I connect to SQLite from 6.0 / AIX?

[Femina Jaffer]

Hello,

Can someone please guide me on how to connect or use SQLite on 6.0?  Can I use this from the GUI or Command prompt?  

Thanks,

Femina

Topic

[Elisha Gould]

Hi,

It can be done with tcl. start hcitcl if your just testing something and enter the commands.

To open the database, you can use:

clsqlite_open

ie clsqlite_open test.dbd

or use the sqlite3 command directly:

sqlite3

ie sqlite3 clsqlitedbcmd test.dbd

and for executing queries:

clsqlitedbcmd eval

ie: clsqlitedbcmd eval {SELECT * from sqlite_master}

to iterate through results use foreach. ie:

set result [clsqlitedbcmd eval {SELECT type, name FROM sqlite_master}]

foreach {type name} $result {

echo “$type, $name”

}

[Peter Heggie]

in a tclproc, this would work:

Code:

       set dbfile [tbllookup sqlite “DBFILE”]
       set sqlInsert “insert into audit (tstamp,msgid,mrn,visit,msgtype,function,patloc,admitdate,dischdate)”
       set sqlValues “select strftime(’%Y-%m-%d %H:%M:%f’,’now’,’localtime’),’$mid’,’$mrn’,’$pan’,’$typ’,’$pgm’,’$ploc’,’$padm’,’$pdis'”
       set sql “$sqlInsert $sqlValues”

       package require sqlite

       # connect to database
       set result [catch {sqlite DBCMD $dbfile } ]
       if { $result != 0 } { echo “audittgt sqlite connect: $result” ; return $dispList }
       if {$debug > 1} {echo “[gts] $module after sqlite connect”}

       # set timeout, not too long
       set result [catch {DBCMD timeout 300 } ]
       if { $result != 0 } { echo “audittgt sqlite timeout: $result” ; return $dispList }
       if {$debug > 1} {echo “[gts] $module after sqlite set timeout”}
       
       # insert row
       set result [catch {DBCMD eval $sql} ]
       if { $result != 0 } {
         echo “audittgt sqlite timeout response: $result” ; return $dispList
       } else {
         if {$debug > 0} {echo “[gts] $module insert successful”}
       }

       # close connection
       set result [catch {DBCMD close } ]
       if { $result != 0 } { echo “audittgt sqlite close response: $result” ; return $dispList }

Using the above code you need to create table ‘sqlite’ with a key name of ‘DBFILE’ that has a value of the actual SQLite database (path & file name).  And you need the ‘package require sqlite’ statement.

To create SQLite tables like this, I have a shell script that creates a table:

Code:

#!/usr/bin/ksh
cd /test/cis5.8/chonltst/audit
TMP=”/tmp/hci_sqlite_tmp.sql”
touch $TMP

SQL=”drop table audit;”
echo “${SQL}” > $TMP
SQL=”create table audit (tstamp varchar(30),msgid varchar(15),mrn varchar(15),”
SQL=”${SQL} visit varchar(15),msgtype varchar(20),function varchar(20),”
SQL=”${SQL} patloc varchar(10),admitdate varchar(14),dischdate varchar(14));”
echo “${SQL}” >> $TMP
SQL=”CREATE INDEX audit_tstamp_idx on audit(tstamp);”
SQL=”${SQL} CREATE INDEX audit_mrn_idx on audit(mrn);”
SQL=”${SQL} CREATE INDEX audit_pan_idx on audit(visit);”
echo “${SQL}” >> $TMP
SQL=”select * from sqlite_master;”
echo “${SQL}” >> $TMP
                 
cat $TMP
cat $TMP | sqlite audit

And another script to put some data in it (if needed):

Code:

insert into audit (tstamp,msgid,mrn,visit,msgtype,patloc,admitdate,dischdate) values (’2012-05-07 09:57:39.808′,’11385267′,’0000203509′,’600076079′,’ADT^A08′,’4NME^4414^01^^D’,”,”);

If you are creating a lookup table, this script reads a file and writes each record into the SQLite table as a row, splitting up the record into fields that positionally match the columns of the table:

Code:

#!/usr/bin/ksh
#
OUTFILE=/home/hci/scripts/data/audit_insert.sql
cat /dev/null > $OUTFILE

SQLFILE=/tmp/hci_audit_build_select.sql
cat /dev/null > $SQLFILE

DATFILE=/home/hci/scripts/data/audit.csv
cat /dev/null > $DATFILE

cd /test/cis5.8/chonltst/audit

/home/hci/scripts/util/audit_setup.ksh

OFS=$IFS
IFS=’|’
while read p1 p2 p3 p4 p5 p6 junk
do
 SQL=”insert into audit (tstamp,msgid,mrn,visit,msgtype,patloc,admitdate,dischdate)”
 SQL=”${SQL} values (’$p1′,’$p2′,’$p3′,’$p4′,’$p5′,’$p6′,’$p7′,’$p8’);”
 echo “${SQL}” >> $OUTFILE
done $SQLFILE

CT=`cat $SQLFILE | sqlite audit`

echo “audit table reloaded – $CT records written”

You would change the first example Insert statement to a Select statement; see Elisha’s example for reading Select results into variables.

Peter Heggie
PeterHeggie@crouse.org

[Femina Jaffer]

Thank you all so much.  This was very helpful indeed!

[David Barr]

Peter Heggie wrote:


[code]

[Peter Heggie]

why?

Peter Heggie
PeterHeggie@crouse.org

[David Barr]

Because then you don’t have to worry about whether or not your data contains characters that are “special” in the SQL syntax. For example, quotes or backslashes in the data could cause syntax errors or erroneous results if they are directly included in the query without special processing. By letting Sqlite do the variable substitution you avoid this problem.

This is more important if you are doing web development or something like that with Sqlite. It is common for hackers to try to put values in forms on submission that would enable them to bypass security when the values are not handled correctly.

Even if you aren’t worried about people sending you deliberately bad data, I think it is still a better idea to let Sqlite handle the string processing.

I’m not sure if Sqlite does this, but some databases take several steps when handling a query. Some of the steps like parsing the query and preparing the plan for how to join multiple tables can be cached if the same query is executed repeatedly. If you link variables in your programming language to fields in the query, it is easier for the database to do this caching, so you may see performance improvement by using this style of programming, but this is depends on which libraries and databases you are using.

[Peter Heggie]

ok I guess I don’t know how tcl works. I thought that invoking a function with an argument variable without quotes would be equivalent to invoking a function with the resolved value.

I know I was thinking of something else when I used the apostrophes – I was worried about embedded spaces affecting the order and position of the argument values (ex: where 5 arguments get interpreted as 6 arguments because of one embedded space), but that was fuzzy logic / unnecessary because we do have commas separating them. I know SQL Server prefers apostrophes to quotes, for varchar, but this is probably not required for SQLite.

So you are saying that some processing, like parsing, can be passed off to the routines in the SQLite package, meaning passed off to the SQLite executable, instead of the TCL executable? And that is good for performance of parsing, as well as good for database variable/value parsing and management?

Thank you for the explanation. I don’t understand the finer points of tcl.

Peter Heggie
PeterHeggie@crouse.org

[David Barr]

Peter Heggie wrote:

So you are saying that some processing, like parsing, can be passed off to the routines in the SQLite package, meaning passed off to the SQLite executable, instead of the TCL executable? And that is good for performance of parsing, as well as good for database variable/value parsing and management?

Yes, processing can be handled by SQLite instead of TCL. The Sqlite package is a library that is linked into the same executable as TCL, so it isn’t a separate executable. And yes, this can be good for performance. Not because string processing is very performance critical but because if you keep passing the same query multiple times the library can cache it. If you change the query every time by substituting different values into the text of the query, then it might be harder for the Sqlite to cache the execution plan. This speculation based on more in depth experience with other database programming APIs and servers–I haven’t looked in depth at how Sqlite attempts to optimize queries. In general I think it is a better approach.

[Peter Heggie]

ok I think I understand – thank you.

I assume that the ‘same query’ performance improvement will increase if you are using the same connection/same ‘open’, and just running the same query multiple times, just substituting different values to insert, but keeping the table & columns referenced the same.

I’m hoping that the new 6.0 db protocol has or will have the capability of reusing the connection opened up by the (outbound) thread. I think I am seeing in the process log a statement that the ‘connection’ is being opened and I think this is happening in the Start mode, leaving me to hope that the Run mode is using the existing connection.

Code:

Oct 10, 2013 12:44:43 PM com.lawson.cloverleaf.dbprotocol.CJDDBProtocolOB doInit
INFO: Running the doInit method in database-outbound protocol

[/code]

Peter Heggie
PeterHeggie@crouse.org

[David Barr]

I found this page which seems to support my statements that queries are being cached:

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

Quote:

The “eval” method described above keeps a cache of prepared statements for recently evaluated SQL commands.

This page probably also applies although it is not TCL specific:

http://docs.blackberry.com/en/developers/deliverables/17952/BP_Optimizing_SQLite_database_performance_1219781_11.jsp

Quote:

Use SQL parameters

To execute a set of statements of the same format, first prepare a generic statement that uses SQL parameters. You can execute the statement by iterating through the variable values and binding the values to the named variables in each iteration.

It seems that binding happens automatically in TCL when you pass dollar or colon specified variables to Sqlite rather than expanding them in your own code.

[Max Drown (Infor)]

In CIS 6.0, you don’t need to include the line “package require sqlite”.

-- Max Drown (Infor)

[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:

—  Usage: sqlite /path/dbName.db < fileName.sql

DROP TABLE IF EXISTS test;

CREATE TABLE test (
   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)
);

02) Create some test data. Add this code to a file named test.dat.

Code:

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

03) Populate the database with the data. Add this code to a file called insert.tcl and issue the command “tcl insert.tcl” from the command line.

Code:

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

set fileName test.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 test (
           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”
   }

}

04) View the contents of the data. Place this code into a file called test.tcl and then issue this command from the command line “tcl test.tcl”.

Code:

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

set query “select * from test”

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”
}

05) View the contents of the database directly. Issue the command “sqlite test.db” from the command line. This will open the database test.db and place you into the sqlite shell. Now issue the command “select * from test;”. Type .exit to close the database and exit the shell.

06) Notice that all database accesses in tcl are wrapped in catch statements. This is the recommended best practice.

07) Here is a Cloverleaf TPS tclproc that will insert the data into the database.

Code:

proc tps_sqlite_update { args } {
   global HciConnName HciSiteDir
   set module “tps_sqlite_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 2]
           set lname [lindex $msg 3]
           set fname [lindex $msg 4]
           set mname [lindex $msg 5]
           set dob [lindex $msg 6]
           set gender [lindex $msg 7]
           set acct_num [lindex $msg 8]
           set ssn [lindex $msg 9]
           set location [lindex $msg 10]
           set room [lindex $msg 11]
           set bed [lindex $msg 12]
           set visit_num [lindex $msg 13]
           set admit_date [lindex $msg 14]
           set discharge_date [lindex $msg 15]

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

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

           set query “select db_id from test where id_num=$id_num”
           set db_id [DBCMD onecolumn $query]

           if {$db_id == “”} {
               if {[catch {DBCMD eval {
                   insert into test (
                       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: “}
           } else {
               if {[catch {DBCMD eval {
                   update test
                   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: “}
           }

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

           lappend dispList “CONTINUE $mh”
       }

       time {}
       shutdown {}
       default {}
   }

   return $dispList
}

08) Here is an example xltp tclproc that will query a database from an xlate. You will need to expand this code to do better error handling. I wrote this proc only as a demo of what is capable with SQLite.

Code:

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

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

   set column  [lindex $xlateInVals 0]
   set db_id   [lindex $xlateInVals 1]

   set query “select $column from test where db_id=$db_id”
   set result [DBCMD onecolumn $query]

   set xlateOutVals [list $result]

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

-- Max Drown (Infor)

[Femina Jaffer]

Thank you, Max.  This is very helpful.

Femina

[Femina Jaffer]

Thanks again everyone.

I have another question, when would you use SQL Lite in CLoverleaf.  What is it best used for?

[Max Drown (Infor)]

Femina Jaffer wrote:

Thanks again everyone.

I have another question, when would you use SQL Lite in CLoverleaf.

-- Max Drown (Infor)

[Peter Heggie]

And it is highly available. Oracle and SQL Server databases are typically on another server, so if there is a server problem or network problem or database problem, your interface could be severly impacted. Having a local SQLite database avoids these problems.

Peter Heggie
PeterHeggie@crouse.org

[Author]

Replies