How to Install and Use SQLite on 5.7

[Max Drown (Infor)]

I think we need a place to document the sqlite installation process for each version of Cloverleaf (if it differs from version to version). There are a lot of threads on sqlite that contains information, but no one location that contains everything. This would be an ideal topic for a wiki page, but since we don’t have a wiki yet, let’s use this thread.

As information is compiled from contributors, I’ll edit this post to include all the information.

====

In the Release Notes for 5.7:

Quote:

*  Bundled but not part of Cloverleaf

-- Max Drown (Infor)

Topic

[Rob Abbott]

Quote:

* How do we access this bundle from Cloverleaf tps procs?

package require sqlite

Official documentation on Tcl sqlite API here:

<a href="http://www.sqlite.org/tclsqlite.html&#8221; class=”bbcode_url”>http://www.sqlite.org/tclsqlite.html

Command-line access to a sqlite DB is through the “sqlite3” command.

Rob Abbott
Cloverleaf Emeritus

[Max Drown (Infor)]

Example tcl from the 2009 User Conference:

Code:

#############################################################################
# Name:         ug2009Lite
# Purpose:      Example procedure to access a SQLITE database
# UPoC type:    tps
# Args:         tps keyedlist containing the following keys:
#               MODE    run mode (”start” “run”)
#               MSGID   message handle
#               ARGS    user-supplied arguments: None
#
# Returns: CONTINUE message
#
# Notes:
#               —– The requirements —–
#       This is a contrived example to demonstrate SQLITE calls via Tcl
#      
#       It is assumed that a database of the name people.db exists in
# $HciSiteDir/tclprocs/lib with a single table named PEOPLE
# of the format:
#
# Column Name Type
# ———– ——-
#  EMPNO varchar  
#  FIRSTNAME varchar
#  MIDINIT varchar
#  LASTNAME varchar
#  WORKDEPT varchar
#  PHONE varchar
#  DOB date
#  JOB varchar
#  EDLEVEL integer
#  SEX varchar
#  HIREDATE date
#  SALARY integer
#  BONUS integer
#  COMMISION integer
#
#
# This procedure will implement routines to query the database
# and insert data as required
#
# Use namespaces for shared data – one could use globals
#
# Input data is assumed to be a comma separated values (CSV) file
# with each record having fields in the same order as the column
# names above.
#
# Use the csv package, part of tcllib, to parse records
#############################################################################
proc ug2009Lite { args } {

   keylget args MODE mode
   
   switch -exact — $mode {

start {

   # Nothing specific
   return “”
}

run {

   # Just get and continue the message
   keylget args MSGID mh

   # If no sqlite package all to Error database
   if {$liteSubs::LITEAVAIL ne “”} {
echo n$liteSubs::myName:
Cannot load SQLITE: $liteSubs::LITEAVAIL
echo All records to Error Database! ……………..

msgmetaset $mh USERDATA $liteSubs::LITEAVAIL
return “{ERROR $mh}”
   }

   # Assumes we receive complete file of records
   # All or none.  If one errors, error all
   set records [msgget $mh]

   foreach rec [split $records n] {

# Clean up and avoid empties
set rec [string trim $rec r]
if {[string trim $rec] eq “”} { continue }
if {[string trim $rec ,] eq “”} { continue }

# Attempt to use CSV
if {$liteSubs::LITECSV eq “”} {
   set rec [csv::split $rec]
} else {
   set rec [split $rec ,]
}

if {![liteSubs::insertRec $rec]} {

   echo n$liteSubs::myName: Error inserting record
   echo $rec
   echo To Error Database! ………………..

   msgmetaset $mh USERDATA “Error inserting to database”
   return “{ERROR $mh}”
}
   }

   # Issue a query to the database and echo the results
   liteSubs::queryDB “SELECT * FROM PEOPLE”

   foreach rslt [lsort -integer [array names liteSubs::RESULTS]] {

# Get keyed list
set klst $liteSubs::RESULTS($rslt)

# Format and Echo results
echo nResult #$rslt
foreach ky [keylkeys klst] {
   set colname [format %-15s $ky:]
   echo t$colname[keylget klst $ky]
}
   }

   return “{CONTINUE $mh}”
}
   }
}

namespace eval liteSubs {
   
   # A namespace to provide storage for shared procedures and variables
   # Any statements not inside a procedure will be executed when the file
   # is loaded

   # Name of this procedure
   set myName $::HciConnName/level3Lite

   # Make sure we can get the sqlite package.  Set a flag if problems
   set LITEAVAIL {}

   if {[catch { package require sqlite } err]} {

set LITEAVAIL $err
   }

   # Use the csv package if there
   set LITECSV {}

   if {[catch { package require csv } err]} {

set LITECSV $err

echo n$myName: Cannot load CSV packagen$err
echo Attempt to split on comma may produce errors!n
   }

   # Assume database path
   set DSN [file join $::HciSiteDir tclprocs LIB people.db]

   # An easy way to build an SQL statement is to set variables inside braces
   # and use subst command
   set INSERT {INSERT INTO PEOPLE VALUES (’$EMPNO’,’$FIRST’,’$MID’,’$LAST’,
    ‘$DEPT’,$PHONE,’$DOB’,’$JOB’,$EDLEVEL,’$SEX’,’$HIREDATE’,$SALARY,
$BONUS,$COMMISION)}

   # A list of variable from above
   set varList [list EMPNO FIRST MID LAST DEPT PHONE DOB JOB EDLEVEL SEX]
   lappend varList HIREDATE SALARY BONUS COMMISION

   ###########################################################################
   # Name:     insertRec
   #
   # Purpose:  Build insert statement and insert
   #
   # Args:     the record to insert
   #
   # Returns:  The return value of issueSQL
   #
   # Notes:    Assumes the record consists of space delimited fields that
   # align with the pre-build INSERT statement
   ###########################################################################
   proc insertRec {rec} {
variable INSERT ;# Insert statement
variable varList ;# List of variables

# Just in case of names like O’hare, etc
set rec [string map {’ ”} $rec]

# Assume rec is a list
foreach var $varList val [split $rec] {set $var $val}

# Call routine to issue the insert and return it’s return value
return [issueSQL [subst $INSERT]]
   }
   
   ##########################################################################
   # Name: issueSQL – issue SQL statement
   #
   # Use: issueSQL
   # sql = The sql statement
   #
   # Notes: Issue an SQL statement to the database.  Returns error if any
   # errors encountered, else nothing.
   #
   # Can be used for most any SQL statement, e.g., INSERT,
   # UPDATE, CREATE, etc.
   #
   # Returns:  1 if OK else 0
   ##########################################################################
   proc issueSQL {sql} {

variable myName ;# Name of procedure
variable CONNECTED ;# Connected flag

# Assume good return
set rtn 1

# Connect to Database
# make sure it is closed
closeDB

connectDB
if {!$CONNECTED} {return 0}

# Issue the SQL
set err “”; set errcode 0
catch {DBCMD eval $sql} err
catch {DBCMD errorcode} errcode

# See if error
set errMsg “”
if {![lempty $err] || ![string equal $errcode 0]} {

   if {![lempty $err]} {
set errMsg “ERROR INSERTING – $err ”
   }

   echo n$myName/issueSQL: SQLITE returns errorcode: $errcoden
   set rtn 0
}

closeDB
return $rtn
   }

   ##########################################################################
   # Name: queryDB – Query the DB
   #
   # Use: queryDB
   # query = The query statement
   #
   # Notes: Performs a query on the database.
   #
   # Results are place in RESULTS array
   #
   # Returns:  nothing sets array
   ##########################################################################
   proc queryDB {query} {

variable RESULTS ;# Results array
variable myName ;# Name of procedure
variable CONNECTED      ;# Connected flag

# Attempt to connect to DB.  Assume connect and disconnect each time
# If using a persistent connection, attempt simple query and
# if fail, attempt reconnect
closeDB
connectDB
if {!$CONNECTED} { return “” }

# Clear results arrays
array unset RESULTS
array unset RSLTS

# Issue the query and check for errors

set err “”; set errcode 0; set cnt 0; set cols {}
catch {DBCMD eval $query RSLTS {

   # Get the column names once
   if {[lempty $cols]} {set cols $RSLTS(*)}

set klst {}
foreach c $cols {keylset klst $c $RSLTS($c)}

incr cnt
set RESULTS($cnt) $klst

array unset RSLTS
   }

} err

catch {DBCMD errorcode} errcode

set errMsg {}
if {$err ne “” || $errcode != 0} {

   if {$err ne “”} {
set errMsg “ERROR DURING QUERY – $err ”
   }

   echo n$myName/queryDB return errorcode: $errcoden
}

# Just in case no results found
if {![info exists RESULTS(1)]} {set RESULTS(1) {}}

return “”
   }

   ######################################################################
   # Name: connectDB
   #
   # Purpose: Attempt to Connect to the database
   #
   # Args: None
   #
   # Notes: This subroutine will attempt to connect to the database.
   #
   # It will first check for a database handle.  If one
   # exists, assume the database is already open
   #
   # Returns: Nothing if success, error message if error
   ######################################################################
   proc connectDB {} {
variable DSN ;# Database name
variable myName ;# Name of procedure
variable CONNECTED 1    ;# Connected flag

# OK, try to connect

catch {sqlite DBCMD $DSN} err

set ecode “”
catch {DBCMD errorcode} ecode

# If we didn’t connect, set CONNECTED

if {![string equal $ecode 0]} {

   echo n$myName/connectDB: CONNECTING TO $DSN: $ecode
   set CONNECTED 0

}

return “”
   }

   ######################################################################
   # Name: closeDB
   #
   # Purpose: Attempt to Disconnect from the database
   #
   # Args: None
   #
   # Returns: Nothing
   #
   # Notes: Simply attempts disconnect
   ######################################################################
   proc closeDB {} {
variable myName ;# Name of procedure

catch {DBCMD close}

return “”
   }
}

-- Max Drown (Infor)

[Gary Atkinson]

I just finally got around to installing sqlite on my test server.

[Gary Atkinson]

What I found was that this piece of code:

set DSN [file join $::HciSiteDir tclprocs LIB people.db]

I needed to change to:

set DSN [file join $::HciSiteDir tclprocs lib people.db]

After I did this, all the records were inserted.  😀

[Max Drown (Infor)]

Gary, are you on Cloverleaf 5.7?

-- Max Drown (Infor)

[Gary Atkinson]

No, 5.5.  What scenarios do you use in the “real” world with sqlite?

[Max Drown (Infor)]

I have an interface that needs to change a value based on previously sent messages. I store a few fields in a sqlite database and query the database in the xlate. It’s very good stuff.

-- Max Drown (Infor)

[Jerry Tilsley]

Gary,

We are on 5.5 also, and use sqlite a little bit.  A couple of the scenario’s for which sqlite is used:

1.) We record charge messages from our ECG system so that if a message is resent due to a timeout, or manual resend then we can suppress a duplicate charge message.

2.) We record header information for documents that are going to uploaded into our electronic document system, then once an hour we pull that information and build an import xml file for the documents.

[Author]

Replies