PostgreSQLPreparedStatement

From Xojo Documentation

Class (inherits from PreparedSQLStatement)

Used to create a PreparedSQLStatement for a PostgreSQL Database.

Methods
Bind ExecuteSQL
BindType SelectSQL

Notes

fa-info-circle-32.png
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
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:

Var db As New PostgreSQLDatabase
db.Host = "127.0.0.0"
db.UserName = "admin"
db.Password = "admin"

Try
db.Connect
db.SQLExecute("BEGIN TRANSACTION")
db.SQLExecute("CREATE TABLE Persons (Name TEXT, Age INTEGER)")

Dim insertPerson As PostgreSQLPreparedStatement = _
db.Prepare("INSERT INTO Persons (Name, Age) VALUES ($1, $2)")

// Add some sample data
insertPerson.Bind(0, "john")
insertPerson.Bind(1, 20)
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.Commit

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 SQLSelect:

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.Commit

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

See Also

Database Class, PostgreSQLDatabase, PreparedSQLStatement classes.