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 the Acu4GL for DBMaker interface 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:
1. 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.
2. 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.
3. 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.
4. 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 the interface 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 the interface 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 data source data type, but don't worry about getting an exact fit. So you can use PIC 9(9) whenever the data source 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 itemSQL_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 the interface 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.