F.5.3 Acu4GL for DBMaker: Data Type Mapping

Overview of DBMaker data types

DBMaker defines certain generic data types. The types that Acu4GL for DBMaker uses are:

SQL_CHAR
SQL_DECIMAL (n+m, m)
SQL_TIME
SQL_SMALLINT
SQL_INTEGER
SQL_REAL
SQL_FLOAT
SQL_DOUBLE
SQL_LONGBINARY
SQL_SERIAL
SQL_BINARY
SQL_LONGVARBINARY
SQL_DATE
SQL_TIMESTAMP

Mapping COBOL data types to DBMaker data types

When Acu4GL for DBMaker creates a table, it uses what it determines to be the best match of a data type for any particular column.

This means that the database column will be able to hold any data that the COBOL data type can hold, and is as close as possible to the type of data that the COBOL program is using. The actual algorithm used is rather complicated, but the general rules are as follows:

User preferences take precedence. This means that the XFD directives specified are checked first. Therefore, when data should be of type DATE or BINARY, a DATE or BINARY type is located and used.

If the COBOL data type is usage float or usage double, a database type of FLOAT, REAL, or DOUBLE is used, depending on what is available.

If the COBOL data type is numeric, a numeric type is used in the database. The numeric type chosen depends on how large the COBOL data type is, and how many digits to the right of the decimal point (if any) there are.

For example, if the COBOL data type is PIC 99, the database types checked for are SMALLINT, INTEGER, BIGINT, DECIMAL, NUMERIC. The first of these that exists is the type that will be used for that column.

For another example, if the COBOL data type is PIC 9(6)v99, the database types checked for are DECIMAL, NUMERIC, DOUBLE, FLOAT, REAL, CHAR. Again, the first of these types that exists is the type that will be used for that column.

Anything else will use CHAR.

Mapping DBMaker data types to COBOL data types

Sometimes developers are in a situation where they need to create a COBOL File Description based on an existing database table. The most important thing to understand in this situation is that there is almost nothing that you can do wrong! When Acu4GL for DBMaker opens a database table, the only thing it checks is that the column names match the COBOL data names.

Note: DBMaker has an 18-character limit on column names.

When Acu4GL for DBMaker reads data from the database, it essentially does a COBOL-style MOVE from the native data type to the COBOL data type, whatever it is. And since most types have a CHAR representation (in other words, you can actually display most data types, using a standard DBMaker-capable tool), using PIC X(nn) for each column will work perfectly well.

A better general rule is to use a COBOL type that closely matches the database data type, but don't worry about getting an exact fit. So you can use PIC 9(9) whenever the database has an INTEGER type.

If you have more information about the database type, you might be able to use a different COBOL representation. For example, if you know that a particular column in an DBMaker database has values only in the range 0-999, you could use PIC 999 for your COBOL data. The COMP-type you use is really determined by your own preferences, and should have little bearing on the COBOL data type you choose.

If you want to somehow choose your COBOL data types so that there is a best fit, you can use the following mapping:

SQL_CHAR              PIC X(nn)nn =size of item
SQL_DECIMAL           PIC 9(n)v9(m)
SQL_SMALLINT          PIC 9(5) COMP-5
SQL_INTEGER           PIC 9(9) COMP-5
SQL_REAL              USAGE FLOAT
SQL_FLOAT             USAGE FLOAT
SQL_DOUBLE            USAGE DOUBLE
SQL_BIGINT            PIC 9(9) COMP-5
SQL_LONGVARBINARY     PIC X(nn)
SQL_DATE              PIC 9(6) or PIC 9(8)
SQL_TIMESTAMP         USAGE DISPLAY
SQL_TIME
SQL_LONGBINARY
SQL_SERIAL(START)

Note: The BINARY data types are usually of a form that COBOL can't understand anyway. You will usually just read these columns, and rewrite them unchanged. If you have more information about the data in the columns, you might be able to do something else, but this requires more knowledge about the columns.

The DECIMAL, NUMERIC, DATE, and TIMESTAMP types usually have special representations in a database, which really doesn't match any COBOL data type exactly. When Acu4GL for DBMaker binds the data (a technical term), it asks the database to return it in character form, so the most efficient COBOL data type would probably be USAGE DISPLAY.