SQLitePreparedStatement.BindType doesn't get all the types

Databases
1

I just wanted to check here before I file a bug report.

This code, taken almost exactly from the Language Reference, works perfectly…

Var db As New SQLiteDatabase
Try
  db.Connect
  db.ExecuteSQL("CREATE TABLE Persons(Name, Age)")
  
  Var ps As SQLitePreparedStatement = _
  db.Prepare("INSERT INTO Persons (Name, Age) VALUES (?, ?)")
  
  ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
  ps.BindType(1, SQLitePreparedStatement.SQLITE_INTEGER)
  
  ps.ExecuteSQL("john", 20)
  
  ps = db.Prepare("SELECT * FROM Persons WHERE Name = ? AND Age >= ?")
  ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
  ps.BindType(1, SQLitePreparedStatement.SQLITE_INTEGER)
  
  Var rs As RowSet = ps.SelectSQL("john", 20)
  For Each row As DatabaseRow In rs
    MessageBox("Name: " + row.Column("Name").StringValue + _
    " Age: " + row.Column("Age").StringValue)
  Next
  rs.Close
Catch error As DatabaseException
  MessageBox("Database Error: " + error.Message)
End Try

This code, which simply passes the exact same data but in the form of an array, does not.

Var db As New SQLiteDatabase
Try
  db.Connect
  db.ExecuteSQL("CREATE TABLE Persons(Name, Age)")
  
  Var ps As SQLitePreparedStatement = _
  db.Prepare("INSERT INTO Persons (Name, Age) VALUES (?, ?)")
  
  Var types() As Integer = Array( _
  SQLitePreparedStatement.SQLITE_TEXT, _
  SQLitePreparedStatement.SQLITE_INTEGER _
  )
  ps.BindType(types)
  
  Var values() As Variant = Array( "john", 20 )
  ps.Bind(values)
  
  ps.ExecuteSQL
  
  ps = db.Prepare("SELECT * FROM Persons WHERE Name = ? AND Age >= ?")
  ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
  ps.BindType(1, SQLitePreparedStatement.SQLITE_INTEGER)
  
  Var rs As RowSet = ps.SelectSQL("john", 20)
  For Each row As DatabaseRow In rs
    MessageBox("Name: " + row.Column("Name").StringValue + _
    " Age: " + row.Column("Age").StringValue)
  Next
  rs.Close
Catch error As DatabaseException
  MessageBox("Database Error: " + error.Message)
End Try

It gives me the following error…

2 parameters are being bound, but only 1 types were specified

Clearly, two BindTypes are being specified. Why is it only getting one of them?

PreparedSQLStatement.BindType(types() As Integer)

Supported for all project types and targets.

Specify types for multiple bind values. Each Database plug-in will have its own values.

2

In case you’re wondering, this code also works… albeit, not as elegantly…

Var db As New SQLiteDatabase
Try
  db.Connect
  db.ExecuteSQL("CREATE TABLE Persons(Name, Age)")
  
  Var ps As SQLitePreparedStatement = _
  db.Prepare("INSERT INTO Persons (Name, Age) VALUES (?, ?)")
  
  Var types() As Integer = Array( _
  SQLitePreparedStatement.SQLITE_TEXT, _
  SQLitePreparedStatement.SQLITE_INTEGER _
  )
  
  For i As Integer = 0 To types.Count - 1
    ps.BindType(i, types(i))
  Next
  
  Var values() As Variant = Array( "john", 20 )
  For i As Integer = 0 To values.Count - 1
    ps.Bind(i, values(i))
  Next
  
  ps.ExecuteSQL
  
  ps = db.Prepare("SELECT * FROM Persons WHERE Name = ? AND Age >= ?")
  ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
  ps.BindType(1, SQLitePreparedStatement.SQLITE_INTEGER)
  
  Var rs As RowSet = ps.SelectSQL("john", 20)
  For Each row As DatabaseRow In rs
    MessageBox("Name: " + row.Column("Name").StringValue + _
    " Age: " + row.Column("Age").StringValue)
  Next
  rs.Close
Catch error As DatabaseException
  MessageBox("Database Error: " + error.Message)
End Try
3

You’re mixing the old and new versions. ExecuteSQL doesn’t use a PreparedStatement. You just pass the values in the function call.

db.ExecuteSQL("INSERT INTO Persons (Name, Age) VALUES (?, ?)", "john", 20)
4

Where in the documentation does it denote old vs new versions?

How then would I use

.Bind(values() As Variant)

and

.BindType(types() As Integer)

?

My goal here is to dynamically form the prepared statements from arrays.

5

The docs page for SQLitePreparedStatement states that it is no longer needed in most cases. ExecuteSQL does the binding for you. Prepared statements are for the most part unneeded.

6

Ah, so this would be the correct way to do it…

Var db As New SQLiteDatabase
Try
  db.Connect
  db.ExecuteSQL("CREATE TABLE Person (Name, Age)")
  
  Var sql As String = "INSERT INTO Person (Name, Age) VALUES (?, ?);"
  Var values() As Variant = Array( "john", 20 )
  
  db.ExecuteSQL(sql, values)
  
  Var sql2 As String = "SELECT * FROM Person WHERE Name = ? AND Age >= ?"
  Var values2() As Variant = Array( "john", 20 )
  
  Var rs As RowSet = db.SelectSQL(sql2, values2)
  For Each row As DatabaseRow In rs
    MessageBox("Name: " + row.Column("Name").StringValue + _
    " Age: " + row.Column("Age").StringValue)
  Next
  rs.Close
Catch error As DatabaseException
  MessageBox("Database Error: " + error.Message)
End Try