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.

asked 17 Jun '10, 07:04

Volker%20Barth's gravatar image

Volker Barth
31.5k318461676
accept rate: 33%

Comment Text Removed

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.

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

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.

permanent link

answered 17 Jun '10, 07:12

Volker%20Barth's gravatar image

Volker Barth
31.5k318461676
accept rate: 33%

edited 17 Jun '10, 19:48

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%20Hay's gravatar image

Bruce Hay
2.6k1510
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
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×32
×25
×5

question asked: 17 Jun '10, 07:04

question was seen: 2,429 times

last updated: 17 Jun '10, 19:48