Thanks for the suggestions. Because of issues with date formats and also the fact that SQLDescribeParam is based on ordinal position and not name, the default binding behavior will not always work for us. Also, we are trying to avoid having everything default to varchar. Is it possible the patch you are suggesting will be implemented in a future release of the DBD::ODBC module?
Lee Anne -----Original Message----- From: Martin J. Evans [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 28, 2005 5:18 AM To: Lee Anne Lester Cc: dbi-users@perl.org Subject: RE: Numeric value out of range error On 27-Sep-2005 Lee Anne Lester wrote: > I am getting a "Numeric value out of range error" when specifically > binding a placeholder for a decimal column as a decimal type. I'm > using DBD::ODBC connecting to Microsoft SQL Server from a Solaris machine. > The value being bound is 10.00. The settings in database are precision > 5 and a scale 2. I have found that I don't get the error as long as > there are no numbers to the left of the decimal. Example: A value of > .5 works. > > Can anyone advise how to make this work? Is there a way to define the > precision and scale when calling bind_param? > > Thanks in advance for any help. > > Lee Anne Lester > > SQL> create table jaeb (FieldDecimal numeric(5,2)) insert into jaeb SQL> values ('10.00') insert into jaeb values (10.00) select * from jaeb +-------------+ | FieldDecimal| +-------------+ | 10.00 | | 10.00 | +-------------+ SQLRowCount returns 2 2 rows fetched So you can insert without binding OK but as you say: #!/usr/local/bin/perl -w use DBI; my $dbh = DBI->connect('dbi:ODBC:test', 'Martin_Evans', 'easysoft'); my $sql = q/insert into jaeb values (?)/; my $sth = $dbh->prepare($sql); $sth->bind_param(1, 10.00, {TYPE=>3}); $sth->execute(); fails. Changing the bind_param to remove the forcing the type to SQL_NUMERIC $sth->bind_param(1, 10.00); appears to insert correctly but a string "10" is still passed in (I'd presume this is because 10.00 is converted to "10"). Passing 10.98 also works correctly. In these cases the parameter is bound as SQL_C_CHAR, SQL_VARCHAR. The actual problem appears to be: SQLBindParameter(0x8258ea0,1,1(Input::),1,3,5,5,0x82580f0,5,0x82580d0) i.e. bind parameter 1 as input, ValueType=SQL_C_CHAR, ParameterType=SQL_NUMERIC, ColumnSize=5, DecimalDigits=5. This instructs MS SQL Server to convert your SQL_C_CHAR string into a numeric 5,5 before inserting - this will not fit. Interestingly, your trace shows "cbColDef=2, scale=2," and I cannot reproduce this. The problem is that you are saying this parameter must be a SQL_NUMERIC but there is no way to specify a scale and DBD::ODBC does not use SQLDescribeParam when you force a bind type. This could be changed to work but it is not a 5 minute job to do properly. The patch (to _dbd_rebind_ph) below makes it work but it is a bit of a hack: case SQL_TIME: case SQL_TYPE_TIME: /* fSqlType = SQL_VARCHAR;*/ break; + case SQL_NUMERIC: + case SQL_DECIMAL: + + if (phs->sv_buf && *phs->sv_buf) { + char *cp; + cp = strchr(phs->sv_buf, '.'); + if (cp) { + ibScale = 0; + cp++; + while (*cp != '\0' && isdigit(*cp)) { + cp++; + ibScale++; + } + } + } + break; case SQL_TIMESTAMP: case SQL_TYPE_TIMESTAMP: Why not just pass your parameters in as strings and leave the parameter type off the bind call. Martin -- Martin J. Evans Easysoft Ltd, UK Development > SAMPLE CODE > ============= >#!/usr/local/bin/perl -Tw > > use strict; > use DBI; > > my $dsn = "dbi:ODBC:dsn_IRB"; > > our $dbh = DBI->connect($dsn, '', '', {AutoCommit=>0, RaiseError=>1, > FetchHashKeyName=>'NAME_uc'}); > > unlink('dbitrace.log') if(-e 'dbitrace.log'); > > DBI->trace(9, 'dbitrace.log'); > > my $sth = $dbh->prepare(q{INSERT INTO tblDecimalTest (FieldDecimal) > VALUES (?)}); > > $sth->bind_param(1, 10.00, {TYPE=>3} ); > > $sth->execute(); > > $sth->finish; > > $dbh->commit; > > $dbh->disconnect; > ~ > > > > VERSIONS > ========= > > Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) > May 3 2005 23:18:38 > Copyright (c) 1988-2003 Microsoft Corporation > Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4) > > unixODBC version: 2.2.7 using EasySoft ODBC bridge > > Perl Version = 5.008007 > DBI Version = 1.48 > DBD::ODBC Version = 1.13 > > > OUTPUT FROM TRACE - LEVEL 9 > ============================ > DBI 1.48-nothread default trace level set to 0x0/9 (pid 5169) > >> prepare DISPATCH (DBI::db=HASH(0x25300c) rc1/1 @2 g0 ima2201 > pid#5169) at ./insert.pl line 14 > -> prepare for DBD::ODBC::db (DBI::db=HASH(0x25300c)~0x260a18 > 'INSERT INTO tblDecimalTest (FieldDecimal) VALUES (?)') > New DBI::st (for DBD::ODBC::st, parent=DBI::db=HASH(0x260a18), id=) > dbih_setup_handle(DBI::st=HASH(0x260b20)=>DBI::st=HASH(0x1b9d48), > DBD::ODBC::st, 260b2c, Null!) > dbih_make_com(DBI::db=HASH(0x260a18), 136f30, DBD::ODBC::st, 212, > 0) thr#0 > dbih_setup_attrib(DBI::st=HASH(0x1b9d48), Err, > DBI::db=HASH(0x260a18)) SCALAR(0x1face0) (already defined) > dbih_setup_attrib(DBI::st=HASH(0x1b9d48), State, > DBI::db=HASH(0x260a18)) SCALAR(0x1fad40) (already defined) > dbih_setup_attrib(DBI::st=HASH(0x1b9d48), Errstr, > DBI::db=HASH(0x260a18)) SCALAR(0x1fad10) (already defined) > dbih_setup_attrib(DBI::st=HASH(0x1b9d48), TraceLevel, > DBI::db=HASH(0x260a18)) 0 (already defined) > dbih_setup_attrib(DBI::st=HASH(0x1b9d48), FetchHashKeyName, > DBI::db=HASH(0x260a18)) 'NAME_uc' (already defined) > dbih_setup_attrib(DBI::st=HASH(0x1b9d48), HandleSetErr, > DBI::db=HASH(0x260a18)) undef (not defined) > dbih_setup_attrib(DBI::st=HASH(0x1b9d48), HandleError, > DBI::db=HASH(0x260a18)) undef (not defined) > initializing sth query timeout to 0 > ignore named placeholders = 0 > dbd_preparse scanned 1 distinct placeholders > SQLPrepare returned 0 > > dbd_st_prepare'd sql f2787256, ExecDirect=0 > INSERT INTO tblDecimalTest (FieldDecimal) VALUES (?) > <- prepare= DBI::st=HASH(0x260b20) at ./insert.pl line 14 > >> bind_param DISPATCH (DBI::st=HASH(0x260b20) rc1/1 @4 g0 ima1 > pid#5169) at ./insert.pl line 16 > -> bind_param for DBD::ODBC::st (DBI::st=HASH(0x260b20)~0x1b9d48 1 > 10 HASH(0x1209b8)) bind 1 <== '10' (attribs: HASH(0x1209b8)), type 3 > <- bind_param= 1 at ./insert.pl line 16 > >> execute DISPATCH (DBI::st=HASH(0x260b20) rc1/1 @1 g0 ima1041 > pid#5169) at ./insert.pl line 18 > -> execute for DBD::ODBC::st (DBI::st=HASH(0x260b20)~0x1b9d48) > dbd_st_execute (outparams = 0)... > bind 1 <== 10 (size 2/3/0, ptype 6, otype 1, sqltype 3) bind 1 <== > '10' (len 2/2, null 0) > bind 1: CTy=1, STy=DECIMAL, CD=2, Sc=2, VM=2. > SQLBindParameter: idx = 1: fParamType=1, name=1, fCtype=1, > SQL_Type = 3, cbColDef=2, scale=2, rgbValue = 13ddc0, cbValueMax=2, cbValue = 2 > Param value = 10 > rebind check char Param 1 (10) > dbd_st_execute (for hstmt 2787256 before)... > dbd_st_execute (for hstmt 2787256 after, rc = -1)... > dbd_error: err_rc=-1 rc=0 s/d/e: 2787256/2497728/2496296 > dbd_error: SQL-22003 (native 0): [unixODBC][Microsoft][ODBC SQL Server > Driver]Numeric value out of range (SQL-22003) > dbd_error: err_rc=-1 rc=0 s/d/e: 0/2497728/2496296 > dbd_error: err_rc=-1 rc=0 s/d/e: 0/0/2496296 st_execute/SQLExecute > error -1 recorded: [unixODBC][Microsoft][ODBC SQL Server > Driver]Numeric value out of range (SQL-22003)(DBD: > st_execute/SQLExecute err=-1) > !! ERROR: -1 '[unixODBC][Microsoft][ODBC SQL Server Driver]Numeric > value out of range (SQL-22003)(DBD: st_execute/SQLExecute err=-1)' > (err#0) > <- execute= undef at ./insert.pl line 18 > >> DESTROY DISPATCH (DBI::st=HASH(0x260b20) rc1/1 @1 g0 ima4 > pid#5169) > <> DESTROY(DBI::st=HASH(0x260b20)) ignored for outer handle (inner > DBI::st=HASH(0x1b9d48) has ref cnt 1) > >> DESTROY DISPATCH (DBI::st=HASH(0x1b9d48) rc1/1 @1 g0 ima4 > pid#5169) > -> DESTROY for DBD::ODBC::st (DBI::st=HASH(0x1b9d48)~INNER) > SQLFreeStmt called, returned 0. > ERROR: -1 '[unixODBC][Microsoft][ODBC SQL Server Driver]Numeric > value out of range (SQL-22003)(DBD: st_execute/SQLExecute err=-1)' > (err#0) > <- DESTROY= undef > DESTROY (dbih_clearcom) (sth 0x260b20, com 0x2a8690, imp > DBD::ODBC::st): > FLAGS 0x180191: COMSET Warn RaiseError PrintError PrintWarn > PARENT DBI::db=HASH(0x260a18) > KIDS 0 (0 Active) > IMP_DATA undef > NUM_OF_FIELDS -1 > NUM_OF_PARAMS 1 > dbih_clearcom 0x260b20 (com 0x2a8690, type 3) done. > > -- DBI::END > >> disconnect_all DISPATCH (DBI::dr=HASH(0x1fa794) rc1/4 @1 g0 > ima801 pid#5169) at > /usr/perl5.8.7/lib/site_perl/5.8.7/sun4-solaris/DBI.pm line 677 via > ./insert.pl line 0 > -> disconnect_all for DBD::ODBC::dr > (DBI::dr=HASH(0x1fa794)~0x25303c) > <- disconnect_all= '' at > /usr/perl5.8.7/lib/site_perl/5.8.7/sun4-solaris/DBI.pm line 677 via > ./insert.pl line 0 > ! >> DESTROY DISPATCH (DBI::db=HASH(0x260a18) rc1/1 @1 g0 ima4 > pid#5169) during global destruction > ! -> DESTROY for DBD::ODBC::db (DBI::db=HASH(0x260a18)~INNER) > ** auto-rollback due to disconnect without commit returned 1 > DBD::ODBC Disconnected! > ERROR: -1 '[unixODBC][Microsoft][ODBC SQL Server Driver]Numeric > value out of range (SQL-22003)(DBD: st_execute/SQLExecute err=-1)' > (err#0) > ! <- DESTROY= undef during global destruction > DESTROY (dbih_clearcom) (dbh 0x25300c, com 0x136f30, imp global > destruction): > FLAGS 0x180191: COMSET Warn RaiseError PrintError PrintWarn > PARENT DBI::dr=HASH(0x25303c) > KIDS 0 (0 Active) > IMP_DATA undef > dbih_clearcom 0x25300c (com 0x136f30, type 2) done. > > ! >> DESTROY DISPATCH (DBI::dr=HASH(0x25303c) rc1/1 @1 g0 ima4 > pid#5169) during global destruction > ! -> DESTROY in DBD::_::common for DBD::ODBC::dr > (DBI::dr=HASH(0x25303c)~INNER) > ! <- DESTROY= undef during global destruction > DESTROY (dbih_clearcom) (drh 0x1fa794, com 0x136460, imp global > destruction): > FLAGS 0x100215: COMSET Active Warn PrintWarn AutoCommit > PARENT undef > KIDS 1 (1 Active) > IMP_DATA undef > dbih_clearcom 0x1fa794 (com 0x136460, type 1) done. > > ! >> DESTROY DISPATCH (DBI::dr=HASH(0x1fa794) rc1/1 @1 g0 ima4 > pid#5169) during global destruction > ! <> DESTROY for DBI::dr=HASH(0x1fa794) ignored (inner handle gone) > ! >> DESTROY DISPATCH (DBI::db=HASH(0x25300c) rc1/1 @1 g0 ima4 > pid#5169) during global destruction > ! <> DESTROY for DBI::db=HASH(0x25300c) ignored (inner handle gone) > > >
