Interface

# PreparedSQLStatement

<div class="rst-class">

forsearch

</div>

Database

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

## Description

Provides the API for working with prepared statements. Use `Database.Prepare<database.prepare>` to create a <span class="title-ref">PreparedSQLStatement</span>.

## Methods

<div class="rst-class">

table-centered_column_4

</div>

| Name                                          | Parameters                                                                                                                           | Returns                         | Shared |
|-----------------------------------------------|--------------------------------------------------------------------------------------------------------------------------------------|---------------------------------|--------|
| `Bind<preparedsqlstatement.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<preparedsqlstatement.bindtype>`     | index As `Integer</api/data_types/integer>`, type As `Integer</api/data_types/integer>`                                              |                                 |        |
|                                               | types() As `Integer</api/data_types/integer>`                                                                                        |                                 |        |
| `ExecuteSQL<preparedsqlstatement.executesql>` | `ParamArray</api/language/paramarray>` bindItems As `Variant</api/data_types/variant>`                                               |                                 |        |
| `SelectSQL<preparedsqlstatement.selectsql>`   | `ParamArray</api/language/paramarray>` bindItems As `Variant</api/data_types/variant>`                                               | `RowSet</api/databases/rowset>` |        |

## Method descriptions

<div id="preparedsqlstatement.bind">

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

</div>

<div class="rst-class">

forsearch

</div>

PreparedSQLStatement.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 SQLite prepared statement to retrieve data from a *Customers* table. It then displays the data in a Listbox:
>
> ``` xojo
> Var stmt As SQLitePreparedStatement
>
> ' note in a prepared statement you DO NOT put in the quotes
> stmt = SQLitePreparedStatement(db.Prepare("SELECT * FROM Customers WHERE FirstName like ? "))
>
> ' have to tell sqlite what types the items being bound are so it does the right thing
> stmt.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
> 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="preparedsqlstatement.bindtype">

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

</div>

<div class="rst-class">

forsearch

</div>

PreparedSQLStatement.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 class="rst-class">

forsearch

</div>

PreparedSQLStatement.BindType

**BindType**(types() As `Integer</api/data_types/integer>`)

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

<div id="preparedsqlstatement.executesql">

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

</div>

<div class="rst-class">

forsearch

</div>

PreparedSQLStatement.ExecuteSQL

**ExecuteSQL**(`ParamArray</api/language/paramarray>` bindItems 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.
>
> BindItems is optional and is intended for convenience only. If bindItems is not empty, ExecuteSQL will use the passed in values instead of the ones specified by calling Bind.

<div id="preparedsqlstatement.selectsql">

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

</div>

<div class="rst-class">

forsearch

</div>

PreparedSQLStatement.SelectSQL

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

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

## Notes

`Database.SelectSQL<database.selectsql>` and `Database.ExecuteSQL<database.executesql>` both support prepared statements without the need to use any of the prepared statement classes. These cover most cases.

The reasons for using the prepared statement classes are:

- When using an uncommon column type that is likely unique to a particular database server. In that case, `Database.SelectSQL<database.selectsql>` and `Database.ExecuteSQL<database.executesql>` may not function properly and your only choice would be to use the appropriate prepared statement class.
- When repeatedly calling the same SQL statement in a loop. Using prepared statements in this case will increase performance as the prepared statement won't need to be recreated each time as is the case with `Database.SelectSQL<database.selectsql>` and `Database.ExecuteSQL<database.executesql>`.

You do not typically need to work directly with this interface. Instead use the appropriate PreparedStatement class for the database you are using:

- `MySQLPreparedStatement</api/databases/mysqlpreparedstatement>`
- `ODBCPreparedStatement</api/databases/odbcpreparedstatement>`
- `PostgreSQLPreparedStatement</api/databases/postgresqlpreparedstatement>`
- `SQLitePreparedStatement</api/databases/sqlitepreparedstatement>`

## Compatibility

|                       |                               |
|-----------------------|-------------------------------|
| **Project Types**     | Console, Desktop, Mobile, Web |
| **Operating Systems** | iOS, Linux, macOS, Windows    |

<div class="seealso">

`MySQLPreparedStatement</api/databases/mysqlpreparedstatement>`, `ODBCPreparedStatement</api/databases/odbcpreparedstatement>`, `PostgreSQLPreparedStatement</api/databases/postgresqlpreparedstatement>`, `SQLitePreparedStatement</api/databases/sqlitepreparedstatement>`

</div>