To meet your needs, we constantly work to improve Querix products.
This means that Lycia documentation is developing as well.
In case you have found a certain dissonance between the provided information and the actual behavior of Lycia 3 and/or your applications, please, let us know about this via documentation@querix.com so that we can introduce the necessary changes to our documentation.
Thank you for your attention and cooperation.
variable.Declare(statement [, with_scroll, with_hold])
This method is used to initialize a CURSOR variable and to assign a cursor to it together with the statement it is declared for and all other features. After the cursor is declared in this way, it can be referenced by other methods used with cursors. Typically it would then be used with the Open() method which will open the declared cursor and make it ready to fetch rows.
The Declare() method accepts three arguments.
The statement argument can be a character variable or a quoted character string containing the text of the SQL statement associated with the cursor; it can also be represented by a variable of the PREPARED data type which has previously been initialized using the Prepare() method. The SQL statement can be either a SELECT statement or an INSERT statement, depending on whether you want to declare a select or an insert cursor. When declaring a cursor for a SELECT statement, you should include the FOR UPDATE keywords to the statement text, if you want to use the cursor for updating data.
The with_scroll argument specifies whether the cursor should be declared WITH SCROLL. This arguments is of the BOOLEAN data type and is FALSE by default. If you want to declare a cursor WITH SCROLL (applicable only for the cursors declared for a SELECT statement), you should set this argument to 1, otherwise to 0)
The with_hold argument specifies whether the cursor will be declared WITH HOLD - whether it will be closed at the end of a transaction automatically. If you don't want the cursor to be closed automatically, set this argument to 1. The default value is 0 (FALSE).
This method returns sqlca.sqlcode which is 0, if the cursor was closed properly and which has a negative value indicating the error number, if something went wrong while executing the method.
The examples of method calls:
# Declaring a cursor for a normal SQL statement without hold and without scroll
DEFINE cur_v CURSOR
CALL cur_v.Declare("SELECT * FROM customer WHERE customer.cust_id > ? FOR UPDATE")
RETURNING err_code
# Declaring a cursor with hold for a prepared statement
DEFINE cur_v CURSOR,
prep_v PREPARED
CALL prep_v.Prepare("SELECT * FROM customer WHERE customer.cust_id > ?")
CALL cur_v.Declare(prep_v, 0, 1) RETURNING err_code