SQLitePreparedStatement newbie

Databases
1

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 :

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

Purchases table structure :

2

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

@Scott Cadillac
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

You’re welcome Aditya,

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

5

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

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

@Scott Cadillac
Yes it exist with only 1 record:

813×677 24.1 KB

@Dave S & @Wayne Golding
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

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

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

@Dale Arends
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 Cadillac
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” :

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

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

@Thom McGrath
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.