sqlite

[Femina Jaffer]

Hello,

I am ready to expore options for a new project using sqlite.  I very familiar with tsql and pl/sql with SQL Server and Oracle and have done development in that area; but, never have I used sqlite and mysql, so need some assistance please.

I created a database and a table.  I would like to load the table with data using a script (data.sql).  How can I bring this file into SQLite and load the table with values rather then doing this manually line by line?

Any help would be very appreciated.

Thanks,

Femina

Topic

[Charlie Bursell]

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

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

[Max Drown (Infor)]

Check out this post, too.

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

-- Max Drown (Infor)

[Jeff Dinsmore]

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:

Quote:

-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

code

                        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.

Code:

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

Jeff Dinsmore
Chesapeake Regional Healthcare

[Femina Jaffer]

Thanks folks for all the tips….and I have build the sample tables, read a file to insert data into a table and did the select.

The concern I have is how to i build the inbound and outbound threads that reads all the hl7 files from inbound and loads into the sql table (outbound).  Basically, mapping the hl7 to the sql table.  Do I just create a raw route with a proc to do this?   I need baby steps please…? Secondly, how do I use a translate to do this?

Thanks,

Femina

[Jeff Dinsmore]

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:

Code:

create table msgs(destinationSys TEXT, clMsgId TEXT, appSpecificMsgId TEXT, msg TEXT, sendSec NUMERIC, insertSec NUMERIC)

Jeff Dinsmore
Chesapeake Regional Healthcare

[Femina Jaffer]

Jeff,

I need to store the HL7 messages in a SQL database, and then after 24 hours match the records in the db with MRN and send HL7 results back to the client.  This way all the results are grouped by patient.  Has anyone done anything similar?

Thanks,

FJ

[Gary Atkinson]

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.

[Author]

Replies