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

<FIELD_CSTYPE>

<FIELD_SPEC>

<FIELD_TYPE>

<FIELD_TYPENAME>

<FIELD_VBTYPE>

Other field loop expansion tokens

 

 


Copyright © 2018  Synergex International, Inc.