PEPPLER.ORG
Michael Peppler
Sybase Consulting
Menu
Home
Sybase on Linux
Install Guide for Sybase on Linux
General Sybase Resources
General Perl Resources
Freeware
Sybperl
Sybase::Simple
DBD::Sybase
BCP Tool
Bug Tracker
Mailing List Archive
Downloads Directory
FAQs
Sybase on Linux FAQ
Sybperl FAQ
Personal
Michael Peppler's resume

sybperl-l Archive

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,
     systypes u
where base.type = u.type
and not exists (
    select *
    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.

-- 
Ed Avis