http://www.perlmonks.org?node_id=218763

busunsl has asked for the wisdom of the Perl Monks concerning the following question:

Hi folks!

I have the following problem:

I want to write a simple script that compares the contents of two tables for my Sybase DBA Perl Cookbook.

The script iterates over all rows from one table, select the corresponding row from the other table and print the primary keys if the rows are different.
So nothing much.

But if I try to execute a statement with placeholders while another statement has results pending, the Sybase Client Library pukes:

ct_param() failed! at /tmp/xx.pl line 33. DBD::Sybase::st execute failed: OpenClient message: LAYER = (1) ORIGIN + = (1) SEVERITY = (1) NUMBER = (16) Message String: ct_param(): user api layer: external error: This routine cannot be called while results are pending for a comm +and that has been sent to the server. DBD::Sybase::st execute failed: OpenClient message: LAYER = (1) ORIGIN + = (1) SEVERITY = (1) NUMBER = (16) Message String: ct_param(): user api layer: external error: This routine cannot be called while results are pending for a comm +and that has been sent to the server.
The script:
#!/usr/bin/perl use strict; use warnings; use DBI; my $dbh1 = DBI->connect("dbi:Sybase:server=server1;database=database1" +, 'user1', 'passwd1'); my $dbh2 = DBI->connect("dbi:Sybase:server=server2;database=database2" +, 'user2', 'passwd2'); my $sth1 = $dbh1->prepare("select * from table1"); my $sth2 = $dbh2->prepare("select * from table2 where primkey = ?"); $sth1->execute; #---- iterate through table1, check in table2 while (my @row1 = $sth1->fetchrow_array) { $sth2->execute($row1[0]); # this is li +ne 33 my @row2 = $sth2->fetchrow_array; }

Versions:

Perl:        5.8.0 Linux
DBI:         1.30
DBD::Sybase: 0.95

Perl:        5.6.1 NT
DBI:         1.30
DBD::Sybase: 0.91
Anyone came across this before?

Is this a serious limitation in the Sybase Client Library?

The workaround would be to use a selectrow in each iteration, which would be dead slow.

Would a bind_param help?

Replies are listed 'Best First'.
Re: two connections to the same database with DBD::Sybase
by busunsl (Vicar) on Dec 10, 2002 at 13:01 UTC
    I found the error!

    It was on my side :-(

    Hrmph!

    I played around a bit, tried the same script with different servers and got the same error.
    So it wasn't due to messing up connections or statements.
    It was because one statement wasn't finished before the next started.

    A simple $sth2->finish after the fetchrow_array got rid of the error.

      Sybase has a known issue along these lines. From what I know it does not finish and can't be made to finish a fetchrow_array until it reads a null result. As I understand it this is a peculiarity of the Sybase library functions. This is mentioned at Re^4: portable mysql auto_increment in relation to Sybase choking with DBIx::Sequence

      cheers

      tachyon

      s&&rsenoyhcatreve&&&s&n.+t&"$'$`$\"$\&"&ee&&y&srve&&d&&print

      I see you found it... I know that the DBI docs specify that $sth->finish is in general not needed, but in the type of code that you used there is no way for DBD::Sybase to realize that there are no results after the single row fetched through the $sth2 handle (well, it could do a look-ahead fetch, but then it would have to store the fetched row somewhere until the script calls fetch() again. Feasible, but messy).

      I should probably improve the documentation a bit to be more explicit about the need to finish executions properly, either by fetching until no more rows are found, or by using finish().

      Michael

        I should probably improve the documentation a bit to be more explicit about the need to finish executions properly, either by fetching until no more rows are found, or by using finish().

        This might help, but not me ;-(

        One thing I always pray when talking about DBI is: Always finish your statements!

        And then I didn't do it myself :-(

        Perhaps there is room for enhancement somewhere else.
        If there had been the connection parameters (server, database) somewhere in the error messages, I would have found the error earlier.

        Michael, this is a feature request ;-)

Re: two connections to the same database with DBD::Sybase
by grinder (Bishop) on Dec 10, 2002 at 15:55 UTC

    Ok, you have found the solution to your problem, so everything is fine. Just for the record, though, I have run into this problem before, and it's not Sybase-specific, I can do it on Sybase or Oracle!

    I described my trials and tribulations at My script locks up using DBI.

    The Sybase libraries for Perl, written by our own mpeppler are very well done. While the single statement per connection sounds like a limitation, in general it isn't. It turns out that you can do a very neat thing with Sybase that you can't do in most other databases: you can queue up a series of statements in a session handler and process them serially. This usually obviates the need of creating two sessions on a connection. You just stick them all in the one session, and iterate over them.

    For instance (shamelessy stealing from the documentation), you can do legally do something like:

    my $ss = $dbh->prepare(" insert foo(one, two, three) values(1, 2, 3) select * from bar insert foo(one, two, three) values(10, 11, 12) "); $ss->execute;

    Ok, so you don't always need this, but when you do need it (speaking from experience), you need it bad.


    print@_{sort keys %_},$/if%_=split//,'= & *a?b:e\f/h^h!j+n,o@o;r$s-t%t#u'
      The same script I have written to connect the sybase database, but giving the error" Script:
      #!/usr/bin/perl use Time::HiRes qw(sleep); use DBI; # Connect to the db my $dbh = DBI->connect( "dbi:Sybase:server=Server name; port=5000; database=PP_DB; interfa +ces=/opt/sybase/interfaces", "sa", "password" ) or die "Usage ctp name, sa user password, 1st Sub_id, Subscriber Ran +ge";
      Error:
      DBI connect('server=ctp-1-zone157; port=5000; database=PP_DB; interfac +es=/opt/sybase/interfaces','sa',...) failed: OpenClient message: LAYE +R = (6) ORIGIN = (8) SEVERITY = (5) NUMBER = (1) Server ctp-1-zone157, database Message String: ct_connect(): directory service layer: internal direct +ory control layer error: There was an error encountered while binding + to the directory service. at ./connectDB.pl line 6 Usage ctp name, sa user password, 1st Sub_id, Subscriber Range at ./co +nnectDB.pl line 6.
Re: two connections to the same database with DBD::Sybase
by PodMaster (Abbot) on Dec 10, 2002 at 11:57 UTC
    From the error message, it looks like a Sybase Client Library limitation. I can't believe this is real, could it be some kind of licensing issue? (what a crappy client library)


    MJD says you can't just make shit up and expect the computer to know what you mean, retardo!
    ** The Third rule of perl club is a statement of fact: pod is sexy.

      I can't believe this is real

      I can't believe it too.

      could it be some kind of licensing issue?

      No, not a licensing issue.

      (what a crappy client library)

      The library isn't that bad. It's more a problem with the TDS protocol not being able to handle multiple statements in one connection. But it should be able to handle two distinct connections to a single server.

      So perhaps it's just a misfeature in DBD::Sybase.

      Actually Client Library is a very good API. It is very orthogonal, and once you start playing with the Open Server API as well you really "get" it!

      The TDS protocol is limited to one active statement per connection (unless you open client side cursors, which you can actually do with Sybase::CTlib...), but that's usually not a problem.

      Michael