altSQLite variable substitution

Jim Bufalini yoursignup at yahoo.com
Mon Dec 11 09:28:46 CST 2006


Hi Mark,

> Hmmm-
>
> I know this has come up on the list before, but I can't find a
> resolution for it. I keep getting a syntax error on trying variable
> substitution with the altSQLite driver. I've got it down to a simple
> case now, and according to my interpretation of the documentation this
> should work. Instead I get 'syntax error near ")"'.
>
> Here's the errant line:
> revExecuteSQL dbID, "INSERT INTO CDData(id) VALUES(:1)", "tSSN"
>
> and this one works:
> revExecuteSQL dbID, "INSERT INTO CDData(id) VALUES(" &tSSN& ")"
>
> so I know there's no problem with either the database format or the
> data.
>

There are some peculiarities you need to be aware of using the current
revdb.dll and the altSQLite driver.

These apply to using the "bound" form of substitution, which is :1 etc. and
a following variable reference.

1. Never reference a variable that is empty. Rev will crash.
2. Don't reference vars that have chars that can be interpreted as math
symbols (dash, asterisk, plus, etc). They will cause Rev to crash. (I assume
SSN stands for Social Security Number as in XXX-XX-XXXX? For some reason the
drivers attempt to perform math with this eventhough it is "data"  :-(

Try this:

1. Type the SSN column as lower case "blob" (Not the standard BLOB) in the
database. Chris uses lower case to let his driver know this a binary column.
2. Then use Rev's compress function to compress your data as:

put compress(tSSN) into cSSN

Then write your SQL statement like this:

revExecuteSQL dbID, "INSERT INTO CDData(id) VALUES(:1)", "*bcSSN"

This will work in 99% of cases. Always with dash anyway. But I have found
there are some combinations of data which when compressed and referenced as
a bound variable also cause the combination Rev/altSQLite drivers to crash
Rev :-( I have not identified yet exactly what this data combo is yet.

I have switched to ciphering the data instead of using compress, but this is
much more complex as it requires cipher keys, etc. In your case the compress
should work.

Jim






More information about the use-livecode mailing list