My wife says this is so me… the fact that I’m not allowed to touch her macbook says something… *big grin*
Category: DBI
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”);
—
$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
>
> });
>
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);
> }
> }
> }
> }
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);
> }
> }
> }
> }
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:
- Perl POE/DBD::Sybase for the daemon/ASE db connections (using FreeTDS instead of OpenClient)
- SQLite for the local storage (way lower overhead than ASE or SQL Anywhere)
- 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.
In the post Adding TCP Keepalive Support to DBD::Sybase perl module, I describe a patch that I submitted to Michael Peppler for his Perl DBD::Sybase driver. While he implemented it in DBD::Sybase version 1.08, I didn’t mention that for Linux distributions, TCP Keepalive is disabled by default for processes. That means, any application that is to use TCP Keepalive, must be specially built for it or wrapper code must be used.
In order to modify the system settings for TCP Keepalive on Linux, you need to make a change to your /etc/sysctl.conf file and then run /sbin/sysctl -p:
# TCP Keepalive
net.ipv4.tcp_keepalive_probes = 20
net.ipv4.tcp_keepalive_time = 300
net.ipv4.tcp_keepalive_intvl = 60
net.ipv4.tcp_keepalive_probes = 20
net.ipv4.tcp_keepalive_time = 300
net.ipv4.tcp_keepalive_intvl = 60
