Prepared Statement Commit

General
1

What is the proper way to commit to write to the sqlite database? This code below isn’t working.

[code]//Insert data in Listbox then in the database with a PreparedStatement
Dim ps As SQLitePreparedStatement = db.Prepare("INSERT INTO MyTable(sCol_1, sCol_2, iCol_1, iCol_2) VALUES(?, ?, ?, ?)

//Identify each type of data
Listbox1.AddRow
Listbox1.Cell(Listbox1.LastIndex, 0) = rs.Field(“sCol_1”).StringValue
Listbox1.Cell(Listbox1.LastIndex, 1) = rs.Field(“sCol_2”).StringValue
Listbox1.Cell(Listbox1.LastIndex, 3) = Cstr(rs.Field(“iCol_1”).IntegerValue)
Listbox1.Cell(Listbox1.LastIndex, 4) = Cstr(rs.Field(“iCol_2”).IntegerValue)

//add the data type to the binding
ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(1, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(3, SQLitePreparedStatement.SQLITE_INTEGER)
ps.BindType(4, SQLitePreparedStatement.SQLITE_INTEGER)

//add the data type to the binding
ps.Bind (0,“Str_1”)
ps.Bind (1,“Str_2”)
ps.Bind (2, 1)
ps.Bind (3, 2)

//make it happen
ps.SQLExecute(Commit)[/code]

2

what error is it generating? (I see no error checking code in your example)

and if this is being all done inside a loop (which I assume due to the listbox “addrow”) then you
can prepare the statement and bind the types ONCE outside the loop

3

ps.SQLExecute(Commit) ?? This is used in during a transaction.

It should be ps.SQLExecute() ??

4

[quote=342103:@]ps.SQLExecute(Commit) ?? This is used in during a transaction.

It should be ps.SQLExecute() ??[/quote]
good point… his code won’t even compile…

5

Nope

You are missing an end quote "and )

Dim ps As SQLitePreparedStatement = db.Prepare("INSERT INTO MyTable(sCol_1, sCol_2, iCol_1, iCol_2) VALUES(?, ?, ?, ?)")

6

Well it might if Commit is a string that is defined above the snippet somewhere, but that could be doing something super random, in fact, whatever was left in that string.

7

Sorry I made error of missing the end quotes they in my original code. But does the number of Question Marks have to be the same as the VALUES. I’m getting a syntax error in my original code.

VALUES(?, ?, ?, ?)") // 4 ?

It should be ps.SQLExecute() ??? // 4 ?

8

the number of “?” must match the number of BIND statements… which in this particular case is 4
the SQL statement must resolve to be correct regardless of replacement values …
“?” isn’t JUST used to fill out a “VALUES” clause… it could fill in ORDER BY, GROUP BY or any other part of a valid SQL statement

9

It should be:

ps.SQLExecute() or ps.SQLExecute (if you dont like wasting empty brackets)

The ?? was just me second guessing myself as I’ve not used that call in a while.

Have a look at File->New Project->Examples->Database->SQLite->SQLiteExample

Then search the project for execute and it will take you to EditWindow.SaveButton which has a nice example of what you want to do.

10

Thanks I’ll look into the examples to try to find another way to rewrite it. My current way is not working.

11

ps.SQLExecute()
db.Commit()

12

But remember, you only need db.Commit() if you start a transaction, you can write to the database without using Commit as every action that changes the database or its data automatically also has a Commit run after it.

See http://documentation.xojo.com/index.php/SQLiteDatabase (under Transactions) for more info.

13

You may consider using SQLdeLite. It’s open source and a drop in module that enhances the standard database classes to remove the “grunt work” of using prepared statements.

Check it out at https://www.github.com/1701Software/SQLdeLite