Book Library - revExecuteSQL in 7.0 (rc 2)

[rippertoews]

Hi,

I tried updating the command bookupdate to have placeholders to help with have special characters in the data... if I entered in a book with a title that has a quote - I can't update that book unless I take the quote out. I modified these lines

Code: Select all

   ## Construct the SQL statement and execute it on the database
  put "UPDATE books SET status='" & pStatus & "', title='" & pTitle & "',author='" & pAuthor & "',comments='" & pComments &"' WHERE BookID = '" & pBookID & "'" into tSQLStatement
revExecuteSQL sDatabaseID,tSQLStatement

And replaced it with this code...

Code: Select all

   
   put "UPDATE books SET status = :1, title = :2, author = :3, comments = :4 WHERE BookID = :5" into tSQLStatement
   revExecuteSQL sDatabaseID, tSQLStatement,  "pStatus","pTitle","pAuthor","pComments","pBookID"

It says the book was updated but the change never occurs - when I revert to the original code it works, I am making some kind of obvious error?

Thanks,
Ripper

[bangkok]

Special characters... need special treatment.

For instance, single quote have to be replaced with \'

Code: Select all

replace "'" with "\'" in tString

If you try to do a SQL update or insert with a string that contains a single quote, it wont work, because single quotes are used as delimiter by SQL.

Same for CR (carriage return) etc. A string with a CR inside, can create problem when you do a revDataFromQuery with CR delimiter (for records).

2 fixes :

Code: Select all

replace CR with "\n" in tString

or
change the standard delimiter with revDataFromQuery

Code: Select all

put revDataFromQuery (tab,"|",dbID,dbSQL) into tData
replace "|" with CR in tData

[rippertoews]

Thanks for the info... I will try it.

According to the documentation below for revExecuteSQL - is there something wrong with my updated command in the original post... it's not updating even when I'm not using quotes in the title...

This is the part I'm talking about..

Code: Select all

Comments:
The SQLStatement may contain one or more placeholders, which are sequential numbers prepended by a colon. The revExecuteSQL command substitutes the corresponding item in the variablesList for each of these placeholders. For example, if you have two variables called "valueX" and "valueY", you can use a SQLStatement that includes placeholders as follows:

revExecuteSQL myID, "insert into mytable values(:1,:2,:1)", "valueX","valueY"

The content of the variable valueX is substituted for the ":1" in the SQLQuery (in both places where ":1" appears), and the content of valueY is substituted for ":2".

Thanks,
Ripper

[bangkok]

One advice : before to use "variable list", start with the regular form

Code: Select all

put "update mytable set valueX=1 where valueX=0" into tSQL
answer tSQL
revExecuteSQL myID,tSQL
put the result into tResult
answer tResult

or

Code: Select all

put "insert into mytable (valueX,valueY) values ('1','2')" into tSQL
answer tSQL
revExecuteSQL myID,tSQL
put the result into tResult
answer tResult

-it's easier to read
-it allows you to know exactly what you send to the SQL server for easier debuging

[rippertoews]

Thanks for the info - I've tried that and it looks good. But I can't tell if the placeholder values are actually being replaced.. has anyone tried my example in 7.0 rc2?

Thanks,
Ripper