SQLitePreparedStatement newbie

  1. 8 weeks ago

    Aditya N

    Jun 27 Pre-Release Testers, Xojo Pro
    Edited 8 weeks ago

    Hello,

    I have the following SQLitePreparedStatement code to interfacing with my sqlitedatabase :

    Dim stmt As SQLitePreparedStatement = Self.Prepare("SELECT PurchaseID, PurchaseDate, ItemID, TotalPrice, PurchaseLocation, Quantity FROM Purchases WHERE PurchaseID=?")
    stmt.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
    stmt.Bind(0, purchaseNum)
    
    Dim rs As RecordSet = stmt.SQLSelect
    
    If rs <> Nil Then
      rs.Edit
      rs.Field("PurchaseDate").DateValue = purchaseDate
      rs.Field("Quantity").IntegerValue = Quantity 
      rs.Field("ItemID").IntegerValue = ItemID
      rs.Field("TotalPrice").IntegerValue = TotalPrice
      rs.Field("PurchaseLocation").IntegerValue = StoreName
      
      rs.Update
      
      rs.Close
    End If
    
    Return Not Self.Error

    The problem is the rs always failed to grab the "stmt.SQLSelect" or probably both stmt failures on SQLSelect?, i am getting rs/record set out of range because rs is always empty when querying the Purchases table. Upon debugging the rs RecordSet are always empty (NIL).

    This method parameters :

    purchaseDate As Date, purchaseNum As String, quantity As Integer, ItemID As Integer, TotalPrice As Integer, StoreName As Integer

    When querying manually on any SQLite DB Manager it working perfectly :
    -image-

    While debugging the method, purchaseNum is already grabbed from the values that are passed to this method :
    -image-

    Purchases table structure :
    -image-

    Thank you Aditya, for confirming that a record exists with PurchaseID=2.

    Admittedly, I've never looked over the Eddie's Electronic Example in any great detail, so the idea of updating a RecordSet returned by a SELECT statement is not something I do when I need to update a record in SQLite or any other database. Obviously it must work, but I'm not familiar enough with the concept to offer any helpful tips.

    At my day job I spend at least 75% or my time doing Database programming, so I prefer to use SQL DML syntax to directly UPDATE, DELETE, INSERT or MERGE table records.

    The following is an example of how I usually "update" a record in SQLite in my Xojo projects, based on the code you have provided above.

    Self.SQLExecute("BEGIN TRANSACTION")
    
    Dim sql As String 
    sql = "UPDATE Purchases SET PurchaseDate = ?, Quantity = ?, ItemID = ?, TotalPrice = ?, " _
    + " PurchaseLocation = ? WHERE PurchaseID = ?"
    
    Dim stmt As SQLitePreparedStatement = Self.Prepare(sql)
    
    stmt.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
    stmt.BindType(1, SQLitePreparedStatement.SQLITE_INTEGER)
    stmt.BindType(2, SQLitePreparedStatement.SQLITE_INTEGER)
    stmt.BindType(3, SQLitePreparedStatement.SQLITE_INTEGER)
    stmt.BindType(4, SQLitePreparedStatement.SQLITE_INTEGER)
    stmt.BindType(5, SQLitePreparedStatement.SQLITE_INTEGER)
    
    stmt.Bind(0, purchaseDate.SQLDate) '// *.SQLDate returns a date formatted string
    stmt.Bind(1, quantity)
    stmt.Bind(2, ItemID)
    stmt.Bind(3, TotalPrice)
    stmt.Bind(4, StoreName)
    stmt.Bind(5, Val(purchaseNum))
    
    stmt.SQLExecute
    
    If Self.Error Then
    Self.Rollback
    Else
    Self.Commit
    End If

    Note: The above code has not been tested.

    I hope my code suggestion is helpful.

    Edited: Added Transaction, Rollback & Commit for completeness.

  2. Scott C

    Jun 27 Pre-Release Testers, Xojo Pro Vancouver, Canada

    One minor issue I see in your code, which may not make any difference, is that the "PurchaseID" column is defined as an Integer in the database, but in your prepared statement, you are declaring it as SQLITE_TEXT when maybe it should be SQLITE_INTEGER?

    I know SQLite is supposed to be flexible with regards to data-types, but I would have written it a different way.

    Also, your debugger is showing the purchaseNum variable with a value of 2. Does such a record exist in the database, WHERE PurchaseID=2 ?

    I hope that helps.

  3. Aditya N

    Jun 27 Pre-Release Testers, Xojo Pro

    @Scott C
    Thank you, now i have changed it into :

    Dim stmt As SQLitePreparedStatement = Self.Prepare("SELECT PurchaseID, PurchaseDate, ItemID, TotalPrice, PurchaseLocation, Quantity FROM Purchases WHERE PurchaseID=?")
    stmt.BindType(0, SQLitePreparedStatement.SQLITE_INTEGER)
    stmt.Bind(0, Val(purchaseNum))
    
    Dim rs As RecordSet = stmt.SQLSelect

    But still makes no difference even tough i querying it successfully in SQLite Manager.

  4. Scott C

    Jun 27 Pre-Release Testers, Xojo Pro Vancouver, Canada

    You're welcome Aditya,

    And when you query in SQLite Manager, does a record exist for PurchaseID=2 ?

  5. Dave S

    Jun 27 San Diego, California USA
    Edited 8 weeks ago

    I will bet rs.recordcount = 0

    remember NIL is only if the query is not valid.... but a valid query can return zero records, and NOT be an error

  6. Wayne G

    Jun 27 Pre-Release Testers, Xojo Pro New Zealand axisdirect.nz

    Purchasenum = 2, but database only shows 1 record with an PurchaseID of 1, so as @Dave S says you are attempting to edit a non-existent record. You should use an INSERT SQL statement and bind the values to create the new record.

  7. Aditya N

    Jun 28 Pre-Release Testers, Xojo Pro

    @Scott C
    Yes it exist with only 1 record:
    -image-

    @Dave S & @Wayne G
    I am trying to update a record, and i am following the Eddie's Electronic Example, so far so good until that method. I don't have any problem on my other method for accessing the database or finding something but when updating i am getting that error. That code follows up the EE Example. The code above "UpdatePurchase" was called by this button action :

    Dim purchaseID As String
    purchaseID = txtPurchaseID.Text
    
    Dim purchaseDate As Date
    purchaseDate = dtControlPurchaseDate.DateValue
    
    Dim itemName As String
    itemName = ComItemName.Text
    
    Dim quantity As Integer
    quantity = Val(txtQuantity.Text)
    
    Dim totalPrice As Integer
    totalPrice = Val(txtTotalPrice.Text)
    
    Dim purchaseLocation As String
    purchaseLocation = ComStoreName.Text
    
    Dim ItemRS As RecordSet
    itemRS = App.Stockist.FindItemIDbyItemName(itemName)
    
    Dim StoreRS As RecordSet
    StoreRS = App.Stockist.FindStoreIDbyStoreName(purchaseLocation)
    
    Dim StoreID As Integer
    StoreID = StoreRS.Field("StoreID").IntegerValue
    
    Dim ItemID As Integer
    ItemID = ItemRS.Field("ItemID").IntegerValue
    
    
    
    // By starting a transaction, we revert any saved changes
    // in case an error occurs.
    App.Stockist.BeginTransaction
    
    // Existing invoice items are removed before adding the
    // current invoice items. This is simpler than attempting to
    // update invoice items individually.
    If App.Stockist.DeletePurchaseItems(purchaseID) Then
      // Save or Update Invoice
      If mNewPurchase Then
        // This is a new invoice, so a new row in the Invoice table is needed
        If Not App.Stockist.AddPurchase(purchaseDate, ItemID, StoreID, Quantity) Then
          MsgBox("Error saving purchase: " + App.Stockist.ErrorMessage)
          App.Stockist.CancelTransaction
          Return
        End If
      Else
        // Update existing invoice with new total and date
        MsgBox("Outside")
        If Not App.Stockist.UpdatePurchase(purchaseDate, purchaseID, quantity, ItemID, totalPrice, StoreID) Then
          MsgBox("Error updating purchase: " + App.Stockist.ErrorMessage)
          App.Stockist.CancelTransaction
          Return
        End If
      End If
      
      // Save all Invoice Items
      
      // Everything successfully saved, so by ending the transaction
      // the changes are made permanent in the database.
      App.Stockist.EndTransaction
      
      // This property is used by the caller that an invoice was created or changed
      // so that it can update the Invoice list.
      PurchaseNumber = purchaseID
      Self.Close
    Else
      //MsgBox("Error deleting old invoices: " + App.Orders.ErrorMessage)
      App.Stockist.CancelTransaction
      Return
    End If

    BeginTransaction :

    Self.SQLExecute("BEGIN TRANSACTION")

    EndTranscation :

    Self.Commit

    Been spending hours tried to finding the fixes but any possible permutation are not working.

  8. Dale A

    Jun 28 San Diego, California, USA
    Edited 8 weeks ago

    No, no. You are binding the purchaseNum, which has a value of 2, to the PurchaseID which is the Primary Key and has a value of 1. So your Select statement returns zero records since there is no record with a Key value of 2.

    I can't think of any circumstances where you would change the Primary Key. It is the indicator of the record and generally shouldn't, be used as a relevant record data value. A better approach would be to have a purchaseSequenceNumber field to reflect the purchase number for the customer.

  9. Scott C

    Jun 28 Pre-Release Testers, Xojo Pro Answer Vancouver, Canada
    Edited 8 weeks ago

    Thank you Aditya, for confirming that a record exists with PurchaseID=2.

    Admittedly, I've never looked over the Eddie's Electronic Example in any great detail, so the idea of updating a RecordSet returned by a SELECT statement is not something I do when I need to update a record in SQLite or any other database. Obviously it must work, but I'm not familiar enough with the concept to offer any helpful tips.

    At my day job I spend at least 75% or my time doing Database programming, so I prefer to use SQL DML syntax to directly UPDATE, DELETE, INSERT or MERGE table records.

    The following is an example of how I usually "update" a record in SQLite in my Xojo projects, based on the code you have provided above.

    Self.SQLExecute("BEGIN TRANSACTION")
    
    Dim sql As String 
    sql = "UPDATE Purchases SET PurchaseDate = ?, Quantity = ?, ItemID = ?, TotalPrice = ?, " _
    + " PurchaseLocation = ? WHERE PurchaseID = ?"
    
    Dim stmt As SQLitePreparedStatement = Self.Prepare(sql)
    
    stmt.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
    stmt.BindType(1, SQLitePreparedStatement.SQLITE_INTEGER)
    stmt.BindType(2, SQLitePreparedStatement.SQLITE_INTEGER)
    stmt.BindType(3, SQLitePreparedStatement.SQLITE_INTEGER)
    stmt.BindType(4, SQLitePreparedStatement.SQLITE_INTEGER)
    stmt.BindType(5, SQLitePreparedStatement.SQLITE_INTEGER)
    
    stmt.Bind(0, purchaseDate.SQLDate) '// *.SQLDate returns a date formatted string
    stmt.Bind(1, quantity)
    stmt.Bind(2, ItemID)
    stmt.Bind(3, TotalPrice)
    stmt.Bind(4, StoreName)
    stmt.Bind(5, Val(purchaseNum))
    
    stmt.SQLExecute
    
    If Self.Error Then
    Self.Rollback
    Else
    Self.Commit
    End If

    Note: The above code has not been tested.

    I hope my code suggestion is helpful.

    Edited: Added Transaction, Rollback & Commit for completeness.

  10. Aditya N

    Jun 29 Pre-Release Testers, Xojo Pro

    @Dale A
    I don't quite understand your way, but i need to finish this small application, probably will come back later on trying to understand the EE example ways. Anyway, thanks for the tips.

    @Scott C
    Thanks a lot for the example, but somehow the database doesn't get updated even tough there is no error, there is strange not error while debugging "not an error" :
    -image-

    The function now looks like this :

    Self.SQLExecute("BEGIN TRANSACTION")
    Dim sql As String 
    sql = "UPDATE Purchases SET PurchaseDate = ?, Quantity = ?, ItemID = ?, TotalPrice = ?, PurchaseLocation = ? WHERE PurchaseID = ?"
    
    Dim stmt As SQLitePreparedStatement = Self.Prepare(sql)
    
    stmt.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
    stmt.BindType(1, SQLitePreparedStatement.SQLITE_INTEGER)
    stmt.BindType(2, SQLitePreparedStatement.SQLITE_INTEGER)
    stmt.BindType(3, SQLitePreparedStatement.SQLITE_INTEGER)
    stmt.BindType(4, SQLitePreparedStatement.SQLITE_INTEGER)
    stmt.BindType(5, SQLitePreparedStatement.SQLITE_INTEGER)
    
    stmt.Bind(0, purchaseDate.SQLDate) '// *.SQLDate returns a date formatted string
    stmt.Bind(1, quantity)
    stmt.Bind(2, ItemID)
    stmt.Bind(3, TotalPrice)
    stmt.Bind(4, StoreID)
    stmt.Bind(5, Val(purchaseNum))
    
    stmt.SQLExecute
    
    If Self.Error Then
      Self.Rollback
    Else
      Self.Commit
    End If
    
    Return Not Self.Error

    Updated function/method parameters :

    purchaseDate As Date, purchaseNum As String, quantity As Integer, ItemID As Integer, TotalPrice As Integer, StoreID As Integer

    Tried rerunning the app but the database still doesn't get updated. Is it my database initialization is wrong? It's based on EE example.

    Tried running this query on the Sqlite Manager and running perfectly / gets updated

    UPDATE Purchases SET PurchaseDate = '2019-06-27', Quantity = 5, ItemID = 2, TotalPrice = NULL, PurchaseLocation = 1 WHERE PurchaseID = 2
  11. Thom M

    Jun 29 Pre-Release Testers Greater Hartford Area, CT

    Any chance your app is connecting to a different database than you think it is? I can’t find anything wrong with your code, so there has to be something else going on.

  12. Aditya N

    Jun 29 Pre-Release Testers, Xojo Pro

    @Thom M
    Super! Doh just checking thoroughly the EE DB initialization example code, it's recopy the DB itself from the original directory into EE appdata directory on app start up. Now everything work correctly!, thanks to All who helped me out.

or Sign Up to reply!