TCL Syntax error for SQLite query

[Tahir Shaikh]

Hello,

I am new to tcl programming and facing one issue.

I have written SQLite query to insert data in SQLite database as below

set insertRecordQuery “Insert into PatientSelectRecords ( MessageControlId, Hl7InboundRawMessage, InterfaceName, IsProcessed,

                                                          EventType, FirstName, LastName, AriaConnectContract, DateOfBirth )

      values

            ( ‘$messageControlId’, ‘$hl7RawMessage’, ‘$interfaceName’, ‘N’, ‘$eventType’, ‘$firstName’,

            ‘$lastName’, ‘$convertedXlate’ , ‘$dateOfBirth’)”

Here my hl7RawMessage parameter contains some text with ‘ character. e.g. Children’s Hospital

Occurance of above character is throwing exception as incorrect syntax near ‘

Thanks

Topic

[Terry Kellum]

A few suggestions:

Don’t enclose your variables in single quotes.  Single quotes prevent “magic” replacement of variables.

Use the syntax :variable (replacing the $ with a : ).  This removes the variable replacement function from TCL and hands that responsibility to SQLite.

Quoting can be tricky.  You might want to try enclosing your query with Curly Brackets {xxxx} instead of “”.

Your mileage may vary.  Make sure you test well and make sure your test data includes commas and backslashes.

[Brandon Grudt]

Wouldn’t he then have to prepare the SQL statement by assigning the variable to :variable?  I’m not familiar with SQLite, but I know that is the case with MySQL PDO queries.  Something like this?

$stmt->bindParam(‘:MessageControlID’, ‘$messageControlId’, PDO::PARAM_STR);

[Terry Kellum]

No, the assignment is assumed by SQLite.  It sees the colon, and may internally process it as a parameter, I have never needed to do a specific Bind step.  Merely put in the variable and it works.

As for the variable replacements, the colons ( : ) replace the $ in variables.

If you have a TCL variable in your script – $ThisIsMyData your query would appear thusly:

{ UPDATE TABLE mytable

[Author]

Replies