On 28-Sep-2005 Lee Anne Lester wrote: > 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.
I am tempted to ask what the issues you refer to are. The only one I know about is the NULL issue Brian Becker reported in July - for which I posted a patch for DBD::ODBC to this group. Brian did seem to indicate at the time you were setting odbc_default_bind_type - which also prevents DBD::ODBC from calling SQLDescribeParam. > Is it > possible the patch you are suggesting will be implemented in a future > release of the DBD::ODBC module? You can always lobby Jeff and he will probably lobby me for a proper patch. But there is the previous patch (I refer to above) as well and I think that didn't end up anywhere because after presenting the problem and a fix being provided, odbc_default_bind_type was introduced. Martin -- Martin J. Evans Easysoft Ltd, UK Development > 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) >> >> >>
