35383: DBD::Sybase calls the broken sp_tables stored procedure

35383: 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.

(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");
---
<div class="message-stanza-depth-1">
<div class="message-stanza-depth-2">> #     my $sth = $dbh->prepare("sp_tables $table, $schema, $catalog,</div>
</div>
$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
>
>               });
>
Share Button

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

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.

diff Sybase_orig.pm Sybase_new.pm
108a109
<div class="message-stanza-depth-1">>
</div>
109a111,112
<div class="message-stanza-depth-1">>       $stuff->{view}->{$_->[0]}->{text} =~ s/\r//g
>               if (defined ($stuff->{view}->{$_->[0]}->{text}));
</div>
155a159,161
>       $stuff->{procedures}->{$_->[0]}->{text} =~ s/\r//g
>               if (defined ($stuff->{procedures}->{$_->[0]}->{text} ));
>
Share Button

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

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);
>                           }
>                      }
>                  }
>               }
Share Button

Ubuntu 8.04 Hardy Heron has been released! :)

Ubuntu 8.04 LTS Desktop:

http://releases.ubuntu.com/8.04/ubuntu-8.04-desktop-i386.iso.torrent i386
http://releases.ubuntu.com/8.04/ubuntu-8.04-desktop-amd64.iso.torrent AMD64

Kubuntu 8.04 LTS Desktop:

http://releases.ubuntu.com/kubuntu/hardy/kubuntu-8.04-desktop-i386.iso.torrent Kubuntu x86
http://releases.ubuntu.com/kubuntu/hardy/kubuntu-8.04-desktop-amd64.iso.torrent Kubuntu AMD64

Ubuntu 8.04 LTS Server:

http://releases.ubuntu.com/8.04/ubuntu-8.04-server-i386.iso.torrent i386
http://releases.ubuntu.com/8.04/ubuntu-8.04-server-amd64.iso.torrent AMD64

Share Button

Is Sybase’s ASE SQL92 (ANSI SQL2) or SQL99 (ANSI SQL3) compliant?

ASE implements a subset of SQL 92 and isn’t 100% compliant with the SQL 92 standard (no DBMS on the planet is btw).

SQL99 compliance isn’t seriously being looked at by the major commercial DBMS vendors. Disregarding the fact the the SQL standards aren’t all they are cracked up to be, the vendors have too much invested in their own proprietary SQL variants (and other components) to be 100% compliant. If they were 100% compliant with the SQL92/99/whatever standard, then wholesale migrations from one vendor to another would take place.

I believe as time goes forward the opensource DBMSs (PostgreSQL, MySQL, etc) may become far more compliant with the standards than the commercial vendors as vendor lock in doesn’t mean as much to them.

Look into what the vendors (Oracle, MS, IBM,etc) are saying what constitutes as “compliance”. Ask each vendor what parts of the SQL99 standard they will be implementing and which parts they won’t be. If any vendor says that they are 100% compliant with the SQL92 or SQL99 ANSI standard, then that particular person is lying to you. Granted, that person may have been told their DBMS was 100% compliant and believes it. An honest vendor says that they comply with features X,Y and Z of the SQL 92 or SQL 99 standards.

Personally, I have found no significant movement by any of the commercial DBMS vendors to implement the SQL99 standard. So far it has just been lip service IMHO.

Share Button

Sometimes I get tired of some people b*tching about Linux

My favorite Linux ads – sorry for all the videos the last two days:

Share Button

Passover: Tomorrow at sundown we feast (1st seder of Passover)

Share Button

Just because I can: Discovery Channel: I Love the World

Discovery Channel: I Love the World

Share Button

FW: WordPress vs. Drupal: Not that it’s about winning

Selena over at Tending the Garden, posted a very good video of why you would choose WordPress or Drupal. It seems a little slanted towards Drupal but that may just be me 😉

Share Button

Replacing Sybase ASE’s Historical Server: Using OpenSource

I’m working on a Historical Server replacement.  I need to check with my employer as to whether I can open source it or not but the guts of it consist of:

  1. Perl POE/DBD::Sybase for the daemon/ASE db connections (using FreeTDS instead of OpenClient)
  2. SQLite for the local storage (way lower overhead than ASE or SQL Anywhere)
  3. Perl Catalyst / Template::Toolkit web front end with REST web services

All of this goes into a VMWare Appliance running Ubuntu 7.10 Server… a drop-in solution that can even be run on a laptop.  I’m hoping that my employer lets me release it as then I can zip it up and put it on sourceforge so everyone can benefit.

Share Button