Try changing your bind_param to "10.00", {TYPE=>12}
Jeff
> -----Original Message-----
> From: Lee Anne Lester [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, September 27, 2005 4:09 PM
> To: dbi-users@perl.org
> Subject: Numeric value out of range error
>
> 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
>
>
> 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)
>
>
>
>
