SQLCommandMBS.AsRecordSet as RecordSet

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

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.

Some examples using this method:

• /SQL/SQL Unit Tests
• /SQL/SQLite Fetch values with Recordset

SQLCommandMBS.AsRowSet as RowSet

You can use normal RowSet functions to walk through fields and they simply control the command object.
This is for convenience like passing RowSet to other functions in Xojo.

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

The RowSet 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.

Requires Xojo 2019r2 or newer.

Some examples using this method:

• /SQL/SQL Unit Tests
• /SQL/SQLite Fetch values with RowSet

SQLCommandMBS.Cache

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.
We can only cache first result set.

SQLCommandMBS.Cancel

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
https://www.sqlapi.com/ApiDoc/class_s_a_command.html

SQLCommandMBS.Close

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.

SQLCommandMBS.Constructor

See also:

• Constructor(connection as SQLConnectionMBS, SQLCommand as String, CommandType as Integer = 0)

SQLCommandMBS.Constructor(connection as SQLConnectionMBS, SQLCommand as String, CommandType as Integer = 0)

// 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

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:

• Constructor

SQLCommandMBS.CreateParam(name as string, ParamType as Integer, DirType as Integer=0) as SQLParamMBS

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:

• 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

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

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:

• CreateParam(name as string, ParamType as Integer, DirType as Integer=0) as SQLParamMBS

SQLCommandMBS.DB2SQLExecDirect(sql as string)

dim cmd as new SQLCommandMBS // your command
dim con as new SQLConnectionMBS // your connection

// now use an API function
const sql = "some sql command"
cmd.DB2SQLExecDirect sql

Lasterror is set.

SQLCommandMBS.DB2SQLRowCount as Int64

Lasterror is set.

SQLCommandMBS.DestroyParams

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.

SQLCommandMBS.Execute

// 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

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.

SQLCommandMBS.ExecuteCommand(SQLCommand as string, CommandType as Integer=0)

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.

Some examples using this method:

• /SQL/SQL Exception Tests

SQLCommandMBS.ExecuteCommandMT(SQLCommand as string, CommandType as Integer=0)

This is a convenience function.
Internally it calls setCommandText with the given command and calls Execute.

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.

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

SQLCommandMBS.ExecuteMT

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

SQLCommandMBS.FetchFirst as boolean

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.

SQLCommandMBS.FetchLast as boolean

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.

SQLCommandMBS.FetchNext as boolean

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.

Some examples using this method:

• /SQL/CubeSQL Version
• /SQL/Microsoft SQL Stored Procedure
• /SQL/MySQL Fetch rows bulk
• /SQL/MySQL Fetch values
• /SQL/MySQL Query Version
• /SQL/SQLite Display Schema
• /SQL/SQLite Encryption Fetch values
• /SQL/SQLite select version with Trace events
• /SQL/SQLite Write blob to file
• /SQL/Web app/with SQLConnectionMBS

SQLCommandMBS.FetchPos(offset as Integer, relative as boolean = false) as boolean

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.

SQLCommandMBS.FetchPrior as boolean

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.

SQLCommandMBS.Field(index as Integer) as SQLFieldMBS

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)

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.

Raises OutOfBoundsException exception if index parameter is out of range.

See also:

• Field(name as string) as SQLFieldMBS

SQLCommandMBS.Field(name as string) as SQLFieldMBS

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)

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:

• Field(index as Integer) as SQLFieldMBS

SQLCommandMBS.FieldExists(name as string) as Boolean

Returns true if field is found or false if not.

SQLCommandMBS.FieldNames as String()

dim cmd as SQLCommandMBS // your command

dim FieldNames() as String = cmd.FieldNames

SQLCommandMBS.Open

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.

SQLCommandMBS.Option(name as string) as string

dim cmd as SQLCommandMBS // your command

// turn on auto cache
cmd.Option("AutoCache") = "true"

see also:
https://www.sqlapi.com/ApiDoc/class_s_a_command.html
(Read and Write computed property)

SQLCommandMBS.Param(ID as Integer) as SQLParamMBS

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:

• Param(name as string) as SQLParamMBS

SQLCommandMBS.Param(name as string) as SQLParamMBS

// 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

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:

• Param(ID as Integer) as SQLParamMBS

SQLCommandMBS.ParamByIndex(index as Integer) as SQLParamMBS

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.

Raises OutOfBoundsException exception if index parameter is out of range.

SQLCommandMBS.PostgreSQLField(RecordIndex as integer, FieldIndex as integer) as string

See also:

• PostgreSQLField(RecordIndex as integer, FieldName as string) as string

SQLCommandMBS.PostgreSQLField(RecordIndex as integer, FieldName as string) as string

See also:

• PostgreSQLField(RecordIndex as integer, FieldIndex as integer) as string

SQLCommandMBS.PostgreSQLFieldCount as Integer

SQLCommandMBS.PostgreSQLRowCount as Integer

Some examples using this method:

• /SQL/PostgreSQL RecordCount