CubeSQL/SQLite & PreparedStatements

  1. 5 years ago

    Johnny H

    11 Sep 2014 Pre-Release Testers United States - Arkansas

    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?

  2. Peter T

    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...

  3. Johnny H

    11 Sep 2014 Pre-Release Testers United States - Arkansas

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

  4. Phillip Z

    12 Sep 2014 Pre-Release Testers, Xojo Pro Florence, SC

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

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

  5. Johnny H

    12 Sep 2014 Pre-Release Testers United States - Arkansas

    Thanks Phillip! I'll check it out.

  6. 4 years ago
    Edited 4 years ago

    Thank you, Peter for these 1,2,3 points.
    I have spend about 6 hours trying to write simple loop against cubeSQL :-(
    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.
  7. 2 years ago

    Richard D

    19 Nov 2017 Pre-Release Testers, Xojo Pro Europe (UK, London)

    @Johnny H 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?

    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

  8. James N

    19 Nov 2017 Pre-Release Testers, Xojo Pro Christchurch New Zealand

    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.

  9. Phillip Z

    19 Nov 2017 Pre-Release Testers, Xojo Pro Florence, SC

    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.

  10. Dave S

    19 Nov 2017 San Diego, California USA

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

    You should disclose that you have a vested interest in SQLdelite.

  11. Phillip Z

    19 Nov 2017 Pre-Release Testers, Xojo Pro Florence, SC
    Edited 2 years ago

    @Dave S 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?

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

  13. Richard D

    20 Nov 2017 Pre-Release Testers, Xojo Pro Europe (UK, London)

    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???

  14. Edited 2 years ago

    1) When - SQLite prefer it.

    2)
    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.

    3) Bunch of records -- this is READ many records found by SELECT. See above.

or Sign Up to reply!