When using user-defined data types (created with CREATE DOMAIN), the usual APIs seem to desribe the according columns with their base type.
For example: The SQL Anywhere Demo database has a table called Contacts with a column Surname specified as person_name_t, and the latter is defined as (without specifying nullability or a default):
Now, when selecting from Contacts, ISQL displays that column as char(20), and so does
The same seems to apply when using APIs like ODBC, maybe due to the fact that a data type mapping is only defined for base types and not for user-defined types.
So, how can I get the user-defined type of a particular column of a resultset? (Well, obviously I can query the system catalog, but I'm interested in a general approach working with any SELECT statement.)
If possible, it should work with ASA 8, too.
asked 17 Jun '10, 07:04
NOTE: The following works with SA 10 and above, but not with older versions:
The system procedure sa_describe_query (introduced in SA 10) returns both the base and the user-defined datatypes. So, the following call
This is what I'm trying to accomplish with ASA 8.0.3...
Addition: For any kind of computed expression, even sa_describe_query does not return a user-defined type (and one might argue whether the expression still has a user-defined type or not). Examples:
returns person_name_t for the first two columns but not for the computed ones (though at least the left-expression would always fit in the domain type):
This restriction is by design, as clarified in this follow-up Q&A.
From an ESQL application, you can use EXEC SQL DESCRIBE USER TYPES to get this information in version 8. Otherwise, if the query's result set is restricted to a list of columns in a table, you could use the column names and table name to join with SYS.SYSCOLUMN and SYS.SYSUSERTYPE.
answered 17 Jun '10, 15:59