How to Install and Use SQLite on 5.7

Homepage Clovertech Forums Read Only Archives Cloverleaf Cloverleaf How to Install and Use SQLite on 5.7

  • Creator
    Topic
  • #51280

    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)

Viewing 7 reply threads
  • Author
    Replies
    • #69502
      Rob Abbott
      Keymaster

      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
      Director, Product Management - Infor Cloverleaf

    • #69503

      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)

    • #69504
      Gary Atkinson
      Participant

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

    • #69505
      Gary Atkinson
      Participant

      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.  😀

    • #69506

      Gary, are you on Cloverleaf 5.7?

      -- Max Drown (Infor)

    • #69507
      Gary Atkinson
      Participant

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

    • #69508

      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)

    • #69509
      Jerry Tilsley
      Participant

      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.

Viewing 7 reply threads
  • The forum ‘Cloverleaf’ is closed to new topics and replies.

Forum Statistics

Registered Users
4,966
Forums
28
Topics
9,105
Replies
33,625
Topic Tags
248