Tips_todo   >   Sql   >   Statement Objects

Statement Objects

SQL statement objects enable you to send SQL commands to the server and process results.

StatementObj is an object variable that has a subtype initially empty but contains the value returned from executing the session object $newstatement method to generate a statement object called "MySql"

Do SessObj.$newstatement('MySql') Returns StatementObj

STATEMENT OBJECT METHODS

The various statement object methods are listed in this section.

NOTE: The information contained in this section is copied from the F1 Help.

$clear

StatementObj.$clear() clears any pending results or operations on the statement object.

$execdirect

StatementObj.$execdirect([cSQLText]) prepares and executes the SQL text cSQLText. If the cSQLText parameter is not specified then the SQL script of the current method stack is used (defined using the Begin statement, Sta:, and End statement commands.) The SQL statement is executed immediately.

Note that a statement executed directly cannot be re-executed using $execute().

$execute

StatementObj.$execute() executes or re-executes the SQL statement previously prepared using the $prepare method. If this statement was declared using a cursor then this method opens that cursor and executes the cursor statement. Bind variable values are sent to the server with appropriate conversions where required.

$fetch

StatementObj.$fetch([row or list variable] [,iRowCount=1] [,bAppend=kFalse]) fetches one or more rows into the row or list variable.

iRowCount is the maximum number of rows to be fetched.

bAppend=kTrue indicates that the fetched rows are to be appended to the existing rows in the list; otherwise, $fetch() empties the list before fetching the rows.

@BUG:v3, Statement object $fetch spoils $cinst inside a table class

The $fetch doesn't hurt the list variable itself, but it spoils $cinst (which should work as a reference to the list) for the
remainder of the method which contains the $fetch.

@BUGWORKAROUND: What you can do to work around it is set a local var Item Ref (say lvMyRef) to $cinst.$ref before the $fetch. Then for the remainder of the table class method use lvMyRef. (e.g. lvMyRef.$line, lvMyRef.$sort)

This bug is supposed to be fixed in Studio v4.0.

Thanks to Reg Paling for the bug report and work around.

$indexes

StatementObj.$indexes(cTableName[,Type-constant]) generates a result set describing the indexes of the specified table, containing columns for DatabaseOrCatalog, Owner, ColumnName, IndexName, Unique, ColumnPosition.

Parameter cTableName may take the form [Database.][Owner.]Name depending on whether database and owner qualifiers are supported.

Type-constant indicates the type(s) of the indexes to return:
kStatementIndexUnique: unique indexes(default)
kStatementIndexNonUnique: non-unique indexes
kStatementIndexAll: all indexes

$nextnativeerror

StatementObj.$nextnativeerror() returns the next native statement error code and message in StatementObj.$nativeerrorcode and StatementObj.$nativeerrortext if StatementObj.$nativeerrorpending=kTrue

$prepare

StatementObj.$prepare([cSQLText]) prepares the supplied SQL text, or if you omit cSQLText, prepares the SQL text stored in the SQL buffer for the current method stack (defined using the Begin statement, Sta:, and End statement commands).

$results

StatementObj.$results(List variable) populates the List variable with a description of the columns in the current result set, containing columns for ColumnName, OmnisDataTypeText, SQLDataType, Length, Scale, Null.

$rpc, $rpcprocedures, $rpcparameters

Many RDBM systems allow you to create stored procedures on the server. The $rpc methods allow you to call those stored procedures. There are 2 schools of thought when it comes to stored procedures.

1. Put all your business rules in stored procedures in the RDBMS so that no matter what software (Omnis, Java, etc.) is hitting the database the business rules will be enforced. Stored procedures are faster.

2. Don't use stored procedures because it makes your application dependent on the RDBMS, thereby making it impossible to switch to another RDBMS.

Whether or not you use stored procedures depends on your individual situation. As of the time of writing this I have not yet used remote (stored) procedures.

$rpc()

StatementObj.$rpc(cProcedureName,[Param1,]É[ParamN]) calls the remote procedure cProcedureName that must have previously been defined using SessObj.$rpcdefine() passing any parameters Param1É ParamN required.

$rpcprocedures()

StatementObj.$rpcprocedures([cOwner]) generates a result set describing remote procedures available on the server containing DatabaseOrCatalog,Owner,ProcedureName.

If cOwner is an empty string, omitted or not supported then this call returns all available procedures in all available databases.

Otherwise cOwner takes the form [Database.]Owner.

If only Owner is specified, and this form is supported by the object, returns all available procedures owned by Owner in all available databases.

If Database.Owner is specified, and this form is supported by the object, returns all available procedures owned by Owner in Database.

$rpcparameters()

StatementObj.$rpcparameters(cProcedureName) generates a result set describing the parameters used by the remote procedure cProcedureName containing OmnisDataType,OmnisDataSubType, Length,PassType,C5(reserved for future use),C6(reserved for future use),DatabaseOrCatalog,Owner,ParameterName,OmnisDataTypeText,SQLDataType,Scale

$tables

StatementObj.$tables([Type-constant][,cOwner]) generates a result set containing a list of the tables or views on the server, containing columns for Owner, TableOrViewName, TableType, and Description.

The Type-constant indicates the type(s) of the objects to return:
kStatementServerAll: tables and views (default)
kStatementServerTable: tables only
kStatementServerView: views only

If specified, cOwner is used to restrict the returned tables to those owned by a particular user.

The result set may be retrieved using the $fetch() method.

StudioTips Documentation - Copyright 2006 Vencor Software