Re: Win32 - DBD-ODBC

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.

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

Replies are listed 'Best First'.
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
Hi Vsarkiss, Thanks again. I know the $ordercode is a number because I am typing it in at the prompt. Here is what DBI trace reveals: C:\testing_area\adpay>dbitest.pl DBI 1.48-ithread default trace level set to 0x0/3 (pid 2400) -> DBI->connect(dbi:ODBC:Advisor Reports, advisor, **, HASH(0x2252f0)) -> DBI->install_driver(ODBC) for MSWin32 perl=5.008007 pid=2400 ruid=0 euid=0 install_driver: DBD::ODBC version 1.13 loaded from C:/Perl/site/lib/DBD/ODBC.pm New DBI::dr (for DBD::ODBC::dr, parent=, id=) dbih_setup_handle(DBI::dr=HASH(0x192a24c)=>DBI::dr=HASH(0x19abd4c), DBD::ODBC::dr, 0, Null!) dbih_make_com(Null!, 0, DBD::ODBC::dr, 92, 0) thr#2243f4 <- install_driver= DBI::dr=HASH(0x192a24c) -> connect for DBD::ODBC::dr (DBI::dr=HASH(0x192a24c)~0x19abd4c 'Advisor Reports' 'advisor' ** HASH(0x19ac04c)) thr#2243f4 New DBI::db (for DBD::ODBC::db, parent=DBI::dr=HASH(0x19abd4c), id=) dbih_setup_handle(DBI::db=HASH(0x19abd10)=>DBI::db=HASH(0x19ac040), DBD::ODBC::db, 1929fa0, Null!) dbih_make_com(DBI::dr=HASH(0x19abd4c), 19ad40c, DBD::ODBC::db, 224, 0) thr#2243f4 SQLConnect 'Advisor Reports', 'advisor' SQLGetFunctions - SQL_MoreResults supported: 1 <- connect= DBI::db=HASH(0x19abd10) at DBI.pm line 598 -> STORE for DBD::ODBC::db (DBI::db=HASH(0x19ac040)~INNER 'RaiseError' 1) thr#2243f4 DBD::ODBC unsupported attribute passed (RaiseError) STORE DBI::db=HASH(0x19ac040) 'RaiseError' => 1 <- STORE= 1 at DBI.pm line 645 -> STORE for DBD::ODBC::db (DBI::db=HASH(0x19ac040)~INNER 'PrintError' 1) thr#2243f4 DBD::ODBC unsupported attribute passed (PrintError) STORE DBI::db=HASH(0x19ac040) 'PrintError' => 1 <- STORE= 1 at DBI.pm line 645 -> STORE for DBD::ODBC::db (DBI::db=HASH(0x19ac040)~INNER 'AutoCommit' 1) thr#2243f4 <- STORE= 1 at DBI.pm line 645 -> STORE for DBD::ODBC::db (DBI::db=HASH(0x19ac040)~INNER 'Username' 'advisor') thr#2243f4 DBD::ODBC unsupported attribute passed (Username) STORE DBI::db=HASH(0x19ac040) 'Username' => 'advisor' <- STORE= 1 at DBI.pm line 648 via C:\testing_area\adpay\dbitest.pl line 9 <> FETCH= 'advisor' ('Username' from cache) at DBI.pm line 648 <- connect= DBI::db=HASH(0x19abd10) -> STORE for DBD::ODBC::db (DBI::db=HASH(0x19ac040)~INNER 'dbi_connect_closure' CODE(0x19abc5c)) thr#2243f4 DBD::ODBC unsupported attribute passed (dbi_connect_closure) STORE DBI::db=HASH(0x19ac040) 'dbi_connect_closure' => CODE(0x19abc5c) <- STORE= 1 at DBI.pm line 668 via C:\testing_area\adpay\dbitest.pl line 9 -> prepare for DBD::ODBC::db (DBI::db=HASH(0x19abd10)~0x19ac040 'SELECT customercode FROM customerorders WHERE ordercode = ?') thr#2243f4 New DBI::st (for DBD::ODBC::st, parent=DBI::db=HASH(0x19ac040), id=) dbih_setup_handle(DBI::st=HASH(0x19ac100)=>DBI::st=HASH(0x2253b0), DBD::ODBC::st, 19ac10c, Null!) dbih_make_com(DBI::db=HASH(0x19ac040), 19adec4, DBD::ODBC::st, 212, 0) thr#2243f4 dbd_preparse scanned 1 distinct placeholders SQLPrepare returned 0 dbd_st_prepare'd sql f28450464, ExecDirect=0 SELECT customercode FROM customerorders WHERE ordercode = ? <- prepare= DBI::st=HASH(0x19ac100) at dbitest.pl line 10 Enter ordercode>1555336 -> bind_param for DBD::ODBC::st (DBI::st=HASH(0x19ac100)~0x2253b0 1 '1555336' 'SQL_VARCHAR') thr#2243f4 DBI::st=HASH(0x2253b0)->bind_param(...): attribute parameter 'SQL_VARCHAR' is not a hash ref at C:\testing_area\adpay\dbitest.pl line 17, <> line 1. <> DESTROY(DBI::st=HASH(0x19ac100)) ignored for outer handle (inner DBI::st=HASH(0x2253b0) has ref cnt 1) -> DESTROY for DBD::ODBC::st (DBI::st=HASH(0x2253b0)~INNER) thr#2243f4 <- DESTROY= undef <> DESTROY(DBI::db=HASH(0x19abd10)) ignored for outer handle (inner DBI::db=HASH(0x19ac040) has ref cnt 1) -> DESTROY for DBD::ODBC::db (DBI::db=HASH(0x19ac040)~INNER) thr#2243f4 <- DESTROY= undef -- DBI::END -> disconnect_all for DBD::ODBC::dr (DBI::dr=HASH(0x192a24c)~0x19abd4c) thr#2243f4 <- disconnect_all= '' at DBI.pm line 677 via C:\testing_area\adpay\dbitest.pl line 0 ! -> DESTROY in DBD::_::common for DBD::ODBC::dr (DBI::dr=HASH(0x19abd4c)~INNER) thr#2243f4 ! <- DESTROY= undef during global destruction ! <> DESTROY for DBI::dr=HASH(0x192a24c) ignored (inner handle gone)	[reply]