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) >> >> >>
