PostgreSQLPreparedStatement

Class (inherits from PreparedSQLStatement)

Used to create a PreparedSQLStatement for a PostgreSQL Database.

Methods

Bind	ExecuteSQL
BindType	SelectSQL

Notes

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