FIELD_SQLTYPE
Inserts a SQL compatible data definition of the field. By default the data types for Microsoft SQL Server are used but you can select one of the other supported databases either by using the -database command line option, or by setting the CODEGEN_DATABASE_TYPE environment variable.
Usage
<FIELD_SQLTYPE>
Alternate forms
None
Resulting Values
|
Synergy Data Type |
SQL Server Type |
MySQL Type |
PostgreSQL Type |
|
Alpha |
VARCHAR(n) |
VARCHAR(n) |
VARCHAR(n) |
|
Alpha (Binary) |
CHAR(n) |
VARBINARY(n) |
CHAR(n) |
|
Auto sequence
|
BIGINT |
BIGINT |
BIGINT |
|
Auto time
|
BIGINT |
BIGINT |
BIGINT |
|
Binary
|
CHAR(n) |
VARBINARY(n) |
CHAR(n) |
|
Boolean
|
BIT |
BOOLEAN |
BOOLEAN |
|
Date (YYYYMMDD)
|
DATE |
DATE |
DATE |
|
Date (YYMMDD)
|
DATE |
DATE |
DATE |
|
Date (YYYYJJJ)
|
DECIMAL(7) |
DECIMAL(7) |
NUMERIC(7) |
|
Date (YYJJJ)
|
DECIMAL(5) |
DECIMAL(5) |
NUMERIC(5) |
|
Date (YYYYPP)
|
DECIMAL(6) |
DECIMAL(6) |
NUMERIC(6) |
|
Date (YYPP)
|
DECIMAL(4) |
DECIMAL(4) |
NUMERIC(4) |
|
Decimal |
DECIMAL(n) |
DECIMAL(n) |
NUMERIC(n) |
|
Enum
|
INT |
INT |
INT |
|
Implied Decimal |
DECIMAL(n,p) |
DECIMAL(n,p) |
NUMERIC(n,p) |
|
Integer (i1)
|
SMALLINT |
TINYINT |
SMALLINT |
|
Integer (i2) |
SMALLINT |
SMALLINT |
SMALLINT |
|
Integer (i4) |
INT |
INT |
INT |
|
Integer (i8) |
BIGINT |
BIGINT |
BIGINT |
|
Structure field
|
VARCHAR(n) |
VARCHAR(n) |
VARCHAR(n) |
|
Time (HHMMSS) |
TIME(0) |
TIME(0) |
TIME(0) |
|
Time (HHMM) |
TIME(0) |
TIME(0) |
TIME(0) |
|
User Defined Alpha |
CHAR(n) |
CHAR(n) |
CHAR(n) |
|
User Defined Numeric |
CHAR(n) |
CHAR(n) |
CHAR(n) |
|
User Defined Time-stamp (see below) |
DATETIME2 |
TIMESTAMP |
TIMESTAMP |
|
User Defined Date (all others) |
CHAR(n) |
CHAR(n) |
CHAR(n) |
User-Defined Time-stamp Fields
User-defined time-stamp fields can be implemented in the same way as supported by xfODBC:
•Define the field as a user-defined date type, length 20
•Set the fields "user string" to ^CLASS^=YYYYMMDDHHMISSUUUUUU
•Use the <IF USERTIMESTAMP> and <IF NOTUSERTIMESTAMP> expressions to detect the fields in field loops.
•Use the SQL CONVERT function to convert a YYYYMMDDHHMISSUUUUUU string into a DATETIME2 value as required by SQL Server. Note that DATETIME2 fields are only supported from SQL Server 2008. An example of using CONVERT is shown below:
a20field = %datetime
a26field = %string(^d(a20field),"XXXX-XX-XX XX:XX:XX.XXXXXX")
Then when building your SQL statement you would do something like this
sql = sql + "CONVERT(DATETIME2," + a26field + ",21)"
See also
•Other field loop expansion tokens
Copyright © 2012 Synergex International, Inc.