Salesforce

DBMS Type Support (Magic xpa 4.x)

« Go Back

Information

 
Created BySalesforce Service User
Approval Process StatusPublished
Objective
Description

DBMS Type Support (Magic xpa 4.x)

The utility uses the existing connection to the data source selected and sends the function SQLGetInfo with the fInfoType as SQL_ALL_TYPES, which returns information about all the data types supported. After receiving this information, the utility sends the function SQLGetTypeInfo for every data type in order to see if the data type is searchable (in other words, if the data type can be used in a WHERE clause).

To retrieve the same information, simply connect to the data source with Connect\Full Connect, and select Catalogue\SQLGetTypeInfo with fSQLType = ‘SQL_ALL_TYPES’. After that, select Results\GetData All. You will see the list of data types in the result window. To retrieve the SEARCHABLE parameter for each data type, select Catalogue\SQLGetTypeInfo with fSQLOption of the data type you want. Than select Results\SQLBindCol when icol = 9 (the SEARCHABLE parameter is the ninth parameter in this function). After that, select Results\SQLFetch. The last parameter in the result window (rgbValue) is the SEARCHABLE parameter value. The values are:

0 - SQL_UNSEARCHABLE if the data type cannot be used in a WHERE clause.

1 - SQL_LIKE_ONLY if the data type can be used in a WHERE clause only with the LIKE predicate.

2 - SQL_ALL_EXCEPT_LIKE if the data type can be used in a WHERE clause with all comparison operators except LIKE.

3 - SQL_SEARCHABLE if the data type can be used in a WHERE clause with any comparison operator

The information in the utility log file is listed in 3 columns:

1. DBMS Type Name – The name of the data type in the DBMS.

2. SQL Data Type - the equivalent core SQL data type defined by ODBC

3. Searchable - a True\False value that indicates if the data type can appear in a WHERE clause.

DBMS TYPE NAME

SQL DATA TYPE

SEARCHABLE

GUID

INVALID SQLTYPE

FALSE

BIT

SQL_BIT

TRUE

BYTE

SQL_TINYINT

TRUE

LONGBINARY

SQL_LONGVARBINARY

FALSE

VARBINARY

SQL_VARBINARY

FALSE

BINARY

SQL_BINARY

FALSE

LONGCHAR

SQL_LONGVARCHAR

FALSE

CHAR

SQL_CHAR

TRUE

CURRENCY

SQL_NUMERIC

TRUE

INTEGER

SQL_INTEGER

TRUE

COUNTER

SQL_INTEGER

TRUE

SMALLINT

SQL_SMALLINT

TRUE

REAL

SQL_REAL

TRUE

DOUBLE

SQL_DOUBLE

TRUE

DATETIME

SQL_TIMESTAMP

TRUE

VARCHAR

SQL_VARCHAR

TRUE

Reference
Attachment 
Attachment