DBD::Sybase / DBD::ODBC + FreeTDS woes - placeholders and implicit datatype conversions

[Richard Gration]

Hi All,

I'm trying to issue SQL queries, which contain placeholders, from a
Gentoo Linux desktop to a Redhat Linux 7.1 server running Sybase ASE
11.0.3, using freetds (and unixODBC) and DBI. It all works well with a
variety of CLI clients: sqsh, tsql (freetds) and isql (unixODBC). The
problem comes when using DBI and placeholders.

freetds does not support placeholders at all, in any TDS version it
implements, so that kiboshes DBD::Sybase. They suggest using their ODBC
lib and DBD::ODBC. Everything is OK until trying to use a placeholder for
a numeric argument. Then the dreaded

========
Implicit conversion from datatype 'VARCHAR' to 'NUMERIC' is not allowed.
Use the CONVERT function to run this query.
========

error message appears. I've tried all sorts of things to get round this
but nothing works for me. I know that there is a little(!) magic inside
perl for allowing scalar values to be both strings and numbers as needed
and I wonder if this is the issue here.

Please, please, please does anybody know how I can solve this problem? I
have googled and peered into source code and nothing I've tried works.

Thanks for reading
Rich

 

[Anthony Mandic]

I'm trying to issue SQL queries, which contain placeholders, from a
Gentoo Linux desktop to a Redhat Linux 7.1 server running Sybase ASE
11.0.3, using freetds (and unixODBC) and DBI. It all works well with a
variety of CLI clients: sqsh, tsql (freetds) and isql (unixODBC). The
problem comes when using DBI and placeholders.

freetds does not support placeholders at all, in any TDS version it
implements, so that kiboshes DBD::Sybase. They suggest using their ODBC
lib and DBD::ODBC. Everything is OK until trying to use a placeholder for
a numeric argument. Then the dreaded

========
Implicit conversion from datatype 'VARCHAR' to 'NUMERIC' is not allowed.
Use the CONVERT function to run this query.
========

error message appears. I've tried all sorts of things to get round this
but nothing works for me. I know that there is a little(!) magic inside
perl for allowing scalar values to be both strings and numbers as needed
and I wonder if this is the issue here.

Please, please, please does anybody know how I can solve this problem? I
have googled and peered into source code and nothing I've tried works.

What's wrong with using Open Client? Does it work on Gentoo?
Posting the code where you get the error might help too.

-am © 2005

 

[Richard Gration]

What's wrong with using Open Client? Does it work on Gentoo?

Sybase ASE 11.0.3 is available only as an rpm for Redhat. It is also quite
picky about its libraries and often one needs to install compat library
rpms to get it working.

Posting the code where you get the error might help too.

The script below will try to make a query against the pubs2 database
which comes with Sybase and is used in SQL examples in the documentation.
The qty column of the salesdetail table is a smallint. No such problem
with placeholders happens when an anlogous query is executed against a
varchar column.

rich@richg ~/perl $ cat syb.pl
#!/usr/bin/perl

BEGIN {
# FreeTDS libs are in /usr/lib/
$ENV{SYBASE} = '/usr';
# Sybase chokes if LANG=en_GB.utf8
$ENV{LANG} = 'en_GB';
}

use DBI;
use Data:ump;

use strict;
use warnings;

my $dbh = DBI->connect('dbi:ODBCubs2','sa','xxxxxxxx') or die "Oh NO!!:$!\n";

# Works
my $sd1 = $dbh->selectall_arrayref('select * from salesdetail where qty = 75',{Columns=>{}});
print "\n",Data:ump::dump($sd1),"\n";

# Doesn't work
my $sd2 = $dbh->selectall_arrayref('select * from salesdetail where qty = ?',{Columns=>{}},75);
print "\n",Data:ump::dump($sd2),"\n";

$dbh->disconnect if (defined $dbh);
rich@richg ~/perl $ ./syb.pl

[
{
discount => 40,
ord_num => 234_518,
qty => 75,
stor_id => 7896,
title_id => "TC3218",
},
{
discount => 40,
ord_num => 234_518,
qty => 75,
stor_id => 7896,
title_id => "TC7777",
},
]
DBD::ODBC::db selectall_arrayref failed: [unixODBC][FreeTDS][SQL
Server]Implicit conversion from datatype 'VARCHAR' to 'SMALLINT' is not
allowed. Use the CONVERT function to run this query. (SQL-42000)(DBD:
st_execute/SQLExecute err=-1) at ./syb.pl line 23.

undef
rich@richg ~/perl $


Thanks for the reply
Rich

 

[Anthony Mandic]

Sybase ASE 11.0.3 is available only as an rpm for Redhat. It is also quite
picky about its libraries and often one needs to install compat library
rpms to get it working.

That may well be, but its also all of ASE. What about just what's
needed to get Open Client working? Have you tried copying it over
to see?

Posting the code where you get the error might help too.

The script below will try to make a query against the pubs2 database
which comes with Sybase and is used in SQL examples in the documentation.
The qty column of the salesdetail table is a smallint. No such problem
with placeholders happens when an anlogous query is executed against a
varchar column. ....
# Works
my $sd1 = $dbh->selectall_arrayref('select * from salesdetail where qty = 75',{Columns=>{}});
print "\n",Data:ump::dump($sd1),"\n";

# Doesn't work
my $sd2 = $dbh->selectall_arrayref('select * from salesdetail where qty = ?',{Columns=>{}},75); ....
DBD::ODBC::db selectall_arrayref failed: [unixODBC][FreeTDS][SQL
Server]Implicit conversion from datatype 'VARCHAR' to 'SMALLINT' is not
allowed. Use the CONVERT function to run this query. (SQL-42000)(DBD:
st_execute/SQLExecute err=-1) at ./syb.pl line 23.

OK. I'm not sure what it thinks is going on. As far as I know,
ASE's parser should know to turn the string "75" into a numeric
value. You could try a convert statement to see what it does.
What about assigning the "75" to a variable in Perl and passing
that in the string?

I'm sure Michael Peppler could offer some advice too.

-am © 2005

 

[Anthony Mandic]

I haven't, for a couple of reasons: One, I didn't think it stood a cat in
hell's chance of working and two,

There's only one way to find out.

it falls into the "hack" category and
makes migration of code from the dev environment to the live environment
difficult and also upgrades etc.

Well, have you looked at the commercial alternatives then? Like
Open link for example http://www.openlinksw.com/

I have tried that, with identical results. I've also tried convert, and
that works, but I have a 25,000 line application with 100s of queries
already written, which work with ct-lib, so converting them all is out of
the question.

Well, my last suggest would be to fashion the string outside of
the function call. That would mean massive conversion of your
code as well, so its probably not on the cards either.

I would rather re-install OSes than do that!

You've never installed Windows NT, have you?

I sure ... in fact I was hoping he would!

You could always try a private email. He might be on holidays
though.

-am © 2005

 

[Richard Gration]

[ Apologies for multi-post, something happened to the post in this ng and
I really wanted to thank you both]

Thank you very much, both Anthony and Michael, for the useful advice,
comments and encouragement. I have it all working now I'm so happy!
Thank you, thank you, thank you.

I installed Sybase 12.5.3 on Gentoo very easily. I had a bit of trouble
installing apache, but that was all down to Gentoo's package management
system. I loaded some backups from the 11.0.3 server and they were
converted fine by Sybase. Perl connects fine via DBD::Sybae. sqsh insisted
on compiling against freetds, but again that's a Gentoo pkg mgmt issue.
Not a problem!

I hope this info is of some use to someone else.

Rich