Platforms to show: All Mac Windows Linux Cross-Platform

Back to SQLPreparedStatementMBS class.

SQLPreparedStatementMBS.Bind(name As String, value as Variant)

Type Topic Plugin Version macOS Windows Linux iOS Targets
method SQL MBS SQL Plugin 14.1 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Defines the value for one parameter.
Example
dim db as SQLDatabaseMBS // your db connection
dim sql as string = "Insert into test_tbl(fid, fvarchar20) values(:fid, :fvarchar20)"
dim v as Variant = db.Prepare(sql)
dim p as SQLPreparedStatementMBS = v

p.BindType("fid", SQLPreparedStatementMBS.kTypeLong)
p.BindType("fvarchar20", SQLPreparedStatementMBS.kTypeString)
p.Bind("fid", 2345)
p.Bind("fvarchar20", "Hello World by name")

p.SQLExecute

Version 16.4 and newer allow you to bind BLOB fields using a Memoryblock or a String value.
Older versions only accepted string.

When passing variant for value, MemoryBlock and Strings without text encoding are converted to byte values (BLOB). Texts and Strings with encoding are converted to text values. Other types are translated as good as possible. Raises exceptions if you pass anything which is not recognized.

With version 19.0 or later, you can pass folderitem to stream blob from file. This may raise exception if file can't be opened.

See also:

SQLPreparedStatementMBS.Bind(name As String, value as Variant, type as Integer)

Type Topic Plugin Version macOS Windows Linux iOS Targets
method SQL MBS SQL Plugin 14.1 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Defines one paramater with value and type.
Example
dim db as SQLDatabaseMBS // your db connection
dim sql as string = "Insert into test_tbl(fid, fvarchar20) values(:fid, :fvarchar20)"
dim v as Variant = db.Prepare(sql)
dim p as SQLPreparedStatementMBS = v

p.Bind("fid", 2345, SQLPreparedStatementMBS.kTypeLong)
p.Bind("fvarchar20", "Hello World by name", SQLPreparedStatementMBS.kTypeString)

p.SQLExecute

Version 16.4 and newer allow you to bind BLOB fields using a Memoryblock or a String value.
Older versions only accepted string.

When passing variant for value, MemoryBlock and Strings without text encoding are converted to byte values (BLOB). Texts and Strings with encoding are converted to text values. Other types are translated as good as possible. Raises exceptions if you pass anything which is not recognized.

With version 19.0 or later, you can pass folderitem to stream blob from file. This may raise exception if file can't be opened.

With plugin version 16.4 and newer binding type is optional. In that case the type is determinated from the value type.

See also:

SQLPreparedStatementMBS.Bind(Values as Dictionary)

Type Topic Plugin Version macOS Windows Linux iOS Targets
method SQL MBS SQL Plugin 16.4 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Sets the parameters based on the keys and values in the dictionary.
Example
dim db as SQLDatabaseMBS // your database
dim pic as Picture // some picture
dim jpegData as MemoryBlock = pic.GetData(Picture.FormatJPEG, 80)

// get an insert command
dim sql as string = "Insert into BlobTest(name, image) values (:name, :image)"
dim p as SQLPreparedStatementMBS = db.Prepare(sql)

// put parameter values in a dictionary
dim d as new Dictionary

// by param index
d.Value(0) = "logo.jpg"
// by param name
d.Value("image") = jpegData

// bind values and run it
p.Bind(d)
p.SQLExecute

The dictionary is saved to fill parameters later.
Keys can be String, Text or numeric types. Text and String are used to pick parameters by name. Numeric values are used to pick parameter by index (zero based).
MemoryBlock and Strings without text encoding are converted to byte values (BLOB).
Texts and Strings with encoding are converted to text values.
Raises exceptions if you pass anything which is not recognized.
Other types are translated as good as possible.

See also:

SQLPreparedStatementMBS.Bind(values() as Variant)

Type Topic Plugin Version macOS Windows Linux iOS Targets
method SQL MBS SQL Plugin 11.2 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Sets the value list with the given values.

You can either pass values to the SQLExecute/SQLSelect method or call Bind methods to set values. You have to define for each parameter both the type and the value.

Version 16.4 and newer allow you to bind BLOB fields using a Memoryblock or a String value.
Older versions only accepted string.

When passing variant for value, MemoryBlock and Strings without text encoding are converted to byte values (BLOB). Texts and Strings with encoding are converted to text values. Other types are translated as good as possible. Raises exceptions if you pass anything which is not recognized.

With version 19.0 or later, you can pass folderitem to stream blob from file. This may raise exception if file can't be opened.

See also:

SQLPreparedStatementMBS.Bind(zeroBasedIndex as Integer, value as Variant)

Type Topic Plugin Version macOS Windows Linux iOS Targets
method SQL MBS SQL Plugin 11.2 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Defines the value for one parameter.

You can either pass values to the SQLExecute/SQLSelect method or call Bind methods to set values. You have to define for each parameter both the type and the value.

Version 16.4 and newer allow you to bind BLOB fields using a Memoryblock or a String value.
Older versions only accepted string.

When passing variant for value, MemoryBlock and Strings without text encoding are converted to byte values (BLOB). Texts and Strings with encoding are converted to text values. Other types are translated as good as possible. Raises exceptions if you pass anything which is not recognized.

With version 19.0 or later, you can pass folderitem to stream blob from file. This may raise exception if file can't be opened.

Raises OutOfBoundsException exception if index parameter is out of range.

See also:

SQLPreparedStatementMBS.Bind(zeroBasedIndex as Integer, value as Variant, type as Integer)

Type Topic Plugin Version macOS Windows Linux iOS Targets
method SQL MBS SQL Plugin 11.2 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Defines one paramater with value and type.

You can either pass values to the SQLExecute/SQLSelect method or call Bind methods to set values. You have to define for each parameter both the type and the value.

Version 16.4 and newer allow you to bind BLOB fields using a Memoryblock or a String value.
Older versions only accepted string.

When passing variant for value, MemoryBlock and Strings without text encoding are converted to byte values (BLOB). Texts and Strings with encoding are converted to text values. Other types are translated as good as possible. Raises exceptions if you pass anything which is not recognized.

With plugin version 16.4 and newer binding type is optional. In that case the type is determinated from the value type.

With version 19.0 or later, you can pass folderitem to stream blob from file. This may raise exception if file can't be opened.

Raises OutOfBoundsException exception if index parameter is out of range.

See also:

SQLPreparedStatementMBS.BindType(name As String, type as Integer)

Type Topic Plugin Version macOS Windows Linux iOS Targets
method SQL MBS SQL Plugin 14.1 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Defines the type of one value.
Example
dim db as SQLDatabaseMBS // your db connection
dim sql as string = "Insert into test_tbl(fid, fvarchar20) values(:fid, :fvarchar20)"
dim v as Variant = db.Prepare(sql)
dim p as SQLPreparedStatementMBS = v

p.BindType("fid", SQLPreparedStatementMBS.kTypeLong)
p.BindType("fvarchar20", SQLPreparedStatementMBS.kTypeString)
p.Bind("fid", 2345)
p.Bind("fvarchar20", "Hello World by name")

p.SQLExecute

With plugin version 16.4 and newer binding type is optional. In that case the type is determinated from the value type.

See also:

Some examples using this method:

SQLPreparedStatementMBS.BindType(types() as Integer)

Type Topic Plugin Version macOS Windows Linux iOS Targets
method SQL MBS SQL Plugin 11.2 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Defines the types for all values.

You can either pass values to the SQLExecute/SQLSelect method or call Bind methods to set values. You have to define for each parameter both the type and the value.

With plugin version 16.4 and newer binding type is optional. In that case the type is determinated from the value type.

See also:

SQLPreparedStatementMBS.BindType(zeroBasedIndex as Integer, type as Integer)

Type Topic Plugin Version macOS Windows Linux iOS Targets
method SQL MBS SQL Plugin 11.2 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Defines the type of one value.

You can either pass values to the SQLExecute/SQLSelect method or call Bind methods to set values. You have to define for each parameter both the type and the value.

With plugin version 16.4 and newer binding type is optional. In that case the type is determinated from the value type.

See also:

SQLPreparedStatementMBS.Clear

Type Topic Plugin Version macOS Windows Linux iOS Targets
method SQL MBS SQL Plugin 14.1 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Clears all parameters for reusing the SQL Prepared statement.

SQLPreparedStatementMBS.Constructor   Private

Type Topic Plugin Version macOS Windows Linux iOS Targets
method SQL MBS SQL Plugin 11.2 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
The private constructor.

This constructor makes sure you don't create useless SQLPreparedStatementMBS objects by error. The only way to create an object is to use the prepare method in the database class.
This constructor is private to make sure you don't create an object from this class by error. Please use designated functions to create objects.

SQLPreparedStatementMBS.ExecuteSQL(ParamArray bindItems As Variant)

Type Topic Plugin Version macOS Windows Linux iOS Targets
method SQL MBS SQL Plugin 19.5 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Runs the SQL command with the given parameters.

You can decide whether you pass values here or call Bind methods.

When passing variant for value, MemoryBlock and Strings without text encoding are converted to byte values (BLOB). Texts and Strings with encoding are converted to text values. Other types are translated as good as possible. Raises exceptions if you pass anything which is not recognized.

With version 19.0 or later, you can pass folderitem to stream blob from file. This may raise exception if file can't be opened.

If bindItems is not nil, we bind parameters using it.
If you like to pass an array of variant for the values, please pass this to Bind() and no parameters here. Otherwise passing an array here would create a variant array with your array as content.

This version of the function raises always exceptions, while SQLExecute only if you set RaiseException property to true.

Some examples using this method:

SQLPreparedStatementMBS.ExecuteSQLMT(ParamArray bindItems As Variant)

Type Topic Plugin Version macOS Windows Linux iOS Targets
method SQL MBS SQL Plugin 19.5 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Runs the SQL command with the given parameters.

You can decide whether you pass values here or call Bind methods.

When passing variant for value, MemoryBlock and Strings without text encoding are converted to byte values (BLOB). Texts and Strings with encoding are converted to text values. Other types are translated as good as possible. Raises exceptions if you pass anything which is not recognized.

If bindItems is not nil, we bind parameters using it.

With version 19.0 or later, you can pass folderitem to stream blob from file. This may raise exception if file can't be opened.

This version of the function raises always exceptions, while SQLExecuteMT only if you set RaiseException property to true.

If you like to pass an array of variant for the values, please pass this to Bind() and no parameters here. Otherwise passing an array here would create a variant array with your array as content.

The work is performed on a preemptive thread, so this function does not block the application and can yield time to other Xojo threads. Must be called in a Xojo thread to enjoy benefits. If called in main thread will block, but keep other background threads running.

SQLPreparedStatementMBS.SelectSQL(ParamArray bindItems As Variant) As RowSet

Type Topic Plugin Version macOS Windows Linux iOS Targets
method SQL MBS SQL Plugin 19.4 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Runs the query with the given parameters.

Returns the RowSet object or nil on error.
You can decide whether you pass values here or call Bind methods.

For this method to work, you need to have somewhere a property with SQLDatabaseMBS so Xojo includes our SQLDatabase plugin which provides the RecordSet functionality.

The record set may not have a valid RecordCount or have working movefirst/movelast/moveprev methods unless the underlaying database supports those and Scrollable result sets is enabled/supported.

If bindItems is not nil, we bind parameters using it.
If you like to pass an array of variant for the values, please pass this to Bind() and no parameters here. Otherwise passing an array here would create a variant array with your array as content.

When passing variant for value, MemoryBlock and Strings without text encoding are converted to byte values (BLOB). Texts and Strings with encoding are converted to text values. Other types are translated as good as possible. Raises exceptions if you pass anything which is not recognized.

This version of the function raises always exceptions, while SQLSelect only if you set RaiseException property to true.

Some examples using this method:

SQLPreparedStatementMBS.SelectSQLMT(ParamArray bindItems As Variant) As Rowset

Type Topic Plugin Version macOS Windows Linux iOS Targets
method SQL MBS SQL Plugin 19.5 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All

Returns the RowSet object or nil on error.
You can decide whether you pass values here or call Bind methods.

For this method to work, you need to have somewhere a property with SQLDatabaseMBS so Xojo includes our SQLDatabase plugin which provides the RecordSet functionality.

The record set may not have a valid RecordCount or have working movefirst/movelast/moveprev methods unless the underlaying database supports those and Scrollable result sets is enabled/supported.

If bindItems is not nil, we bind parameters using it.
If you like to pass an array of variant for the values, please pass this to Bind() and no parameters here. Otherwise passing an array here would create a variant array with your array as content.

When passing variant for value, MemoryBlock and Strings without text encoding are converted to byte values (BLOB). Texts and Strings with encoding are converted to text values. Other types are translated as good as possible. Raises exceptions if you pass anything which is not recognized.

This version of the function raises always exceptions, while SQLSelectMT only if you set RaiseException property to true.

The work is performed on a preemptive thread, so this function does not block the application and can yield time to other Xojo threads. Must be called in a Xojo thread to enjoy benefits. If called in main thread will block, but keep other background threads running.

SQLPreparedStatementMBS.SQLExecute(ParamArray bindItems as Variant)

Type Topic Plugin Version macOS Windows Linux iOS Targets
method SQL MBS SQL Plugin 11.2 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Runs the SQL command with the given parameters.

You can decide whether you pass values here or call Bind methods.

When passing variant for value, MemoryBlock and Strings without text encoding are converted to byte values (BLOB). Texts and Strings with encoding are converted to text values. Other types are translated as good as possible. Raises exceptions if you pass anything which is not recognized.

If you like to pass an array of variant for the values, please pass this to Bind() and no parameters here. Otherwise passing an array here would create a variant array with your array as content.

With version 19.0 or later, you can pass folderitem to stream blob from file. This may raise exception if file can't be opened.

SQLPreparedStatementMBS.SQLExecuteMT(ParamArray bindItems as Variant)

Type Topic Plugin Version macOS Windows Linux iOS Targets
method SQL MBS SQL Plugin 16.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Runs the SQL command with the given parameters.

You can decide whether you pass values here or call Bind methods.

The work is performed on a preemptive thread, so this function does not block the application and can yield time to other Xojo threads. Must be called in a Xojo thread to enjoy benefits. If called in main thread will block, but keep other background threads running.

When passing variant for value, MemoryBlock and Strings without text encoding are converted to byte values (BLOB). Texts and Strings with encoding are converted to text values. Other types are translated as good as possible. Raises exceptions if you pass anything which is not recognized.

If you like to pass an array of variant for the values, please pass this to Bind() and no parameters here. Otherwise passing an array here would create a variant array with your array as content.

With version 19.0 or later, you can pass folderitem to stream blob from file. This may raise exception if file can't be opened.

SQLPreparedStatementMBS.SQLSelect(ParamArray bindItems as Variant) As RecordSet

Type Topic Plugin Version macOS Windows Linux iOS Targets
method SQL MBS SQL Plugin 11.2 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Runs the query with the given parameters.

Returns the recordset object or nil on error.
You can decide whether you pass values here or call Bind methods.

For this method to work, you need to have somewhere a property with SQLDatabaseMBS so Xojo includes our SQLDatabase plugin which provides the RecordSet functionality.

The record set may not have a valid RecordCount or have working movefirst/movelast/moveprev methods unless the underlaying database supports those and Scrollable result sets is enabled/supported.

If you like to pass an array of variant for the values, please pass this to Bind() and no parameters here. Otherwise passing an array here would create a variant array with your array as content.

When passing variant for value, MemoryBlock and Strings without text encoding are converted to byte values (BLOB). Texts and Strings with encoding are converted to text values. Other types are translated as good as possible. Raises exceptions if you pass anything which is not recognized.

With version 19.0 or later, you can pass folderitem to stream blob from file. This may raise exception if file can't be opened.

SQLPreparedStatementMBS.SQLSelectMT(ParamArray bindItems as Variant) As RecordSet

Type Topic Plugin Version macOS Windows Linux iOS Targets
method SQL MBS SQL Plugin 16.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Runs the query with the given parameters.

Returns the recordset object or nil on error.
You can decide whether you pass values here or call Bind methods.

For this method to work, you need to have somewhere a property with SQLDatabaseMBS so Xojo includes our SQLDatabase plugin which provides the RecordSet functionality.

The record set may not have a valid RecordCount or have working movefirst/movelast/moveprev methods unless the underlaying database supports those and Scrollable result sets is enabled/supported.

If you like to pass an array of variant for the values, please pass this to Bind() and no parameters here. Otherwise passing an array here would create a variant array with your array as content.

When passing variant for value, MemoryBlock and Strings without text encoding are converted to byte values (BLOB). Texts and Strings with encoding are converted to text values. Other types are translated as good as possible. Raises exceptions if you pass anything which is not recognized.

With version 19.0 or later, you can pass folderitem to stream blob from file. This may raise exception if file can't be opened.

The work is performed on a preemptive thread, so this function does not block the application and can yield time to other Xojo threads. Must be called in a Xojo thread to enjoy benefits. If called in main thread will block, but keep other background threads running.

The items on this page are in the following plugins: MBS SQL Plugin.


The biggest plugin in space...