SQLite programming question – how to execute dot commands from Tcl

[Jim Kosloskey]

I am in need of executing the SQLite .import command from inside a Tcl proc. The copy Tcl extension for SQLite does not give me what I want.

How can this be done?

I have searched but have not found a resolution to this. I find some verbiage related to what appears to be jumping back and forth between Tcl and sqlite at the command line for scripting like shell scripts but not how to invoke the dot commands from inside Tcl.

Thanks

email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.

Topic

[Charlie Bursell]

The “copy” method

The “copy” method copies data from a file into a table. It returns the number of rows processed successfully from the file. The syntax of the copy method looks like this:

<i>dbcmd</i>  <b>copy</b>  <i>conflict-algorithm</i>   <i>table-name </i>  <i>file-name </i>     ?<i>column-separator</i>?   ?<i>null-indicator</i>?

Conflict-algorithm must be one of the SQLite conflict algorithms for the INSERT statement: <i>rollback</i>, <i>abort</i>, <i>fail</i>,<i>ignore</i>, or <i>replace</i>. See the SQLite Language section for ON CONFLICT for more information. The conflict-algorithm must be specified in lower case.

Table-name must already exists as a table. File-name must exist, and each row must contain the same number of columns as defined in the table. If a line in the file contains more or less than the number of columns defined, the copy method rollbacks any inserts, and returns an error.

Column-separator is an optional column separator string. The default is the ASCII tab character \t.

Null-indicator is an optional string that indicates a column value is null. The default is an empty string. Note that column-separator and null-indicator are optional positional arguments; if null-indicator is specified, a column-separator argument must be specified and precede the null-indicator argument.

The copy method implements similar functionality to the <b>.import</b> SQLite shell command.

[Jim Kosloskey]

Thanks Charlie – I read all that before asking the question and tried all of the limited ways I know to try to invoke the dot commands from inside my proc..

The .import though allows for the first row to be the Column Names (if the Table does not exist) or to skip  some number of rows (whether the Table exists or not).

The copy requires the Table already exist and does not allow skipping.

The situation I have is the imported file first row is column names which I would like to use – or – define the Table and skip the first record from the imported file.

.import does that nicely, copy does not.

So, if I can execute .import (and the other dot commands since I should set the mode) from inside my Tcl proc, that would simplify matters.

I know how to work around that but I would like to use the built-in SQLite functions if I can.

email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.

[Jeff Dawson]

Hi Jim,

I’ve done something like this in the past to issue the sqlite dot commands via tcl.  There may be an easier way but I know this does work.

#!/usr/bin/env tcl

global hciRoot
global hciSite
global siteDir

set hciRoot $env(HCIROOT)
set hciSite $env(HCISITE)
set siteDir $env(HCISITEDIR)

set dbFile “$hciRoot/sqlLiteDbs/ADT/EpicADT.db”

# create batch file of commands to backup sqlite db
set batchFileName “$hciRoot/sqlLiteDbs/ADT/EpicADTbatch.txt”
set batchFile [open $batchFileName w]
#puts $batchFile “.backup TEST.db”
#puts $batchFile “.help”
puts $batchFile “.backup TEST.db”
close $batchFile
set backupCmd [exec sqlite $dbFile < $batchFileName]

• This reply was modified 7 months, 2 weeks ago by Jeff Dawson.
• This reply was modified 7 months, 2 weeks ago by Jeff Dawson.

[Jim Kosloskey]

Jeff,

Thanks, I will give that a try. What do you get back from the

set backupCmd [exec sqlite $dbFile < $batchFileName] ? Is it something that can be interrogated to determine success or failure?

I don’t know why this was not obvious to me.

Jim

 

 

email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.

[Jeff Dawson]

You could echo variable backupCmd out the contents of the command like the .help but if you wanted to trap an error there’s a few ways this could be accomplished with the tcl catch command.

set rc [catch {set backupCmd [exec sqlite $dbFile < $batchFileName]} error]
echo “rc $rc”
echo “error $error”

I updated the spelling of the dot command so it would fail:

rc 1
error Error: unknown command or invalid arguments: “b”. Enter “.help” for help

Then updated the backup command and ran it successfully:

rc 0
error

Another way would be to wrap the catch with an If statement and forgo the set rc (return code) command.

• This reply was modified 7 months, 2 weeks ago by Jeff Dawson.

[Jim Kosloskey]

Jeff,

Thanks again. As is normally the case, I have been yanked off into another topic and it could be a few days before I get back to this.

Jim

email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.

[Jim Kosloskey]

Well, I finally had the opportunity to cycle back to this.

It appears the version of SQLite released with Cloverleaf 2209 does not support the –skip option with .import so this is a not possible activity in SQLite.

The release of SQLite is 3.31.0 dated 2020-01-22 somewhere between that release and the most current release (3.45.1 dated 2024-01-30) that support must have been added since the current online doc shows that usage.

I wonder what other currently documented features are not usable. Kind of disappointing and not at all encouraging to try to rely on SQLite for future designs.

So, it looks like in order to do this properly I have to first pre-process the files to get rid of the header – more work than should be necessary and additional consumption of resources.

It would be nice to know why the version is so out-of-date in relationship to the Cloverleaf release.

It would also be nice to be able to bring SQLite up-t0-date within the license INFOR has and not have to wait for the next release of Cloverleaf which may or may not provide the latest release of SQLite.

Something for other to be aware of.

• This reply was modified 7 months ago by Jim Kosloskey.

email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.

[Jeff Dinsmore]

Jim,

You can use the SQLite Tcl extension/API commands directly from your Tcl script.

To check if a table exists:

if { ! [llength [$dbHandle eval “PRAGMA table_info(yourTableName)” ]] } { puts “table does not exist – do something.” }

To create a table:

if { [catch {$dbHandle eval “create table yourTableName(mrn INTEGER, personLastName TEXT, personFirstName TEXT, personMiddleName TEXT)”} catchErr] } { puts “Table creation failed (catchErr= $catchErr)” }

Then, you can open your file, skip any number of lines, then write the modified content to another file and use the copy command to write the file data to the DB.

Or, you could just open the DB, open the file, optionally skip the first N lines, then write the DB a row at a time.

Read a line of the file, write a row to the DB.

 

Jeff Dinsmore
Chesapeake Regional Healthcare

[Jim Kosloskey]

Jeff,

Thanks. That is along the lines of what I was thinking would need to be done given the out-of-synch situation between the distributed SQLite and the current on-line doc for SQLite. My point is we cannot use the on-line SQLite documentation to drive what we could do since it is always current but the release of SQLite we get with Cloverleaf is not.

My suggestion would be that if INFOR is not going to make it possible to use the latest release of Cloverleaf under their license irrespective of the Cloverleaf release, then it might be useful for INFOR to distribute the SQLite Doc in effect for the release of SQLite it does distribute.

I am assuming here that just like the ODBC Drivers the SQLite tool cannot be independently downloaded but needs to be certified to run with the release of Cloverleaf distributed. If this is not true, then it would be helpful if the Cloverleaf documentation somewhere (release notes, install, help) indicated that could be done.

The product (SQLite) has the ability built-in to skip records while importing just not in the release distributed with Cloverleaf (at least at the release level of Cloverleaf I am using).

It seems a shame to have to code around a release mis-match.

email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.

[Jeff Dinsmore]

Cloverleaf will likely always be a bit behind on SQLite and Tcl, so we’ll probably never have the latest features – even if we’re running the most recent version of Cloverleaf.  If we’re running an older CL version, the included SQLite and Tcl versions will be older as well.

I didn’t find documentation for non-current versions of SQLite, but did find a revision history that might be helpful  https://www.sqlite.org/changes.html

I wasn’t really suggesting you code around anything – just suggesting an alternative approach that’s perhaps better/faster/more capable.

For my money, exec-ing a shell function from Tcl is not my preferred method.  If I can use Tcl extension function to do SQLite stuff within the Tcl shell, that’s more desirable solution for me.

 

Jeff Dinsmore
Chesapeake Regional Healthcare

[Jim Kosloskey]

That is fair.

email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.

[Author]

Replies