Brian, If you summarize/resend the patches, preferably with documentation and patches to the tests to validate the patches, I would be likely to put them in at least the subversion repository and roll out a proper patch if things look good.
Jeff > -----Original Message----- > From: Brian Becker [mailto:[EMAIL PROTECTED] > Sent: Wednesday, September 28, 2005 11:33 AM > To: Martin J. Evans; Lee Anne Lester > Cc: dbi-users@perl.org > Subject: RE: Numeric value out of range error > > Sorry you were missing the outcome on that whole issue. We > decided to take off the odbc_default_bind_type that we had > set to 12 and returned it back to 0 to let the DBD do the > SQLDescribeParam as it was designed. > We also applied the patch that you sent previously which does > address the issue. > > The issue with dates is just that it has to be in the > specific format (is 9075) which is a problem for us but > unrelated to the issue at hand...we are simply describing it > as varchar and letting the database figure it out on its own. > This is however why we are describing our own params. > > The SQL Describe param ordinal thing has to do with Stored > procedure calls for SQL Server. The order your proc defines > it attributes is the order they are described in so for example > > Create procedure someproc as > Field1 as int > Field2 as varchar > ... > > If you try and call someproc @Field2=?, @Field1=? -- the > fields are described incorreclty by SQLDescribeParam (Field2 > as int and Field1 as varchar). This seems odd as they were > given the attribute names so you would think it could be > matched up by name -- but it simply does it by position. > This was confirmed with Microsoft. > > So to bring us back we have applied both patches -- the > Isnull from a while back and this most recent one. Both were > described as "hacks" and we are concerned putting these into > production not knowing if these will make it into the next > version or if we will need to re-apply these "hacks" to the > code and risk it breaking something else in the newer > versions (some variable changes name or new condition block > is added or who knows what else). > > Hope that cleared things up. > > Brian Becker > Jaeb Center for Health Research > 15310 Amberly Drive > Suite 350 > Tampa, FL 33647 > tel: 813-975-8690 > fax: 813-975-8761 > email: [EMAIL PROTECTED] > http://www.jaeb.org > > > > -----Original Message----- > From: Martin J. Evans [mailto:[EMAIL PROTECTED] > Sent: Wednesday, September 28, 2005 11:03 AM > To: Lee Anne Lester > Cc: dbi-users@perl.org > Subject: RE: Numeric value out of range error > > > 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) > >> > >> > >> >
