CallSQL

This macro will execute a single SQL call to a database – the result of the SQL call can be saved into a CSV file for further processing by other macros for example CSV Order Import or ProgressImport

Example parameter configuration

The configuration shown above is an example of how to select from a database and writing the query result into a CSV file. The connection to the database is “Oracle on PC-DbServer”. The SQL query is located in the file “.\custom\CmdCallSQL.properties” and has the property key “getOrdersExtrusion”. The result of the query is saved to the CSV file “.\data\sqlToCSV.csv”

Parameter descriptions

(* = configuration required)

callType
The type of SQL call to perform:
select = perform a select (the default value)
execute = perform update or inserts (currently not supported)
call = call stored procedures (currently not supported)

datasourceName ^*
Enter the name of the data source profile describing how to connect to the database. The profile is created and edited using the menu “Functions->Settings->General” tab Integration”. It is possible to connect to any database with a supported JDBC or ODBC driver.

dateFormat
The date format to use when writing to CSV files for date columns returned by the SQL call

For additional examples se Formatting dates

decimalCharacter
The decimal character to use when writing to CSV files for floating decimal columns returned by the SQL call. The default character is ‘.’

numberFormat
The format of the numbers when writing to CSV files for integer and floating decimal columns returned by the SQL call.

#.## means for instance 10.57 (rounded from e.g. 10.56739). A hash symbol (#) indicates the max number of decimals, in this case it’s two.
The default numberFormat is 0.##.

onSQLException
Determines what action the macro should take in case an SQL Exception is received when trying to perform the call on the database. An SQL Exception is generated in e.g. the event the data base server is unreachable or the supplied SQL statement could not be parsed by the SQL server. Possible actions are:
abort = abort the macro command – possible commands after this one will not be executed
continue = continue to next macro command

onSelectZeroRows
Determines what action the macro should take in case a SQL select query returned zero rows. Possible actions are:
abort = abort the macro command – possible commands after this one will not be executed
continue = continue to next macro command

outputFilename
The path and name of the output file. Note that a path relative to the working directory of the ROB-EX client can be specified.

outputFormat
The output format of the file generated. Possible values are:
csv = standard CSV output with ‘;’ as separator and “ as text qualifier
excel = currently not supported

propertyFilename
The path and name of the text file containing the SQL statement to execute. Note that a path relative to the working directory of the ROB-EX client can be specified.

The property file may contain multiple SQL statements, the parameter propertyKey (see below) specifies which of the SQL statements in the file to use.

The SQL statements are loaded from the file on each execution of the macro, thus it is not needed to restart the ROB-EX client after editing the query file.

The default value is .\custom\CmdCallSQL.properties

propertyKey ^*

This value specifies what SQL statement to select in the text file specified by parameter propertyFilename. The text file contais pairs of keys and statements in the format:

key=statement

The key is a unique keyword across the file and the statement is the SQL statement itself. An example CmdCallSQL.properties file may contain the following text, note how the SQL statement may be written across multiple lines by ending each line (except for the last line) with ‘ \’ (no spaces after the \ character !):

getOrders=SELECT DISTINCT ORDER_NAME, MATERIAL_NUMBER, MATERIAL_DESCR, \
START_QUANTITY, DELIVERY_DATE, \
case STATUS_TYPE WHEN 4 THEN 1 WHEN 12 THEN 1 WHEN 3 THEN 0 ELSE 0 END as ROBEX_STATUS, \
COMPLETED_IN_JOB, \
CURRENT_TIMESTAMP as LastProgressTime \
  FROM [OracleData] \
where DEPARTMENT_CODE='MYDEPARTMENT' \
order by ORDER_NAME

In this example above the value of propertyKey must be set to: getOrders

The property file may contain multiple “key=statement” pairs allowing multiple different CallSQL macro commands to share the same property text file.

The SQL statements are loaded from the file on each execution of the macro, thus it is not needed to restart the ROB-EX client after editing the query file.