Free Join Queries

By using free join queries, 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. Unlike table queries, free queries 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 a free join query you can use logical names to specify the data source (with the function "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 free join queries in your applications increases the flexibility of the database management.

Please note the following about free join queries:

• A free join query is only meant for situations where table or cursor queries can not be used. It is advised to use table queries or cursor queries wherever possible before resorting to free join queries.
• A free join query can be used inside a loop instruction, which makes that a specific piece of code will be performed for each record that is returned by the query.
• The options that are described for tablequeries don't apply for free join queries.
• AMT does not perform any check on free join queries as is done when generating a table query. This means that you have to check these queries manually.
  
• 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 free join queries

A free join query can be defined with the following syntax

<Query name> : Query

Item	Description
<Query name>	Name of the query, e.g. "Query1".
Query	Indicates that the query is a free join query.

In a free join query there are no fields to assign values to. Only a SQL command can be set using:

<Query name>.SQL := <SQL command>

and appended with

<Query name>.SQL += <SQL command to append>

<SQL command> and <SQL command to append> should be text literals 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 EXECSQL function should be used.
• When the SQL command performs a SELECT or any other command that retrieves data from the database the loop command should be used as in the example at the end of this page.

Inside a loop the retrieved data can be accessed using the following syntax:

<Variable> := <Query name>.<Function> (<Column>)

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.

Functions & Properties

Name	Parameter	Description
ASINTEGER	<Column>	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.
ASNUMERIC	<Column>	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.
ASSTRING	<Column>	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.
CLOSE	---	Closes the open query.
CURSORNAME	---	When using SQL Cursors in an Oracle database this propertyneeds to be set to the name of the SQL Cursor.
DATABASE	---	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" (see also the "AMT Administration Manual"). If the specification is empty ( ' ' ), the internal runtime database of the application will be used. E.g. q_ST31.DATABASE := 'DATABASE_Z'
EOF	---	Read only property which is set to true when the end of the result set is reached or when the result set is empty.
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).
EXECSQL	[<SQL text>]	Directs the specified SQL statement to the database manager. Only use this command for inserts and updates. For retrieval of data use the loop command. When no <SQL text> is specified the SQL text set in <Query name>.SQL is used. When <SQL text> is specified it will also be stored in <Query name>.SQL.
ISNULL	<Column>	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	[<SQL text>]	Opens the query specified in <SQL text> and the first record of the result set will be read into memory. After the call <Query name>.SQL will contain the specified query. When no <SQL text> is specified the query set in <Query name>.SQL will be used.
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	[<SQL text>]	Prepares the SQL statement for executing. When no <SQL text> is specified the query set in <Query name>.SQL will be used. Note: SQL statement should contain either a command that performs a changing action on the database (e.g. UPDATE).
PREPAREOPEN	[<SQL text>]	Prepares the SQL statement for opening the result set. When no <SQL text> is specified the query set in <Query name>.SQL will be used. Note: SQL statement should contain a command resulting in a result set (e.g. SELECT).
ROWSAFFECTED	---	Read only property that holds the number of rows (records) affected by the last operation on the database.
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 DbsDate defined in <ParamName> or <ParamNumber> to the given <Value> or NULL.
	<Value>|NULL
SETPARAMDATETIME	<ParamName>|<ParamNumber>	Sets the parameter of type DbsDateTime 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
SETPARAMTIME	<ParamName>|<ParamNumber>	Sets the parameter of type DbsTime defined in <ParamName> or <ParamNumber> to the given <Value> or NULL.
	<Value>|NULL
SCHEMA	---	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: When the remote database has a 'prefix' defined this prefix is returned without the trailing dot else the remote schema is returned When the remote database has not been set the schema name of the active user database is returned.
SQL	---	Property that contains the specified SQL statement.  E.g. q_UPD_32.SQL := 'UPDATE TBL01 SET FLD23 = 12'
Unicode	---	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. q_UPD_32.UNICODE := False

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.
• When using SetParamXxx on routine parameters/variables the OpenPrepared or ExecPrepared should be executed in the same routine otherwise result will become unpredicable
• Changing the value inside the free query loop where it is used has no effect on the loop results until the loop is started again.

Be aware that a COMMIT on a database will close any open Free Join Query connections to that database. After a COMMIT the connections for Free Join Queries have to be re-established again. Another possibility is to create a database alias for the database, let the Table or Cursor Query use the primary database name and the Free Join Query the alias.

Example

Examples using Prepared SQL