Update Prepared Statement Syntax

General
1

I have a question about the Update Prepared Statement Syntax. I have my ID column set at 0. I got an answer in my last question for help.

You must Bind every “?” in the order they appear (ID is last in the statement, but first in you bind index).

If my ID column is 0 the WHERE ID is last in the syntax, How do I rewrite it to correct for the column 0 ?

I ID Col_1 Col_2 0 Item1 Item 2 1 NewItem1 NewItem2 // New Added Recored

[code]Dim ps as PreparedSQLStatement = db.Prepare (“UPDATE dbTable SET Col_1 = ?, Col_2 = ? WHERE ID = ?”)

ps.BindType(0, SQLitePreparedStatement.SQLITE_INTEGER)
ps.BindType(1, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(2, SQLitePreparedStatement.SQLITE_TEXT)

ps.Bind(0, 1)
ps.Bind(1, “NewItem1”)
ps.Bind(2, “NewItem2”)

ps.SQLExecute()[/code]

2

Yes you bind in the order they appear.

FYI the open source SQLdeLite code I wrote makes using prepared statements much easier: https://github.com/1701software/sqldelite

3

COL_1 is bind #0
COL_2 is bind #1
ID is BIND #2

Dim ps as PreparedSQLStatement = db.Prepare ("UPDATE dbTable SET Col_1 = ?, Col_2 = ? WHERE ID = ?")


ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT) 
ps.BindType(1, SQLitePreparedStatement.SQLITE_TEXT) 
ps.BindType(2, SQLitePreparedStatement.SQLITE_INTEGER)   


ps.Bind(0, "NewItem1")
ps.Bind(1, "NewItem2")
ps.Bind(2, 1)

ps.SQLExecute()
4

If WHERE ID selects the Row to UPDATE. Is there a way to rewrite it for ID Column 0 if it can be done at all.

5

Not understanding your question I guess… “ID COLUMN 0”???
is that “ID=0” , “ID=column0” where did “COLUMN 0” enter the equation?

perhaps is you post it here as if it were a “normal” SQL statement it might clarify things

6

Sorry I did not make it clear. My unique ID column is 0

ID  Col_1          Col_2
0   Item1          Item 2
1   NewItem1       NewItem2   // New Added Recored
7

So then what is your question?
The order of binding relates to the PREPARED Statement… NOT to the physical structure of the database table… that is totally unrelated…

// the first value is the ORDER in the PS, NOT the table
ps.Bind(0, "Item1")  //the FIRST "?" in the PS (col_1)
ps.Bind(1, "Item2") // the SECOND "?" in the PS (col_2)
ps.Bind(2, 0) // The 3rd "?" in the PS (ID)
8

Sorry you don’t understand my question. At the moment this is the only source code I can find from my purchased pdf book by Eugene Dankin. Can I rewrite it like this to force the order to match Prepared Statement to the physical database table or is that a syntax error. Rewritten below. The order of ? from left to right to the first value is the ORDER in the PS.

Dim ps as PreparedSQLStatement = db.Prepare ("UPDATE dbTable  WHERE ID = ?  SET Col_1 = ?, Col_2 = ?")

ps.BindType(0, SQLitePreparedStatement.SQLITE_INTEGER)   
ps.BindType(1, SQLitePreparedStatement.SQLITE_TEXT) 
ps.BindType(2, SQLitePreparedStatement.SQLITE_TEXT) 

ps.Bind(0, 1)
ps.Bind(1, "NewItem1")
ps.Bind(2, "NewItem2")

ps.SQLExecute()

I thought the prepared statement finely merges to make changes to the physical database at the ps.SQLExecute(). There is a relation at that point as far to what I read and understand it.

9

No you cannot do that as it creates an improper SQL Statement. The order of the SET items are not important, but the WHERE clause IS… There does NOT need to be a physical relationship between the order or the SET items and the physical order, that is why they are NAMED (col_1, col_2 and ID)

The “result” of your Prepared statement (in this case) is IDENTICAL to if you had execute this instead

"UPDATE dbTable SET col_1='NewItem1',col_2='NewItem2' WHERE ID=1

the Prepared Statement is a security/convience method. It insures that no SQL Injection can occur, and that any strings are properly escaped (ie. if they include commas, quotes etc.)… with a PS you don’t need to worry about that

10

ps.Bind(0, 1)

ps.bind(0,1) does not mean ‘Apply this value to the first field in my table’
It means 'insert this value into the SQL statement where you see the first question mark.

If the table was this
ID,Name,Owns

and the statement is this

update thetable set  Name = ?, Owns = ? where ID = ?

your binding would be

ps.Bind(0, "Dog") ps.Bind(1, "Fleas") ps.Bind(2, 1)

Xojo essentially turns the statement into this, using the bindings one by one

update thetable set  Name = 'Dog' , Owns = 'Fleas'   where ID = 1
11

Thanks guys for your help. My original Sqlite Update method in my project is updating 30 columns in a record. I had to make my code example simplified and reduced to 3 columns to make it easier to communicate. I keep getting a “Error:1 - Unable to prepare statement” when I test it. I’m just trying to nail down what in the Update Prepared Statement syntax is wrong.

12

that usually means you have a mismatch in the count of “?” vs Bind

13

Thanks Dave that helps a lot to track down the problem.