---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
` to set up the bind.
>
> This example creates a PostgreSQL prepared statement to retrieve data from a *Customers* table. It then displays the data in a Listbox:
>
> ``` xojo
> Var stmt As PostgreSQLPreparedStatement
>
> ' note in a prepared statement you DO NOT put in the quotes
> stmt = PostgreSQLPreparedStatement(db.Prepare("SELECT * FROM Customers WHERE FirstName like ? "))
>
> ' have to tell PostgreSQL what types the items being bound are so it does the right thing
> stmt.BindType(0, PostgreSQLPreparedStatement.POSTGRESQL_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`, value As `Variant`, type As `Integer`)
> Binds a *value* and its *type* at the parameter *index* for the prepared statement.
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
**Bind**(values() As `Variant`)
> Binds multiple *values* for the prepared statement.
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
forsearch
PostgreSQLPreparedStatement.BindType
**BindType**(index As `Integer`, type As `Integer`)
> Use this to specify an exact *type* for the parameter *index*. Each Database plug-in will have its own values.
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
forsearch
PostgreSQLPreparedStatement.ExecuteSQL
**ExecuteSQL**(`ParamArray` bindValues() As `Variant`)
> Same as SelectSQL but does not return a result set. Executes and returns the result set of the prepared statement.
>
> BindValues 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.
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
forsearch
PostgreSQLPreparedStatement.SelectSQL
**SelectSQL**(`ParamArray` bindValues() As `Variant`) As `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` interface.
## Notes
Note
The use of the prepared statement classes is rare because `Database.SelectSQL
` and `Database.ExecuteSQL` utilize them automatically. See `PreparedSQLStatement` for information on cases where using prepared statement classes is appropriate.
PostgreSQL uses \$1, \$2, as its markers in the prepared statement, i.e. `"SELECT * FROM Persons WHERE Name = $1"`.
The type is generally set automatically. You only need to use the BindType method to set the type of a BYTEA column.
These are the constants to use with the BindType method:
| Constants |
|------------------|
| POSTGRESQL_BYTEA |
``` xojo
insertPerson.Bind(1, 20)
insertPerson.BindType(1, PostgreSQLPreparedStatement.POSTGRESQL_BYTEA)
```
## Sample code
This sample connects to a PostgreSQL database, creates a table, add data to it and then queries it using a prepared statement:
``` xojo
Var db As New PostgreSQLDatabase
db.Host = "127.0.0.0"
db.UserName = "admin"
db.Password = "admin"
Try
db.Connect
db.ExecuteSQL("BEGIN TRANSACTION")
db.ExecuteSQL("CREATE TABLE Persons (Name TEXT, Age INTEGER)")
Var insertPerson As PostgreSQLPreparedStatement = _
db.Prepare("INSERT INTO Persons (Name, Age) VALUES ($1, $2)")
' Add some sample data
insertPerson.Bind(0, "john")
insertPerson.Bind(1, 19)
insertPerson.ExecuteSQL
insertPerson.Bind(0, "mary")
insertPerson.Bind(1, 20)
insertPerson.ExecuteSQL
insertPerson.Bind(0, "jane")
insertPerson.Bind(1, 21)
insertPerson.ExecuteSQL
insertPerson.Bind(0, "jim")
insertPerson.Bind(1, 22)
insertPerson.ExecuteSQL
db.CommitTransaction
Var ps As PostgreSQLPreparedStatement = _
db.Prepare("SELECT * FROM Persons WHERE Name LIKE $1 AND Age >= $2")
ps.Bind(0, "j%")
ps.Bind(1, 20)
Var rs As RowSet = ps.SelectSQL
Do Until rs.AfterLastRow
MessageBox("Name:" + rs.Column("Name").StringValue + _
" Age: " + rs.Column("Age").StringValue)
rs.MoveToNextRow
Loop
Catch error As DatabaseException
MessageBox("DB Error: " + error.Message)
Return
End Try
```
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This sample connects to a PostgreSQL database, creates a table and then queries it using a prepared statement. Rather than binding the values separately, it instead passes them in the call to SelectSQL:
``` xojo
Var db As New PostgreSQLDatabase
db.Host = "127.0.0.0"
db.User = "admin"
db.Password = "admin"
Try
db.Connect
db.ExecuteSQL("BEGIN TRANSACTION")
db.ExecuteSQL("CREATE TABLE Persons (Name TEXT, Age INTEGER)")
Var insertPerson As PostgreSQLPreparedStatement = _
db.Prepare("INSERT INTO Persons (Name, Age) VALUES ($1, $2)")
insertPerson.ExecuteSQL("john", 20)
insertPerson.ExecuteSQL("mary", 21)
insertPerson.ExecuteSQL("jane", 22)
db.CommitTransaction
Var ps As PostgreSQLPreparedStatement = _
db.Prepare("SELECT * FROM Persons WHERE Name LIKE $1 AND Age >= $2")
Var rs As RowSet = ps.SelectSQL("j%", 20)
Do Until rs.AfterLastRow
MessageBox("Name:" + rs.Column("Name").StringValue + _
" Age: " + rs.Column("Age").StringValue)
rs.MoveToNextRow
Loop
Catch error As DatabaseException
MessageBox("DB Error: " + error.Message)
Return
End Try
```
## Compatibility
| | |
|-----------------------|-----------------------|
| **Project Types** | Console, Desktop, Web |
| **Operating Systems** | All |
`Object` parent class; `Database`, `PostgreSQLDatabase`, `PreparedSQLStatement` classes.