UltraLite Database User's Guide
Dynamic SQL
Introduction to dynamic SQL
Dynamic SQL can be used from UltraLite components, but not from static development models. The steps in executing dynamic SQL statements are common to all components:
Prepare the statement using a prepared statement method on the connection object. The name of the method varies slightly with the interface.
Preparing a statement causes the character string representing the statement to be parsed and optimized (prepared) and returns an object representing the prepared statement. The optimization is necessarily less involved than that in Adaptive Server Anywhere.
Set the value of any parameters.
Optionally, when the statement has input parameters (specified as ‘?' in the statements), then your application can call methods on the prepared statement object to set the value of these parameters. Any parameters for which values are not set are set to NULL.
Execute the statement.
If the statement is an INSERT, UPDATE, or DELETE, use the ExecuteStatement method. This method returns the number of rows modified by the statement.
It the statement is a SELECT statement, use the ExecuteQuery method. This method returns an object that holds the query result set.
For queries, navigate the result set and access the values in the result set.
You can use methods on the result set object to set the position to different rows in the result set. Some examples are Next, Previous, First, Last, Relative, BeforeFirst, and AfterLast.
When the current position is at a row of the result set, the values of columns in the result set can be obtained by methods that get values. The names of the methods depend on the interface. The methods convert data to application data types automatically. For example, an integer result expression can automatically converted to a string if the result is assigned to a string variable.
For repeated execution of a prepared statement, repeat steps 2 through 4.
The values for input variables persist after a prepared statement is executed. If you use a different value, you must reset the value of the parameter.