Adapter properties and commands

Defines the JDBC driver-specific connection to a database. See the JDBC driver documentation for details. Generally, the command format is:

-URL jdbc:driver://host:port/database[?custom_settings]

The corresponding adapter command is -URL connection_string.

Specifies the username for the connection. This is optional property. For many drivers, you can specify the username as part of the connection string. Some drivers might use authentication other than username and password, and some drivers might not require authentication at all. The corresponding adapter command is -USR user_name (or -USER user_name).

Specifies the password for the connection. This command is optional for the reasons described in the -USER command. The corresponding adapter command is -PWD password (or -PASSWORD password).

Specifies the fully-qualified Java™ class name for the java.sql.Driver interface implementation in the JDBC driver, typically provided in the driver documentation. This command is optional. If you omit the -DRIVER command, the adapter searches all available drivers to locate the driver specified in the URL connection string. The corresponding adapter command is -DRV driver_class (or -DRIVER driver_class).

Instructs the adapter how to configure the driver in respect to automatic commit of SQL statements performed by the driver. Default is off. Use the default setting specific to the driver and database.

When set to on, the adapter explicitly requests the driver to enable and perform auto-commit.

When set to off, the adapter explicitly requests the driver to disable and not perform auto-commit.

The corresponding adapter command is -AC (or - AUTOCOMMIT).

Specifies the name of the database catalog. It is case-sensitive. The adapter does not include quotation marks around the catalog name. This is an optional property.

The corresponding adapter command is -CAT catalog_name (or -CATALOG catalog_name).

Specifies the name of the database schema. It is a case-sensitive. The adapter does not include quotation marks around the schema name. This is an optional property.

The corresponding adapter command is -SCH schema_name (or -SCHEMA schema_name).

Specifies the name of the stored procedure to execute. It is case-sensitive and does not include quotation marks around the procedure name. This command overrides the -TABLE command.

• When used in an input card, do not specify any input arguments. The procedure can return a result set or a single set of values through output arguments and a return value.
• When used in an output card and the procedure returns data, the card is automatically configured for a request and response exchange using schema types that represent input and output data.
• When used in a PUT function, any specified output arguments or return values are ignored, because there is no mechanism to provide those values back to the map.

The corresponding adapter command is -PROC procedure_name (or -PROCEDURE procedure_name).

Specifies the name of the table or view to access. It is case-sensitive and does not include quotation marks around the table name. The corresponding adapter command is -TBL table_name (or -TABLE table_name).

Specifies the name of the columns to access. It is case-sensitive and does not include quotation marks around the column name. The corresponding adapter command is -COLUMNS column [, column [, …]] where column is a comma separated list of columns. The corresponding adapter command is -COL (or -COLUMNS).

Specifies the alternate string format for date columns. If not specified, the format is the JDBC default yyyy-mm-dd. The corresponding adapter command is -DF (or -DATEFORMAT).

Specifies the alternate string format for time columns. If not specified, the format is the JDBC default hh:mm:ss.[fff...]. The corresponding adapter command is -TF (or -TIMEFORMAT).

Specifies the alternate string format for timestamp columns. If not specified, the format is the JDBC default yyyy-mm-dd hh:mm:ss.[fff...]. The corresponding adapter command is -TSF (or -TIMESTAMPFORMAT).

Specifies the mode of operation to use in PUT map function and output card when the Table property is specified. The default value is Insert. The meaning of the values is as follows:

• Insert: Each input row is inserted to the target table. The corresponding adapter command value is insert.
• Update: Each input row is used to update the corresponding row in the target table. The corresponding adapter command value is update.
• Insert First: Each input row is inserted to the target table. If the operation fails due to an integrity constraint error (such as a primary key violation), use the input row data to update the matching row in the target table. The corresponding adapter command value is insert_first.
• Update First: Each input row is used to update the matching row in the target table. If the table does not contain a matching row, insert the input row into the target table. The corresponding adapter command value is update_first.
• Delete: Each input row is used to delete the matching row in the target table. The corresponding adapter command value is delete.

The corresponding adapter command is -WM (or -WRITEMODE).

Specifies the text of the query statement to execute when fetching the data. When GET map function is used with the adapter, the query statement text may contain query bind parameters (as '?' characters), and the values for the parameters are provided as the third argument of the GET function, with '|' character used as the value separator. The corresponding adapter command is -QRY (or -QUERY statement_text).

This property is used as an alternative to provide query statements through a file. The SELECT statement for fetching rows from the database is provided through file. Generally, the command format is:

-URL jdbc_connection_url -USR username -PWD password -QUERYFILE file_path 

The corresponding adapter command is -QRYF (or -QUERYFILE).

Specifies the custom Data Manipulation Language (DML) statement to use for writing rows to the database. When specified, the schema, table and write mode properties are ignored. The number of bind parameters in the statement must match the number of fields in the records pushed to the adapter. The corresponding adapter command is -DML statement_text.

This property is used as an alternative to provide DML statements through a file. The custom DML statement for writing rows to the database is provided through file. Generally, the command format is:

-URL jdbc_connection_url -USR username -PWD password -DMLFILE file_path 

The corresponding adapter command is -DMLF (or -DMLFILE).

Specifies the list of column names to use in the WHERE clause of SELECT and UPDATE statements that the JDBC adapter generates automatically in the following scenarios:

• By a GET function when the -TABLE command is specified. The adapter assumes that the fields in the records match the number and order of columns in the generated WHERE clause.
• By a PUT function or output card when the -TABLE command is specified and the -WRITEMODE command specifies INSERT_FIRST, UPDATE, or UPDATE_FIRST. The adapter assumes that the fields in the records match the number and order of columns in the target table.

The -KEY command is ignored in all other scenarios. If not specified, the JDBC adapter inspects the table in the database (specified by the combination of catalog, schema and table properties), determines the columns that comprise the primary key of the table, and lists those columns in the WHERE clause. If a column name in the list contains the exclamation point (!) or comma (,), use the exclamation point (!) as the release character.

The corresponding adapter command is -KEY name [,name[, …]].

A list of comma-separated index [type] entries that specify how to bind input-record field values to parameters in the user-defined statement that the adapter performs on the database. The index value specifies the position of the respective parameter in the statement, starting with position 1. The type value specifies the data type to use for binding input data values to the statement parameter indicated by the index position, and is one of the following: TEXT, BINARY, INT8, INT16, INT32, INT64, DECIMAL, FLOAT32, FLOAT64, DATE, TIME, TIMESTAMP. The default value is TEXT. A single space character should be specified between each index value and the corresponding type value. No other whitespace characters are allowed in the list. The -BIND command applies only to the following scenarios:

• A GET function or output card that specifies the -QUERY command.
• A PUT function or output card that specifies the -DML command.

The corresponding adapter command is -BIND index[ type][,index[ type][, …]].

Specifiies the maximum number of records to return from the database. The value 0 means all available records.

The corresponding adapter command is -RL (or -ROWLIMIT).

If set parameters in the statement can be named instead of using ? placeholder. Named Parameters should be specified as {name}. This is applicable with QUERY and DML.

The corresponding adapter command is -NP (or -NAMEDPARAMS).

Decides whether to exclude or include read-only columns, when to generate the schema or when to perform the table operations. It is enabled when source or target object is table. When specified, the read-only columns would be excluded while generating table queries or statements.

The corresponding adapter command is -ERO (or -EXCLUDEREADONLY).

Here is an example of the adapter command line that includes the Exclude Read-Only Columns command:

-URL jdbc_connection_url -USR username -PWD password -ERO -CAT catalog_name -SCH schema_name -TBL table_name

Specifies the comma-separated list of column names to be included in SET clause. Whitespace characters should not be included unless they are part of the actual column names.

The corresponding adapter command is -SETCOL (or -SETCOLUMNS).

Column labels are typically available when importing schemas for SELECT statements which include AS keyword with the columns in the list. This setting is applicable only when generating schemas.

The corresponding adapter command is -UCL (or -USECOLUMNLABELS).

This property is applicable only on target action and can only be used with target as Table and Write Mode as Insert and Insert First. When the value is set to true, the adapter truncates the specified table prior to inserting rows to it.

The corresponding adapter command is -TRC (or -TRUNCATE).

Here is an example of the adapter command line that includes the Truncate command:

-URL jdbc_connection_url -USR username -PWD password -WM insert -TRC -MVM null_value -CAT catalog_name -SCH schema_name -TBL table_name -BS 1

Determines whether to treat missing (empty) string values in the delimited input data as Null value or empty string value. The corresponding adapter command is -MVM (or -MISSINGVALUEMODE). The supported property values are Null Value and Empty String, and the corresponding adapter command values are null_value and empty_string.

Specifies one or more SQL statements to execute after connecting to the database and prior to fetching any rows from the database or writing any rows to the database. The statements are committed by the adapter after the last one is executed, or automatically if Autocommit is specified or the statements are non-transactional (e.g. DDL statements). Bind parameters are not supported – the statements must be static statements. The adapter assumes that individual statements are separated by a semicolon. Each semicolon and backslash character that is integral part of a statement needs to be escaped by a backslash character.

The corresponding adapter command is -PRESQL.

This property is used to provide Pre-SQL statements through a file. The file contains one or more SQL statements to execute after connecting to the database and before fetching any rows from the database or writing any rows to the database.

The corresponding adapter command is -PRESQLF (or -PRESQLFILE).

Here is an example of the adapter command line that includes the Pre SQL File command:

-URL jdbc_connection_url -USR username -PWD password -WM insert -MVM null_value -CAT catalog_name -SCH schema_name -TBL table_name -BS 1 -PRESQLF file_path

Specifies one or more SQL statement to execute after fetching or writing all rows and prior to disconnecting from the database. The statements are committed by the adapter after the last one is executed, or automatically if Autocommit is specified or the statements are non-transactional (e.g. DDL statements). Bind parameters are not supported – the statements must be static statements. The adapter assumes that individual statements are separated by a semicolon. Each semicolon and backslash character that is integral part of a statement needs to be escaped by a backslash character.

The corresponding adapter command is -POSTSQL.

This property is used as an alternative to provide Post-SQL statements through a file. Specifies one or more SQL statement to execute after fetching or writing all rows and before disconnecting from the database, through file. The corresponding adapter command is -POSTSQLF (or -POSTSQLFILE).

-URL jdbc_connection_url -USR username -PWD password -WM insert -MVM null_value -CAT catalog_name -SCH schema_name -TBL table_name -BS 1 -POSTSQLF file_path

Specifies the number of rows per fetch that the JDBC driver retrieves from the database. The fetch size must be a non-negative number. This is an optional property. If you omit it or specify a fetch size of 0, the JDBC driver determines the optimal fetch size.

The corresponding adapter command is -FS fetch_size (or -FETCHSIZE fetch_size).

The corresponding adapter command is -BS batch_size (or -BATCHSIZE batch_size).

Specifies the number of rows to retrieve from a database table. This command is optional. If you omit it, the default is one row. A value of S retrieves all rows. The -QTY adapter command is used when fetching data from the database and it specifies the maximum number of rows to fetch from the database. The default value is 1, and the values S means all available rows. The -QTY command must always be accompanied by the -LSN 0 command. The -QTY and -LSN commands are managed automatically when the adapter is utilized in input card in the Design Server web UI. But when the adapter command line is specified directly, the -QTY and -LSN commands must be included in it manually.

Whether to read from the source in bursts or read the source in its entirety.

Specifies the fetch unit. 0 means Fetch All.

When using an API to create or overwrite an input JDBC card:

• When Fetch As is set to Integral mode, set the Fetch Unit to 1 and specify the following commands so that adapter reads and returns all available rows at one time:
  • -QTY 1
  • -LSN 0
  • -ARRAYSIZE 0
• When Fetch As is set to Burst mode, set the Fetch Unit to 1 and specify the following commands so that adapter reads and returns one row per burst:
  • -QTY S
  • -LSN 0

The corresponding adapter command are -EROWH (or -ERRORROWHANDLING).

The corresponding adapter command values are none, error_table and error_file.

Specifies the name of the error-table database catalog. It is case-sensitive and does not include quotation marks around the name.

The corresponding adapter command is -ECAT catalog_name (or -ERRORCATALOG catalog_name).

Specifies the name of the error-table database schema. It is case-sensitive and does not include quotation marks around the name.

The corresponding adapter command is -ESCH schema_name (or -ERRORSCHEMA schema_name).

Specifies the name of the error table where the JDBC adapter stores invalid input records. This is an optional property. When the error table is specified, the adapter stores the invalid input record as part of the current transaction and continues to process subsequent input records. When the adapter cannot insert an invalid record into the error table, the adapter reports the error and the map stops executing. When the error table is not specified, the adapter reports the first invalid record and the map stops executing.

The corresponding adapter command is -ETBL table_name (or -ERRORTABLE table_name).

For example:

create table error_table (
    rowNumber      number,
    vendorCode     number,
    SQLState       varchar2(16 char),
    reason         varchar2(1024 char),
    rawData        blob
);

Specifies the character-set encoding to convert character data between native (core engine) and Java (adapter) components. By default, the adapter uses the default system locale encoding. In most cases, you do not need to change the character-set encoding. Custom encoding is applicable only in advanced scenarios. For example, when you exchange binary data with the adapter directly, the data is interpreted as text data and is encoded in a non-default encoding.

The corresponding adapter command is -CS (or -CHARSET).

Specifies the the character set for converting contents of SQL statement files between native format and driver Java format. By default the current system locale charset is used.

The corresponding adapter command is -SFCS (or -SQLFILESCHARSET).

Specifies the cardinality of field components of the row group in the imported schema. The default value is Always, resulting in fields being always marked as optional (0:1). When set to Mandatory, the fields are always marked as mandatory (1:1). When set to Nullable Columns, the fields are marked optional only if they are found to be nullable. This property applies only to schema generation operation in the Design Server UI. It is not applicable to read and write operations performed during a map run.

This property specifies the level of logging to use for the log (trace) file produced by the adapter. The default is Off. The value Informational means log informational, the value Errors Only means log error messages only, and the value Verbose means log debug and trace level messages along with the informational and error messages.

The corresponding adapter command is:

-T [E|V] [+] [file_path]

-T -> Log adapter informational messages.

-TE -> Log only adapter errors.

-TV -> Use verbose (debug) logging. The log file records all activity that occurs while the adapter is producing or consuming messages.

+ -> Appends the trace information to the existing log file. Omit this argument to create a new log file.

file_path -> The full path to the adapter trace log. If you omit this keyword, the adapter creates the m4jdbc.mtr log file in the map directory.

Specifies the location of the log file to which to write log messages. If not specified, the default log file name m4jdbc.mtr is used, and the file is stored to the directory in which the executed compiled map resides.

Specifies the action to perform on the transaction when the transaction fails. The default value is Rollback.