Up Prev Next
From: "Avis, Ed" <avised at kbcfp dot com>
Subject: RE: DBD::Sybase and SQL_DATA_TYPE
Date: Mar 15 2005 1:40PM
Using DBD::Oracle as an example it appears that SQL_DATA_TYPE isn't
what I wanted anyway. I was hoping for some string I could use
verbatim in a table definition. (I want to run an arbitrary query and
put its results into a table - like 'select into' but sucking the data
from one database connection and putting it into another.)
It appears I have to glue together TYPE_NAME, PRECISION and maybe a few
other things. Annoyingly on Sybase both LOCAL_TYPE_NAME and TYPE_NAME
can be user-defined types, so I have to look them up in systypes. As
a first approximation:
select base.name + '(' + convert(varchar(10), u.length) + ')'
+ case when u.allownulls = 1 then ' null' else ' not null' end
from systypes base,
where base.type = u.type
and not exists (
from systypes smaller
where smaller.type = u.type
and smaller.usertype < base.usertype
and u.name = 'my_user_defined_type'
Ick. Now the DBI documentation says that TYPE_NAME is 'for use in
CREATE TABLE statements etc', and indeed it can be *in the same
database*. But these type names could not be used in general, not even
for creating tables in another database on the same server.
Should we ask the DBI developers to clarify the intended meaning of
TYPE_NAME? I would prefer it to contain something that has a fighting
chance of working in 'create table' on any SQL RDBMS.