The Command object

Properties
CommandText CommandType Connection
ErrorCode ErrorCodeEx ErrorInfo
FieldCount FieldName FieldPos
FieldSize FieldType FieldValue
FieldValueAsLong FieldValueAsShort FieldValueAsDouble
FieldValueAsString FieldValueAsDate FieldValueAsBinary
isEmpty isFieldNull isFieldRequired
isOpened ParamDirection ParamValue
ParamValueAsLong ParamValueAsShort ParamValueAsDouble
ParamValueAsString ParamValueAsDate ParamValueAsBinary
ParamCount RowsCount XMLDocument
XMLString    

 

CommandText property
Syntax command.CommandText
Description Retrieves the SQL statement from the current command object.
Type String
Access Read / Write
CommandType property
Syntax command.CommandType
Description Retrieves the type of command text.
  1. cmdTypeUnknown [0] - Undefined
  2. cmdTypeSQLStmt [1] - SQL Statement
  3. cmdTypeStoredProc[2] - Stored Procedure
Type Number [Short]
Access Read Only
Connection property
Syntax command.Connection
Description Retrieves connection object of the current Command object
Access Read / Write
ErrorCode property
Syntax command.ErrorCode
Description Retrieves the last error code of the command object.
Notes OpenX error handler has two main error categories. System error (component level) and SQL Server error (SQL Server level).
System Error values could be found in OpenX Appendixes
In case of SQL Server error, ErrorCode returns native error code from SQL Server. Please check SQL Server documentation for error codes.
Type Long
Access Read Only
ErrorCodeEx property
Syntax command.ErrorCodeEx
Description Retrieves the row number pointing to error in the command text. (read-only)
Notes Covers SQL Server errors only. Its value is 0 in case of System Error.
Type Long
Access Read Only
ErrorInfo property
Syntax command.ErrorInfo
Description Returns the last error text of the command object. (read-only)
Notes System Error values could be found in OpenX Appendixes
In case of SQL Server error, ErrorInfo has native error text from SQL Server.
Type String
Access Read Only
FieldCount property
Syntax command.FieldCount
Description Retrieves the number of columns in the result set. (read-only)
Type Long
Access Read Only
FieldName property
Syntax command.FieldName(Long fieldNum)
Description Returns the field name or field alias (as specified in the SQL statement) for the fieldNum column. Fields are numbered starting with 1. (read-only)
Notes if fieldNum less then zero or greater then value returned by FieldCount, FieldName will raise an exception.
Type String
Access Read Only
FieldPos property
Syntax command.FieldPos(String fieldName)
Description Retrieves a position of the field in a result set. Fields are numbered starting with 1.
Type Long
Access Read Only
FieldSize property
Syntax command.FieldSize(Variant fieldIdx)
Description Retrieves declared size of the current data field.
Notes fieldIdx must be VT_BSTR, VT_I2 or VT_I4 (String, Short or Long)
Type Long
Access Read Only
FieldType property
Syntax command.FieldType(Variant fieldIdx)
Description Retrieves field data type. Possible values are:
  1. dtUnknown [0]
  2. dtShort [1] - smallint
  3. dtLong [2] - integer
  4. dtDouble [3] - money, numeric, float
  5. dtDateTime [4] - datetime, date, time
  6. dtString [5] - char, varchar
  7. dtBytes [6]
  8. dtLongBinary [7] - image
  9. dtLongChar [8] - text
  10. dtBLob [9] - image
  11. dtCLob [10] - text
  12. dtCursor [11] (for Oracle only - it is not supported in this release)
Notes fieldIdx must be VT_BSTR, VT_I2 or VT_I4 (String, Short or Long). FieldType will raise an exception if fieldIdx points to unexisted field.
Type String
Access Read Only
FieldValue property
Syntax command.FieldValue(Variant fieldIdx)
Description Retrieves the value of the field of the current row of the result set.
Notes fieldIdx must be VT_BSTR, VT_I2 or VT_I4 (String, Short or Long). FieldValue will raise an exception if fieldIdx points to unexisted field. Type of the property depends on FieldType.
Type Variant
Access Read Only
FieldValueAsLong property
Syntax command.FieldValueAsLong(Variant fieldIdx)
Description Retrieves the value of the field of the current row of the result set as Long Integer. FieldValueAsLong will raise an exception if the field type is not convertible to Long
Notes fieldIdx must be VT_BSTR, VT_I2 or VT_I4 (String, Short or Long). FieldValue will raise an exception if fieldIdx points to unexisted field. Type of the property depends on FieldType.
Type Long
Access Read Only
FieldValueAsShort property
Syntax command.FieldValueAsShort(Variant fieldIdx)
Description Retrieves the value of the field of the current row of the result set as Short Integer. FieldValueAsShort will raise an exception if the field type is not convertible to Short
Notes fieldIdx must be VT_BSTR, VT_I2 or VT_I4 (String, Short or Long). FieldValueAsShort will raise an exception if fieldIdx points to unexisted field. Type of the property depends on FieldType.
Type Short
Access Read Only
FieldValueAsDouble property
Syntax command.FieldValueAsDouble(Variant fieldIdx)
Description Retrieves the value of the field of the current row of the result set as Double. FieldValueAsDouble will raise an exception if the field type is not convertible to Double
Notes fieldIdx must be VT_BSTR, VT_I2 or VT_I4 (String, Short or Long). FieldValueAsDouble will raise an exception if fieldIdx points to unexisted field. Type of the property depends on FieldType.
Type Double
Access Read Only
FieldValueAsString property
Syntax command.FieldValueAsString(Variant fieldIdx)
Description Retrieves the value of the field of the current row of the result set as String. FieldValueAsString will raise an exception if the field type is not convertible to String
Notes fieldIdx must be VT_BSTR, VT_I2 or VT_I4 (String, Short or Long). FieldValueAsString will raise an exception if fieldIdx points to unexisted field. Type of the property depends on FieldType.
Type String
Access Read Only
FieldValueAsDate property
Syntax command.FieldValueAsDate(Variant fieldIdx)
Description Retrieves the value of the field of the current row of the result set as Date. FieldValueAsDate will raise an exception if the field type is not convertible to Date
Notes fieldIdx must be VT_BSTR, VT_I2 or VT_I4 (String, Short or Long). FieldValueAsDate will raise an exception if fieldIdx points to unexisted field. Type of the property depends on FieldType.
Type Date
Access Read Only
FieldValueAsBinary property
Syntax command.FieldValueAsBinary(Variant fieldIdx)
Description Retrieves the value of the field of the current row of the result set.
The result will be of type VT_ARRAY | VT_UI1, which is a variant array of unsigned one-byte characters.
Notes fieldIdx must be VT_BSTR, VT_I2 or VT_I4 (String, Short or Long). FieldValueAsBinary will raise an exception if fieldIdx points to unexisted field. Type of the property depends on FieldType.
Type Variant
JScript - VBArray
VBScript - Array
C/C++ - Variant [VT_ARRAY | VT_UI1]
Access Read Only
isEmpty property
Syntax command.isEmpty
Description Indicates whether the result set is empty or never returns result set.
Notes if no command.MoveNext() method has yet been called on the command object, command.isEmpty has the side effect of moving the cursor to the first (next) row of the result set. In other words, isEmpty is equal to Not command.MoveNext().
Type Boolean
Access Read Only
isFieldNull property
Syntax command.isFieldNull(Variant fieldIdx)
Description Indicates whether the field's value is NULL.
Type Boolean
Access Read Only
isFieldRequired property
Syntax command.isFieldRequired(Variant fieldIdx)
Description Indicates whether the field value could be NULL.
Type Boolean
Access Read Only
isOpened property
Syntax command.isOpened
Description Returns a Boolean indicating whether a command object is open. The command object is opened either by calling the Execute() method on the command and some logical command are still not executed or using the connection.CreateCommand() method.
Type Boolean
Access Read Only
ParamDirection property
Syntax command.ParamDirection
Description Sets or returns a Long indicating a parameter direction.
  1. ptParamInput = 0
  2. ptParamInputOutput = 1
  3. ptParamOutput = 2
  4. ptParamReturn = 3
Type Long
Access Read / Write
ParamValue property
Syntax command.ParamValue(paramNum | paramName )
Description Sets or returns the command parameter specified by its position or name.
Type Variant
Access Read / Write
ParamValueAsLong property
Syntax command.ParamValueAsLong(paramNum | paramName )
Description Sets or returns the command parameter specified by its position or name.
Type Long
Access Read / Write
ParamValueAsShort property
Syntax command.ParamValueAsShort(paramNum | paramName )
Description Sets or returns the command parameter specified by its position or name.
Type Short
Access Read / Write
ParamValueAsDouble property
Syntax command.ParamValueAsDouble(paramNum | paramName )
Description Sets or returns the command parameter specified by its position or name.
Type Double
Access Read / Write
ParamValueAsString property
Syntax command.ParamValueAsString(paramNum | paramName )
Description Sets or returns the command parameter specified by its position or name.
Type String
Access Read / Write
ParamValueAsDate property
Syntax command.ParamValueAsDate(paramNum | paramName )
Description Sets or returns the command parameter specified by its position or name.
Type Date
Access Read / Write
ParamValueAsBinary property
Syntax command.ParamValueAsBinary(paramNum | paramName )
Description Sets or returns the command parameter specified by its position or name.
Type Variant [VT_ARRAY | VT_UI1]
Access Read / Write
ParamCount property
Syntax command.ParamCount
Description Returns the number of parameters for SQL statement or stored procedure specified in the command object.
Type Long
Access Read Only
RowsCount property
Syntax command.RowsCount
Description The number of rows in the result set of the current command before fetch.
Notes command.RowsCount must be called right after connection.CreateCommand(SQL, true) or after command.Execute(). To get a result set after calculating the number of rows, the Command must be executed again by calling command.Requery() method.
Type Long
Access Read Only
XMLDocument property
Syntax command.XMLDocument
Description Returns result set as a XML document.
Notes You should not call command.Execute method before using command.XMLDocument.
Data type codes used in XML tag "type":
  • 1 - boolean
  • 2 - int(Short)
  • 3 - int(Long)
  • 4 - double
  • 5 - double(Numeric)
  • 6 - datetime
  • 7 - string
  • 8 - binary
  • 9 - image(LongBinary)
  • 10 - text(LongChar)
  • 11 - image(BLob)
  • 12 - text(CLob)
    		•
    Type DOMDocument
    Access Read Only
    XMLString property
    Syntax command.XMLString
    Description Returns result set as a XML string.
    Notes You should not call command.Execute method before using command.XMLString .
    Type String
    Access Read Only

     

    Methods
    Cancel Close
    Execute FetchOutputParams
    MoveNext Prepare
    Requery SetCommandText

     

    Cancel method
    Syntax command.Cancel()
    Description Attempts to cancel the pending result set, or current statement execution. The exact definition is vendor-dependent.
    Notes Sybase and Interbase servers doesn't support cancellation of a statement execution.
    Return None
    Close method
    Syntax command.Close()
    Description Closes the active Command. The only way to re-establish the Command is to execute it again.
    Return None
    Execute method
    Syntax command.Execute()
    Description Executes Command object's SQL statement.
    Return None
    FetchOutputParams method
    Syntax command.FetchOutputParams()
    Description Fetches the next row of output parameters (after execution of a stored procedure).
    Returns True if the next row was fetched, otherwise returns False
    Notes Vendor dependent property.
    Return Boolean
    MoveNext method
    Syntax command.MoveNext()
    Description Changes the current row to the next row of a Command's result set.
    Returns True if the next row was fetched, otherwise returns False. (False also means that loop has reached the end of the result set)
    Return Boolean
    Prepare method
    Syntax command.Prepare()
    Description Validates and prepares the current command for execution.
    Notes Prepare() method compiles the command, but does not execute it. The method detects syntax errors in command text and verifies the existance of database objects.
    Return None
    Requery method
    Syntax command.Requery()
    Description Re-executes the current Command and obtains a new result set. Identical to call a sequence of Close() and Execute() methods. The cursor is moved to the first row of the result set.
    Return None
    SetCommandText method
    Syntax command.SetCommandText(string sqlStatement, Long SQLType = cmdTypeUnknown)
    Description Sets the SQL statement in the current Command object to sqlStatement [string]. If a Command object was opened SetCommandText() method closes the command and removes all result sets.
    Possible SQLType values:
    1. cmdTypeUnknown [0] - Undefined
    2. cmdTypeSQLStmt [1] - SQL Statement
    3. cmdTypeStoredProc[2] - Stored Procedure
    Notes "text" and "image" fields must be at the end of SELECT statement. command.FieldValue will retrieve longchar or binary data for these columns.
    Return None