Official | Restrictions on the database table format

(1) Cautions when outputting to the Date data type

The standard format to output type D (date) data is YYYY/MM/DD. Therefore, if the Date and Time type fields and formats are different for each database, the data is not output correctly. In this case, specify the format in the output format.

The following figure illustrates an example of output to the TIME data type that indicates the time for DB2.

Figure 4.104 Cautions when outputting type D data

(2) Restrictions on aborting during writing

If you abort writing to a database table during the process using the Abort button or the KILL command, the writing might already have been committed depending on the timing.

Do not abort while writing to database tables.

(3) Cautions for database links using SQLite3

When database table information is linked to an SQLite3 database, if a conversion is input to and output from the same table and Database connection ID of the database table information used for I/O is the same, the conversion remains unfinished. Ensure that input and output have different connection IDs.

If the conversion remains unfinished, abort the process.

(4) Cautions for converting blank characters

When you select Null for Handling of 0-byte input data in the Output Settings screen of the Data Processing Settings screen to convert blank characters, if null values are not allowed in the fields of the database for output, the conversion results in an error because the data cannot be written to the database table.

(5) Cautions when the character encoding differs between the database client and the database

If the character encoding differs between the database client (set with environment variable NLS_LANG for Oracle) and the database itself, the character encoding is automatically converted when you input the database client data to the database. In this case, a digit overflow error might occur because of the change in data size due to conversion of the character encoding.

The following figure shows an example of digit overflow if you try to input a 10-byte data item into a fixed-length, type M database of 14 bytes, when the database client is in Shift JIS encoding and the database is in UTF-8 encoding. This figure assumes that UTF-8 fullwidth characters are three-byte characters.

= Remarks =

Each UTF-8 character is represented by multiple bytes. The size of a UTF-8 character might differ depending on the type of database.

Figure 4.105 Example of digit overflow error when inputting data to the database

The following two methods will avoid a digit overflow and input data into the database correctly. Note that the table of the database itself is defined as 14 bytes in this example.

Set Size to 10 bytes in the field settings of the database.: Figure 4.106 Example of setting Size to 10 bytes in the field settings of the database
Set Fixed/variable length to Variable length in the field settings of the database.: Figure 4.107 Example of setting Fixed/variable length to Variable length in the field settings of the database

(6) Cautions for database linking using PostgreSQL

For PostgreSQL, when an error occurs during a transaction, the data is rolled back. Therefore, if Update method is set to Execute SQL 2 only if SQL 1 fails, the data is rolled back when SQL1 fails.

If database table information is linked to a PostgreSQL database, conversion is not guaranteed when using the following settings:

I/O category: : Output
Specification method: : SQL statement specification
Update method: : Execute SQL 2 only if SQL 1 fails