SQLite, Bindings, Int64 and A Tired Brain

[Eric_Kringle]

When updating to prepared statements I’ve run into a hitch. I’ve been using the LastRowID to get an internal number I can use across tables. It used to work just fine with the following code:

var AddressRS as new DatabaseRow
//Update Information in _StudentAddress
AddressRS.Column("InternalStudentID") = Session.pCurrentStudentID
AddressRS.Column("Address") = StudentBasicInfoContainer1.HomeAddress1Field.text
AddressRS.Column("Address2") = StudentBasicInfoContainer1.HomeAddress2Field.text
AddressRS.Column("City") = StudentBasicInfoContainer1.CityField.text
AddressRS.Column("State") = StudentBasicInfoContainer1.StateField.text
AddressRS.Column("PostalCode") = StudentBasicInfoContainer1.ZipField.text
AddressRS.Column("Plus4") = StudentBasicInfoContainer1.PlusFourField.text

try
  Session.db.AddRow("_StudentAddress", AddressRS)
end try

Now I’m trying to update everything and future-proof my code a bit, but my updated code no longer works. Everything binds except Session.pCurrentStudentID, so the majority of the information goes into the database, but the common key isn’t there.

My assumption is that, since pCurrentStudentID is an Int64 (because the program I’m using to maintain my database was spitting out huge auto incremented numbers) and the SQLite column is an Integer that it doesn’t want to play nice. But the large number is coming from SQLite in the first place. Xojo is the one that didn’t want to accept the number as an integer.

Here is the almost-working code:

session.pCurrentInternalStudentID=Session.db.LastRowID
MessageBox (session.pCurrentInternalStudentID.tostring) 
//I only used this to make sure the value was being properly set. It is.

try
  //_STUDENTADDRESSTABLE
  var sql as string =  "INSERT INTO _StudentAddress (InternalStudentID, AddressType, Address, City, State, PostalCode, Plus4, Country, Address2) VALUES (?,?,?,?,?,?,?,?,?)"
  var ps as SQLitePreparedStatement = session.db.prepare(sql)
  
  ps.BindType(0, SQLitePreparedStatement.SQLITE_INTEGER)//This is where it doesn't bind properly.
  ps.BindType(1, SQLitePreparedStatement.SQLITE_INTEGER)
  ps.BindType(2, SQLitePreparedStatement.SQLITE_TEXT)
  ps.BindType(3, SQLitePreparedStatement.SQLITE_TEXT)
  ps.BindType(4, SQLitePreparedStatement.SQLITE_TEXT)
  ps.BindType(5, SQLitePreparedStatement.SQLITE_TEXT)
  ps.BindType(6, SQLitePreparedStatement.SQLITE_TEXT)
  ps.BindType(7, SQLitePreparedStatement.SQLITE_TEXT)
  ps.BindType(8, SQLitePreparedStatement.SQLITE_TEXT)
  

  ps.bind(0, session.pCurrentStudentID)// I'm assuming it's because session.pCurrentInternalStudentID is an Int64, but I can't figure out how to convert it.
  ps.bind(1, 1)
  ps.bind(2, StudentBasicInfoContainer1.HomeAddress1Field.Value)
  ps.bind(3, StudentBasicInfoContainer1.CityField.Value)
  ps.bind(4, StudentBasicInfoContainer1.StatePopup.SelectedRowValue)
  ps.bind(5, StudentBasicInfoContainer1.ZipField.Value)
  ps.bind(6, StudentBasicInfoContainer1.PlusFourField.Value)
  ps.bind(7, "United States")
  ps.bind(8, StudentBasicInfoContainer1.HomeAddress2Field.Value)
  
  ps.SQLExecute
end try

Any help would be greatly appreciated.

[Edwin_van_den_Akker]

Have you considered using a SQL statement as shown in the code below?
You could include the values as parameters in the ExecuteSQL method.

Try
  ' Use a SQL query... the underscore symbol lets you split a xojo line, to continue on the next line. This makes it easier to read :-)
  
  db.ExecuteSQL "INSERT INTO _StudentAddress (InternalStudentID, AddressType, Address, City, State, PostalCode, Plus4, Country, Address2) VALUES (?,?,?,?,?,?,?,?,?)", _
  session.pCurrentStudentID), _ 
  1, _ 
  StudentBasicInfoContainer1.HomeAddress1Field.Value, _ 
  StudentBasicInfoContainer1.CityField.Value, _ 
  StudentBasicInfoContainer1.StatePopup.SelectedRowValue, _ 
  StudentBasicInfoContainer1.ZipField.Value, _ 
  StudentBasicInfoContainer1.PlusFourField.Value, _ 
  "United States", _ 
  StudentBasicInfoContainer1.HomeAddress2Field.Value
  
  
  ' Xojo will deal with the bindings. Prepared statements in your code are not necessary anymore
  
Catch e As DatabaseException
  'handle the error, if one occured.
  
  MessageBox e.Message
  
End

Check the Database.ExecuteSQL docs for the correct syntax.

[Edwin_van_den_Akker]

To update the data, you could use the ExecuteSQL in almost the same way.

Updating multiple columns

[Eric_Kringle]

Thank you, and I will certainly give that a try.

I’m coming back to (hobby) programming after a 10 year hiatus, and while some things have come back very quickly, some have not (and some have changed and I’m still trying to wrap my head around them).

I appreciate the help!

[Edwin_van_den_Akker]

@Eric_Kringle

…some have changed…

Especially now, that the framework is gradually switching to API 2. Lots of things change. I have good hopes for the new 2020r2 version of Xojo. I have seen amazing stuff in the Beta version.