Table Queries

<Application>

Name of the external application that contains the requested table. In the Control Center, 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 need to 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.

If for the external application (object) a generation set is available with the same name as for the calling application, then the revision for that generation set will be used. In all other cases, always the newest revision that is available will be used (disregarding the generation sets).

Example:

Suppose that the application "AppA" contains an external call to the table "T1" in application "AppB". Both applications use a generation set called "Prod". The details are as follows:

If AppA is generated through the generation set "Prod", then the revision 1.1 will be used for "T1" . If AppA is generated through the generation set 'Test', then the revision 1.3 will be used for '"T1".

At runtime there are two different procedures for the handling of "external table 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 performance 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 table query is executed, you will receive an error message at runtime.

<Fieldname>

<Expression>

Expression, returning the value for the indicated table field. This expression may also be a reference to another fieldname. Depending on the NULL values property setting, a field may be given the value null, like in the examples below. The null can not be used inside an operation.

Examples:

(A)
qtb1A.field1 := null
qtb1A.insert ()

(B)
qtb1A.field1 := null
qtb1A.update (field1)

(C)
qtb1A.field1 := null + 'abc'
// This is not allowed

<Function>

<Function parameters>

<Table>

<Table query>

CLEAR

Deletes all values from the query except Lionrecno, Lionmodifieddate, Lionmodifiedtime, Lioncreateddate and Lioncreatedtime. Clear is always followed by parentheses. No information is specified between those parentheses.

DELETE

Deletes the data record that is most recently read within the query.

EQUAL

Assigns the index key to the record that is specified between parentheses.

By default, AMT will fetch increasingly more rows when doing a select query on the database to optimize performance. For database queries where the result set size is already known, this default behaviour can be changed with this function by setting the number of rows which should be fetched per run.

The FETCHROWS function should be coded before the INDEX function. Setting the FETCHROWS function to 0 (zero) will reset it, re-enabling the default behavior. The <numrows> parameter must be a non-negative integer literal.

Please note that this function will not limit the number of rows in a result set, it is only related to performance between AMT and the database. The default behaviour gives a good general performance, this function should therefore only be used in cases where there are specific performance issues.

Syntax: <tablequery>.FETCHROWS (<numrows>)

Example:

qtest.fetchrows (250)
qtest.index (index-abc)

loop qtest
  ..
endloop

A FIND with the suboption FIRST, always returns a new result set (which is not influenced by any previous retrievals). If there is already a query started through the same index that uses the same variable(s) as is/ are requested in the FIND (FIRST) command, that query will be closed automatically.

When using the suboption NEXT, you have to specify also the .INDEX, .EQUAL, .START and .WHERE. Unless the values for inquiry through the index have changed, the behavior for the FIND(NEXT) is the same as for the FINDNEXT.

If the values for the inquiry through the index have changed, the FIND(NEXT) option will be considered as a FIND(FIRST).

The suboption has to be specified between (), so the syntax becomes: <Tablequery>.FIND ( FIRST|NEXT ). The .FIND option cannot be used in combination with .GETFIRST or LOOP <Tabelequery>.

Example:

loop
  qtest.index (index-abc)
  qtest.equal (sa-key)
  qtest.start (0, 0)
  qtest.find (next)
  { The returning of any next value and the
    comparing of the keys is based on the actual
    value for the result set for the indec
    INDEX-ABC.}
  if not resok then break
    ..
  if sa-restart = 'Y'
    sa-key := 'NEXT KEY'
    // Next loop iteration starts a new query
  endif
endloop

FINDNEXT

Fetches the next value, based on the previous inquiry on the same index. This option can only be used in combination with FIND (FIRST | NEXT). The actual value for the specified inquiry on the index is always maintained. This means that also if the same index is read again, the starting value will be set according to the previous inquiry on the index and the found value will be added to the same result set.

In difference with the option FIND(NEXT), a FINDNEXT will not be executed as a FIND(FIRST) in case of a change in one of the inquiry values that are specified for the index. If there is a change in one of these values, the FINDNEXT will continue based on these new specifications.

Makes sure that the next FIND (NEXT) is always regarded to be a FIND (FIRST).

GETFIRST

Fetches the first record from the result set and closes the result set.

INDEX

Selects the index used to retrieve records from the database. The key values are set using both the EQUAL and the START function. To scan on the table, you have to specify the INDEX with empty parentheses (e.g. qPARAM.INDEX () ). In that case, the EQUAL and START properties do not apply. When the index specifier is set to 'NONE', no index will be used and the database server has to find the optimal path for returning the results.

If you use the suboption "LOCKED", the record will be locked at the moment that it is read by the Table query. In difference with the LOCKED function, the next commands that refer to the same table query will not automatically) lock the requested records.

Permitted syntaxes:

<Table query>.INDEX ()<Table query>.INDEX (<Indexname>)

<Table query>.INDEX (<Indexname>, KEYONLY)

<Table query>.INDEX (LOCKED)<Table query>.INDEX (<Indexname>, LOCKED)

<Table query>.INDEX (<Indexname>, LOCKED, KEYONLY)

<Table query>.INDEX (NONE)

<Table query>.INDEX (NONE, LOCKED)

qtest.index (index-abc, locked)

INSERT

Inserts the given values into the table. This function together with the assignment of a buffer content to a table query and the deletion of the buffers content afterwards, can be written as a single instruction. See also the examples A & B below, which are similar as are examples C & D:

(A)
icust.insert ()
qcust := icust
icust.clear ()

(B)
icust.insert (qcust, clear)

(C)
icust.insert ()
icust.clear ()

(D)
icust.insert (clear)

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

Reads only the keys of the <Index> and Lionrecno, which results in a faster execution. In contrast to Cursor Queries the remaining fields retain are blank/zero

Locks the records at the time that another request for those records is done by using the same Table query variable. So, these records will also be locked if these table queries are not executed directly after the LOCKED function is set for these table queries.

If the LOCKED function is used inside a report, every table query that uses the same variable causes a lock of the records throughout the program. If this function is used inside a form, these next table queries will lock the records only until the screenresults have been sent to the user. So, in that case this function only influences the table queries that are encountered before the end of the sections Display_main or Process_main or before the end of the event routine.

Concurrent transactions only have rights to read the locked records. To avoid loss of performance or eventual occurrences of deadlock situations that may be caused when the same records are also requested through other transactions, you might want to release the lock with a COMMIT or SAVERECOVERYNAME. For the same reason, you might also consider using the .INDEX suboption "LOCK" where this is possible. In difference with the .LOCKED, this suboption will not place a lock for next requests that use the same Table query variable.

RESULTOKTO

Directs result status of a command to the specified boolean or alphanumeric (length 5) variable (instead of using the system item RESOK).

If set to "TRUE", the pointer position for the opened table will be retained. So, if this would be done in a loop and that loop has been executed before, AMT will continue reading the next record when that loop is executed again. If set to false, the pointer will be reset to its previous position.

Example:

loop for ldg-occurence := 1 to 10
  if ldg-occurrrence = 1
    qvweur.retainptr (false)  // Reset previous result
  endif
  qvweur.index (fvweur)
  qvweur.start ('')
  qvweur.retainptr (true)  // Keep result set open
  loop qvweur
    break
  endloop
  if resok then <record found action> else <no records found anymore action>
endloop

Sets a WhereFreeSQL parameter of type Alpha defined in <ParamName> to the given <AlphaValue> or NULL.

Syntax: <tablequery>.SETPARAMALPHA ( <ParamName>, <AlphaValue> )

Sets a WhereFreeSQL parameter of type Boolean defined in <ParamName> to the given <BooleanValue> or NULL.

Syntax: <tablequery>.SETPARAMBOOLEAN ( <ParamName>, <BooleanValue> )

Sets a WhereFreeSQL parameter of type Date defined in <ParamName> to the given <DbsDateValue> or NULL.

Syntax: <tablequery>.SETPARAMDATE ( <ParamName>, <DbsDateValue> )

Sets a WhereFreeSQL parameter of type DateTime defined in <ParamName> to the given <DbsDateTimeValue> or NULL.

Syntax: <tablequery>.SETPARAMDATETIME ( <ParamName>, <DbsDateTimeValue> )

Sets a WhereFreeSQL parameter of type Integer(64) defined in <ParamName> to the given <IntegerValue> or NULL.

Syntax: <tablequery>.SETPARAMINTEGER ( <ParamName>, <IntegerValue> )

Sets a WhereFreeSQL parameter of type Numeric defined in <ParamName> to the given <NumericValue> or NULL.

Syntax: <tablequery>.SETPARAMNUMERIC ( <ParamName>, <NumericValue> )

Sets a WhereFreeSQL parameter of type String defined in <ParamName> to the given <StringValue> or NULL.

Syntax: <tablequery>.SETPARAMSTRING ( <ParamName>, <StringValue> )

Sets a WhereFreeSQL parameter of type Time defined in <ParamName> to the given <DbsTimeValue> or NULL.

Syntax: <tablequery>.SETPARAMTIME ( <ParamName>, <DbsTimeValue> )

START

UPDATE

Updates the specified fields to the assigned values. To update all fields, use the * operand. E.g. qPARAM.UPDATE (*).

To increase performance, AMT will only update a field when the value in the memory buffer of the query has been changed. This means that it is possible that after the database values have been read by the query, even though the database has been updated by a different query, the memory buffer values of the query have not changed and thus no update will be performed on the database. It is therefore recommended not to update the same record by multiple queries or alternatively reread the potentially changed record before updating.

query1.index (idx1)
query1.equal (custno)
query1.getfirst ()
...
query2.index (idx1)
query2.equal (custno)
query2.getfirst ()
query2.sendinvite := 'Y'
query2.update (*)
...
query1.sendinvite := 'N'
query1.update (sendinvite)

query1.index (idx1)
query1.equal (custno)
query1.getfirst ()
...
query2.index (idx1)
query2.equal (custno)
query2.getfirst ()
query2.sendinvite := 'Y'
query2.update (*)
...
query1.index (idx1)
query1.equal (custno)
query1.getfirst ()
query1.sendinvite := 'N'
query1.update (sendinvite)

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 AMT 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. It is not allowed to use array items of tables in the where clausule.

Syntax:

<Table query>.WHERE ( <Condition> )

See also example H.

WhereFreeSql is an advanced function is for writing complex subselections which are not possible with the Tablequery Where function.

To write and validate correct SQL is the responsibility of the developer. The statement string needs to be correct SQL that can be added to an existing WHERE statement which is already created for the equal/start/where functions.

When using WhereFreeSql, records are not retrieved in block mode anymore. Meaning that when you do not process all the records, or when you have commits inside the loop, the performance might be impacted in a negative way due to the plans generated for the query by the SQL engine. After a commit, the next record is retrieved by re-executing the query with the current record as start values. Find/Find(Next) options are not supported in combination with WhereFreeSql.

Syntax:

<tablequery>.WHEREFREESQL(<SqlWhereString>)

Parameters:

Parameters can be defined as @paramname or :paramname depending on the target database. Unlike a 'Free Join Query' the SQL statement does not support ? for parameters.

• Parameter names should be preceded by either a colon ':' or a at sign '@' (e.g. :Param1 or @Param1).
  • The at sign is primarily intended for use with MSSQL databases.
  • The colon is primarily intended for use with Oracle databases.
  • When the colon is used with an MSSQL database or when the at sign is used with an Oracle database, automatic translation between the two characters will take place.
• The parameter names are case sensitive and may only contain the alphabetical characters A to Z and a to z, the numeric characters 0 to 9 and an underscore (_).
  Any other character will not be interpreted as being part of the parameter name.
• Values of the parameters can be set with the SetParamXxx function, where Xxx is a value type. E.g. SetParamAlpha, SetParamInteger etc. See the various types above.
• The <Value> given in the SetParamXxx statement will be converted to the type of the SetParamXxx function.

Example:

tq.index (index02)
tq.equal ('K1', 'K2')
tq.start (3, 4)
tq.where (tq.maint <> 'D')
tq.wherefreesql ('value2 in (select val from tablex where field2 = @val1 or field3 = @val2)')
tq.setparamalpha ('@val1', 'NL')
tq.setparaminteger ('@val2', 12)
loop tq
  ...
endloop