Statement Objects

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

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

Do SessObj.$newstatement() Returns StmntObj

The various statement object methods are listed in this section. The information contained in this section is copied from the F1 Help.

Calculate SQLText as "SELECT max(Author_pkey) FROM Author"

Do sessobj.$newstatement() Returns StmntObj

Do StmntObj.$execdirect(SQLText) Returns FlagOK
If not(FlagOK)
   OK message (Icon) {Execute SQL error.////StmntObj.$nativeerrortext = [StmntObj.$nativeerrortext]}
Else
   
   Do StmntObj.$fetchinto(MaxValue) Returns FetchStatus
   If FetchStatus=kFetchError
      OK message (Icon) {Fetch error.////StmntObj.$nativeerrortext = [StmntObj.$nativeerrortext]}
   Else
      
      OK message (Icon) {The maximum Author_pkey in the Author table = [MaxValue]}
      
   End If
End If

If you are using a list variable with is bound to a table class , you can get to the table class's statement object using the notation:

; Find out the current $sqltext
Calculate #S1 as List.$statementobject().$sqltext

$execdirect

StmntObj.$execdirect([cSQLText])

Prepares and executes the SQL text cSQLText.

A statement executed directly cannot be re-executed using $execute().

$execdirect combines the $prepare and $execute statement object methods into a single method. Use $prepare and $execute if you need to execute the same SQL text just with different variable values. Putting the $prepare before the loop and then $execute inside the loop is much faster than using $execdirect inside the loop.

Calculate SQLText as "SELECT * FROM book"
Do StmntObj.$execdirect(SQLText) Returns FlagOK
If not(FlagOK)
   
   Do StmntObj.$nativeerrortext() Returns ErrorText
   OK message SQL Error (Icon) {$nativeerrortext = [StmntObj.$nativeerrortext]////when executing the SQL Script:////[SQLText]}
   
Else
   
   OK message (Icon) {The $execdirect ran successfully for the SQL script:////[SQLText]}
   
End If
Quit method FlagOK

$prepare & $execute

StmntObj.$prepare([cSQLText])

Prepares the supplied SQL text.

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

Use $prepare and $execute if you need to execute the same SQL text just with different variable values. Putting the $prepare before the loop and then $execute inside the loop is much faster than using $execdirect inside the loop.

; Prepare the SQL text using bind variables pointing to columns in a list.
Calculate SQLText as "INSERT INTO Author (Author_pkey,AuthorName) VALUES( @[List.Author_pkey], @[List.AuthorName])"
Do StmntObj.$prepare(SQLText) Returns FlagOK
If FlagOK
   
   ; Loop through the list, inserting the new records using the $execute method.
   For List.$line from 1 to List.$linecount step 1
      
      Do StmntObj.$execute(SQLText) Returns FlagOK
      If not(FlagOK)
         Break to end of loop
      End If
      
   End For
End If
If not(FlagOK)
   Do StmntObj.$nativeerrortext() Returns ErrorText
   OK message SQL Error (Icon) {$nativeerrortext = [StmntObj.$nativeerrortext]////for the SQL Script:////[SQLText]}
End If

If FlagOK
   OK message (Icon) {The $execdirect ran successfully for the SQL script:////[SQLText]}
End If

Quit method FlagOK

$nativeerrorcode & $nativeerrortext

StmntObj.$nativeerrorcode()

returns the native error statement code.

StmntObj.$nativeerrortext() returns the native error statement text.

The native error text is useful for determining what went wrong when preparing or executing a SQL script.

If not(FlagOK)
   Do StmntObj.$nativeerrorcode() Returns ErrorCode
   Do StmntObj.$nativeerrortext() Returns ErrorText
End If

$fetch

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

Do StmntObj.$fetch(List,kFetchAll) Returns FetchStatus

If FetchStatus=kFetchError
   
   OK message SQL Error (Icon) {$nativeerrortext = [StmntObj.$nativeerrortext]}
   
Else If FetchStatus=kFetchFinished
   
   ; All of the selected records have been fetched into the list.
   
Else If FetchStatus=kFetchOk
   
   ; There are more selected records waiting to be fetched.
   
End If

$tables

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

$columns

StmntObj.$columns(cTableName)

generates a result set describing the columns of the specified table, containing columns for DatabaseOrCatalog, Owner, ColumnName, OmnisDataTypeText, OmnisDataType, OmnisDataSubType, SQLDataType, Length, Scale, Null, Index, PrimaryKey, Description.

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

If only Name is specified, it returns column information for all tables with Name in all available databases.

If Owner.Name is specified then it returns column information for all tables with Name owned by Owner in all available databases.

$indexes

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

$clear

StmntObj.$clear()

$results

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

Calculate SQLText as "SELECT * FROM book"
Do StmntObj.$execdirect(SQLText) Returns FlagOK
If FlagOK
   
   Do StmntObj.$results(ResultsList) Returns FlagOK
   
End If
Quit method FlagOK

$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 two 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, PHP, 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()

StmntObj.$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()

StmntObj.$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()

$sqltext

The $sqltext property returns the full SQL that was last sent to the RDBMS. The $sqltext property is very useful for debugging SQL errors or SQL that just doesn't seem to be working.

If you are using a statement object you can get the value directly from the statment object.

; Find out the current $sqltext
Calculate #S1 as StmntObj.$sqltext

If you are using a list defined from a table class you can get to the $sqltext from the table class's statement object as follows:

; Find out the current $sqltext
Calculate #S1 as List.$statementobject().$sqltext

StudioTips Documentation - Copyright 2006 Vencor Software