ODBC Scalar Data Types

Previous Next

Mapping of USoft Developer data type names to ODBC SQL data type is handled as follows:

ODBC Scalar data types

USoft Developer data type

ODBC SQL data type

BINARY

SQL_BINARY

BIT

SQL_BIT

CHAR

SQL_CHAR

CLOB

SQL_LONGVARCHAR

DATE

SQL_TIMESTAMP

DATETIME

SQL_TIMESTAMP

DECIMAL

SQL_DECIMAL

DOUBLE PRECISION

SQL_FLOAT

FLOAT

SQL_FLOAT

FLOAT4

SQL_REAL

IMAGE

SQL_LONGVARBINARY

INT

SQL_INTEGER

INTEGER

SQL_INTEGER

INTEGER1

SQL_TINYINT

LONG

SQL_LONGVARCHAR

LONG VARBINARY

SQL_LONGVARBINARY

LONG VARCHAR

SQL_LONGVARCHAR

LONGRAW

SQL_LONGVARBINARY

MONEY

SQL_FLOAT

NCHAR

SQL_CHAR

NUMBER

SQL_NUMERIC

NUMERIC

SQL_NUMERIC

NVARCHAR

SQL_VARCHAR

RAW

SQL_VARBINARY

REAL

SQL_REAL

SMALLDATETIME

SQL_TIMESTAMP

SMALLINT

SQL_SMALLINT

SMALLMONEY

SQL_REAL

TEXT

SQL_LONGVARCHAR

TIME

SQL_TIMESTAMP

TIMESTAMP

SQL_TIMESTAMP

TINYINT

SQL_TINYINT

VARBINARY

SQL_VARBINARY

VARCHAR

SQL_VARCHAR

VARCHAR2

SQL_VARCHAR

When application tables are generated directly on ODBC, USoft Developer retrieves the ODBC DBMS specific data type names from the connected ODBC driver, using the function: SQLGetTypeInfo. If the ODBC-driver does not support this level-1 function or when generating SQL script files (containing CREATE TABLE and INDEX statements), then the following default data type names are used:

ODBC Default data types

Required Data Type

Default Name

SQL_CHAR

CHAR

SQL_VARCHAR

VARCHAR

SQL_LONGVARCHAR

LONG VARCHAR

SQL_DECIMAL

DECIMAL

SQL_NUMERIC

NUMERIC

SQL_INTEGER

INTEGER

SQL_SMALLINT

SMALLINT

SQL_TINYINT

TINYINT

SQL_REAL

REAL

SQL_FLOAT

FLOAT

SQL_BIT

BIT

SQL_BINARY

BINARY

SQL_VARBINARY

VARBINARY

SQL_LONGVARBINARY

LONG VARBINARY

SQL_DATE

DATE

SQL_TIME

TIME

SQL_TIMESTAMP

TIMESTAMP

When application tables are generated directly on ODBC and the ODBC driver does support the level 1 function SQLGetTypeInfo, but the ODBC-driver does not support a particular ODBC SQL data type, then the following table shows the data type that will be substituted. If that data type is also not supported, then the substitute for that will be used, and so on, down the table.

ODBC substitute data types

Required Data Type

Substitute

SQL_CHAR

SQL_VARCHAR

SQL_VARCHAR

SQL_CHAR

SQL_DECIMAL

SQL_NUMERIC

SQL_NUMERIC

SQL_DECIMAL

SQL_SMALLINT

SQL_INTEGER

SQL_INTEGER

SQL_SMALLINT

SQL_TINYINT

SQL_SMALLINT

SQL_REAL

SQL_FLOAT

SQL_FLOAT

SQL_REAL

SQL_BINARY

SQL_VARBINARY

SQL_VARBINARY

SQL_BINARY

SQL_BIT

SQL_BINARY

SQL_BIT

SQL_CHAR

SQL_TIMESTAMP

SQL_DATE

When application tables are generated, USoft Developer will rename existing tables before creating the new application tables. Because "RENAME TABLE" is not defined in the ODBC SQL syntax specification, USoft Developer will generate the following standard syntax when generating SQL-script files:

ALTER TABLE table_old_name RENAME TABLE table_new_name

This may result in errors when running the SQL-script file for the specific ODBC DBMS because the rename syntax is not correct. If this is the case, you must change this syntax into the ODBC DBMS-specific syntax. To avoid problems with non-executable SQL script files for ODBC databases, always try to generate application tables by connecting directly to the ODBC DBMS via an ODBC driver (preferably one with ODBC-API level-1 conformance).

NOTE: Universal date_to_char and char_to_date functions are available for ODBC. The syntax is:

SELECT date_to_char(<expression>,<format>) FROM <table>

SELECT char_to_date(<expression>,<format>) FROM <table>