The SQL source (SRC = "SQL") enables data to be read in any SQL database accessible by an Adelia SQL Driver (Oracle, DB2, SQL Server and ODBC).
The following source-related parameters are recognized:
Input parameters (DATA_SRC)
Parameter name |
Type |
Mandatory |
Description |
DBM |
string |
Yes (*) |
DBMS type (alias): - DB2: API driver for DB2 UDB, - Oracle: API driver for Oracle (8.0 and >), - SQL Server: API driver for SQL Server, - ODBC: API driver for ODBC. The Oracle7 and Oracle8 values are recognized for compatibility with existing scripts. |
DLL |
string |
Yes (*) |
Replacement for DBM. Enables an API driver to be specified directly using its DLL name (e.g.: apiora.dll). |
DATABASE DB |
string |
Yes |
Name, alias or URL access of the database containing the data to be transferred. |
USER |
string |
No |
User connected to the database. |
PASSWORD |
string |
No |
User's password. |
CONNECT |
string |
No |
Database connection string in the form "user.password". Parameter supported for compatibility. We recommend using the USER and PASSWORD parameters. |
CONNECT |
string |
No |
Database connection string in the form "user.password". If this parameter is not specified, a system connection will be attempted. |
REQUEST |
string |
Yes (**) |
SQL query producing all the data to be exported. Any valid "SELECT" type SQL query can be used to produce the data. |
TABLE |
string |
Yes (**) |
Depreciated: The parameter is accepted if the script's version is 1.0. Name of the table (schema.tablename) whose data are to be exported. Shortcut for (REQUEST="SELECT * FROM schema.tablename") |
Output parameters (DATA_DEST)
Parameter name |
Type |
Mandatory |
Description |
DBM |
string |
Yes (*) |
DBMS type (alias): - DB2 : API driver for DB2 UDB, - Oracle: API driver for Oracle (8.0 and >), - SQL Server: API driver for SQL Server, - ODBC: API driver for ODBC. The Oracle7 and Oracle8 values are recognized for compatibility with existing scripts. |
DLL |
string |
Yes (*) |
Replacement for DBM. Enables an API driver to be specified directly using its DLL name (e.g.: apiora7.dll). |
DATABASE DB |
string |
Yes |
Name, alias or URL access of the database containing the data to be transferred. |
USER |
string |
No |
User connected to the database. |
PASSWORD |
string |
No |
User's password. |
CONNECT |
string |
No |
Database connection string in the form "user.password". Parameter supported for compatibility. |
CONNECT |
string |
No |
Database connection string in the form "user.password". If this parameter is not specified, a system connection will be attempted. |
SCHEMA |
string |
No |
Owner schema for the table (version 2.0). |
TABLE |
string |
Yes |
Name of the table (schema.tablename) receiving the data. Note: The format (schema.tablename) is supported for compatibilty reasons only if the script's version is earlier than 2.0. If not, the format will be interpreted as a long SQL name containing a dot. In version 2.0, the owner schema must be specified in the SCHEMA parameter. |
MODE |
string |
No |
Data insertion mode: - INSERT: the data are inserted into the table (default setting). - REPLACE: the data replace the data already in the table. You can also specify a DELETE clause, failing which all the existing data in the table are deleted. - UPDATE: the data is updated or added, based on the table's primary key. If the table does not have a key, or if the key contains all the columns, the data is added. - CREATE: the table is recreated using the input information. This option is only possible if the data source is able to accurately describe the structure of the table to be generated. |
DELETE |
string |
No |
If the DELETE clause is specified (in REPLACE mode), a "DELETE FROM table WHERE clause" query is run before the data are inserted. |
COMMIT |
integer |
No |
Estimated volume in Kb of the data to be inserted into the table between two validation (Commit) points. If this parameter is not specified, a value of "1000" (1 Mb) will be set by default. Setting this parameter to zero or a negative value causes the system to run in AUTOCOMMIT mode (validating each inserted line automatically). |
Notes:
(*) |
Parameters DBM and DLL are mutually exclusive. |
(**) |
Parameters REQUEST and TABLE are mutually exclusive. |
Click below for further details about:
The DATA_SRC command.
The DATA_DEST command.