Class

PostgreSQLPreparedStatement

Database

Description

Used to create a PreparedSQLStatement for a PostgreSQL Database.

Methods

Name	Parameters	Returns
Bind	index As Integer, value As Variant
BindType	index As Integer, type As Integer
ExecuteSQL	ParamArray bindValues() As Variant
SelectSQL	ParamArray bindValues() As Variant	RowSet

Method descriptions

PostgreSQLPreparedStatement.Bind

Bind(index As Integer, value As Variant)

Binds a value and its type for the prepared statement.

Use Database.Prepare 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:

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

PostgreSQLPreparedStatement.BindType

BindType(index As Integer, type As Integer)

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

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.

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

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.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, 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.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:

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