Beefy Boxes and Bandwidth Generously Provided by pair Networks
No such thing as a small change
 
PerlMonks  

DBD::Sybase and ?-style Placeholders

by abstracts (Hermit)
on May 20, 2002 at 16:15 UTC ( [id://167858]=perlquestion: print w/replies, xml ) Need Help??

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

Hello everybody

I have been scratching my head for 3 days now to get access to a Sybase database. I have worked before with PostgreSQL and MySQL and never had such problem. The problem is that whenever I execute an SQL statement with '?' placeholders, it brings back no results. There do not seem to be any errors (no exceptions raised, no undefs returned). All I get is a warning that ct_send(CS_DESCRIBE_INPUT) returned 0 (I have no idea what that means).

The specs of the system:

  • perl 5.6.1
  • DBD::Sybase 0.94
  • DBI 1.21
  • FreeTDS 0.53
  • Debian GNU/Linux
The DBD::Sybase states that
  
syb_dynamic_supported (bool)
           This is a read-only attribute that returns TRUE if the
           dataserver you are connected to supports ?-style
           placeholders. Typically placeholders are not supported
           when using DBD::Sybase to connect to a MS-SQL server.
Now I get "1" for that which should mean that I can use "?" placeholders, but I can't. Also, the docs state that if the TDS version is 5.0, I should be able to use "?".

Does anybody know what I'm doing wrong? Is it a FreeTDS problem or a DBD problem, or a Sybase problem or my poor understanding's problem? Please help and thank you very much in advance.


Here is the output of the test program:

$ ./placeholder.pl 
OpenClient version: **
Dynamic Supported:  *1*
Performing sql (no placeholders):
           code: 000
          value: United States
------------------------------------------------------------------------------
1 record(s) found
Performing sql (with placeholders):
elem 0 coltype 47 size 3
ct_send(CS_DESCRIBE_INPUT) returned 0 at 
/usr/local/lib/perl/5.6.1/DBD/Sybase.pm line 105.
0 record(s) found

And here is the code of the test:
#!/usr/bin/perl -w use strict; use DBI; use Config::IniFiles; use vars qw/$servers/; my $server = 'servername'; eval{ $servers = Config::IniFiles->new( -file => 'config.cnf'); my $dbh = get_dbh($server); print "OpenClient version: *$dbh->{syb_oc_version}*\n"; # prints nothing print "Dynamic Supported: *$dbh->{syb_dynamic_supported}*\n"; # prints 1 print "Performing sql (no placeholders):\n"; my @vals = do_sql_direct($server, qq#select * from ct_countries where code = '000'#); print_vals(@vals); print "Performing sql (with placeholders):\n"; my @vals2 = do_sql($server, qq#select * from ct_countries where code = ?#, '000'); print_vals(@vals2); }; die "Error: $@\n" if $@; ################################################################ # perform sql without placeholders # sub do_sql_direct{ my ($server, $sql) = @_; my $dbh = get_dbh($server); my $sth; $sth = $dbh->prepare($sql); $sth->execute(); my @rows; eval{ while(my $r = $sth->fetchrow_hashref()){ push @rows, $r; } }; warn "***$@***\n" if $@; return @rows; } ################################################################ # perform sql using ?-style placeholders sub do_sql{ my ($server, $sql, @params) = @_; my $dbh = get_dbh($server); my $sth; $sth = $dbh->prepare($sql) or die "Cannot prepare $sql: \n"; my $i = 1; for(@params){ $sth->bind_param($i, $_) or die "Cannot bind param $i: $_\n"; $i++; } $sth->execute() or die "Cannot execute\n"; # also tried $sth->execute(@params) without bind_params # didn't work either. my @rows; eval{ while(my $r = $sth->fetchrow_hashref()){ push @rows, $r; } }; warn "***$@***\n" if $@; return @rows; }
################################################################ # you don't need to read below this point. # # the rest of the code # # get the database handle associated with database name # sub get_dbh{ my $dbname = shift; $servers->SectionExists($dbname) or die "Unknown database: $dbname +\n"; my $dbh = $servers->val($dbname, 'dbh'); return $dbh if $dbh; my $driver = $servers->val($dbname, 'driver') or die "Database $dbname requires a driver\n"; my $server = $servers->val($dbname, 'server') or die "Database $dbname requires a server\n"; my $user = $servers->val($dbname, 'user') or die "Database $dbname requires a user\n"; my $pass = $servers->val($dbname, 'pass') or die "Database $dbname requires a pass\n"; my $database = $servers->val($dbname, 'database'); my $dsn = "dbi:$driver:server=$server"; $dsn .= ";database=$database" if $database; $dbh = DBI->connect( $dsn, $user, $pass, { RaiseError => 1 } ); $servers->setval($dbname, 'dbh', $dbh); return $dbh; } sub print_vals{ for my $row (@_){ for(sort keys %$row){ printf "%15s: %s\n", $_, $row->{$_}; } print "-" x 78 . "\n"; } printf "%d record(s) found\n", scalar(@_); }

Replies are listed 'Best First'.
Re: DBD::Sybase and ?-style Placeholders
by mpeppler (Vicar) on May 20, 2002 at 16:38 UTC
    The problem here is that FreeTDS doesn't properly support querying the server capabilities.

    Placeholder handling is done by the server, not by DBD::Sybase or by the client libraries (this uses the ct_dynamic() and friends API). DBD::Sybase queries the server with a ct_capability() call to see if placeholders are supported. This will return false if the connection is at TDS 4.x, but I believe that FreeTDS stubbs out the ct_capability call, just returning succeed.

    So syb_dynamic_supported doesn't get set correctly with FreeTDS, unfortunately.

    BTW - placeholders work to a certain extent when using FreeTDS with a Sybase server (i.e. TDS version 5.x), but TDS 7.x (used by MS-SQL) hasn't been correctly implemented yet.

    Michael

Re: DBD::Sybase and ?-style Placeholders
by jsegal (Friar) on May 20, 2002 at 16:46 UTC
    I have had no problem with placeholders using DBI and DBD::Sybase, but I use the Sybase client libraries (libct), not FreeTDS. (I am currently using DBD::Sybase v. 0.91 and DBI version 1.16 with perl5.6.1 on both Compaq OSF and RedHat linux, but this has worked with older versions as well, and I suspect it still works with the newer versions of DBI and DBD).

    In short, I would suspect FreeTDS...

    --JAS
Re: DBD::Sybase and ?-style Placeholders
by scain (Curate) on May 20, 2002 at 16:23 UTC
    My recollection is that DBD::Sybase doesn't support placeholders. I know that with DBD::Sybase, freeTDS, and MSSQL server it doesn't work. It is likely then that DBD::Sybase is the problem.

    Scott

Re: DBD::Sybase and ?-style Placeholders
by thor (Priest) on May 21, 2002 at 01:54 UTC
    Try replacing this:
    my @vals2 = do_sql($server, qq#select * from ct_countries where code = ?#, '000');
    With this:
    my @vals2 = do_sql($server, qq#select * from ct_countries where code=?#, '000');
    I seem to remember having this same problem and I stumbled on this working. Don't ask me why it doesn't work the "normal" way, but I use it all the time.

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://167858]
Approved by VSarkiss
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others examining the Monastery: (7)
As of 2024-11-06 08:22 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    chatterbot is...






    Results (32 votes). Check out past polls.