2008 April 24 | Jason L. Froebe: Ramblings of a Geek

Apr 24

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.

(ASE 15 stored procedure “sp_tables” doesn’t 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”);
—

> # my $sth = $dbh->prepare(”sp_tables $table, $schema, $catalog,

$type”);
219a220,238
> 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
>
> });
>

Listen to this podcast

Apr 24

35381: SQL::Translator::Parser::DBI::Sybase needs to strip the carriage return from stored procs/views sql

ASE, Databases, Perl, Sybase 1 Comment »

35381: SQL::Translator::Parser::DBI::Sybase needs to strip the carriage return from stored procs/views sql

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
SQL::Translator::0.09000

Sybase ASE will often store the SQL text for stored procedures and views
with the carriage return embedded (\r). We need to strip that out else
we will receive “^M” in the output.

Sybase_orig.pm Sybase_new.pm

108a109

>

109a111,112

> $stuff->{view}->{$_->[0]}->{text} =~ s/\r//g
> if (defined ($stuff->{view}->{$_->[0]}->{text}));

155a159,161
> $stuff->{procedures}->{$_->[0]}->{text} =~ s/\r//g
> if (defined ($stuff->{procedures}->{$_->[0]}->{text} ));
>

Listen to this podcast

Apr 24

SQL::Translator::Parser::DBI::Sybase bug (35380) causes script to fail when sp_helpindex doesn’t return a result set

ASE, DBI, Perl, Sybase 1 Comment »

35380: SQL::Translator::Parser::DBI::Sybase fails when sp_helpindex is empty

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
SQL::Translator::0.09000

Sybase ASE’s sp_helpindex will normally return a result set containing
the column “INDEX_NAME” which parse() requires. When no index exists,
sp_helpindex returns a error string similar to “Object does not have any
indexes.” but no result set.

The following workaround simply wraps the selectall_hash with an eval,
if the eval results in no error, then try to use the hash.

$ diff Sybase_orig.pm Sybase_new.pm
252,259c252,259
< my $h = $dbh->selectall_hashref("sp_helpindex
< $table_info->{TABLE_NAME}", ‘INDEX_NAME’);
< foreach (values %{$h}) {
< my $fields = $_->{‘INDEX_KEYS’};
< $fields =~ s/\s*//g;
< my $i = $table->add_index(
< name =>
< $_->{INDEX_NAME},
—
> my $h;
> eval { $h = $dbh->selectall_hashref("sp_helpindex $table_info->{TABLE_NAME}", ‘INDEX_NAME’) };
> unless ($@) {
> foreach (values %{$h}) {
> my $fields = $_->{‘INDEX_KEYS’};
> $fields =~ s/\s*//g;
> my $i = $table->add_index(
> name => $_->{INDEX_NAME},
262,263c262,263
< if ($_->{‘INDEX_DESCRIPTION’} =~ /unique/i) {
< $i->type(‘unique’);
—
> if ($_->{‘INDEX_DESCRIPTION’} =~ /unique/i) {
> $i->type(‘unique’);
265,276c265,277
< # we could make this a primary key if there
< # isn’t already one defined and if there
< # aren’t any nullable columns in thisindex.
<
< if (!defined($table->primary_key())) {
< $table->primary_key($fields)
< unless grep {
< $table->get_field($_)->is_nullable()
< } split(/,\s*/, $fields);
< }
< }
< }
—
> # we could make this a primary key if there
> # isn’t already one defined and if there
> # aren’t any nullable columns in thisindex.
>
> if (!defined($table->primary_key())) {
> $table->primary_key($fields)
> unless grep {
> $table->get_field($_)->is_nullable()
> } split(/,\s*/, $fields);
> }
> }
> }
> }

Listen to this podcast

Apr 24