Cursor Queries

Cursor queries are used to approach the database in a Unisys DMS II kind of way, which is used in Unisys Cobol.

A cursor query always refers to the table that has been specified in the cursor query definition. At runtime it will retain one cursor position per table index, including the lionrecno index. The cursors are always set to a logical position, which means that the position is a virtual one. In fact, the cursor position can be set at a record (row), just before the first record, between two consecutive records and after the last record. There is no physical relation with a specific record. In the descriptions below, the term current position will be used to indicate the virtual position in the cursor.

Initially the cursor is set to the virtual position prior to the index's first record. After a query instruction has been executed, the current cursor position will always be maintained and used as starting point for the next retrieval. Because the cursor is set virtually, it will even remain its logical position if the query instruction did not return any results. For example, if a <Cursor query>.FINDFIRST did not return any results it will virtually position before the record with the next higher key (logically depending on the sort order). If the sort order for the index is descending the cursor will be set to the logical position before the record with the next lower key. Because of this, a FINDNEXT will continue from the record that was read when the FINDFIRST was executed. This behavior differs from the other query types, which always continue according to the current result set. If a Table query or Free join query doesn't return a result set, it would be impossible to search further with f.i. a FINDNEXT instruction.

Exit status

In specific situations when a cursor query could not be executed, RESOK will be set to "FALSE" and SI-DBSTATUS will be filled with a number indicating the cause. The values for SI-DBSTATUS have the following meaning:

Value:	Explanation:
1	Record not found
2	Duplicate insert
3	Duplicate update
4	Deadlock
5	No current record
6	Constraint conflict

In the cases when one of the values 2 until 6 are returned, the record buffer is not updated. The values of the previous find will then be retained.

Please note that the use of Cursor Queries is only supported in case the database engine for the runtime database is either MS-SQL or Oracle. Support for DB2 will be added in the future.

Syntax

A cursor query can be defined with the following syntax:

<Cursor query> : Cursorquery ( [<Application>.]<Table> )

Item	Description
<Application>	Name of the external application that contains the requested table. In the ControlCenter, this application name has to be specified and linked to the required ODBC-settings (see also the administration Manual). Omit this option if you do not refer to an external table in your query. To use this option, it is needed that the external application uses the same repository as the calling application. The generation options for both applications are expected to be set equally. Always the newest revision that is available will be used (disregarding the gensets). At runtime there are two different procedures for the handling of "external cursor queries". The external connection for a report is established in its start up procedure. For forms and global routines, the connection is made only after the first execution of the query. This means that these objects cannot use the requested fields after the query transaction has been executed. So, if you would use these fields in your form or global routine before the cursor query is executed, you will receive an error message at runtime.
<Cursor query>	Name to identify the cursor query.
<Table>	Name to identify the table that must be used by the cursor query.


Besides defining cursor queries in the Local Definition, it is also possible to define cursor queries as a Global Definition.

Block assignment

With the following syntax, the values from the query specified on the right are assigned to the query on the left of the assign function (:=).

<Cursor query>.* := <Cursor query>.*

Field assignment

Field assignments can be written as follows:

<Cursor query>.<Fieldname> := <Numeric value> | <Alpha value>

Item	Description
<Cursor query>	Name to identify the table query.
<Fieldname>	Fieldname within the cursor query. Please note that it is not possible to change the values for the fields Lionrecno, Lionmodifieddate, Lionmodifiedtime, Lioncreateddate and Lioncreatedtime.
<Numeric value>	Value to assign to the selected field. This can be a numeric value or another fieldname.
<Alpha value>	Value to assign to the selected field. This can be a literal or another fieldname.

Functions

Cursor query functions can be used as follows:

<Cursor query>.<Function> ( [<Function parameters>] )

Item	Description
<Cursor query>	Name to identify the cursor query.
<Function>	Function (from the list below) that applies to the cursor query.
<Function parameters>	Specification of the parameter values to be used by the Cursor query function. In the description of the concerned function, you can read if a specification is required and what kind of values are then expected.

Function descriptions

Name

Description

CLEAR ()

Clears all field values except Lionrecno, Lionmodifieddate, Lionmodifiedtime, Lioncreateddate and Lioncreatedtime, without changing the internal index pointers.

DELETE ()

Removes the record from the database, without changing the internal index pointers.

FINDCURRENT

Rereads the current record.

Syntax:

<Cursor query>.FINDCURRENT ( [<Index>] [, LOCKED] )
<Index>	Specification for the actual index.
LOCKED	Locks the record that is read by the Cursor query.

FINDFIRST

Retrieves the first record, based on the index or Lionrecno and optional WHERE clause specifications. The current cursor position does not influence the retrieval for the FINDFIRST.

If a FINDFIRST is preceded by a WHERE instruction (without any other instruction in between), the first record for the current index that corresponds with the WHERE condition will be read.

If the FINDFIRST is not directly preceded by a WHERE instruction, the first record defined by the index is fetched initially.

If no record is found then the index keys are set to a virtual location preceding the record with the next higher key or in case no higher key can be found, to the position after the end of the table. In the latter case, a FINDNEXT will set RESOK to "False". A FINDPRIOR will then return the last logical record for the index.

Syntax:

<Cursor query>.FINDFIRST (<Index> [, LOCKED] [, KEYONLY])
<Index>	Specification for the actual index.
LOCKED	Locks the record that is read by the Cursor query.
KEYONLY	Reads only the keys of the <Index> and Lionrecno, which results in a faster execution. The remaining fields retain their original values.

Example:

table_abc.where (table_abc.key1 >= 'B')
table_abc.findfirst (index1)

FINDLAST

Retrieves the last record, based on the index and optional WHERE clause specifications. The current cursor position does not influence the retrieval for the FINDLAST.

If a FINDLAST is preceded by a WHERE instruction (without any other instruction in between), the last record for the current index that corresponds with the WHERE condition will be read.

If the FINDLAST is not directly preceded by a WHERE instruction, the last record according to the index is fetched initially.

If no record is found, the cursor position is set before the beginning of the table. A FINDNEXT will return the first record in the table.

Syntax:

<Cursor query>.FINDLAST (<Index> [, LOCKED] [, KEYONLY])
<Index>	Specification for the actual index.
LOCKED	Locks the record that is read by the Cursor query.
KEYONLY	Reads only the keys of the <Index> and Lionrecno, which results in a faster execution. The remaining fields retain their original values.

Example:

table_abc.where (table_abc.key1 <= 'H')
table_abc.findlast (index1)

FINDNEXT

Retrieves the next record, based on the index and optional WHERE clause specifications and the current cursor position.

If a FINDNEXT is preceded by a WHERE instruction (without any other instructions in between), the next record for the current index that corresponds with the WHERE condition will be fetched (using the current cursor position as starting point).

If the FINDNEXT is not directly preceded by a WHERE instruction, then the record after the current cursor position will be read.

Syntax:

<Cursor query>.FINDNEXT ( <Index> [, LOCKED] [, KEYONLY] )
<Index>	Changes the index to the specified index.
LOCKED	Locks the record that is read by the Cursor query.
KEYONLY	Reads only the keys of the <Index> and Lionrecno, which results in a faster execution. The remaining fields retain their original value.

Example:

table_abc.where (table_abc.key1 >= 'B')
table_abc.findnext (index1)

FINDPRIOR

Retrieves the previous record, based on LIONRECNO (insert order) or index specification.

If a FINDPRIOR is preceded by a WHERE instruction (without any other instructions in between), the previous record for the current index that corresponds with the WHERE condition will be fetched (starting from the current cursor position).

If the FINDPRIOR is not directly preceded by a WHERE instruction, then the record before the current cursor position will be fetched.

Syntax:

<Cursor query>.FINDPRIOR ( <Index> [, LOCKED] [, KEYONLY] )
<Index>	Changes the index to the specified index.
LOCKED	Locks the record that is read by the Cursor query.
KEYONLY	Reads only the keys of the <Index> and Lionrecno, which results in a faster execution. The remaining fields retain their original value.

Example:

table_abc.where (table_abc.key1 >= 'H')
table_abc.findprior (index1)

INSERT ()

Inserts the record into the database.

ISOLATION

Specifies under which isolation level the following queries (using the current query) will be read.

Syntax:

<Cursor query>.ISOLATION ( <Isolation level > )

One of the following values:

READ_UNCOMMITTED (Default level)
READ_COMMITTED
REPEATABLE_READ
SERIALIZABLE

More information about these isolation levels can be found in the MsSql Book with a search on one of the level names.

LOCKTABLE()

Locks the whole table.

SET

Sets the current index to <Index1> and the internal key values according to the <Location> value.

Syntax:

<Cursor query>.SET ( <Index1>, <Location>)
<Index1>	Changes the current index to the specified index.
<Location>	BEGINNING \| ENDING \| <Index2>
	BEGINNING	Sets the internal key values to LOW values. This makes that the next FINDNEXT will retrieve the first record.
	ENDING	Sets the internal key values to HIGH values. This makes that the next FINDPRIOR will retrieve the last record.
	<Index2>	Sets the internal key values to the position marked by <Index2> and fills the record buffer with the values in the record at this position. The current record pointer for <Index1> is set to the row where <Index2> is positioned.

UPDATE

Updates the current record.

Syntax:

<Cursor query>.UPDATE ( [<Field specification>] )

Specification of one or more fieldnames within the cursor query that must be
updated. If multiple fieldnames are specified, these must be separated with
a comma (,). If all fields in the current record must be updated, it is possible
to use the asterisk sign (*). Please note that it is not possible to change the
values for the fields LIONRECNO, LIONMODIFIEDDATE, LIONMODIFIEDTIME,
LIONCREATEDDATE and LIONCREATEDTIME.

WHERE

Sends an extra condition to the database server. By using this option, the table data will be filtered by the database server. Since the [Lion_Verson] application receives only the results for the condition and therefore does not have to filter the data itself, the performance of the application will increase.

The WHERE instruction supports the operators =, <>, <, >, <=, >=, NOT, AND, OR, IN and LIKE. The supported functions for this clause are LEFT, LOWER, RIGHT, SUBSTRING and UPPER.

For optimal performance, you can specify index keys in the condition.

The WHERE must always be specified directly before the FIND operation.

It is not allowed to use array items of tables in the where clausule.

Syntax:

<Cursor query>.WHERE ( <Condition> )

See also the description of the WHERE conditions.

Example

The examples all use a table that is called T1.

The fields in T1 are:

key1 : alpha 1

data1 : alpha 10

key2 : alpha 2

The indexes in T1 are:

Index1 on key1 ascending.

Index2 on key2 ascending.

T1 contains the following records:

key1:	data1:	key2:	Lionrecno:
A	rec1	uu	4
B	rec2	ua	3
C	rec3	vw	9
F	rec4	xy	1
G	rec5	xx	2
H	rec6	xz	7
U	rec7	xa	5
V	rec8	bb	6
Y	rec9	ba	8

Logical view via index2:

key1:	data1:	key2:	Lionrecno:
Y	rec9	ba	8
V	rec8	bb	6
B	rec2	ua	3
A	rec1	uu	4
C	rec3	vw	9
U	rec7	xa	5
G	rec5	xx	2
F	rec4	xy	1
H	rec6	xz	7

Logical view via lionrecno:

key1:	data1:	key2:	Lionrecno:
F	rec4	xy	1
G	rec5	xx	2
B	rec2	ua	3
A	rec1	uu	4
U	rec7	xa	5
V	rec8	bb	6
H	rec6	xz	7
Y	rec9	ba	8
C	rec3	vw	9

The Cursor query is defined as follows:

CQ1: Cursorquery (T1)

Below, you can see the results for the actions that are performed through the Cursor query CQ1.

Action:			Result:
CQ1.FINDFIRST (index1)			Record:	A	REC1	UU	4
			RESOK:	TRUE
			Comments:	If it is the first access to the table via index1, a FINDNEXT will give the same result.
CQ1.FINDNEXT (index1,KEYONLY)			Record:	B	REC2	UU	3
CQ1.FINDNEXT (index1,KEYONLY)			RESOK:	TRUE
CQ1.FINDNEXT (index1)			Record:	C	REC3	VW	9
CQ1.FINDNEXT (index1)			RESOK:	TRUE
CQ1.WHERE (key1 < ‘C’) CQ1.FINDNEXT (index1)			Record:	C	REC3	VW	9
			RESOK:	FALSE
			Comments:	RESOK is false because the current position is at record with key1 = ‘C’. If a FINDPRIOR would have been done, the row with key1 = ‘B’ would have been found. A FINDLAST would select the row with key1 = ‘B’. A FINDFIRST would select the row with key1 = ‘A’ If RESOK is false, like in this case, the current position of the index is after the last row.
CQ1.FINDPRIOR (index1)			Record:	Y	REC9	BA	8
CQ1.FINDPRIOR (index1)			RESOK:	TRUE
CQ1.WHERE (key1 = ‘B’) CQ1.FINDFIRST (index1)			Record:	B	REC2	UA	3
CQ1.WHERE (key1 = ‘B’) CQ1.FINDFIRST (index1)			RESOK:	TRUE
CQ1.SET (index2,index1)			Record:	B	REC2	UA	3
CQ1.SET (index2,index1)			RESOK:	TRUE
CQ1.FINDPRIOR (index2)			Record:	V	REC8	BB	6
CQ1.FINDPRIOR (index2)			RESOK:	TRUE
CQ1.FINDNEXT (index1)			Record:	C	REC3	VW	9
CQ1.FINDNEXT (index1)			RESOK:	TRUE
CQ1.WHERE (key1 < ‘G’) CQ1.FINDLAST (index1)			Record:	F	REC4	XY	1
CQ1.WHERE (key1 < ‘G’) CQ1.FINDLAST (index1)			RESOK:	TRUE
CQ1.WHERE (key1 < ‘G’) CQ1.FINDFIRST (index1)			Record:	A	REC1	UU	4
CQ1.WHERE (key1 < ‘G’) CQ1.FINDFIRST (index1)			RESOK:	TRUE
CQ1.FINDNEXT (index2)			Record:	B	REC2	UA	3
CQ1.FINDNEXT (index2)			RESOK:	TRUE
CQ1.WHERE (key1 = ‘J’) CQ1.FINDFIRST (index1)			Record:	B	REC2	UA	3
CQ1.WHERE (key1 = ‘J’) CQ1.FINDFIRST (index1)			RESOK:	FALSE
CQ1.SET (index2,index1)			Record:	B	REC2	UA	3
			RESOK:	FALSE
			Comments:	Cursor position of index2 remains the same because current position of index1 does not point to a valid record.
CQ1.FINDNEXT (index2)			Record:	A	REC1	UU	4
			RESOK:	TRUE
			Comments:	If this would have been the first time the table is accessed via index2, the first record with key2 = ‘ba’ would have been selected. This is because then, the current cursor position would be before the logical first row of index2.
CQ1.FINDNEXT (index1)			Record:	U	REC7	XA	5
			RESOK:	TRUE
			Comments:	Because the current cursor position is before the row with key1 = ‘J’, according to the where clause and the findfirst before.
CQ1.SET (index1,BEGINNING)			Record:	U	REC7	XA	5
CQ1.SET (index1,BEGINNING)			RESOK:	TRUE
CQ1.FINDNEXT (index1)			Record:	A	REC1	UU	4
CQ1.FINDNEXT (index1)			RESOK:	TRUE
CQ1.SET (index1,ENDING)			Record:	A	REC1	UU	4
CQ1.SET (index1,ENDING)			RESOK:	TRUE
CQ1.FINDNEXT (index1)			Record:	A	REC1	UU	4
CQ1.FINDNEXT (index1)			RESOK:	FALSE
CQ1.FINDPRIOR (index1)			Record:	Y	REC9	BA	8
CQ1.FINDPRIOR (index1)			RESOK:	TRUE
CQ1.FINDPRIOR (index1)			Record:	V	REC8	BB	6
CQ1.FINDPRIOR (index1)			RESOK:	TRUE
CQ1.DELETE			Record:	V	REC8	BB	6
			RESOK:	TRUE
			Comments:	DELETE does not change the current cursor position.
CQ1.FINDNEXT (index1)			Record:	Y	REC9	BA	8
CQ1.FINDNEXT (index1)			RESOK:	TRUE
CQ1.FINDPRIOR (index1)			Record:	U	REC7	XA	5
CQ1.FINDPRIOR (index1)			RESOK:	TRUE
(New record selection)
J	RECNEW	PP
CQ1.INSERT			Record:	J	RECNEW	PP	10
			RESOK:	TRUE
			Comments:	Insert does not change the current cursor position.
CQ1.FINDPRIOR (index2)			Record:	B	REC2	UA	3
CQ1.FINDPRIOR (index2)			RESOK:	TRUE
CQ1.FINDPRIOR (index2)			Record:	J	RECNEW	PP	10
CQ1.FINDPRIOR (index2)			RESOK:	TRUE
CQ1.FINDNEXT (index1)			Record:	Y	REC9	BA	8
			RESOK:	TRUE
			Comments:	Record with key1 ‘V’ has been deleted before and the insert has not changed the current cursor position.
CQ1.WHERE (key1 = ‘J’) CQ1.FINDFIRST (index1)			Record:	J	RECNEW	PP	10
CQ1.WHERE (key1 = ‘J’) CQ1.FINDFIRST (index1)			RESOK:	FALSE
(New record selection)
RECUPD
CQ1.UPDATE			Record:	J	RECUPD	PP	10
CQ1.UPDATE			RESOK:	TRUE
(New record selection)
W
CQ1.UPDATE			Record:	W	RECUPD	PP	10
			RESOK:	TRUE
			Comments:	UPDATE does not change the current cursor position.
CQ1.FINDNEXT (index1)			Record:	U	REC7	XA	8
CQ1.FINDNEXT (index1)			RESOK:	TRUE
CQ1.FINDNEXT (index1)			Record:	W	RECUPD	PP	10
CQ1.FINDNEXT (index1)			RESOK:	TRUE
CQ1.WHERE (key1 < ‘A’) CQ1.FINDLAST (index1)			Record:	W	RECUPD	PP	10
CQ1.WHERE (key1 < ‘A’) CQ1.FINDLAST (index1)			RESOK:	FALSE
CQ1.FINDPRIOR (index1)			Record:	W	RECUPD	PP	10
CQ1.FINDPRIOR (index1)			RESOK:	FALSE
CQ1.WHERE (key1 < ‘A’) CQ1.FINDLAST (index1)			Record:	W	RECUPD	PP	10
CQ1.WHERE (key1 < ‘A’) CQ1.FINDLAST (index1)			RESOK:	FALSE
CQ1.FINDNEXT (index1)			Record:	A	REC1	UU	4
CQ1.FINDNEXT (index1)			RESOK:	TRUE
CQ1.WHERE (key1 < ‘A’) CQ1.FINDFIRST (index1)			Record:	A	REC1	UU	4
CQ1.WHERE (key1 < ‘A’) CQ1.FINDFIRST (index1)			RESOK:	FALSE
CQ1.FINDPRIOR (index1)			Record:	Y	REC9	BA	8
CQ1.FINDPRIOR (index1)			RESOK:	TRUE
CQ1.WHERE (key1 < ‘A’) CQ1.FINDFIRST (index1)			Record:	Y	REC9	BA	8
CQ1.WHERE (key1 < ‘A’) CQ1.FINDFIRST (index1)			RESOK:	FALSE
CQ1.FINDNEXT (index1)			Record:	Y	REC9	BA	8
CQ1.FINDNEXT (index1)			RESOK:	FALSE
CQ1.WHERE (key1 =‘C’) CQ1.FINDNEXT (index1)			Record:	Y	REC9	BA	8
			RESOK:	FALSE
			Comments:	Because current cursor position is after last row of the table.
CQ1.SET (index1,BEGINNING)			Record:	Y	REC9	BA	8
CQ1.SET (index1,BEGINNING)			RESOK:	TRUE
CQ1.WHERE (key1 = ‘C’) CQ1.FINDNEXT (index1)			Record:	C	REC3	VW	9
CQ1.WHERE (key1 = ‘C’) CQ1.FINDNEXT (index1)			RESOK:	TRUE
CQ1.FINDCURRENT (index2)			Record:	W	RECUPD	PP	10
CQ1.FINDCURRENT (index2)			RESOK:	TRUE
CQ1.FINDNEXT (index2)			Record:	B	REC2	UA	3
CQ1.FINDNEXT (index2)			RESOK:	TRUE
CQ1.FINDCURRENT (index1)			Record:	C	REC3	VW	9
CQ1.FINDCURRENT (index1)			RESOK:	TRUE
CQ1.SET (index1,BEGINNING)			Record:	C	REC3	VW	9
CQ1.SET (index1,BEGINNING)			RESOK:	TRUE
LOOP WHILE RESOK CQ1.WHERE (key1 =‘C’) CQ1.FINDNEXT(index1)			Records:	C	REC3	VW	9
				F	REC4	XY	1
				G	REC5	XX	2
				H	REC6	YZ	7
				U	REC7	XA	5
				W	RECUPD	PP	10
ENDLOOP