Homepage › Clovertech Forums › Read Only Archives › Cloverleaf › Cloverleaf › sqlite
- This topic has 7 replies, 5 voices, and was last updated 10 years, 6 months ago by Gary Atkinson.
-
CreatorTopic
-
March 14, 2014 at 10:18 pm #54100Femina JafferParticipant
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
-
CreatorTopic
-
AuthorReplies
-
-
March 15, 2014 at 1:21 am #80159Charlie BursellParticipant
Try this tutorial: http://souptonuts.sourceforge.net/readme_sqlite_tutorial.html
Many other assets on the web. Google is your friend 😀
-
March 15, 2014 at 5:42 pm #80160Max Drown (Infor)Keymaster
-
March 17, 2014 at 1:20 pm #80161Jeff DinsmoreParticipant
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) 0set 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 $logfileif { $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 $dbNamepackage 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 envif { [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 -
March 19, 2014 at 8:54 pm #80162Femina JafferParticipant
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
-
March 20, 2014 at 4:15 pm #80163Jeff DinsmoreParticipant
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 -
March 20, 2014 at 4:37 pm #80164Femina JafferParticipant
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
-
March 20, 2014 at 5:27 pm #80165Gary AtkinsonParticipant
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.
-
-
AuthorReplies
- The forum ‘Cloverleaf’ is closed to new topics and replies.