Xojo Developer Conference
25/27th April 2018 in Denver.
MBS Xojo Conference
6/7th September 2018 in Munich, Germany.

Platforms to show: All Mac Windows Linux Cross-Platform

Next items

SQLCommandMBS.AsRecordSet as RecordSet
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method SQL MBS SQL Plugin 13.0 Yes Yes Yes Yes No
Function: Returns a recordset using the command to query fields.
Notes:
You can use normal RecordSet functions to walk through fields and they simply control the command object.
This is for convenience like passing RecordSet to report functions in Real Studio.

For this method to work, you need to have somewhere a property with SQLDatabaseMBS so Real Studio 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.

Feedback, Comments & Corrections

SQLCommandMBS.Cache
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method SQL MBS SQL Plugin 16.1 Yes Yes Yes Yes No
Function: Caches values.
Notes:
The plugin will load the whole recordset and store it in memory.
Now you can move forward/backward as needed to read data.

If you set Option("AutoCache") = "true", the plugin will call Cache automatically for all result sets.

Feedback, Comments & Corrections

SQLCommandMBS.Cancel
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method SQL MBS SQL Plugin 9.3 Yes Yes Yes Yes No
Function: Attempts to cancel the pending result set, or current statement execution.
Notes:
Only if isExecuting is true, doing cancel makes sense.

Cancel can cancel the following types of processing on a statement:

A function running asynchronously on the statement.
A function running on the statement on another thread.
After an application calls a function asynchronously, it checks repeatedly to determine whether it has finished processing. While the function is processing, an application can call Cancel to cancel the function.

In a multithread application, the application can cancel a function that is running synchronously on a statement.

see also
http://www.sqlapi.com/OnLineDoc/Command_Cancel.html

Feedback, Comments & Corrections

SQLCommandMBS.Close
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method SQL MBS SQL Plugin 9.3 Yes Yes Yes Yes No
Function: Closes the specified command object.
Notes:
Use the Close method to close the command explicitly.

A command will be implicitly closed in destructor, so you don't have to call Close method explicitly.

Feedback, Comments & Corrections

SQLCommandMBS.Constructor
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method SQL MBS SQL Plugin 9.3 Yes Yes Yes Yes No
Function: Creates a new command object with no connection and no command text.

See also:

Feedback, Comments & Corrections

SQLCommandMBS.Constructor(connection as SQLConnectionMBS, SQLCommand as String, CommandType as Integer = 0)
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method SQL MBS SQL Plugin 9.3 Yes Yes Yes Yes No
Function: This constructor initializes a new SQLCommandMBS object.
Example:
// your connection
dim con as SQLConnectionMBS
dim SQL as string = "Insert into test_tbl(fid, fvarchar20) values(:id, :name)"

// create command object
dim cmd as new SQLCommandMBS(con, sql)

// assign values by name of parameter:
cmd.Param("id").setAsLong(2)
cmd.Param("name").setAsString(new SQLStringMBS("Some string (2)"))

// Insert first row
cmd.Execute
Notes:
Connection: the connection to associated with the command.
SQLCommand: A string which represents command text string (an SQL statement or a stored procedure name). If it is an empty string, no command text is associated with the command, and you have to call setCommandText method later.
CommandType: The type of command like kCommandTypeUnknown, kCommandTypeSQLStatement, kCommandTypeSQLStatementRaw or kCommandTypeStoredProcedure.

All text strings sent to the plugin must have a defined encoding. Else the internal text encoding conversions will fail.

See also:

Feedback, Comments & Corrections

SQLCommandMBS.CreateParam(name as string, ParamType as Integer, DirType as Integer=0) as SQLParamMBS
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method SQL MBS SQL Plugin 9.3 Yes Yes Yes Yes No
Function: Creates parameter associated with the specified command.
Notes:
Parameters

name:A string representing the name of parameter.
ParamType:Type of the parameter's value. Use the kDataType constants.
ParamSize:An integer value represents parameter's value size.
ParamPrecision:An integer value represents parameter's value precision.
ParamScale:An integer value represents parameter's value scale.
DirType:Type of the parameter. Use the kParamDirType* constants.

Returns a new SQLParamMBS object on success or nil on any error.

Normally you should not create parameters by yourself. The Library automatically detects whether the command has parameters in terms of the command text and implicitly creates a set of SAParam objects.

Nevertheless, if you call CreateParam explicitly you have to delete all SAParam objects created automatically by the Library before. Use DestroyParams method before the first call of CreateParam method.

See also:

Feedback, Comments & Corrections

SQLCommandMBS.CreateParam(name as string, ParamType as Integer, NativeType as Integer, ParamSize as Integer, ParamPrecision as Integer, ParamScale as Integer, DirType as Integer=0) as SQLParamMBS
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method SQL MBS SQL Plugin 9.3 Yes Yes Yes Yes No
Function: Creates parameter associated with the specified command.
Notes:
Parameters

name:A string representing the name of parameter.
ParamType:Type of the parameter's value. Use the kDataType constants.
ParamSize:An integer value represents parameter's value size.
ParamPrecision:An integer value represents parameter's value precision.
ParamScale:An integer value represents parameter's value scale.
DirType:Type of the parameter. Use the kParamDirType* constants.

Returns a new SQLParamMBS object on success or nil on any error.

Normally you should not create parameters by yourself. The Library automatically detects whether the command has parameters in terms of the command text and implicitly creates a set of SAParam objects.

Nevertheless, if you call CreateParam explicitly you have to delete all SAParam objects created automatically by the Library before. Use DestroyParams method before the first call of CreateParam method.

See also:

Feedback, Comments & Corrections

SQLCommandMBS.DestroyParams
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method SQL MBS SQL Plugin 9.3 Yes Yes Yes Yes No
Function: Destroys all parameters associated with the specified command.
Notes:
DestroyParams method destroys all parameters either created automatically by the Library or by user.

Normally you should not create and delete parameters by yourself. The Library automatically detects whether the command has parameters, implicitly creates a set of SAParam objects and then deletes them in SACommanddestructor. But if you have some reason to create parameters explicitly use CreateParam method and then call DestroyParams method to delete all parameters after your work with parameters is over.

Feedback, Comments & Corrections

SQLCommandMBS.Execute
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method SQL MBS SQL Plugin 9.3 Yes Yes Yes Yes No
Function: Executes the current command.
Example:
// your connection
dim con as SQLConnectionMBS
dim SQL as string = "Insert into test_tbl(fid, fvarchar20) values(:id, :name)"

// create command object
dim cmd as new SQLCommandMBS(con, sql)

// assign values by name of parameter:
cmd.Param("id").setAsLong(2)
cmd.Param("name").setAsString(new SQLStringMBS("Some string (2)"))

// Insert first row
cmd.Execute
Notes:
Use the Execute method to execute the query or stored procedure specified in the command text. Execute method calls Prepare method implicitly if needed. If the command has input parameters, they should be bound before calling Execute method. Input parameters represented by SAParam object. To bind input variables assign a value to SAParam object returning by Param or ParamByIndex methods.

A command (an SQL statement or procedure) can have a result set after executing. To check whether a result set exists use isResultSet method. If result set exists, a set of SAField objects is created after command execution. Rows from the result set can be fetched one by one using FetchNext method. To get field description or value use Field method.

Output parameters represented by SAParam objects. They are available after command execution. To get parameter description or value use Param or ParamByIndex methods.

Feedback, Comments & Corrections

SQLCommandMBS.ExecuteCommand(SQLCommand as string, CommandType as Integer=0)
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method SQL MBS SQL Plugin 10.2 Yes Yes Yes Yes No
Function: Executes the given command.
Notes:
This is a convenience function.
Internally it calls setCommandText with the given command and calls Execute.

All text strings sent to the plugin must have a defined encoding. Else the internal text encoding conversions will fail.

Feedback, Comments & Corrections

SQLCommandMBS.ExecuteCommandMT(SQLCommand as string, CommandType as Integer=0)
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method SQL MBS SQL Plugin 10.4 Yes Yes Yes Yes No
Function: Executes the given command.
Notes:
This is a convenience function.
Internally it calls setCommandText with the given command and calls Execute.

The work is performed on an extra thread, so this function can yield time to other Xojo (Real Studio) threads. And it calles the Working event regularly. For best user experience run this command on a Xojo (Real Studio) thread, so your GUI stays responsive.

All text strings sent to the plugin must have a defined encoding. Else the internal text encoding conversions will fail.

Feedback, Comments & Corrections

SQLCommandMBS.ExecuteMT
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method SQL MBS SQL Plugin 10.4 Yes Yes Yes Yes No
Function: Executes the current command.
Notes:
Use the Execute method to execute the query or stored procedure specified in the command text. Execute method calls Prepare method implicitly if needed. If the command has input parameters, they should be bound before calling Execute method. Input parameters represented by SAParam object. To bind input variables assign a value to SAParam object returning by Param or ParamByIndex methods.

A command (an SQL statement or procedure) can have a result set after executing. To check whether a result set exists use isResultSet method. If result set exists, a set of SAField objects is created after command execution. Rows from the result set can be fetched one by one using FetchNext method. To get field description or value use Field method.

Output parameters represented by SAParam objects. They are available after command execution. To get parameter description or value use Param or ParamByIndex methods.

The work is performed on an extra thread, so this function can yield time to other Xojo (Real Studio) threads. And it calles the Working event regularly. For best user experience run this command on a Xojo (Real Studio) thread, so your GUI stays responsive.

Feedback, Comments & Corrections

SQLCommandMBS.FetchFirst as boolean
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method SQL MBS SQL Plugin 11.1 Yes Yes Yes Yes No
Function: Fetches first row from a result set.
Notes:
Same as FetchNext, but jumps to the first row.
Returns true if the row was fetched; otherwise false.

Not supported for Interbase and SQLite.

When you cache the result set, you can always move within the result set.

Feedback, Comments & Corrections

SQLCommandMBS.FetchLast as boolean
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method SQL MBS SQL Plugin 11.1 Yes Yes Yes Yes No
Function: Fetches last row from a result set.
Notes:
Same as FetchNext, but jumps to the last row.
Returns true if the row was fetched; otherwise false.

Not supported for Interbase and SQLite.

When you cache the result set, you can always move within the result set.

Feedback, Comments & Corrections

SQLCommandMBS.FetchNext as boolean
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method SQL MBS SQL Plugin 9.3 Yes Yes Yes Yes No
Function: Fetches next row from a result set.
Notes:
Returns true if the next row was fetched; otherwise false .

Use FetchNext method to fetch row by row from the result set.

Each column of fetched row is represented by SAField object. If a result set exists after the last command execution, a set of SAField objects is created implicitly. To check whether a result set exists use isResultSet method. FetchNext method updates value parts of SAField objects.

To get field description or value use Field method.

When you cache the result set, you can always move within the result set.

Feedback, Comments & Corrections

SQLCommandMBS.FetchPos(offset as Integer, relative as boolean = false) as boolean
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method SQL MBS SQL Plugin 15.1 Yes Yes Yes Yes No
Function: Fetches a row by index.
Notes:
Returns true if the row was fetched; otherwise false.
You may need to request recordset to be scrollable to have this work.
For that, please set Option("Scrollable") = "true" before doing the query.

When you cache the result set, you can always move within the result set.

Feedback, Comments & Corrections

SQLCommandMBS.FetchPrior as boolean
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method SQL MBS SQL Plugin 11.1 Yes Yes Yes Yes No
Function: Fetches previous row from a result set.
Notes:
Returns true if the row was fetched; otherwise false.
Same as FetchNext, just going back inside the result set.

Not supported for Interbase and SQLite.

When you cache the result set, you can always move within the result set.

Feedback, Comments & Corrections

SQLCommandMBS.Field(index as Integer) as SQLFieldMBS
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method SQL MBS SQL Plugin 9.3 Yes Yes Yes Yes No
Function: Returns the column specified by its position in the result set.
Example:
dim c as SQLCommandMBS // your command object

// get field by name
dim f1 as SQLFieldMBS = c.Field("FirstName")

// get field by Index
dim f2 as SQLFieldMBS = c.Field(1)
Notes:
index: A one-based field number in a result set.

Use Field method to access a field by its name or position in the result set.
For Cached result sets, please use Value() function to get values.

Using an index smaller than 1 and greater then the value returned by FieldCount method will result in a failed assertion.

A set of SAField objects creates implicitly after the command execution if the result set exists.SAField object contains full information about a column: name, type, size, value.

See also:

Feedback, Comments & Corrections

SQLCommandMBS.Field(name as string) as SQLFieldMBS
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method SQL MBS SQL Plugin 9.3 Yes Yes Yes Yes No
Function: Returns the column specified by its name in the result set.
Example:
dim c as SQLCommandMBS // your command object

// get field by name
dim f1 as SQLFieldMBS = c.Field("FirstName")

// get field by Index
dim f2 as SQLFieldMBS = c.Field(1)
Notes:
name: A string that represents a name of the requested field.

Returns a reference to a SAField object.

Use Field method to access a field by its name or position in the result set.
For Cached result sets, please use Value() function to get values.

Using a non-existent field name will throw an exception.

A set of SAField objects creates implicitly after the command execution if the result set exists.SAField object contains full information about a column: name, type, size, value.

See also:

Feedback, Comments & Corrections

SQLCommandMBS.FieldNames as String()
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method SQL MBS SQL Plugin 14.0 Yes Yes Yes Yes No
Function: Returns an array with all the field names for quick inspection.

Feedback, Comments & Corrections

SQLCommandMBS.Open
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method SQL MBS SQL Plugin 9.3 Yes Yes Yes Yes No
Function: Opens the specified command object.
Notes:
Use the Open method to open the command explicitly.

A command will be implicitly opened by any method that needs an open command, therefore you don't have to call it explicitly.

To test whether a command is opened use isOpened method.

Feedback, Comments & Corrections

SQLCommandMBS.Option(name as string) as string
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
property SQL MBS SQL Plugin 9.3 Yes Yes Yes Yes No
Function: A string value of a specific command option.
Example:
dim cmd as SQLCommandMBS // your command

// turn on auto cache
cmd.Option("AutoCache") = "true"
Notes:
see also:
http://www.sqlapi.com/OnLineDoc/Command_Option.html
(Read and Write computed property)

Feedback, Comments & Corrections

SQLCommandMBS.Param(ID as Integer) as SQLParamMBS
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method SQL MBS SQL Plugin 9.3 Yes Yes Yes Yes No
Function: Returns the command parameter specified by its position.
Notes:
ID: A position of parameter specified in the command text. Normally position is a number stated in the command text after a colon (for example, 1 for :1, 5 for :5).

Returns a reference to a SAParam object which is only valid as long as the param object is not deleted by the library.

Use Param method to access a parameter by its name or position (in SQL statement). If, for example, you want to walk through all the parameters use ParamByIndex method.

If parameters were not created before calling Param method the Library creates them implicitly (can query native API if needed and therefore can throw exception on error) and then returns the specified parameter.

Passing a value of name or position which does not specified in the command text will throw an exception.

SAParam object contains full information about a parameter: name, type, size, etc. Values for the input parameters can be assigned to SAParam object.

See also:

Feedback, Comments & Corrections

SQLCommandMBS.Param(name as string) as SQLParamMBS
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method SQL MBS SQL Plugin 9.3 Yes Yes Yes Yes No
Function: Returns the command parameter specified by its name.
Example:
// your connection
dim con as SQLConnectionMBS
dim SQL as string = "Insert into test_tbl(fid, fvarchar20) values(:id, :name)"

// create command object
dim cmd as new SQLCommandMBS(con, sql)

// assign values by name of parameter:
cmd.Param("id").setAsLong(2)
cmd.Param("name").setAsString(new SQLStringMBS("Some string (2)"))

// Insert first row
cmd.Execute
Notes:
Name: A string that represents a name of the requested parameter. Normally name is a string stated in the command text after a colon (for example, 'city' for :city, 'my city' for :"my city") or a parameter name in a stored procedure or function.

Returns a reference to a SAParam object which is only valid as long as the param object is not deleted by the library.

Use Param method to access a parameter by its name or position (in SQL statement). If, for example, you want to walk through all the parameters use ParamByIndex method.

If parameters were not created before calling Param method the Library creates them implicitly (can query native API if needed and therefore can throw exception on error) and then returns the specified parameter.

Passing a value of name or position which does not specified in the command text will throw an exception.

SAParam object contains full information about a parameter: name, type, size, etc. Values for the input parameters can be assigned to SAParam object.

See also:

Feedback, Comments & Corrections

SQLCommandMBS.ParamByIndex(index as Integer) as SQLParamMBS
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method SQL MBS SQL Plugin 9.3 Yes Yes Yes Yes No
Function: Returns the command parameter specified by index.
Notes:
Index: A zero-based index of the requested parameter in the array of SAParam objects. It must be greater than or equal to 0 and 1 less than the value returned by ParamCount method.

Returns a reference to a SAParam object.

Normally you should use Param method to access a parameter by its name or position (in SQL statement). ParamByIndex method can be used if, for example, you want to walk through all the parameters.

If parameters were not created before calling ParamByIndex method the Library creates them implicitly (can query native API if needed and therefore can throw exception on error) and then returns the specified parameter.

Passing a negative value of index or a value greater or equal than the value returned by ParamCount method will result in a failed assertion.

SAParam object contains full information about a parameter: name, type, size, etc. Values for the input parameters can be assigned to SAParam object.

Feedback, Comments & Corrections

SQLCommandMBS.Prepare
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method SQL MBS SQL Plugin 9.3 Yes Yes Yes Yes No
Function: Prepares command before execution.
Notes:
Prepare method compiles the command, but does not execute it. The method detects syntax errors in command text and verifies the existence of database objects.

Execute method calls Prepare method implicitly if needed, therefore you don't have to call it explicitly.

Feedback, Comments & Corrections

SQLCommandMBS.setCommandText(SQLCommand as string, CommandType as Integer = 0)
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method SQL MBS SQL Plugin 9.3 Yes Yes Yes Yes No
Function: Sets the command text.
Example:
dim s as new SQLCommandMBS

s.setCommandText "select * from test"

MsgBox s.CommandText
Notes:
SQLCommand: A string which represents command text string (an SQL statement or a stored procedure name).
CommandType: The type of command like kCommandTypeUnknown, kCommandTypeSQLStatement, kCommandTypeSQLStatementRaw or kCommandTypeStoredProcedure.

It's not necessary to set a command type explicitly, because it is defined automatically in terms of command text string. But if you still have any reason to do it, use one of the kCommandType* constants. To get command type use CommandType method.

Feedback, Comments & Corrections

SQLCommandMBS.SetParameters(Params as dictionary)
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method SQL MBS SQL Plugin 16.4 Yes Yes Yes Yes No
Function: Sets the parameters based on the keys and values in the dictionary.
Example:
dim con as SQLConnectionMBS // your connection
dim pic as picture // some picture

// get picture data
dim jpegData as MemoryBlock = pic.GetData(Picture.FormatJPEG, 80)

// parse a SQL command
dim sql as string = "Insert into BlobTest(name, image) values (:name, :image)"
dim cmd as new SQLCommandMBS(con, sql)

dim d as new Dictionary
// set by param index
d.Value(0) = "test.jpg"
// set by param name
d.Value("image") = jpegData

// set all parameters together
cmd.SetParameters d
cmd.Execute
Notes:
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.

Feedback, Comments & Corrections

Next items

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




Links
MBS FileMaker Plugins