Class

MySQLPreparedStatement


Description

Used to create a PreparedSQLStatement for a MySQL Database.

Methods

Name

Parameters

Returns

Shared

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


MySQLPreparedStatement.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 MySQL prepared statement to retrieve data from a Customers table. It then displays the data in a Listbox:

Var stmt As MySQLPreparedStatement

' note in a prepared statement you DO NOT put in the quotes
stmt = MySQLPreparedStatement(db.Prepare("SELECT * FROM Customers WHERE FirstName like ? "))

' have to tell MySQL what types the items being bound are so it does the right thing
stmt.BindType(0, MySQLPreparedStatement.MYSQL_TYPE_STRING)
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

MySQLPreparedStatement.BindType

BindType(index As Integer, type As Integer)

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


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


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

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

MySQL uses the "?" character as the parameter indicator in the prepared statement, i.e. "SELECT * FROM Persons WHERE Name = ?".

You must bind the type of the parameters using BindType.

These are the constants to use with the BindType method:

Constant

MYSQL_TYPE_BLOB

MYSQL_TYPE_DATE

MYSQL_TYPE_DATETIME

MYSQL_TYPE_DOUBLE

MYSQL_TYPE_FLOAT

MYSQL_TYPE_LONG

MYSQL_TYPE_LONGLONG

MYSQL_TYPE_NULL

MYSQL_TYPE_SHORT

MYSQL_TYPE_STRING

MYSQL_TYPE_TIME

MYSQL_TYPE_TIMESTAMP

MYSQL_TYPE_TINY

If you add a MySQL database directly to your project, then you need to cast the result of the call to Database.Prepare to the MySQLCommunityServer class. If you do not cast you will get a NilObjectException when you try to use it (refer to the example below).

Sample code

The following sample connects to a MySQL database, adds a table and populates it. It then uses a Prepared Statement to query the table and get a RowSet.

Var db As New MySQLCommunityServer
db.Host = "127.0.0.0"
db.Username = "admin"
db.Password = "admin"

If Not db.Connect Then Return

db.ExecuteSQL("CREATE TABLE Persons (Name, Age)")
db.ExecuteSQL("INSERT INTO Persons (Name, Age) VALUES ('john', 20)")
db.ExecuteSQL("INSERT INTO Persons (Name, Age) VALUES ('john', 21)")
db.ExecuteSQL("INSERT INTO Persons (Name, Age) VALUES ('john', 22)")
db.ExecuteSQL("INSERT INTO Persons (Name, Age) VALUES ('john', 20)")
db.ExecuteSQL("INSERT INTO Persons (Name, Age) VALUES ('john', 21)")
db.ExecuteSQL("INSERT INTO Persons (Name, Age) VALUES ('john', 22)")
db.ExecuteSQL("COMMIT")

Var ps As PreparedSQLStatement = _
  db.Prepare("SELECT * FROM Persons WHERE Name = ? AND Age >= ?")

ps.BindType(0, MySQLPreparedStatement.MYSQL_TYPE_STRING)
ps.BindType(1, MySQLPreparedStatement.MYSQL_TYPE_LONG)
ps.Bind(0, "john")
ps.Bind(1, 20)

Var rs As RowSet = ps.SelectSQL
If db.Error Then
  MessageBox(db.ErrorMessage)
Else
  While Not rs.AfterLastRow
    MessageBox("Name: " + rs.Field("Name").StringValue + _
      " Age: " + rs.Field("Age").StringValue)
    rs.MoveToNextRow
  Wend
End If

This code passes the values to bind using the SelectSQL method rather than calling the Bind method:

Var db As New MySQLCommunityServer
db.Host = "127.0.0.0"
db.Username = "admin"
db.Password = "admin"

If Not db.Connect Then Return

db.ExecuteSQL("CREATE TABLE Persons (Name, Age)")
db.ExecuteSQL("INSERT INTO Persons (Name, Age) VALUES ('john', 20)")
db.ExecuteSQL("INSERT INTO Persons (Name, Age) VALUES ('john', 21)")
db.ExecuteSQL("INSERT INTO Persons (Name, Age) VALUES ('john', 22)")
db.ExecuteSQL("INSERT INTO Persons (Name, Age) VALUES ('john', 20)")
db.ExecuteSQL("INSERT INTO Persons (Name, Age) VALUES ('john', 21)")
db.ExecuteSQL("INSERT INTO Persons (Name, Age) VALUES ('john', 22)")
db.ExecuteSQL("COMMIT")

Var ps As PreparedSQLStatement = _
  db.Prepare("SELECT * FROM Persons WHERE Name = ? AND Age >= ?")

ps.BindType(0, MySQLPreparedStatement.MYSQL_TYPE_STRING)
ps.BindType(1, MySQLPreparedStatement.MYSQL_TYPE_LONG)

Var rs As RowSet = ps.SelectSQL("john", 20)
If db.Error Then
  MessageBox(db.ErrorMessage)
  Return
Else
  While Not rs.AfterLastRow
    MessageBox("Name: " + rs.Field("Name").StringValue + _
      " Age: " + rs.Field("Age").StringValue)
    rs.MoveToNextRow
  Wend
End If

This code uses and reuses a Prepared Statement for the Insert:

Var db As New MySQLCommunityServer
db.Host = "127.0.0.0"
db.Username = "admin"
db.Password = "admin"

If Not db.Connect Then Return

db.ExecuteSQL("CREATE TABLE Persons(Name, Age)")

Var ps As PreparedSQLStatement = _
  db.Prepare("INSERT INTO Persons (Name, Age) VALUES (?, ?)")

ps.BindType(0, MySQLPreparedStatement.MYSQL_TYPE_STRING)
ps.BindType(1, MySQLPreparedStatement.MYSQL_TYPE_LONG)

ps.ExecuteSQL("john", 20)
ps.ExecuteSQL("john", 21)
ps.ExecuteSQL("john", 22)
ps.ExecuteSQL("john", 20)
ps.ExecuteSQL("john", 21)
ps.ExecuteSQL("john", 22)
db.ExecuteSQL("COMMIT")

ps = db.Prepare("SELECT * FROM Persons WHERE Name = ? AND Age >= ?")
ps.BindType(0, MySQLPreparedStatement.MYSQL_TYPE_STRING)
ps.BindType(1, MySQLPreparedStatement.MYSQL_TYPE_LONG)

Var rs As RowSet = ps.SelectSQL("john", 20)
If db.Error Then
  MessageBox(db.ErrorMessage)
  Return
Else
  While Not rs.AfterLastRow
    MessageBox("Name: " + rs.Field("Name").StringValue + _
      " Age: " + rs.Field("Age").StringValue)
    rs.MoveToNextRow
  Wend
End If

This code uses a MySQL Database that was added to the project, so it has to cast the result from db.Prepare.

Var ps As MySQLPreparedStatement
ps = MySQLPreparedStatement(CustomerDB.Prepare("SELECT * FROM Persons WHERE Name = ? AND Age >= ?"))
ps.Bind(0, "John")
ps.BindType(0, MySQLPreparedStatement.MYSQL_TYPE_STRING)
ps.Bind(1, 20)
ps.BindType(1, MySQLPreparedStatement.MYSQL_TYPE_LONG)

Var rs As RowSet = ps.SelectSQL

Compatibility

All project types on all supported operating systems.