# SQLitePreparedStatement.BindType doesn't get all the types

**URL:** <https://forum.xojo.com/t/sqlitepreparedstatement-bindtype-doesnt-get-all-the-types/58933>\
**Category:** Databases\
**Created:** [November 19, 2020, 12:04am UTC](https://forum.xojo.com/t/sqlitepreparedstatement-bindtype-doesnt-get-all-the-types/58933 "2020-11-19T00:04:16Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Kristin\_Green](https://forum.xojo.com/user_avatar/forum.xojo.com/kristin_green/32/3167_2.png) [@Kristin\_Green](https://forum.xojo.com/u/Kristin_Green)\
**Post date:** [November 19, 2020, 12:04am UTC](https://forum.xojo.com/t/sqlitepreparedstatement-bindtype-doesnt-get-all-the-types/58933/1 "2020-11-19T00:04:17Z")

</div>

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

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

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

```auto
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](http://documentation.xojo.com/api/databases/preparedsqlstatement.html).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.

---

<div class="post-metadata">

**Author:** ![Kristin\_Green](https://forum.xojo.com/user_avatar/forum.xojo.com/kristin_green/32/3167_2.png) [@Kristin\_Green](https://forum.xojo.com/u/Kristin_Green)\
**Post date:** [November 19, 2020, 12:11am UTC](https://forum.xojo.com/t/sqlitepreparedstatement-bindtype-doesnt-get-all-the-types/58933/2 "2020-11-19T00:11:45Z")

</div>

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

```auto
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

```

---

<div class="post-metadata">

**Author:** ![Tim\_Hare](https://forum.xojo.com/user_avatar/forum.xojo.com/tim_hare/32/176_2.png) [@Tim\_Hare](https://forum.xojo.com/u/Tim_Hare)\
**Post date:** [November 19, 2020, 1:33am UTC](https://forum.xojo.com/t/sqlitepreparedstatement-bindtype-doesnt-get-all-the-types/58933/3 "2020-11-19T01:33:10Z")

</div>

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

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

```

---

<div class="post-metadata">

**Author:** ![Kristin\_Green](https://forum.xojo.com/user_avatar/forum.xojo.com/kristin_green/32/3167_2.png) [@Kristin\_Green](https://forum.xojo.com/u/Kristin_Green)\
**Post date:** [November 19, 2020, 1:49am UTC](https://forum.xojo.com/t/sqlitepreparedstatement-bindtype-doesnt-get-all-the-types/58933/4 "2020-11-19T01:49:54Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Tim\_Hare](https://forum.xojo.com/user_avatar/forum.xojo.com/tim_hare/32/176_2.png) [@Tim\_Hare](https://forum.xojo.com/u/Tim_Hare)\
**Post date:** [November 19, 2020, 2:45am UTC](https://forum.xojo.com/t/sqlitepreparedstatement-bindtype-doesnt-get-all-the-types/58933/5 "2020-11-19T02:45:40Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Kristin\_Green](https://forum.xojo.com/user_avatar/forum.xojo.com/kristin_green/32/3167_2.png) [@Kristin\_Green](https://forum.xojo.com/u/Kristin_Green)\
**Post date:** [November 19, 2020, 9:40pm UTC](https://forum.xojo.com/t/sqlitepreparedstatement-bindtype-doesnt-get-all-the-types/58933/6 "2020-11-19T21:40:54Z")

</div>

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