Database request

Home Back Next

Databases are working passive. They need to be requested.

Requests are

•	SELECT Reads data from the database. This brings the data.

•	INSERT Writes data into the database.

•	UPDATE Actualizes existing data.

•	DELETE Deletes a data set.

•	CALL/EXEC Procedure Calls a subroutine without parameters

•	CALL/SELECT Function Calls a subroutine with parameters

•	User defined database request

Input data are connected directly to the database element.

The results a database request brings (mostly SELECT or CALL) will be used with the database results element.

The result data are valid on "Ready" and "Ok" and "Result Count not zero".

More complex data handling are easier to handle if they are realized with sequence chains. The result data are corresponding the database request for the same database connection and the same request id.

Parameters can be used in the SQL statement with a leading ":". If the SQL statement requires a ":" it must be doubled.

User defined database requests require detailed knowledge for SQL and the specific database used. Even if the request does not use input data one input element need to be defined, it is used for the request.

Detailed information about the status of the database request will be returned in the output named "Error Codes". This field contains three variables:

Byte 0: Server error. The database server is reachable and it works well, but the request can not be handled properly. The database server brings an error text in this case. The text will be shown in the logic table online diagnostics. It will be shown also in the diagnostics logger it this is not switched off.

Byte 1: Connection error. The database server is unreachable.

Byte 2: Cache error. The cache will be used only if this is configured in the database connection. The cache can return an error if the path or file is unreachable, or of the file is read only. It can come also if the medium runs out of disk space.

Hint for ODBC with Oracle:

Please enter the username in capital letters for the schema name. Oracle does not support the schema browsing.

Enable file caching

If the connection to the database is interrupted, the calls are cached in a file.

The following conditions must be met for this.

•	The buffering must be activated in the connection.

•	The buffering must be activated for each database command block.

This function can only be activated if the respective command should NOT return any data. This is always the case with INSERT, UPDATE, DELETE, never with SELECT. With CALL and with user-defined commands only if no results are expected ("Maximum expected results" = 0).

If this has been fulfilled, then stored procedure calls are cached also.

The background to this regulation is that the response data can no longer be assigned when requests are caches. Subsequent caching of commands that provide answers is therefore not intended.