Class

# MySQLPreparedStatement

<div class="rst-class">

forsearch

</div>

Database

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

## Description

Used to create a PreparedSQLStatement for a MySQL Database.

## Methods

<div class="rst-class">

table-centered_column_4

</div>

| Name                                            | Parameters                                                                                                                           | Returns                         | Shared |
|-------------------------------------------------|--------------------------------------------------------------------------------------------------------------------------------------|---------------------------------|--------|
| `Bind<mysqlpreparedstatement.bind>`             | index As `Integer</api/data_types/integer>`, value As `Variant</api/data_types/variant>`                                             |                                 |        |
|                                                 | index As `Integer</api/data_types/integer>`, value As `Variant</api/data_types/variant>`, type As `Integer</api/data_types/integer>` |                                 |        |
|                                                 | values() As `Variant</api/data_types/variant>`                                                                                       |                                 |        |
| `BindType<mysqlpreparedstatement.bindtype>`     | index As `Integer</api/data_types/integer>`, type As `Integer</api/data_types/integer>`                                              |                                 |        |
| `ExecuteSQL<mysqlpreparedstatement.executesql>` | `ParamArray</api/language/paramarray>` bindValues() As `Variant</api/data_types/variant>`                                            |                                 |        |
| `SelectSQL<mysqlpreparedstatement.selectsql>`   | `ParamArray</api/language/paramarray>` bindValues() As `Variant</api/data_types/variant>`                                            | `RowSet</api/databases/rowset>` |        |

## Method descriptions

<div id="mysqlpreparedstatement.bind">

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

</div>

<div class="rst-class">

forsearch

</div>

MySQLPreparedStatement.Bind

**Bind**(index As `Integer</api/data_types/integer>`, value As `Variant</api/data_types/variant>`)

> Binds a *value* at the parameter *index* for the prepared statement.
>
> Use `Database.Prepare<database.prepare>` to set up the bind.
>
> This example creates a MySQL prepared statement to retrieve data from a *Customers* table. It then displays the data in a Listbox:
>
> ``` xojo
> Var stmt As MySQLPreparedStatement
>
> ' note in a prepared statement you DO NOT put in the quotes
> stmt = MySQLPreparedStatement(db.Prepare("SELECT * FROM Customers WHERE FirstName like ? "))
>
> ' have to tell MySQL what types the items being bound are so it does the right thing
> stmt.BindType(0, MySQLPreparedStatement.MYSQL_TYPE_STRING)
> stmt.Bind(0, TextField1.Text)
>
> ' perform the search
> Var rs As RowSet = stmt.SelectSQL
>
> ListBox1.RemoveAllRows
> ListBox1.ColumnCount = rs.ColumnCount
> ListBox1.HasHeader = True
>
> Var hasHeadings As Boolean
>
> While Not rs.AfterLastRow
>   ListBox1.AddRow("")
>
>   For i As Integer = 0 To rs.ColumnCount-1
>     If Not hasHeadings Then ListBox1.HeaderAt(i) = rs.ColumnAt(i+1).Name
>     ListBox1.CellTextAt(ListBox1.LastAddedRowIndex, i) = rs.ColumnAt(i+1).StringValue
>   Next
>
>   rs.MoveToNextRow
>   hasHeadings = True
> Wend
> ```

**Bind**(index As `Integer</api/data_types/integer>`, value As `Variant</api/data_types/variant>`, type As `Integer</api/data_types/integer>`)

> Binds a *value* and its *type* at the parameter *index* for the prepared statement.

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

**Bind**(values() As `Variant</api/data_types/variant>`)

> Binds multiple *values* for the prepared statement.

<div id="mysqlpreparedstatement.bindtype">

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

</div>

<div class="rst-class">

forsearch

</div>

MySQLPreparedStatement.BindType

**BindType**(index As `Integer</api/data_types/integer>`, type As `Integer</api/data_types/integer>`)

> Use this to specify an exact *type* for the parameter *index*. Each Database plug-in will have its own values.

<div id="mysqlpreparedstatement.executesql">

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

</div>

<div class="rst-class">

forsearch

</div>

MySQLPreparedStatement.ExecuteSQL

**ExecuteSQL**(`ParamArray</api/language/paramarray>` bindValues() As `Variant</api/data_types/variant>`)

> Same as SelectSQL but does not return a result set. Executes and returns the result set of the prepared statement.
>
> The *bindValues* parameter is optional and is intended for convenience only. If bindValues is not empty, ExecuteSQL will use the passed in values instead of the ones specified by calling Bind.

<div id="mysqlpreparedstatement.selectsql">

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

</div>

<div class="rst-class">

forsearch

</div>

MySQLPreparedStatement.SelectSQL

**SelectSQL**(`ParamArray</api/language/paramarray>` bindValues() As `Variant</api/data_types/variant>`) As `RowSet</api/databases/rowset>`

> Executes and returns the result set of the prepared statement.
>
> The *bindValues* parameter is optional and is intended for convenience only. If *bindValues* is not empty SelectSQL will use the passed in values instead of the ones specified by calling Bind.

## Interfaces

This class implements the `PreparedSQLStatement</api/databases/preparedsqlstatement>` interface.

## Notes

<div class="note">

<div class="title">

Note

</div>

The use of the prepared statement classes is rare because `Database.SelectSQL<database.selectsql>` and `Database.ExecuteSQL<database.executesql>` utilize them automatically. See `PreparedSQLStatement</api/databases/preparedsqlstatement>` for information on cases where using prepared statement classes is appropriate.

</div>

MySQL uses the "?" character as the parameter indicator in the prepared statement, i.e. `"SELECT * FROM Persons WHERE Name = ?"`.

You must bind the type of the parameters using BindType.

These are the constants to use with the BindType method:

| Constant             |
|----------------------|
| MYSQL_TYPE_BLOB      |
| MYSQL_TYPE_DATE      |
| MYSQL_TYPE_DATETIME  |
| MYSQL_TYPE_DOUBLE    |
| MYSQL_TYPE_FLOAT     |
| MYSQL_TYPE_LONG      |
| MYSQL_TYPE_LONGLONG  |
| MYSQL_TYPE_NULL      |
| MYSQL_TYPE_SHORT     |
| MYSQL_TYPE_STRING    |
| MYSQL_TYPE_TIME      |
| MYSQL_TYPE_TIMESTAMP |
| MYSQL_TYPE_TINY      |

If you add a MySQL database directly to your project, then you need to cast the result of the call to Database.Prepare to the `MySQLCommunityServer</api/databases/mysqlcommunityserver>` class. If you do not cast you will get a NilObjectException when you try to use it (refer to the example below).

## Sample code

The following sample connects to a MySQL database, adds a table and populates it. It then uses a Prepared Statement to query the table and get a `RowSet</api/databases/rowset>`.

``` xojo
Var db As New MySQLCommunityServer
db.Host = "127.0.0.0"
db.Username = "admin"
db.Password = "admin"

If Not db.Connect Then Return

db.ExecuteSQL("CREATE TABLE Persons (Name, Age)")
db.ExecuteSQL("INSERT INTO Persons (Name, Age) VALUES ('john', 20)")
db.ExecuteSQL("INSERT INTO Persons (Name, Age) VALUES ('john', 21)")
db.ExecuteSQL("INSERT INTO Persons (Name, Age) VALUES ('john', 22)")
db.ExecuteSQL("INSERT INTO Persons (Name, Age) VALUES ('john', 20)")
db.ExecuteSQL("INSERT INTO Persons (Name, Age) VALUES ('john', 21)")
db.ExecuteSQL("INSERT INTO Persons (Name, Age) VALUES ('john', 22)")
db.ExecuteSQL("COMMIT")

Var ps As PreparedSQLStatement = _
  db.Prepare("SELECT * FROM Persons WHERE Name = ? AND Age >= ?")

ps.BindType(0, MySQLPreparedStatement.MYSQL_TYPE_STRING)
ps.BindType(1, MySQLPreparedStatement.MYSQL_TYPE_LONG)
ps.Bind(0, "john")
ps.Bind(1, 20)

Var rs As RowSet = ps.SelectSQL

Try
  While Not rs.AfterLastRow
    MessageBox("Name: " + rs.Field("Name").StringValue + _
      " Age: " + rs.Field("Age").StringValue)
    rs.MoveToNextRow
  Wend
Catch error As DatabaseException
  MessageBox(error.Message)
  Return
End Try
```

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

This code passes the values to bind using the SelectSQL method rather than calling the Bind method:

``` xojo
Var db As New MySQLCommunityServer
db.Host = "127.0.0.0"
db.Username = "admin"
db.Password = "admin"

If Not db.Connect Then Return

db.ExecuteSQL("CREATE TABLE Persons (Name, Age)")
db.ExecuteSQL("INSERT INTO Persons (Name, Age) VALUES ('john', 20)")
db.ExecuteSQL("INSERT INTO Persons (Name, Age) VALUES ('john', 21)")
db.ExecuteSQL("INSERT INTO Persons (Name, Age) VALUES ('john', 22)")
db.ExecuteSQL("INSERT INTO Persons (Name, Age) VALUES ('john', 20)")
db.ExecuteSQL("INSERT INTO Persons (Name, Age) VALUES ('john', 21)")
db.ExecuteSQL("INSERT INTO Persons (Name, Age) VALUES ('john', 22)")
db.ExecuteSQL("COMMIT")

Var ps As PreparedSQLStatement = _
  db.Prepare("SELECT * FROM Persons WHERE Name = ? AND Age >= ?")

ps.BindType(0, MySQLPreparedStatement.MYSQL_TYPE_STRING)
ps.BindType(1, MySQLPreparedStatement.MYSQL_TYPE_LONG)

Var rs As RowSet = ps.SelectSQL("john", 20)

Try
  While Not rs.AfterLastRow
    MessageBox("Name: " + rs.Field("Name").StringValue + _
      " Age: " + rs.Field("Age").StringValue)
    rs.MoveToNextRow
  Wend
Catch error As DatabaseException
  MessageBox(error.Message)
  Return
End Try
```

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

This code uses and reuses a Prepared Statement for the Insert:

``` xojo
Var db As New MySQLCommunityServer
db.Host = "127.0.0.0"
db.Username = "admin"
db.Password = "admin"

If Not db.Connect Then Return

db.ExecuteSQL("CREATE TABLE Persons(Name, Age)")

Var ps As PreparedSQLStatement = _
  db.Prepare("INSERT INTO Persons (Name, Age) VALUES (?, ?)")

ps.BindType(0, MySQLPreparedStatement.MYSQL_TYPE_STRING)
ps.BindType(1, MySQLPreparedStatement.MYSQL_TYPE_LONG)

ps.ExecuteSQL("john", 20)
ps.ExecuteSQL("john", 21)
ps.ExecuteSQL("john", 22)
ps.ExecuteSQL("john", 20)
ps.ExecuteSQL("john", 21)
ps.ExecuteSQL("john", 22)
db.ExecuteSQL("COMMIT")

ps = db.Prepare("SELECT * FROM Persons WHERE Name = ? AND Age >= ?")
ps.BindType(0, MySQLPreparedStatement.MYSQL_TYPE_STRING)
ps.BindType(1, MySQLPreparedStatement.MYSQL_TYPE_LONG)

Var rs As RowSet = ps.SelectSQL("john", 20)

Try
  While Not rs.AfterLastRow
    MessageBox("Name: " + rs.Field("Name").StringValue + _
      " Age: " + rs.Field("Age").StringValue)
    rs.MoveToNextRow
  Wend
Catch error As DatabaseException
  MessageBox(error.Message)
  Return
End Try
```

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

This code uses a MySQL Database that was added to the project, so it has to cast the result from db.Prepare.

``` xojo
Var ps As MySQLPreparedStatement
ps = MySQLPreparedStatement(CustomerDB.Prepare("SELECT * FROM Persons WHERE Name = ? AND Age >= ?"))
ps.Bind(0, "John")
ps.BindType(0, MySQLPreparedStatement.MYSQL_TYPE_STRING)
ps.Bind(1, 20)
ps.BindType(1, MySQLPreparedStatement.MYSQL_TYPE_LONG)

Var rs As RowSet = ps.SelectSQL
```

## Compatibility

|                       |                       |
|-----------------------|-----------------------|
| **Project Types**     | Console, Desktop, Web |
| **Operating Systems** | All                   |

<div class="seealso">

`Object</api/data_types/additional_types/object>` parent class; `Database</api/databases/database>`, `MySQLCommunityServer</api/databases/mysqlcommunityserver>`, `PreparedSQLStatement</api/databases/preparedsqlstatement>`

</div>