SAP SQL Anywhere

Question

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.

Accepted Answer

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.

Answer 2

1

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

permanent link

answered 17 Jun '10, 15:59

Bruce Hay
2.6k●1●5●10
accept rate: 48%

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

(17 Jun '10, 16:53) Volker Barth

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.

(17 Jun '10, 17:07) Volker Barth

flat view

SAP SQL Anywhere

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

Follow this question

Related questions