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.