PostgreSQLPreparedStatement

From Xojo Documentation

Class (inherits from PreparedSQLStatement)

Used to create a PreparedSQLStatement for a PostgreSQL Database.

Methods
Bind SQLExecute
BindType SQLSelect

Notes

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:

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

If Not db.Connect Then Return

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

insertPerson.Bind(0, "mary")
insertPerson.Bind(1, 20)
insertPerson.SQLExecute

insertPerson.Bind(0, "jane")
insertPerson.Bind(1, 21)
insertPerson.SQLExecute

insertPerson.Bind(0, "jim")
insertPerson.Bind(1, 22)
insertPerson.SQLExecute

db.Commit

Dim ps As PostgreSQLPreparedStatement = _
db.Prepare("SELECT * FROM Persons WHERE Name LIKE $1 AND Age >= $2")

ps.Bind(0, "j%")
ps.Bind(1, 20)

Dim rs As RecordSet = ps.SQLSelect
If db.Error Then
MsgBox("DB Error: " + db.ErrorMessage)
Return
Else
Do Until rs.EOF
MsgBox("Name:" + rs.Field("Name").StringValue + _
" Age: " + rs.Field("Age").StringValue)
rs.MoveNext
Loop
End If

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:

Dim db As New PostgreSQLDatabase
db.Host = "127.0.0.0"
db.User = "admin"
db.Password = "admin"

If Not db.Connect Then Return

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)")

insertPerson.SQLExecute("john", 20)
insertPerson.SQLExecute("mary", 21)
insertPerson.SQLExecute("jane", 22)
db.Commit

Dim ps As PostgreSQLPreparedStatement = _
db.Prepare("SELECT * FROM Persons WHERE Name LIKE $1 AND Age >= $2")

Dim rs As RecordSet = ps.SQLSelect("j%", 20)
If db.Error Then
MsgBox("DB Error: " + db.ErrorMessage)
Return
Else
Do Until rs.EOF
MsgBox("Name:" + rs.Field("Name").StringValue + _
" Age: " + rs.Field("Age").StringValue)
rs.MoveNext
Loop
End If

See Also

Database Class, PostgreSQLDatabase, PreparedSQLStatement classes.