Win32 - DBD-ODBC

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

Hello,
I am trying to get data from a Sybase DB using DBI and DBD::ODBC.
I have tested the connection using fixed values in the code, but when I try to pass a variable or placeholder, as in the code below, I get the following error message:

DBD::ODBC::st execute failed: [MERANT][ODBC Sybase ASE driver][SQL Ser
+ver]Implicit conversion from datatype 'VARCHAR' to 'NUMERIC' is not a
+llowed.  Use the CONVERT function to run this query.
[download]

And I can't seem to locate anything on the CONVERT function in DBI.

Here's the query I am trying to run:

use DBI;

   my $dbh = DBI->connect('dbi:ODBC:Data Source', 'username', 'passwor
+d', {RaiseError => 1,});
        my $sth = $dbh->prepare('SELECT customercode FROM customerorde
+rs WHERE ordercode = ?')
                or die "Couldn't prepare statement: " . $dbh->errstr;

    print "Enter ordercode> ";
    while ($ordercode = <>) {               # Read input from the user
      my @data;
      chomp $ordercode;
          $sth->execute($ordercode)             # Execute the query
            or die "Couldn't execute statement: " . $sth->errstr;

          # Read the matching records and print them out          
          while (@data = $sth->fetchrow_array()) {
            my $customercode = $data[1];
            my $ordercode = $data[2];
            print "\t $customercode $ordercode\n";
[download]

Now I've searched through PerlMonks and on the Web and I can't find any solution to this problem, other than suggestions that I use a different DBD driver, such as DBD::Sybase. I have downloaded and installed that driver but I am having trouble getting it to work.
I also tried bind_param, as follows:
$sth->bind_param(1, $ordercode, SQL_INTEGER); with no success.
Has anyone come up with a way to pass variables in a query to a Sybase DB using DBD::ODBC on Win32?

Thank you,
David MacDougall
Charleston, SC

Comment on Win32 - DBD-ODBC - Sybase Select or Download Code

Replies are listed 'Best First'.
Re: Win32 - DBD-ODBC - Sybase by VSarkiss (Monsignor) on Oct 20, 2005 at 23:18 UTC
That error message isn't from DBI, it's from SQL Server (Sybase) itself. What data type is the `ordercode` column in the database? If it's an integer, the `bind_param` should do the trick. What I suspect is that it's a `varchar` and DBI is guessing that it's a number (based on the parameter value that's being passed). If so, you can use `bind_param(1, $ordercode, SQL_VARCHAR)` to make sure the right type goes thru. (Your note implies `bind_param` "didn't work", but you don't mention what happened.) If all else fails, you can call `CONVERT` (the Transact-SQL function, not DBI) in your SQL, something like this: `$dbh->prepare(' SELECT customercode FROM customerorders WHERE ordercode = CONVERT(varchar, ?)');` [download] or other type. Again, you don't mention what type the column is, so I'm not sure if that code sample is 180 degrees out of phase. :-) Do not rebuke them with harsh words ... but rather lead them gently - with URLs - so that they may learn wisdom.	[reply] [d/l] [select]
Re^2: Win32 - DBD-ODBC - Sybase by dmacdou (Novice) on Oct 21, 2005 at 17:03 UTC
Hi Vsarkiss, Thanks for the response and suggestions. The data type in the database for `ordercode` is `numeric`. I have tried passing `bind_param(1, $ordercode, SQL_INTEGER)` and `bind_param(1, $ordercode, NUMERIC)` and `bind_param(1, $ordercode, SQL_NUMERIC)`. None of which worked. The data being passed is a number.	[reply] [d/l] [select]
Re^3: Win32 - DBD-ODBC - Sybase by VSarkiss (Monsignor) on Oct 21, 2005 at 17:54 UTC
Hm, that's very odd. Then either `$ordercode` contains non-numeric characters, or for some reason DBI is trying to pass it as a `VARCHAR`. Try to print the value of `$ordercode` to the console or a log file to make sure it contains what you think it does. Also try something like this to see what DBI considers the value to be: `print "DBI thinks ", (DBI::looks_like_number($ordercode) ? "is" : "is not"), " a number\n";` [download] You can also turn on tracing in DBI: `DBI->trace(3); # probably overkill` [download] See the Tracing section in the DBI docs. Do not rebuke them with harsh words ... but rather lead them gently - with URLs - so that they may learn wisdom.	[reply] [d/l] [select]
Re^4: Win32 - DBD-ODBC - Sybase by dmacdou (Novice) on Oct 21, 2005 at 19:24 UTC
Re: Win32 - DBD-ODBC - Sybase by mpeppler (Vicar) on Oct 21, 2005 at 19:11 UTC
If you're not using strict then it may be that SQL_INTEGER isn't defined, but perl isn't telling you that. To get the SQL_xxx symbols you usually need to import them explicitly, like so: `use DBI qw(:sql_types);` [download] Michael	[reply] [d/l]
Re^2: Win32 - DBD-ODBC - Sybase by dmacdou (Novice) on Oct 21, 2005 at 20:30 UTC
Hi Michael, Thank you very much...importing the SQL_xxx symbols has turned the corner for me. Thanks again, David	[reply]