Equivalent SQL and Fortran data types

When you declare host variables in your Fortran programs, the precompiler uses equivalent SQL data types. When you retrieve data of a particular SQL data type into a host variable, ensure that the host variable is of an equivalent data type.

The following table describes the SQL data type and the base SQLTYPE and SQLLEN values that the precompiler uses for host variables in SQL statements.

Table 1. SQL data types, SQLLEN values, and SQLTYPE values that the precompiler uses for host variables in Fortran programs
Fortran host variable data type SQLTYPE of host variable1 SQLLEN of host variable SQL data type
INTEGER*2 500 2 SMALLINT
INTEGER*4 496 4 INTEGER
REAL*4 480 4 FLOAT (single precision)
REAL*8 480 8 FLOAT (double precision)
CHARACTER*n 452 n CHAR(n)
SQL TYPE IS RESULT_SET_LOCATOR 972 4 Result set locator. Do not use this data type as a column type.
SQL TYPE IS BLOB_LOCATOR 960 4 BLOB locator. Do not use this data type as a column type.
SQL TYPE IS CLOB_LOCATOR 964 4 CLOB locator. Do not use this data type as a column type.
SQL TYPE IS BLOB(n) 1≤n≤2147483647 404 n BLOB(n)
SQL TYPE IS CLOB(n) 1≤n≤2147483647 408 n CLOB(n)
SQL TYPE IS ROWID 904 40 ROWID
Notes:
  1. If a host variable includes an indicator variable, the SQLTYPE value is the base SQLTYPE value plus 1.

The following table shows equivalent Fortran host variables for each SQL data type. Use this table to determine the Fortran data type for host variables that you define to receive output from the database. For example, if you retrieve TIMESTAMP data, you can define a variable of type CHARACTER*n.

This table shows direct conversions between SQL data types and Fortran data types. However, a number of SQL data types are compatible. When you do assignments or comparisons of data that have compatible data types, DB2® converts those compatible data types.

Table 2. Fortran host variable equivalents that you can use when retrieving data of a particular SQL data type
SQL data type Fortran host variable equivalent Notes
SMALLINT INTEGER*2  
INTEGER INTEGER*4  
BIGINT not supported  
DECIMAL(p,s) or NUMERIC(p,s) no exact equivalent Use REAL*8
FLOAT(n) single precision REAL*4 1<=n<=21
FLOAT(n) double precision REAL*8 22<=n<=53
CHAR(n) CHARACTER*n 1<=n<=255
VARCHAR(n) no exact equivalent Use a character host variable that is large enough to contain the largest expected VARCHAR value.
BINARY not supported  
VARBINARY not supported  
GRAPHIC(n) not supported  
VARGRAPHIC(n) not supported  
DATE CHARACTER*n If you are using a date exit routine, n is determined by that routine; otherwise, n must be at least 10.
TIME CHARACTER*n If you are using a time exit routine, n is determined by that routine. Otherwise, n must be at least 6; to include seconds, n must be at least 8.
TIMESTAMP CHARACTER*n n must be at least 19. To include microseconds, n must be 26; if n is less than 26, truncation occurs on the microseconds part.
Start of changeTIMESTAMP(0)End of change Start of changeCHARACTER*nEnd of change Start of changen must be at least 19.End of change
Start of changeTIMESTAMP(p) p > 0End of change Start of changeCHARACTER*nEnd of change Start of changen must be at least 19. To include fractional seconds, n must be 20+x where x is the number of fractional seconds to include; if x is less than p, truncation occurs on the fractional seconds part.End of change
Start of changeTIMESTAMP(p) WITH TIME ZONEEnd of change Start of changeno exact equivalentEnd of change Start of changeUse a character host variable that is large enough to contain the largest expected timestamp with time zone value.End of change
Result set locator SQL TYPE IS RESULT_SET_LOCATOR Use this data type only for receiving result sets. Do not use this data type as a column type.
BLOB locator SQL TYPE IS BLOB_LOCATOR Use this data type only to manipulate data in BLOB columns. Do not use this data type as a column type.1
CLOB locator SQL TYPE IS CLOB_LOCATOR Use this data type only to manipulate data in CLOB columns. Do not use this data type as a column type.1
DBCLOB locator not supported  
BLOB(n) SQL TYPE IS BLOB(n) 1≤n≤21474836471
CLOB(n) SQL TYPE IS CLOB(n) 1≤n≤21474836471
DBCLOB(n) not supported  
ROWID SQL TYPE IS ROWID  
XML not supported