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
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(@_);
}