| Subject: | DBD::Sybase calls the broken sp_tables stored procedure |
Centos 5
Linux dba-dev1 2.6.22.9-default #1 SMP Mon Oct 1 11:26:48 CDT 2007 i686
athlon i386 GNU/Linux
Perl v5.8.8 built for i386-linux-thread-multi
DBD::Sybase 1.07 but 1.08 also has the problem.
Due to Sybase CR 497173, sp_tables can not be relied upon to retrieve
table information correctly for ASE 15.
(http://froebe.net/blog/2008/04/09/ase-15-stored-procedure-sp_tables-doesnt-work-with-sql-udfs/)
We need to query the system tables instead. Obviously, the below code
would work in v12.0 and higher. A temp table could probably be used for
prior to 12.0 for the TABLE_TYPE to be filled correctly.
$ diff ../DBD-Sybase-1.07/Sybase.pm Sybase.pm
218c218
< my $sth = $dbh->prepare("sp_tables $table, $schema, $catalog,
$type");
---
Show quoted text
> # my $sth = $dbh->prepare("sp_tables $table, $schema, $catalog,
$type");
219a220,238
Show quoted text> my $sth = $dbh->prepare( q{
> select TABLE_QUALIFIER = db_name()
> , TABLE_OWNER = u.name
> , TABLE_NAME = o.name
> , TABLE_TYPE =
> case o.type
> when "U" then "TABLE"
> when "V" then "VIEW"
> when "S" then "SYSTEM TABLE"
> end
> , REMARKS = NULL
> from sysobjects o
> , sysusers u
> where o.type in ('U', 'V', 'S')
> and id >99
> and o.uid = u.uid
>
> });
>