AMTQuery

By using AMT Query objects, you can specify all possible SQL commands that are provided through your database management system. This means that the options that you can use through such handlers, depend on which database manager you are using. AMT Query objects can also be used to read from external databases. Please note that prefixes may be ignored by SQL, which means that you may need to specify aliases (AS) in your query in order to distinguish records that are located in separate tables and are identified with the same name. Read the documentation for your database manager to learn exactly which syntax must be used.

In an AMT Query object you can use logical names to specify the data source (with the property "DATABASE"). The administrator can link these logical names to physical data sources in the Control Center. Go to System Configuration > Architecture > Databases > Aliases. This way, a programmer does not need any information about where the data is stored, and the program does not have to be altered after changing a data source name or location when for instance the application is transferred from a test environment to a production environment. Thus, using AMT Query objects in your applications increases the flexibility of the database management.

Please note the following about AMT Query objects:

AMT-COBOL does not perform any validation checks on AMT Query objects, as opposed to EXEC DB2/RDMS blocks where the SQL syntax is validated. This means that you have to check these queries manually.
Instantiating(creating) new objects and destroying objects will be done automatically by AMT-COBOL.
Exceptions from free join queries will be logged when the Logging level is set to 1 or higher in the 'Runtime behaviour configuration'.

The syntax of the AMT Query object

Declaration

An AMT Query can be declared the working storage section, as an 01 level object of type AMTQuery, with the following syntax:

01 <Query name> AMTQUERY.

Item	Description
<Query name>	Name of the query, e.g. "Query1".
AMTQUERY	Indicates that the query is an AMT Query object.

Properties & Functions

Properties of an AMT Query object can be read or set (if the property is not read only) just as any other working storage variable.
The syntax for a property of an object is:

<Query name>::<Property>

To call a function of an object, the word INVOKE must be used.
Passing parameters to a function can be done with the use of the word USING.
If there is data being returned from the function call, this can be moved to a variable using the word RETURNING.

INVOKE <Query name>::<Function> [USING <Parameter1> [<Parameter2>] [RETURNING <Variable>]]

Examples:

MOVE 'VM-MSSQL2017' TO Query1::DATABASE.

DISPLAY Query2::ERRORCODE.

INVOKE Query3::CLOSE.

INVOKE Query4::SetParamAlpha USING 0 WS-CITY.

INVOKE Query5::ValueAsString USING 'CUSTNO' RETURNING WS-CUSTNO.

Executing Queries

In an AMT Query object there are no fields to assign values to. Only a SQL command can be set using:

MOVE <SQL command> TO <Query name>::SQL.

<SQL command> should either be text literal, or a variable containing the actual SQL
command to execute.

This SQL command should then be executed in one of two ways:

When the SQL command inserts or updates a record in the database the EXECUTE functions should be used or for prepared SQL statements PREPAREEXEC & EXECPREPARED.
When the SQL command performs a SELECT or any other command that retrieves data from the database the OPEN function should be used or for prepared SQL statements PREPAREOPEN & OPENPREPARED, the returned result set can then be looped through by using the NEXT function until the end of the result set is reached.

While looping through the result set, the retrieved data can be accessed using the following syntax:

INVOKE <Query name>::<Function> USING <Column> RETURNING <Variable>

Item	Description
<Column>	The name or number of the column in the retrieved record from which the value should be stored in the variable.
<Function>	The type convert function to convert the retrieved value to the correct type of the variable.
<Variable>	A variable to store the retrieved value in.

Exception handling AMTQuery

By default, every exception on AMTQuery operations will result in termination of the program with a clear error text.
It is also possible to catch the exceptions with a declarative section.
The properties ERRORCODE and ERRORTEXT can be used to check the error inside a declarative section, outside this section they are empty.

The syntax for a declarative is:

DECLARATIVES.
ERROR-AMTQUERY SECTION.
USE AFTER STANDARD EXCEPTION PROCEDURE ON AMTQUERY.

...

END DECLARATIVES.

For example:

DECLARATIVES.
ERROR-AMTQUERY SECTION.
  USE AFTER STANDARD EXCEPTION PROCEDURE ON AMTQUERY.
  IF Query1::Errorcode NOT = 1
     DISPLAY Query1::Errorcode
     DISPLAY Query1::ErrorText
     STOP RUN
  END-IF.
END DECLARATIVES.

Properties

Name	Access	Description
CURSORNAME	Read / Write	When using SQL Cursors in an Oracle database this property needs to be set to the name of the SQL Cursor.
DATABASE	Read / Write	Stores the specified logical datasource name. The system administrator must link this name to a physical source through an alias. This must be done in the ControlCenter screen "System Configuration > Architecture > Databases > detailed information > Aliases". If the specification is empty ( ' ' ), the internal runtime database of the application will be used. E.g. MOVE 'DATABASE_Z' TO Query1::Database.
EOF	Read only	Read only property which is set to true when the end of the result set is reached or when the result set is empty.
ERRORCODE	Read only	Read only property which contains the native SQL Error code of the database-server for the corresponding AMT Query object. This property will be reset to 0 (zero) outside of the declarative section.
ERRORTEXT	Read only	Read only property which contains the error text of the last action of the corresponding AMT Query object. This property will be empty outside of the declarative section.
ROWSAFFECTED	Read only	Read only property that holds the number of rows (records) affected by the last operation on the database.
SCHEMA	Read only	Read only property used to read the schema or prefix of an Oracle database which can then be used for the free query. When the remote database has been set: If the remote database has a 'prefix' defined, this prefix is returned without the trailing dot, otherwise the remote schema is returned When the remote database has not been set, the schema name of the active user database is returned.
SQL	Read / Write	Property that contains the specified SQL statement. E.g. MOVE WS-SQLTEXT TO Query1::SQL.
Unicode	Read / Write	Property that defines whether the binding to the database should use Unicode. Initially the global application option setting 'Define Unicode alpha fields for all tables' will be used for this property. This default value can be overwritten by setting it to True or False. E.g. MOVE TRUE TO Query1::UNICODE.

Functions

Name	Parameter	Return Type	Description
CLOSE	-	-	Closes the open query.
EXECPREPARED	-	-	Executes the last prepared SQL statement with the current values of the bind parameters. Note: This function should be used with all prepared SQL statements that perform a changing action on the database (e.g. UPDATE).
EXECUTE	-	-	Directs the specified SQL statement to the database manager. Only use this command for inserts and updates. For retrieval of data use the 'OPEN' or 'OPENPREPARED' functions.
ISNULL	<ColumnName>\| <ColumnNumber>	Boolean	Is set to true when the contents of the specified column is Null.
NEXT	-	-	Reads the next record from the result set into memory.
OPEN	-		Opens the query specified in the SQL property and the first record of the result set will be read into memory.
OPENPREPARED	-	-	Opens the last prepared SQL statement with the current values the bind parameters. Note: This function should be used with all prepared SQL statements that return a result set (e.g. SELECT).
PREPAREEXEC	-	-	Prepares the SQL statement for executing. Note: SQL statement should contain a command that performs a changing action on the database (e.g. UPDATE).
PREPAREOPEN	-	-	Prepares the SQL statement for opening the result set. Note: SQL statement should contain a command resulting in a result set (e.g. SELECT).
SETPARAMALPHA	<ParamName>\|<ParamNumber>	-	Sets the parameter of type Alpha defined in <ParamName> or <ParamNumber> to the given <Value> or NULL.
	<Value>\|NULL	-

SETPARAMBOOLEAN	<ParamName>\|<ParamNumber>	-	Sets the parameter of type Boolean defined in <ParamName> or <ParamNumber> to the given <Value> or NULL.
	<Value>\|NULL	-

SETPARAMDATE	<ParamName>\|<ParamNumber>	-	Sets the parameter of type Date defined in <ParamName> or <ParamNumber> to the given <Value> or NULL.
	<Value>\|NULL	-

SETPARAMDATETIME	<ParamName>\|<ParamNumber>	-	Sets the parameter of type DateTime defined in <ParamName> or <ParamNumber> to the given <Value> or NULL.
	<Value>\|NULL	-

SETPARAMDOUBLE	<ParamName>\|<ParamNumber>	-	Sets the parameter of types Float Single or Float Double defined in <ParamName> or <ParamNumber> to the given <Value> or NULL.
	<Value>\|NULL	-

SETPARAMINTEGER	<ParamName>\|<ParamNumber>	-	Sets the parameter of type Integer defined in <ParamName> or <ParamNumber> to the given <Value> or NULL.
	<Value>\|NULL	-

SETPARAMNUMERIC	<ParamName>\|<ParamNumber>	-	Sets the parameter of type Numeric defined in <ParamName> or <ParamNumber> to the given <Value> or NULL.
	<Value>\|NULL	-

SETPARAMSTRING	<ParamName>\|<ParamNumber>	-	Sets the parameter of type String defined in <ParamName> or <ParamNumber> to the given <Value> or NULL.
	<Value>\|NULL	-

VALUEASDOUBLE	<ColumnName>\| <ColumnNumber>	COMP-2 / COMP-1	Reads the field from the table and copies its value to the specified COMP-2 or COMP-1 variable.
VALUEASINTEGER	<ColumnName>\| <ColumnNumber>	Integer	Reads the field from the table and copies its value to the specified integer variable. If the original data field does not contain an integer value, the duplicated value will be converted to integer first before it is placed in the variable.
VALUEASNUMERIC	<ColumnName>\| <ColumnNumber>	Numeric	Reads the field from the table and copies its value to the specified numeric variable. If the original data field does not contain a numeric value, the duplicated value will be converted to numeric first before it is placed in the variable.
VALUEASSTRING	<ColumnName>\| <ColumnNumber>	Alphanumeric	Reads the field from the table and copies its value to the specified string variable. If the original data field does not contain a string value, the duplicated value will be converted to string first before it is placed in the variable.

Remarks

When using prepared SQL statements please note:

When Question Marks '?' are used as parameter values in the prepared SQL statement the values can only be set by using the number of occurrence of the parameter in the SQL statement (zero based).
When parameter names are used the 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.
The <Value> given in the SetParamXxx statement will be converted to the type of the SetParamXxx function.
The maximum length/decimals for the value are retrieved from the <Value> variable.
The value used for a variable at the time of the execution of the SQL statement is not the value of the variable at the time of the SetParamXxx function execution but the value of the variable at the time of the execution of the OpenPrepared or ExecPrepared function.
SetParamXxx can be repeated multiple times on the same parameter, only the last one is used. Don't mix Parameter names and Parameter numbers in that case since this will lead to unpredictable results.

Example

MOVE 'SELECT * FROM T_ADDRESSBOOK' TO Query1::SQL.

INVOKE Query1::OPEN.

PERFORM UNTIL Query1::EOF = TRUE
INVOKE Query1::VALUEASSTRING USING 'NAME' RETURNING WS-NAME
INVOKE Query1::VALUEASSTRING USING 'STREETNAME' RETURNING WS-STREETNAME
INVOKE Query1::VALUEASNUMERIC USING 'HOUSENUMBER' RETURNING WS-HOUSENUMBER
INVOKE Query1::VALUEASSTRING USING 'CITY' RETURNING WS-CITY
INVOKE Query1::NEXT
END-PERFORM.

INVOKE Query1::CLOSE.

Examples using Prepared SQL

MOVE 'INSERT INTO T_ADDRESSBOOK' TO WS-SQL1.
MOVE ' (NAME, PHONENUMBER)' TO WS-SQL2.
MOVE ' VALUES (?, ?)' TO WS-SQL3.
MOVE WS-SQL TO Query2::SQL.

INVOKE Query2::PREPAREEXEC.
INVOKE Query2::SETPARAMALPHA USING 0 WS-NAME.
INVOKE Query2::SETPARAMNUMERIC USING 1 WS-PHONENUMBER.
INVOKE Query2::EXECPREPARED.

MOVE 'REMOTE_DATABASE' TO Query3::DATABASE.
MOVE 'SELECT * FROM T_CUSTOMERS' TO WS-SQL1.
MOVE ' WHERE CUSTNO = @Param1 OR' TO WS-SQL2.
MOVE ' CUSTNAME = @Param2' TO WS-SQL3.
MOVE WS-SQL TO Query3::SQL.

INVOKE Query3::PREPAREOPEN.
INVOKE Query3::SETPARAMINTEGER USING '@Param1' WS-CUSTNO.
INVOKE Query3::SETPARAMSTRING USING '@Param2' WS-CUSTNAME.
INVOKE Query3::OPENPREPARED.

IF Query3::ROWSAFFECTED < 1
DISPLAY 'Customer not found.'
END-IF.