MySQLPreparedStatement

From Xojo Documentation

Class (inherits from PreparedSQLStatement)


New in 2010r4

Used to create a PreparedSQLStatement for a MySQL Database.

Methods
Bind SQLExecute
BindType SQLSelect

Notes

MySQL uses the "?" character as its marker 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 RecordSet.

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

If Not db.Connect Then Return

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

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

Dim rs As RecordSet = ps.SQLSelect
If db.Error Then
MsgBox(db.ErrorMessage)
Else
While Not rs.EOF
MsgBox("Name: " + rs.Field("Name").StringValue + _
" Age: " + rs.Field("Age").StringValue)
rs.MoveNext
Wend
End If

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

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

If Not db.Connect Then Return

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

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

Dim rs As RecordSet = ps.SQLSelect("john", 20)
If db.Error Then
MsgBox(db.ErrorMessage)
Return
Else
While Not rs.EOF
MsgBox("Name: " + rs.Field("Name").StringValue + _
" Age: " + rs.Field("Age").StringValue)
rs.MoveNext
Wend
End If

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

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

If Not db.Connect Then Return

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

Dim 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.SQLExecute("john", 20)
ps.SQLExecute("john", 21)
ps.SQLExecute("john", 22)
ps.SQLExecute("john", 20)
ps.SQLExecute("john", 21)
ps.SQLExecute("john", 22)
db.SQLExecute("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)

Dim rs As RecordSet = ps.SQLSelect("john", 20)
If db.Error Then
MsgBox(db.ErrorMessage)
Return
Else
While Not rs.EOF
MsgBox("Name: " + rs.Field("Name").StringValue + _
" Age: " + rs.Field("Age").StringValue)
rs.MoveNext
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.

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

Dim rs As RecordSet = ps.SQLSelect

See Also

Database Class, MySQLCommunityServer, PreparedSQLStatement