How to get the user-defined data type of a resultset's columns?

[VolkerBarth]

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):

CREATE DOMAIN person_name_t char(20)

Now, when selecting from Contacts, ISQL displays that column as char(20), and so does

select exprtype('select Surname from Contacts', 1)

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.

[VolkerBarth]

The reasoing behind this: When exporting data (e.g. to Excel), I would like to format data based on the user-defined type. Say, I have a column with percent values declared with a domain "percent_t" as "double check (@value between 0.0 and 1.0)", and would like to format that column as a percent value. A similar case would be to distinguish monetary and ordinary numerical values to format those accordingly.

[VolkerBarth · Accepted solution]

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

select name, domain_name, domain_name_with_size, user_type_name
from sa_describe_query('select Surname from Contacts')

returns

'Surname','char','char(20)','person_name_t'

as wanted.

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:

select name, domain_name, domain_name_with_size, user_type_name
from sa_describe_query('select Surname, Surname as Surname2,
    left(Surname, 15) as Surname3, Surname || ''-1'' as Surname4 from Contacts')

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):

'Surname','char','char(20)','person_name_t'
'Surname2','char','char(20)','person_name_t'
'Surname3','char','char(15 CHAR)',
'Surname4','varchar','varchar(22)',

This restriction is by design, as clarified in this follow-up Q&A.

[Former Member]

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.

For:

create table T( pk int primary key, c1 int, c2 money, c3 image)

and

select c1,c2,c3 from T

use

select c.column_name, d.domain_name, ut.type_name
from SYS.SYSTABLE t
    JOIN SYS.SYSCOLUMN c ON (t.table_id = c.table_id)
    JOIN SYS.SYSDOMAIN d ON (c.domain_id = d.domain_id)
    LEFT OUTER JOIN SYS.SYSUSERTYPE ut on (c.user_type = ut.type_id)
where table_name = 'T'
and column_name in ('c1','c2','c3')

[VolkerBarth]

Thanks for your suggestion - unfortunately, I'm neither using ESQL nor dealing with resultsets limited to table columns but with arbitrary queries.

[VolkerBarth]

As stated in my own answer, the fact that my queries are not limited to select columns from different tables might be not a real restriction here, as SA does not seem to treat computed expressions as user-defined types at all. Therefore I might try to use your system-catalog approach at least for those table-based expressions.