Chapter 10: Using Destination-Template Transfer

Datatype qualifiers

Qualifiers tell the access service how to format data that is inserted for a placeholder. If you do not supply a qualifier, the access service applies default transformations.

Qualification is required for date and time values. You can use the ?T, ?t, ?D, and ?d qualifiers for dates, or you can create a custom qualifier using special qualifiers. For information about special qualifiers, see “Special date and time qualifiers”.

The following table defines valid datatype qualifiers.

**Table 10-1: Destination-template transfer datatype qualifiers**
Placeholder/ Qualifier	Definition
?C	Character string enclosed in quotes
?N	Numeric data, no quotes
?D	Standard format Adaptive Server datetime data enclosed in quotes
?T	Standard format SQL_TIMESTAMP data enclosed in quotes, ‘YYYY-MM-DD-hh.mm.ss.nnnnnn’
?d	‘mm/dd/yyyy’
?t	‘hh:mm:ss’
?X	Standard format Adaptive Server hexadecimal data (for example, 0xffee) used for transferring binary data
?x	Standard format hexadecimal data (for example, X’FFEE’) used for transferring binary data
?y	‘yy/mm/dd’
?G	G‘<...>’ used for transferring graphic datatypes or formatting a graphic constant from binary character data
?g	GX‘<...>’ used for transferring graphic datatypes or formatting a graphic constant from binary character data (returns data in hexadecimal format)

The following three tables show the effects of qualifiers on datatypes.

For each table, special circumstances are detailed in the text following the table.

The first table shows the effects of the ?C, ?N, ?D, and ?T qualifiers.

**Table 10-2: Effects of qualifiers on datatypes (1)**
Open Server datatype	Default	Effects (by qualifier)
		?C	?N	?D	?T
CS_CHAR CS_VARCHAR CS_TEXT	?C	Quote	No quote	Convert to Open Server datetime string, quote	Convert to ISO TIMESTAMP, quote
CS_BIT, CS_INT1 CS_INT2 CS_ INT4 CS_REAL CS_FLOAT	?N	Convert to char, quote	Convert to char, no quote	n/a	n/a
CS_MONEY CS_MONEY4 CS_DECIMAL	?N	Convert to char, quote	Convert to char, no quote	n/a	n/a
CS_DATETIME CS_DATETIME4	?D	’MON DD YYYY hh:mm’ [AM or PM]	n/a	’MON DD YYYY hh:mm:ss:nnn’	’YYYY-MM-DD-hh.mm.ss. nnnnnn’

For CS_CHAR, CS_VARCHAR, and CS_TEXT used with the ?D qualifier:

If the source is an ISO TIMESTAMP, it is converted to ‘Mon dd yyyy hh:mm:ss:nnn’.
If it is an ISO DATE, it is converted to ‘Mon dd yy’.
If it is an ISO TIME, it is converted to ‘Mon dd yy hh:mm:ss’ using the value from the DefaultDate property as the date portion of the value.

For CS_CHAR, CS_VARCHAR, and CS_TEXT used with the ?T qualifier:

If the source is an ISO DATE or TIME, the DefaultDate and DefaultTime property values are used to fill in missing information.

The following table shows the effects of the ?y, ?d, ?t, and ?x qualifiers.

**Table 10-3: Effects of qualifiers on datatypes (2)**
Open Server datatype	Default	Effects (by qualifier)
		?y	?d	?t	?x
CS_CHAR CS_VARCHAR CS_TEXT	?C	Quote	Quote	Quote	Convert to hex; leading X’ trailing ’. For example X’ab70’
CS_BIT, CS_INT1 CS_INT2 CS_INT4 CS_REAL CS_FLOAT CS_MONEY CS_MONEY4 CS_DECIMAL	?N	n/a	n/a	n/a	n/a
CS_DATETIME CS_DATETIME4	?D	’yy/mm/dd’	‘yy/mm/dd’	‘hh:mm:ss’	n/a
CS_BINARY CS_VARBINARY CS_IMAGE	?X or ?x	n/a	n/a	n/a	Convert to hex; leading X’ trailing ’. For example X’ab70’

For CS_CHAR, CS_VARCHAR, and CS_TEXT used with the ?y qualifier:

If the source is an ISO DATE, TIME, or TIMESTAMP, it is converted to ‘yy/mm/dd’.

For CS_CHAR, CS_VARCHAR, and CS_TEXT used with the ?d qualifier:

If the source is an ISO DATE, TIME, or TIMESTAMP, it is converted to ‘mm/dd/yy’.

For CS_CHAR, CS_VARCHAR, and CS_TEXT used with the ?t qualifier:

If the source is an ISO DATE, TIME, or TIMESTAMP, it is converted to ‘hh:mm:ss’.

For all datatypes used with the ?x qualifier:

If the target database is ODBC , ?x converts the data to the standard ODBC hexadecimal format (a quoted hexadecimal number with a leading X).

The following table shows the effects of the ?X and ?O qualifiers.

**Table 10-4: Effects of qualifiers on datatypes (3)**
Open Server datatype	Default	Effects (by qualifier)
		?X
CS_CHAR CS_VARCHAR CS_TEXT		Convert to hex; leading 0x, no quote
CS_BIT, CS_ INT1 CS_INT2 CS_INT4 CS_REAL CS_FLOAT CS_MONEY CS_MONEY4 CS_DECIMAL	?N	n/a
CS_DATETIME CS_DATETIME4	?D	n/a
CS_BINARY CS_VARBINARY CS_IMAGE	?X or ?x	Convert to hex; leading 0x, no quote

For CS_BINARY, CS_VARBINARY, and CS_IMAGE datatypes used with the ?X qualifier:

If the target database is ODBC, ?x converts the data to the standard ODBC hexadecimal format (a quoted hexadecimal number with a leading X).

View this book as PDF