Store message in SQLite table

Here’s an example of how I’ve done it in the past.


[code]

Here’s how I do mine.  I do the regsub because a single quote in your message will prevent your message from entering the database properly.  I do the “null” on insert because I have the first field (id) set to auto increment.

I think it is actually preferable to pass the TCL variable names to SQLite rather than the values. This can avoid problems with quotes and other escape sequences in the data and potentially protect you against SQL injection attacks (although that is pretty unlikely in this case).

Here’s some documentation I found on the web:

Tcl variable names can appear in the SQL statement of the second argument in any position where it is legal to put a string or number literal. The value of the variable is substituted for the variable name. If the variable does not exist a NULL values is used. For example:

   db1 eval {INSERT INTO t1 VALUES(5,$bigstring)}

Note that it is not necessary to quote the $bigstring value. That happens automatically. If $bigstring is a large string or binary object, this technique is not only easier to write, it is also much more efficient since it avoids making a copy of the content of $bigstring.

David, are you saying instead of this:

do this

?

I think it is actually preferable to pass the TCL variable names to SQLite rather than the values. This can avoid problems with quotes and other escape sequences in the data and potentially protect you against SQL injection attacks (although that is pretty unlikely in this case).

Here’s some documentation I found on the web:

Tcl variable names can appear in the SQL statement of the second argument in any position where it is legal to put a string or number literal. The value of the variable is substituted for the variable name. If the variable does not exist a NULL values is used. For example:

David, are you saying instead of this:

do this

?

No, you should do this:

And don’t worry about doing a regsub to fix the single quotes. They aren’t a problem if you use this style of programming.

Great.  Thanks for the clarification.

David,

When I use the following tcl statment with curly braces

set insert {insert into dbo.tumble (id, msg) values ($id, $msg)}

I get an error when I execute it:

insert = insert into dbo.tumble (id, msg) values ($id, $msg)

tumble: Failed to insert msg  [DataDirect][ODBC SQL Server Driver][SQL Server]Invalid pseudocolumn “$id”.

stmt_err = SQL_ERROR sql statement = insert into dbo.tumble (id, msg) values ($id, $msg)

If I add single quotes around the params it works fine.

Here’s a code snippet:

       set insert {insert into dbo.tumble (id, msg) values ($id, $msg)}

       if { $debug } { echo “insert = $insert” }

       set stmt_err [odbc SQLExecDirect $hstmt $insert SQL_NTS]

       if { $stmt_err != “SQL_SUCCESS” } {

           # try to determine why the query failed

           catch {odbc SQLGetDiagRec SQL_HANDLE_STMT $hstmt 1

               SqlState NativeError errText 511 TextLength}

           set errormsg “$procName: Failed to insert msgt

               $errText”

           if { $debug } {

               echo “$errormsgnstmt_err = $stmt_errt sql statement =

                   $insert”

           }

Note that this is going against a SQL Server.

Any thoughts on why this is failing?

thanks,

Steve

Tcl will not evaluate variables inside of Braces {}

Assuming your variables are set, try this:

set stmt_err [odbc SQLExecDirect $hstmt [subst $insert] SQL_NTS]

Charlie,

That works, but only where the HL7 msg does not have a single quote in it.  If the msg has a single quote you have to do a regsub on it, as I’m sure you know.  It does’t look like there’s anyway around doing a regsub.

I use:    

thanks,

Steve

set msg [string map

$map]

Do same for ID if it has apostrephes

Note ” aove is two apostrephes not a double quote

That works!

So I’m guessing ‘string map’ is more efficient than ‘regsub’.

thanks,

Steve

In this case, yes

Steve,

The earlier messages in this thread were about SQLite. The way that you bind program variables to database columns is very simple. You’re asking how to do it with DataDirect Connect. For that you need to use SQLBindParameter. There’s an example of how to do this in the Cloverleaf documentation. I think it is something like this:

Thanks David,

I know how to bind/fetch the variables, I mistakenly thought you had found a way to pass a string in to SQL w/o having to modify the string based on the presence of single quotes.

–Steve

With sqlite, you can pass $variable to the library so you don’t have to worry about quotes. With DataDirect, you can either use SQLBindParameter to avoid the quote problems or escape quotes from your variables as you substitute them into the SQL command on your own.