sqlite

Try this tutorial:  http://souptonuts.sourceforge.net/readme_sqlite_tutorial.html

Many other assets on the web.  Google is your friend  😀

Check out this post, too.

https://usspvlclovertch2.infor.com/viewtopic.php?t=6686

It’s easy enough to do in TCL, as Max and Charlie point out.

I also use “SQLite Database Browser”.  It has functions that allow you to import/export a database to/from a SQL file or import/export a table to/from CSV.  I use it to edit/update databases.  SQLite databases are portable between Windows and Linux, at least. It’s a servicable, free graphic tool, but doesn’t perform very well on large data sets.

You can also run SQLite in linemode and import/export to/from SQL and CSV – see SQLite’s .help below:

-bash-3.2$ sqlite3

SQLite version 3.3.6

Enter “.help” for instructions

sqlite> .help

.databases             List names and files of attached databases

.dump ?TABLE? …      Dump the database in an SQL text format

.echo ON|OFF           Turn command echo on or off

.exit                  Exit this program

.explain ON|OFF        Turn output mode suitable for EXPLAIN on or off.

.header(s) ON|OFF      Turn display of headers on or off

.help                  Show this message

.import FILE TABLE     Import data from FILE into TABLE

.indices TABLE         Show names of all indices on TABLE

.mode MODE ?TABLE?     Set output mode where MODE is one of:

                        csv      Comma-separated values

                        column   Left-aligned columns.  (See .width)

                        html     HTML

                        insert   SQL insert statements for TABLE

                        line     One value per line

                        list     Values delimited by .separator string

                        tabs     Tab-separated values

                        tcl      TCL list elements

.nullvalue STRING      Print STRING in place of NULL values

.output FILENAME       Send output to FILENAME

.output stdout         Send output to the screen

.prompt MAIN CONTINUE  Replace the standard prompts

.quit                  Exit this program

.read FILENAME         Execute SQL in FILENAME

.schema ?TABLE?        Show the CREATE statements

.separator STRING      Change separator used by output mode and .import

.show                  Show the current values for various settings

.tables ?PATTERN?      List names of tables matching a LIKE pattern

.timeout MS            Try opening locked tables for MS milliseconds

.width NUM NUM …     Set column widths for “column” mode

sqlite>

Here are the utilities I use to create/open/close SQLite databases.  

The timeout/busy handling is the least obvious.  Unlike a commercial DB solution, you need to handle simultaneous access to a SQLite DB yourself.

Simple init and busy procs are included at the bottom of the code.

The openDb proc auto-creates the required directory structure the first time it’s called.

namespace eval crmcSqliteUtils {

 variable masterDir “/quovadx/crmcdata/sqliteDatabases”
 variable emailRecipients [list jeffrey.dinsmore@chesapeakeregional.com]

}

proc crmcSqliteUtils::dirPath { dbName dirType } {
 
global env
 variable masterDir
 
if { [info exists env(OS)] } {
   if { [regexp “WINDOWS” [string toupper $env(OS)]] } {
 set masterDir “c:/quovadx/crmcdata/sqliteDatabases”
   } else {
set masterDir /quovadx/crmcdata/sqliteDatabases
}
 }

switch -exact [string toupper $dirType] {
MASTER {
return $masterDir
}
ROOT {
return [file join $masterDir $dbName]
}
LOG {
return [file join $masterDir $dbName log]
}
DBDIR {
return [file join $masterDir $dbName db]
}
DB {
return [file join $masterDir $dbName db $dbName]
}
default {
# invalid dirType
}
}

}

proc crmcSqliteUtils::log { dbName msg email } {
 
 variable emailRecipients
 
set callingProcs(numProcs) 0

set l 0
while { ! [catch {set cp [lindex [info level [incr l -1]] 0]} errs] } {
set callingProcs([incr callingProcs(numProcs)]) $cp
}

set cpList [list]
for { set i $callingProcs(numProcs) } { $i > 0 } { incr i -1 } {
lappend cpList “([expr $i * -1])$callingProcs($i)”
}

set logDir [crmcSqliteUtils::dirPath $dbName LOG]
if { ! [file exists $logDir] } {
catch {file mkdir $logDir}
}

 set logPath “$logDir/sqliteDbLog[clock format [clock seconds] -format “%Y%m%d”].txt”
 set logfile [open $logPath “a”]
 
 puts $logfile “[clock format [clock seconds] -format “%Y/%m/%d_%H:%M:%S”] (db= $dbName, callingProcs= $cpList, pid= [pid]) $msg”
 
 close $logfile

if { $email } {
crmcEmail::sendMessage “SQLite DB errors ($dbName)” “db= $dbName, callingProcs= $cpList, pid= [pid]nn$msg” $emailRecipients
}
 
}

proc crmcSqliteUtils::initDirs { dbName } {

 global env

 variable masterDir
variable dbRootDir
 variable logDir
 variable dbDir
 variable dbErrs
 variable emailRecipients
 

 set dbRootDir [crmcSqliteUtils::dirPath $dbName ROOT]
 if { ! [file exists $dbRootDir] } {
   catch {file mkdir $dbRootDir} err
 }
 
 set dbDir [crmcSqliteUtils::dirPath $dbName DBDIR]
 if { ! [file exists $dbDir] } {
   catch {file mkdir $dbDir} err
 }

}

proc crmcSqliteUtils::subSingleQuote { inStr } {
return [string map {’ ”} $inStr]
}

proc crmcSqliteUtils::dbFileLastChangeSec { dbName } {

if { ! [catch {set mtime [file mtime [crmcSqliteUtils::dirPath $dbName DB]]} err] } {
return $mtime
} else {
return 0
}

}

proc crmcSqliteUtils::openDb { readonly dbName dbInitProc dbBusyProc } {

 global env
 
 crmcSqliteUtils::initDirs $dbName

 package require sqlite3
 
 set dbFilePath [crmcSqliteUtils::dirPath $dbName DB]

if { $readonly } {
if { ! [file exists $dbFilePath] } {
# can’t open readonly if database file doesn’t exist
set readonly 0
}
}

 if { [info exists env(debugSqliteDb)] } {
   crmcSqliteUtils::log $dbName “Open DB $dbFilePath” 0
 }
 
 if { [catch {sqlite3 $dbName $dbFilePath -readonly $readonly} dbErrs] } {
   crmcSqliteUtils::log $dbName “Error opening database ($dbErrs)” 1
   crmcSqliteUtils::closeDb $dbName
   return 0
 } else {

 $dbName busy $dbBusyProc

if { ! $readonly } {

if { ! [$dbInitProc $dbName initErrs] } {
crmcSqliteUtils::log $dbName “DB init process $dbInitProc failed ($initErrs)” 1
crmcSqliteUtils::closeDb $dbName
return 0
}
}

 }
 
 return 1  
 
}

proc crmcSqliteUtils::closeDb { dbName } {
 
 global env

 if { [info exists env(debugInsDb)] } {
   crmcSqliteUtils::log $dbName “Close DB” 0
 }
 catch {$dbName close}

 return 1

}

#
# Sample DB init proc
#
#proc crmcChargeDb::dbInit { dbName initErrsName } {
#
# variable nsDbName
#  
# upvar $initErrsName initErrs
#  
# set nsDbName $dbName
#
# set retVal 1
#
# set initErrs “”
#
# if { ! [llength [$dbName eval “PRAGMA table_info(sentCharges)”]] } {
# crmcSqliteUtils::log $dbName “First access – creating sentCharges table” 0
# if { [catch {$dbName eval “create table sentCharges(chargeUid TEXT, sourceSystem TEXT, lastUpdateTclSec INTEGER)”} initErrs] } {
# set retVal 0
# }
# }
#
# return $retVal
#
#}
#

#
# sample DB busy proc
#
#proc crmcChargeDb::openWait { a } {
#
# variable nsDbName
#
#  set waitMsec 2000
#  
#  crmcSqliteUtils::log $nsDbName “Attempt $a – Database $nsDbName is locked – waiting $waitMsec milliseconds” 1
#  
#  after $waitMsec
#  
#  return 0
#  
#}

What are you trying to accomplish?

SQLite will store large chunks of data in a single cell without having to specify a particular size for the given column.

I’ve stored whole messages in SQLite for later sending.  That doesn’t require any disassembly of the message unless you need to store some specific field data in your DB for lookup purposes.

I’ve used the following table structure to store whole messages for later delivery:

Femina- Yes I have done this a few times.  What you need to do is store ADT fields into the sql data, MRN, account etc etc.  Then store the HL7 Messages onto your cloverleaf server.  Then you run a schedule task to query the HL7 Messages against your sqlite database.  If message qualifies sent out else do something else.  Message if you would like to chat offline.