CubeSQL/SQLite & PreparedStatements

I know there are a few Xojo users whom use CubeSQL & Sqlite interchangeably. I’ve used the two in a couple of project, but I’ve always avoided PreparedStatements.

I’m kind of wondering how do you handle PreparedStatements?

There are a few differences between the two plugins’ implementation of prepared statements:

  1. In cubeSQL, one does’t make separate calls to set a parameter’s type and then to bind a value to it. Both are combined into a single call like bindInt, bindDouble, bindText, etc.

  2. cubeSQL parameter numbering is 1-based, not 0-based.

  3. cubeSQL prepared statements (i.e., CubeSQLVMs) may not be re-used from one invocation to the next. For example, if myPS is a CubeSQLVM and mySQLStr is the SQL statement, each and every time (say in a loop) that one wants to use the statement, one must do something like this:

myPS = NIL myPS = mDB.VMPrepare(mySQLstr)
As I recall, I found that I needed to explicitly set the variable to NIL in order to get it to work. This is, AFAIK, unlike every other prepared statement implementation in Xojo. I no longer remember quite why, but Marco Bambini told me that this was a conscious design decision they made…

Thanks Peter… I didn’t realize CubeSQL prepared statements weren’t reusable. Thank You.

Check our SQLdeLite https://github.com/1701software/sqldelite

Open source and makes it much easier to work with SQLite and cubeSQL prepared statements.

Thanks Phillip! I’ll check it out.

Thank you, Peter for these 1,2,3 points.
I have spend about 6 hours trying to write simple loop against cubeSQL :frowning:
Indeed it is important to set = nil statement object, and each loop create it again.

This is my code:

 dim cmd_insert_t1 as string = "INSERT INTO T1(f1,f2) VALUES(?,?);" 
 dim stmt as CubeSQLVM = nil
  
  // We will produce table with records
  // f1      f2
  //  1      0
  //  2      1
  //  3      2
  // ...  
  For i as integer = 1 to kRecordCount
    stmt = nil
    stmt = mCubeDB.VMPrepare( cmd_insert_t1 )
    
    stmt.BindInt( 1, i )
    stmt.BindInt( 2, i - 1 )    
    stmt.VMExecute()
  Next
  
  mCubeDB.SQLExecute( "COMMIT;" )
   stmt = nil

For our Valentina SQLite Server v6 this task looks as expected:

  dim cmd_insert_t1 as string = "INSERT INTO T1(f1,f2) VALUES(?,?)"  
  dim vstmt as VSqlitePreparedStatement = mVLiteDB.PrepareEx( cmd_insert_t1 )
  
  // We will produce table with records
  // f1      f2
  //  1      0
  //  2      1
  //  3      2
  // ...
  mVLiteDB.SQLExecute( "BEGIN TRANSACTION" )
  
  For i as integer = 1 to kRecordCount
    vstmt.BindInt( 0, i )
    vstmt.BindInt( 1, i - 1 )    
    vstmt.SqlExecute()
  Next
  
  mVLiteDB.SQLExecute( "COMMIT" ) 
  vstmt = nil

With Valentina SQLite Server:

  1. you can use 100% the same API with Prepare and BindType() + Bind() methods, as well as additional set of bind methods.
  2. use again 100% compatible to Xojo API zero-based indexes in bind methods. This gives you zero-effort migration step.
  3. Prepared Statement is reusable.

[quote=128782:@Johnny Harris]I know there are a few Xojo users whom use CubeSQL & Sqlite interchangeably. I’ve used the two in a couple of project, but I’ve always avoided PreparedStatements.

I’m kind of wondering how do you handle PreparedStatements?[/quote]

i am in the same situation… i want to do the right thing by using Prepared Statements and i use both CubeSQL and SQLite but don’t know how to start

When I learned prepared statements this discussion was very helpful to identify the difference between sqlite and cubesql.

the best point was from Peter…

Peter Truskier 11 Sep 2014 Pre-Release Testers, Xojo Pro Oakland, CA USA
There are a few differences between the two plugins’ implementation of prepared statements:

  1. In cubeSQL, one does’t make separate calls to set a parameter’s type and then to bind a value to it. Both are combined into a single call like bindInt, bindDouble, bindText, etc.

  2. cubeSQL parameter numbering is 1-based, not 0-based.

  3. cubeSQL prepared statements (i.e., CubeSQLVMs) may not be re-used from one invocation to the next. For example, if myPS is a CubeSQLVM and mySQLStr is the SQL statement, each and every time (say in a loop) that one wants to use the statement, one must do something like this:

myPS = NIL
myPS = mDB.VMPrepare(mySQLstr)
As I recall, I found that I needed to explicitly set the variable to NIL in order to get it to work. This is, AFAIK, unlike every other prepared statement implementation in Xojo. I no longer remember quite why, but Marco Bambini told me that this was a conscious design decision they made.

I still recommend you check out SQLdeLite: https://github.com/1701software/sqldelite

MIT licensed drop in module. Does not change your existing queries or force you to do anything. However if you use its two new methods: SQLdeLiteSelect and SQLdeLiteExecute you can easily execute queries without dealing with prepared statements at all. Supports all Xojo databases classes out of box + cubeSQL.

[quote=360470:@Phillip Zedalis]I still recommend you check out SQLdeLite: https://github.com/1701software/sqldelite
.[/quote]
You should disclose that you have a vested interest in SQLdelite.

MIT licensed totally open source. I offer it because it makes the database classes easier to use. I am not asking for payment. To what interest do you believe I have in offering it for free?

or just use Valentina SQLite Server which have THE SAME binding functions as standard Xojo code

When should i use the prepared statement???
should i used it to read in a record and write a new record/update an existing record??
what about reading a whole bunch of record to be display in listbox, combobox or generating report???

  1. When - SQLite prefer it.

INSERT INTO T VALUES ( :1, :2, … ) << SQL binding
SELECT … WHERE fld > :1 … << SQL binding

So YES for inserts and updates.

In SELECT you can use it also, but you will READ values of found record(s) using RecordSet class.
So reading, generally speaking, is not related to preparedStatement.

  1. Bunch of records – this is READ many records found by SELECT. See above.