Begin statement ([Carriage return][,Linefeed])
| Command group | Flag affected | Reversible | Execute on client | Platform(s) |
|---|---|---|---|---|
| SQL Object Commands | NO | NO | NO | All |
Begin statement ([Carriage return][,Linefeed])
Begin statement ([Carriage return][,Linefeed][,Local])
| Carriage return | If specified, the command appends a carriage return, after it appends each line of the statement |
| Linefeed | If specified, the command appends a line feed, after it appends each line of the statement |
| Local | If specified, this command builds a local statement, which is discarded when the method returns. $prepare() and $execdirect() with no SQL text use it too. No other method, including one it calls, can see or change it, and the shared statement is left |
This command defines the start of a block of SQL statements and text to be stored in the SQL buffer for the current method stack. The current content of the SQL buffer is cleared when you execute this command. The End statement command defines the end of the block. The lines are not checked by Omnis in any way and must be valid SQL in order for the server to be able to use them. To use the SQL buffer, you call the $prepare or $execdirect method of a SQL statement object, passing no parameters.
The Carriage return option causes Omnis to insert a carriage return character between each line of the SQL statement. The Linefeed option causes Omnis to insert a linefeed character between each line of the SQL statement. If you select both Carriage return and Linefeed, then Omnis inserts a carriage return followed by a linefeed. If you select neither option, Omnis separates the statement lines with a space. One example of when you would use these options, is when you use Begin statement, Sta:, End statement, and $execdirect, to add a stored procedure to the database. This makes the procedure more readable when you view it.
Without the Local option, there is a single SQL buffer for each thread, shared by every method running on it. A method you call while you are building a statement, which uses a statement block of its own, therefore replaces your statement. The Local option gives the method that executes Begin statement a local SQL buffer. The Sta: and Get statement commands in that method use it, as do $prepare and $execdirect called with no parameters, until the method returns or executes Begin statement without the Local option. No other method, including one it calls, can see or change it, and the shared statement is left untouched.
It is recommended that you use the Local option unless another method deliberately adds to the same statement, or executes it with $prepare or $execdirect called with no parameters.
# Open a multi-threaded omnis sql connection to
# the datafile mydatafile and create a statement to
# select rows from the table Customers
Calculate lHostname as con(sys(115),'mydatafile.df1')
Do iSessObj.$logon(lHostname,'','','MYSESSION')
Do iSessObj.$newstatement('MyStatement') Returns lStatObj
Begin statement
Sta: Select * From Customers
Sta: Where Cust_ID > 100
End statement
Do lStatObj.$execdirect()
Do lStatObj.$fetch(lMyList,kFetchAll)