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))
SQL> insert into jaeb values ('10.00')
SQL> insert into jaeb values (10.00)
SQL> 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)
>
>
>
