Numeric value out of range error
Numeric value out of range error
am 27.09.2005 22:08:31 von llester
------_=_NextPart_001_01C5C39F.3C272BB4
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
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
=============3D
#!/usr/local/bin/perl -Tw
use strict;
use DBI;
my $dsn =3D "dbi:ODBC:dsn_IRB";
our $dbh =3D DBI->connect($dsn, '', '', {AutoCommit=3D>0, =
RaiseError=3D>1,
FetchHashKeyName=3D>'NAME_uc'});
unlink('dbitrace.log') if(-e 'dbitrace.log');
DBI->trace(9, 'dbitrace.log');
my $sth =3D $dbh->prepare(q{INSERT INTO tblDecimalTest (FieldDecimal)
VALUES (?)});
$sth->bind_param(1, 10.00, {TYPE=3D>3} );
$sth->execute();
$sth->finish;
$dbh->commit;
$dbh->disconnect;
~
VERSIONS
=========3D
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)=20
May 3 2005 23:18:38=20
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 =3D 5.008007
DBI Version =3D 1.48
DBD::ODBC Version =3D 1.13
OUTPUT FROM TRACE - LEVEL 9
==================== =====3D=
===3D
DBI 1.48-nothread default trace level set to 0x0/9 (pid 5169)
>> prepare DISPATCH (DBI::db=3DHASH(0x25300c) rc1/1 @2 g0 =
ima2201
pid#5169) at ./insert.pl line 14
-> prepare for DBD::ODBC::db (DBI::db=3DHASH(0x25300c)~0x260a18
'INSERT INTO tblDecimalTest (FieldDecimal) VALUES (?)')
New DBI::st (for DBD::ODBC::st, parent=3DDBI::db=3DHASH(0x260a18), =
id=3D)
=
dbih_setup_handle(DBI::st=3DHASH(0x260b20)=3D>DBI::st=3DHASH (0x1b9d48),
DBD::ODBC::st, 260b2c, Null!)
dbih_make_com(DBI::db=3DHASH(0x260a18), 136f30, DBD::ODBC::st, 212, =
0)
thr#0
dbih_setup_attrib(DBI::st=3DHASH(0x1b9d48), Err,
DBI::db=3DHASH(0x260a18)) SCALAR(0x1face0) (already defined)
dbih_setup_attrib(DBI::st=3DHASH(0x1b9d48), State,
DBI::db=3DHASH(0x260a18)) SCALAR(0x1fad40) (already defined)
dbih_setup_attrib(DBI::st=3DHASH(0x1b9d48), Errstr,
DBI::db=3DHASH(0x260a18)) SCALAR(0x1fad10) (already defined)
dbih_setup_attrib(DBI::st=3DHASH(0x1b9d48), TraceLevel,
DBI::db=3DHASH(0x260a18)) 0 (already defined)
dbih_setup_attrib(DBI::st=3DHASH(0x1b9d48), FetchHashKeyName,
DBI::db=3DHASH(0x260a18)) 'NAME_uc' (already defined)
dbih_setup_attrib(DBI::st=3DHASH(0x1b9d48), HandleSetErr,
DBI::db=3DHASH(0x260a18)) undef (not defined)
dbih_setup_attrib(DBI::st=3DHASH(0x1b9d48), HandleError,
DBI::db=3DHASH(0x260a18)) undef (not defined)
initializing sth query timeout to 0
ignore named placeholders =3D 0
dbd_preparse scanned 1 distinct placeholders
SQLPrepare returned 0
dbd_st_prepare'd sql f2787256, ExecDirect=3D0
INSERT INTO tblDecimalTest (FieldDecimal) VALUES (?)
<- prepare=3D DBI::st=3DHASH(0x260b20) at ./insert.pl line 14
>> bind_param DISPATCH (DBI::st=3DHASH(0x260b20) rc1/1 @4 g0 ima1
pid#5169) at ./insert.pl line 16
-> bind_param for DBD::ODBC::st (DBI::st=3DHASH(0x260b20)~0x1b9d48 1
10 HASH(0x1209b8))
bind 1 <== '10' (attribs: HASH(0x1209b8)), type 3
<- bind_param=3D 1 at ./insert.pl line 16
>> execute DISPATCH (DBI::st=3DHASH(0x260b20) rc1/1 @1 g0 =
ima1041
pid#5169) at ./insert.pl line 18
-> execute for DBD::ODBC::st (DBI::st=3DHASH(0x260b20)~0x1b9d48)
dbd_st_execute (outparams =3D 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=3D1, STy=3DDECIMAL, CD=3D2, Sc=3D2, VM=3D2.
SQLBindParameter: idx =3D 1: fParamType=3D1, name=3D1, fCtype=3D1, =
SQL_Type
=3D 3, cbColDef=3D2, scale=3D2, rgbValue =3D 13ddc0, cbValueMax=3D2, =
cbValue =3D 2
Param value =3D 10
rebind check char Param 1 (10)
dbd_st_execute (for hstmt 2787256 before)...
dbd_st_execute (for hstmt 2787256 after, rc =3D -1)...
dbd_error: err_rc=3D-1 rc=3D0 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=3D-1 rc=3D0 s/d/e: 0/2497728/2496296
dbd_error: err_rc=3D-1 rc=3D0 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=3D-1)
!! ERROR: -1 '[unixODBC][Microsoft][ODBC SQL Server Driver]Numeric
value out of range (SQL-22003)(DBD: st_execute/SQLExecute err=3D-1)'
(err#0)
<- execute=3D undef at ./insert.pl line 18
>> DESTROY DISPATCH (DBI::st=3DHASH(0x260b20) rc1/1 @1 g0 ima4
pid#5169)
<> DESTROY(DBI::st=3DHASH(0x260b20)) ignored for outer handle (inner
DBI::st=3DHASH(0x1b9d48) has ref cnt 1)
>> DESTROY DISPATCH (DBI::st=3DHASH(0x1b9d48) rc1/1 @1 g0 ima4
pid#5169)
-> DESTROY for DBD::ODBC::st (DBI::st=3DHASH(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=3D-1)'
(err#0)
<- DESTROY=3D undef
DESTROY (dbih_clearcom) (sth 0x260b20, com 0x2a8690, imp
DBD::ODBC::st):
FLAGS 0x180191: COMSET Warn RaiseError PrintError PrintWarn=20
PARENT DBI::db=3DHASH(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=3DHASH(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=3DHASH(0x1fa794)~0x25303c)
<- disconnect_all=3D '' 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=3DHASH(0x260a18) rc1/1 @1 g0 ima4
pid#5169) during global destruction
! -> DESTROY for DBD::ODBC::db (DBI::db=3DHASH(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=3D-1)'
(err#0)
! <- DESTROY=3D undef during global destruction
DESTROY (dbih_clearcom) (dbh 0x25300c, com 0x136f30, imp global
destruction):
FLAGS 0x180191: COMSET Warn RaiseError PrintError PrintWarn=20
PARENT DBI::dr=3DHASH(0x25303c)
KIDS 0 (0 Active)
IMP_DATA undef
dbih_clearcom 0x25300c (com 0x136f30, type 2) done.
! >> DESTROY DISPATCH (DBI::dr=3DHASH(0x25303c) rc1/1 @1 g0 ima4
pid#5169) during global destruction
! -> DESTROY in DBD::_::common for DBD::ODBC::dr
(DBI::dr=3DHASH(0x25303c)~INNER)
! <- DESTROY=3D undef during global destruction
DESTROY (dbih_clearcom) (drh 0x1fa794, com 0x136460, imp global
destruction):
FLAGS 0x100215: COMSET Active Warn PrintWarn AutoCommit=20
PARENT undef
KIDS 1 (1 Active)
IMP_DATA undef
dbih_clearcom 0x1fa794 (com 0x136460, type 1) done.
! >> DESTROY DISPATCH (DBI::dr=3DHASH(0x1fa794) rc1/1 @1 g0 ima4
pid#5169) during global destruction
! <> DESTROY for DBI::dr=3DHASH(0x1fa794) ignored (inner handle gone)
! >> DESTROY DISPATCH (DBI::db=3DHASH(0x25300c) rc1/1 @1 g0 ima4
pid#5169) during global destruction
! <> DESTROY for DBI::db=3DHASH(0x25300c) ignored (inner handle gone)
------_=_NextPart_001_01C5C39F.3C272BB4--
RE: Numeric value out of range error
am 28.09.2005 04:43:46 von jurlwin
Try changing your bind_param to "10.00", {TYPE=3D>12}
Jeff
> -----Original Message-----
> From: Lee Anne Lester [mailto:llester@Jaeb.org]=20
> Sent: Tuesday, September 27, 2005 4:09 PM
> To: dbi-users@perl.org
> Subject: Numeric value out of range error
>=20
> I am getting a "Numeric value out of range error" when=20
> specifically binding a placeholder for a decimal column as a=20
> decimal type. I'm using DBD::ODBC connecting to Microsoft SQL=20
> Server from a Solaris machine.
> The value being bound is 10.00. The settings in database are=20
> 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.
>=20
> Can anyone advise how to make this work? Is there a way to=20
> define the precision and scale when calling bind_param?
>=20
> Thanks in advance for any help.
>=20
> Lee Anne Lester
>=20
>=20
> SAMPLE CODE
> =============3D
> #!/usr/local/bin/perl -Tw
>=20
> use strict;
> use DBI;
>=20
> my $dsn =3D "dbi:ODBC:dsn_IRB";
>=20
> our $dbh =3D DBI->connect($dsn, '', '', {AutoCommit=3D>0,=20
> RaiseError=3D>1, FetchHashKeyName=3D>'NAME_uc'});
>=20
> unlink('dbitrace.log') if(-e 'dbitrace.log');
>=20
> DBI->trace(9, 'dbitrace.log');
>=20
> my $sth =3D $dbh->prepare(q{INSERT INTO tblDecimalTest=20
> (FieldDecimal) VALUES (?)});
>=20
> $sth->bind_param(1, 10.00, {TYPE=3D>3} );
>=20
> $sth->execute();
>=20
> $sth->finish;
>=20
> $dbh->commit;
>=20
> $dbh->disconnect;
> ~
>=20
>=20
>=20
> VERSIONS
> =========3D
>=20
> Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)=20
> May 3 2005 23:18:38=20
> Copyright (c) 1988-2003 Microsoft Corporation
> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
>=20
> unixODBC version: 2.2.7 using EasySoft ODBC bridge
>=20
> Perl Version =3D 5.008007
> DBI Version =3D 1.48
> DBD::ODBC Version =3D 1.13
>=20
>=20
> OUTPUT FROM TRACE - LEVEL 9
> =
==================== =====3D=
===3D
> DBI 1.48-nothread default trace level set to 0x0/9 (pid 5169)
> >> prepare DISPATCH (DBI::db=3DHASH(0x25300c) rc1/1 @2=20
> g0 ima2201
> pid#5169) at ./insert.pl line 14
> -> prepare for DBD::ODBC::db=20
> (DBI::db=3DHASH(0x25300c)~0x260a18 'INSERT INTO tblDecimalTest=20
> (FieldDecimal) VALUES (?)')
> New DBI::st (for DBD::ODBC::st,=20
> parent=3DDBI::db=3DHASH(0x260a18), id=3D)
> =
dbih_setup_handle(DBI::st=3DHASH(0x260b20)=3D>DBI::st=3DHASH (0x1b9d48),
> DBD::ODBC::st, 260b2c, Null!)
> dbih_make_com(DBI::db=3DHASH(0x260a18), 136f30,=20
> DBD::ODBC::st, 212, 0) thr#0
> dbih_setup_attrib(DBI::st=3DHASH(0x1b9d48), Err,
> DBI::db=3DHASH(0x260a18)) SCALAR(0x1face0) (already defined)
> dbih_setup_attrib(DBI::st=3DHASH(0x1b9d48), State,
> DBI::db=3DHASH(0x260a18)) SCALAR(0x1fad40) (already defined)
> dbih_setup_attrib(DBI::st=3DHASH(0x1b9d48), Errstr,
> DBI::db=3DHASH(0x260a18)) SCALAR(0x1fad10) (already defined)
> dbih_setup_attrib(DBI::st=3DHASH(0x1b9d48), TraceLevel,
> DBI::db=3DHASH(0x260a18)) 0 (already defined)
> dbih_setup_attrib(DBI::st=3DHASH(0x1b9d48), FetchHashKeyName,
> DBI::db=3DHASH(0x260a18)) 'NAME_uc' (already defined)
> dbih_setup_attrib(DBI::st=3DHASH(0x1b9d48), HandleSetErr,
> DBI::db=3DHASH(0x260a18)) undef (not defined)
> dbih_setup_attrib(DBI::st=3DHASH(0x1b9d48), HandleError,
> DBI::db=3DHASH(0x260a18)) undef (not defined)
> initializing sth query timeout to 0
> ignore named placeholders =3D 0
> dbd_preparse scanned 1 distinct placeholders
> SQLPrepare returned 0
>=20
> dbd_st_prepare'd sql f2787256, ExecDirect=3D0
> INSERT INTO tblDecimalTest (FieldDecimal) VALUES (?)
> <- prepare=3D DBI::st=3DHASH(0x260b20) at ./insert.pl line 14
> >> bind_param DISPATCH (DBI::st=3DHASH(0x260b20) rc1/1 @4 g0 ima1
> pid#5169) at ./insert.pl line 16
> -> bind_param for DBD::ODBC::st=20
> (DBI::st=3DHASH(0x260b20)~0x1b9d48 1 10 HASH(0x1209b8)) bind 1=20
> <== '10' (attribs: HASH(0x1209b8)), type 3
> <- bind_param=3D 1 at ./insert.pl line 16
> >> execute DISPATCH (DBI::st=3DHASH(0x260b20) rc1/1 @1=20
> g0 ima1041
> pid#5169) at ./insert.pl line 18
> -> execute for DBD::ODBC::st (DBI::st=3DHASH(0x260b20)~0x1b9d48)
> dbd_st_execute (outparams =3D 0)...
> bind 1 <== 10 (size 2/3/0, ptype 6, otype 1, sqltype 3) bind=20
> 1 <== '10' (len 2/2, null 0)
> bind 1: CTy=3D1, STy=3DDECIMAL, CD=3D2, Sc=3D2, VM=3D2.
> SQLBindParameter: idx =3D 1: fParamType=3D1, name=3D1,=20
> fCtype=3D1, SQL_Type =3D 3, cbColDef=3D2, scale=3D2, rgbValue =
> 13ddc0, cbValueMax=3D2, cbValue =3D 2
> Param value =3D 10
> rebind check char Param 1 (10)
> dbd_st_execute (for hstmt 2787256 before)...
> dbd_st_execute (for hstmt 2787256 after, rc =3D -1)...
> dbd_error: err_rc=3D-1 rc=3D0 s/d/e: 2787256/2497728/2496296
> dbd_error: SQL-22003 (native 0): [unixODBC][Microsoft][ODBC=20
> SQL Server Driver]Numeric value out of range (SQL-22003)
> dbd_error: err_rc=3D-1 rc=3D0 s/d/e: 0/2497728/2496296
> dbd_error: err_rc=3D-1 rc=3D0 s/d/e: 0/0/2496296=20
> st_execute/SQLExecute error -1 recorded:=20
> [unixODBC][Microsoft][ODBC SQL Server Driver]Numeric value=20
> out of range (SQL-22003)(DBD:
> st_execute/SQLExecute err=3D-1)
> !! ERROR: -1 '[unixODBC][Microsoft][ODBC SQL Server=20
> Driver]Numeric value out of range (SQL-22003)(DBD:=20
> st_execute/SQLExecute err=3D-1)'
> (err#0)
> <- execute=3D undef at ./insert.pl line 18
> >> DESTROY DISPATCH (DBI::st=3DHASH(0x260b20) rc1/1 @1 g0 ima4
> pid#5169)
> <> DESTROY(DBI::st=3DHASH(0x260b20)) ignored for outer handle =
(inner
> DBI::st=3DHASH(0x1b9d48) has ref cnt 1)
> >> DESTROY DISPATCH (DBI::st=3DHASH(0x1b9d48) rc1/1 @1 g0 ima4
> pid#5169)
> -> DESTROY for DBD::ODBC::st (DBI::st=3DHASH(0x1b9d48)~INNER)
> SQLFreeStmt called, returned 0.
> ERROR: -1 '[unixODBC][Microsoft][ODBC SQL Server=20
> Driver]Numeric value out of range (SQL-22003)(DBD:=20
> st_execute/SQLExecute err=3D-1)'
> (err#0)
> <- DESTROY=3D undef
> DESTROY (dbih_clearcom) (sth 0x260b20, com 0x2a8690, imp
> DBD::ODBC::st):
> FLAGS 0x180191: COMSET Warn RaiseError PrintError PrintWarn=20
> PARENT DBI::db=3DHASH(0x260a18)
> KIDS 0 (0 Active)
> IMP_DATA undef
> NUM_OF_FIELDS -1
> NUM_OF_PARAMS 1
> dbih_clearcom 0x260b20 (com 0x2a8690, type 3) done.
>=20
> -- DBI::END
> >> disconnect_all DISPATCH (DBI::dr=3DHASH(0x1fa794) rc1/4 @1 g0
> ima801 pid#5169) at
> /usr/perl5.8.7/lib/site_perl/5.8.7/sun4-solaris/DBI.pm line=20
> 677 via ./insert.pl line 0
> -> disconnect_all for DBD::ODBC::dr
> (DBI::dr=3DHASH(0x1fa794)~0x25303c)
> <- disconnect_all=3D '' at
> /usr/perl5.8.7/lib/site_perl/5.8.7/sun4-solaris/DBI.pm line=20
> 677 via ./insert.pl line 0
> ! >> DESTROY DISPATCH (DBI::db=3DHASH(0x260a18) rc1/1 @1 g0 ima4
> pid#5169) during global destruction
> ! -> DESTROY for DBD::ODBC::db (DBI::db=3DHASH(0x260a18)~INNER)
> ** auto-rollback due to disconnect without commit returned 1
> DBD::ODBC Disconnected!
> ERROR: -1 '[unixODBC][Microsoft][ODBC SQL Server=20
> Driver]Numeric value out of range (SQL-22003)(DBD:=20
> st_execute/SQLExecute err=3D-1)'
> (err#0)
> ! <- DESTROY=3D undef during global destruction
> DESTROY (dbih_clearcom) (dbh 0x25300c, com 0x136f30, imp global
> destruction):
> FLAGS 0x180191: COMSET Warn RaiseError PrintError PrintWarn=20
> PARENT DBI::dr=3DHASH(0x25303c)
> KIDS 0 (0 Active)
> IMP_DATA undef
> dbih_clearcom 0x25300c (com 0x136f30, type 2) done.
>=20
> ! >> DESTROY DISPATCH (DBI::dr=3DHASH(0x25303c) rc1/1 @1 g0 ima4
> pid#5169) during global destruction
> ! -> DESTROY in DBD::_::common for DBD::ODBC::dr
> (DBI::dr=3DHASH(0x25303c)~INNER)
> ! <- DESTROY=3D undef during global destruction
> DESTROY (dbih_clearcom) (drh 0x1fa794, com 0x136460, imp global
> destruction):
> FLAGS 0x100215: COMSET Active Warn PrintWarn AutoCommit=20
> PARENT undef
> KIDS 1 (1 Active)
> IMP_DATA undef
> dbih_clearcom 0x1fa794 (com 0x136460, type 1) done.
>=20
> ! >> DESTROY DISPATCH (DBI::dr=3DHASH(0x1fa794) rc1/1 @1 g0 ima4
> pid#5169) during global destruction
> ! <> DESTROY for DBI::dr=3DHASH(0x1fa794) ignored (inner handle =
gone)
> ! >> DESTROY DISPATCH (DBI::db=3DHASH(0x25300c) rc1/1 @1 g0 ima4
> pid#5169) during global destruction
> ! <> DESTROY for DBI::db=3DHASH(0x25300c) ignored (inner handle =
gone)
>=20
>=20
>=20
>=20
RE: Numeric value out of range error
am 28.09.2005 11:17:45 von Martin.Evans
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(0x1b9 d48),
> 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)
>
>
>
RE: Numeric value out of range error
am 28.09.2005 16:45:54 von llester
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. Is it
possible the patch you are suggesting will be implemented in a future
release of the DBD::ODBC module?
Lee Anne
-----Original Message-----
From: Martin J. Evans [mailto:martin.evans@easysoft.com]=20
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=20
> binding a placeholder for a decimal column as a decimal type. I'm=20
> 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.
>=20
> Can anyone advise how to make this work? Is there a way to define the
> precision and scale when calling bind_param?
>=20
> Thanks in advance for any help.
>=20
> Lee Anne Lester
>=20
>=20
SQL> create table jaeb (FieldDecimal numeric(5,2)) insert into jaeb=20
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 =3D DBI->connect('dbi:ODBC:test', 'Martin_Evans', 'easysoft'); =
my
$sql =3D q/insert into jaeb values (?)/; my $sth =3D =
$dbh->prepare($sql);
$sth->bind_param(1, 10.00, {TYPE=3D>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=3DSQL_C_CHAR,
ParameterType=3DSQL_NUMERIC, ColumnSize=3D5, DecimalDigits=3D5. 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=3D2, scale=3D2," 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 =3D SQL_VARCHAR;*/
break;
+ case SQL_NUMERIC:
+ case SQL_DECIMAL:
+ =20
+ if (phs->sv_buf && *phs->sv_buf) {
+ char *cp;
+ cp =3D strchr(phs->sv_buf, '.');
+ if (cp) {
+ ibScale =3D 0;
+ cp++;
+ while (*cp !=3D '\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
> =============3D
>#!/usr/local/bin/perl -Tw
>=20
> use strict;
> use DBI;
>=20
> my $dsn =3D "dbi:ODBC:dsn_IRB";
>=20
> our $dbh =3D DBI->connect($dsn, '', '', {AutoCommit=3D>0, =
RaiseError=3D>1,=20
> FetchHashKeyName=3D>'NAME_uc'});
>=20
> unlink('dbitrace.log') if(-e 'dbitrace.log');
>=20
> DBI->trace(9, 'dbitrace.log');
>=20
> my $sth =3D $dbh->prepare(q{INSERT INTO tblDecimalTest (FieldDecimal)=20
> VALUES (?)});
>=20
> $sth->bind_param(1, 10.00, {TYPE=3D>3} );
>=20
> $sth->execute();
>=20
> $sth->finish;
>=20
> $dbh->commit;
>=20
> $dbh->disconnect;
> ~
>=20
>=20
>=20
> VERSIONS
> =========3D
>=20
> Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)=20
> May 3 2005 23:18:38=20
> Copyright (c) 1988-2003 Microsoft Corporation
> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
>=20
> unixODBC version: 2.2.7 using EasySoft ODBC bridge
>=20
> Perl Version =3D 5.008007
> DBI Version =3D 1.48
> DBD::ODBC Version =3D 1.13
>=20
>=20
> OUTPUT FROM TRACE - LEVEL 9
> =
==================== =====3D=
===3D
> DBI 1.48-nothread default trace level set to 0x0/9 (pid 5169)
> >> prepare DISPATCH (DBI::db=3DHASH(0x25300c) rc1/1 @2 g0
ima2201
> pid#5169) at ./insert.pl line 14
> -> prepare for DBD::ODBC::db (DBI::db=3DHASH(0x25300c)~0x260a18=20
> 'INSERT INTO tblDecimalTest (FieldDecimal) VALUES (?)')
> New DBI::st (for DBD::ODBC::st, parent=3DDBI::db=3DHASH(0x260a18),
id=3D)
> =
dbih_setup_handle(DBI::st=3DHASH(0x260b20)=3D>DBI::st=3DHASH (0x1b9d48),
> DBD::ODBC::st, 260b2c, Null!)
> dbih_make_com(DBI::db=3DHASH(0x260a18), 136f30, DBD::ODBC::st, =
212,=20
> 0) thr#0
> dbih_setup_attrib(DBI::st=3DHASH(0x1b9d48), Err,
> DBI::db=3DHASH(0x260a18)) SCALAR(0x1face0) (already defined)
> dbih_setup_attrib(DBI::st=3DHASH(0x1b9d48), State,
> DBI::db=3DHASH(0x260a18)) SCALAR(0x1fad40) (already defined)
> dbih_setup_attrib(DBI::st=3DHASH(0x1b9d48), Errstr,
> DBI::db=3DHASH(0x260a18)) SCALAR(0x1fad10) (already defined)
> dbih_setup_attrib(DBI::st=3DHASH(0x1b9d48), TraceLevel,
> DBI::db=3DHASH(0x260a18)) 0 (already defined)
> dbih_setup_attrib(DBI::st=3DHASH(0x1b9d48), FetchHashKeyName,
> DBI::db=3DHASH(0x260a18)) 'NAME_uc' (already defined)
> dbih_setup_attrib(DBI::st=3DHASH(0x1b9d48), HandleSetErr,
> DBI::db=3DHASH(0x260a18)) undef (not defined)
> dbih_setup_attrib(DBI::st=3DHASH(0x1b9d48), HandleError,
> DBI::db=3DHASH(0x260a18)) undef (not defined)
> initializing sth query timeout to 0
> ignore named placeholders =3D 0
> dbd_preparse scanned 1 distinct placeholders
> SQLPrepare returned 0
>=20
> dbd_st_prepare'd sql f2787256, ExecDirect=3D0
> INSERT INTO tblDecimalTest (FieldDecimal) VALUES (?)
> <- prepare=3D DBI::st=3DHASH(0x260b20) at ./insert.pl line 14
> >> bind_param DISPATCH (DBI::st=3DHASH(0x260b20) rc1/1 @4 g0 ima1
> pid#5169) at ./insert.pl line 16
> -> bind_param for DBD::ODBC::st (DBI::st=3DHASH(0x260b20)~0x1b9d48 =
1
> 10 HASH(0x1209b8)) bind 1 <== '10' (attribs: HASH(0x1209b8)), type =
3
> <- bind_param=3D 1 at ./insert.pl line 16
> >> execute DISPATCH (DBI::st=3DHASH(0x260b20) rc1/1 @1 g0
ima1041
> pid#5169) at ./insert.pl line 18
> -> execute for DBD::ODBC::st (DBI::st=3DHASH(0x260b20)~0x1b9d48)
> dbd_st_execute (outparams =3D 0)...
> bind 1 <== 10 (size 2/3/0, ptype 6, otype 1, sqltype 3) bind 1 =
<===20
> '10' (len 2/2, null 0)
> bind 1: CTy=3D1, STy=3DDECIMAL, CD=3D2, Sc=3D2, VM=3D2.
> SQLBindParameter: idx =3D 1: fParamType=3D1, name=3D1, fCtype=3D1, =
> SQL_Type =3D 3, cbColDef=3D2, scale=3D2, rgbValue =3D 13ddc0, =
cbValueMax=3D2,
cbValue =3D 2
> Param value =3D 10
> rebind check char Param 1 (10)
> dbd_st_execute (for hstmt 2787256 before)...
> dbd_st_execute (for hstmt 2787256 after, rc =3D -1)...
> dbd_error: err_rc=3D-1 rc=3D0 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=3D-1 rc=3D0 s/d/e: 0/2497728/2496296
> dbd_error: err_rc=3D-1 rc=3D0 s/d/e: 0/0/2496296 st_execute/SQLExecute =
> error -1 recorded: [unixODBC][Microsoft][ODBC SQL Server=20
> Driver]Numeric value out of range (SQL-22003)(DBD:
> st_execute/SQLExecute err=3D-1)
> !! ERROR: -1 '[unixODBC][Microsoft][ODBC SQL Server Driver]Numeric
> value out of range (SQL-22003)(DBD: st_execute/SQLExecute err=3D-1)'
> (err#0)
> <- execute=3D undef at ./insert.pl line 18
> >> DESTROY DISPATCH (DBI::st=3DHASH(0x260b20) rc1/1 @1 g0 ima4
> pid#5169)
> <> DESTROY(DBI::st=3DHASH(0x260b20)) ignored for outer handle =
(inner
> DBI::st=3DHASH(0x1b9d48) has ref cnt 1)
> >> DESTROY DISPATCH (DBI::st=3DHASH(0x1b9d48) rc1/1 @1 g0 ima4
> pid#5169)
> -> DESTROY for DBD::ODBC::st (DBI::st=3DHASH(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=3D-1)'
> (err#0)
> <- DESTROY=3D undef
> DESTROY (dbih_clearcom) (sth 0x260b20, com 0x2a8690, imp
> DBD::ODBC::st):
> FLAGS 0x180191: COMSET Warn RaiseError PrintError PrintWarn=20
> PARENT DBI::db=3DHASH(0x260a18)
> KIDS 0 (0 Active)
> IMP_DATA undef
> NUM_OF_FIELDS -1
> NUM_OF_PARAMS 1
> dbih_clearcom 0x260b20 (com 0x2a8690, type 3) done.
>=20
> -- DBI::END
> >> disconnect_all DISPATCH (DBI::dr=3DHASH(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=20
> ./insert.pl line 0
> -> disconnect_all for DBD::ODBC::dr
> (DBI::dr=3DHASH(0x1fa794)~0x25303c)
> <- disconnect_all=3D '' at
> /usr/perl5.8.7/lib/site_perl/5.8.7/sun4-solaris/DBI.pm line 677 via=20
> ./insert.pl line 0
> ! >> DESTROY DISPATCH (DBI::db=3DHASH(0x260a18) rc1/1 @1 g0 ima4
> pid#5169) during global destruction
> ! -> DESTROY for DBD::ODBC::db (DBI::db=3DHASH(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=3D-1)'
> (err#0)
> ! <- DESTROY=3D undef during global destruction
> DESTROY (dbih_clearcom) (dbh 0x25300c, com 0x136f30, imp global
> destruction):
> FLAGS 0x180191: COMSET Warn RaiseError PrintError PrintWarn=20
> PARENT DBI::dr=3DHASH(0x25303c)
> KIDS 0 (0 Active)
> IMP_DATA undef
> dbih_clearcom 0x25300c (com 0x136f30, type 2) done.
>=20
> ! >> DESTROY DISPATCH (DBI::dr=3DHASH(0x25303c) rc1/1 @1 g0 ima4
> pid#5169) during global destruction
> ! -> DESTROY in DBD::_::common for DBD::ODBC::dr
> (DBI::dr=3DHASH(0x25303c)~INNER)
> ! <- DESTROY=3D undef during global destruction
> DESTROY (dbih_clearcom) (drh 0x1fa794, com 0x136460, imp global
> destruction):
> FLAGS 0x100215: COMSET Active Warn PrintWarn AutoCommit=20
> PARENT undef
> KIDS 1 (1 Active)
> IMP_DATA undef
> dbih_clearcom 0x1fa794 (com 0x136460, type 1) done.
>=20
> ! >> DESTROY DISPATCH (DBI::dr=3DHASH(0x1fa794) rc1/1 @1 g0 ima4
> pid#5169) during global destruction
> ! <> DESTROY for DBI::dr=3DHASH(0x1fa794) ignored (inner handle =
gone)
> ! >> DESTROY DISPATCH (DBI::db=3DHASH(0x25300c) rc1/1 @1 g0 ima4
> pid#5169) during global destruction
> ! <> DESTROY for DBI::db=3DHASH(0x25300c) ignored (inner handle =
gone)
>=20
>=20
>=20
RE: Numeric value out of range error
am 28.09.2005 17:02:36 von Martin.Evans
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:martin.evans@easysoft.com]
> 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(0x1b9 d48),
>> 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)
>>
>>
>>
RE: Numeric value out of range error
am 28.09.2005 17:32:38 von bbecker
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. =20
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=3D?, @Field1=3D? -- 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). =20
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: bbecker@jaeb.org
http://www.jaeb.org
-----Original Message-----
From: Martin J. Evans [mailto:martin.evans@easysoft.com]=20
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
>=20
> -----Original Message-----
> From: Martin J. Evans [mailto:martin.evans@easysoft.com]=20
> 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
>=20
> On 27-Sep-2005 Lee Anne Lester wrote:
>> I am getting a "Numeric value out of range error" when specifically=20
>> binding a placeholder for a decimal column as a decimal type. I'm=20
>> 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.
>>=20
>> Can anyone advise how to make this work? Is there a way to define
the
>=20
>> precision and scale when calling bind_param?
>>=20
>> Thanks in advance for any help.
>>=20
>> Lee Anne Lester
>>=20
>>=20
> SQL> create table jaeb (FieldDecimal numeric(5,2)) insert into jaeb=20
> SQL> values ('10.00') insert into jaeb values (10.00) select * from
jaeb
> +-------------+
>| FieldDecimal|
> +-------------+
>| 10.00 |
>| 10.00 |
> +-------------+
> SQLRowCount returns 2
> 2 rows fetched
>=20
> So you can insert without binding OK but as you say:
>=20
>#!/usr/local/bin/perl -w
> use DBI;
> my $dbh =3D DBI->connect('dbi:ODBC:test', 'Martin_Evans', 'easysoft');
my
> $sql =3D q/insert into jaeb values (?)/; my $sth =3D =
$dbh->prepare($sql);
> $sth->bind_param(1, 10.00, {TYPE=3D>3}); $sth->execute();
>=20
> fails.
>=20
> Changing the bind_param to remove the forcing the type to SQL_NUMERIC
>=20
> $sth->bind_param(1, 10.00);
>=20
> 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.
>=20
> The actual problem appears to be:
>=20
> SQLBindParameter(0x8258ea0,1,1(Input::),1,3,5,5,0x82580f0,5, 0x82580d0)
>=20
> i.e. bind parameter 1 as input, ValueType=3DSQL_C_CHAR,
> ParameterType=3DSQL_NUMERIC, ColumnSize=3D5, DecimalDigits=3D5. This
instructs
> MS SQL Server to convert your SQL_C_CHAR string into a numeric 5,5
> before inserting - this will not fit.
>=20
> Interestingly, your trace shows "cbColDef=3D2, scale=3D2," and I =
cannot
> reproduce this.
>=20
> 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.
>=20
> 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:
>=20
> case SQL_TIME:
> case SQL_TYPE_TIME:
> /* fSqlType =3D SQL_VARCHAR;*/
> break;
> + case SQL_NUMERIC:
> + case SQL_DECIMAL:
> + =20
> + if (phs->sv_buf && *phs->sv_buf) {
> + char *cp;
> + cp =3D strchr(phs->sv_buf, '.');
> + if (cp) {
> + ibScale =3D 0;
> + cp++;
> + while (*cp !=3D '\0' && isdigit(*cp)) {
> + cp++;
> + ibScale++;
> + }
> + }
> + }
> + break;
> case SQL_TIMESTAMP:
> case SQL_TYPE_TIMESTAMP:
>=20
>=20
> Why not just pass your parameters in as strings and leave the
parameter
> type off the bind call.
>=20
> Martin
> --
> Martin J. Evans
> Easysoft Ltd, UK
> Development
>=20
>=20
>> SAMPLE CODE
>> =============3D
>>#!/usr/local/bin/perl -Tw
>>=20
>> use strict;
>> use DBI;
>>=20
>> my $dsn =3D "dbi:ODBC:dsn_IRB";
>>=20
>> our $dbh =3D DBI->connect($dsn, '', '', {AutoCommit=3D>0, =
RaiseError=3D>1,=20
>> FetchHashKeyName=3D>'NAME_uc'});
>>=20
>> unlink('dbitrace.log') if(-e 'dbitrace.log');
>>=20
>> DBI->trace(9, 'dbitrace.log');
>>=20
>> my $sth =3D $dbh->prepare(q{INSERT INTO tblDecimalTest (FieldDecimal) =
>> VALUES (?)});
>>=20
>> $sth->bind_param(1, 10.00, {TYPE=3D>3} );
>>=20
>> $sth->execute();
>>=20
>> $sth->finish;
>>=20
>> $dbh->commit;
>>=20
>> $dbh->disconnect;
>> ~
>>=20
>>=20
>>=20
>> VERSIONS
>> =========3D
>>=20
>> Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)=20
>> May 3 2005 23:18:38=20
>> Copyright (c) 1988-2003 Microsoft Corporation
>> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
>>=20
>> unixODBC version: 2.2.7 using EasySoft ODBC bridge
>>=20
>> Perl Version =3D 5.008007
>> DBI Version =3D 1.48
>> DBD::ODBC Version =3D 1.13
>>=20
>>=20
>> OUTPUT FROM TRACE - LEVEL 9
>> =
==================== =====3D=
===3D
>> DBI 1.48-nothread default trace level set to 0x0/9 (pid 5169)
>> >> prepare DISPATCH (DBI::db=3DHASH(0x25300c) rc1/1 @2 g0
> ima2201
>> pid#5169) at ./insert.pl line 14
>> -> prepare for DBD::ODBC::db (DBI::db=3DHASH(0x25300c)~0x260a18=20
>> 'INSERT INTO tblDecimalTest (FieldDecimal) VALUES (?)')
>> New DBI::st (for DBD::ODBC::st, =
parent=3DDBI::db=3DHASH(0x260a18),
> id=3D)
>> =
dbih_setup_handle(DBI::st=3DHASH(0x260b20)=3D>DBI::st=3DHASH (0x1b9d48),
>> DBD::ODBC::st, 260b2c, Null!)
>> dbih_make_com(DBI::db=3DHASH(0x260a18), 136f30, DBD::ODBC::st, =
212,
>> 0) thr#0
>> dbih_setup_attrib(DBI::st=3DHASH(0x1b9d48), Err,
>> DBI::db=3DHASH(0x260a18)) SCALAR(0x1face0) (already defined)
>> dbih_setup_attrib(DBI::st=3DHASH(0x1b9d48), State,
>> DBI::db=3DHASH(0x260a18)) SCALAR(0x1fad40) (already defined)
>> dbih_setup_attrib(DBI::st=3DHASH(0x1b9d48), Errstr,
>> DBI::db=3DHASH(0x260a18)) SCALAR(0x1fad10) (already defined)
>> dbih_setup_attrib(DBI::st=3DHASH(0x1b9d48), TraceLevel,
>> DBI::db=3DHASH(0x260a18)) 0 (already defined)
>> dbih_setup_attrib(DBI::st=3DHASH(0x1b9d48), FetchHashKeyName,
>> DBI::db=3DHASH(0x260a18)) 'NAME_uc' (already defined)
>> dbih_setup_attrib(DBI::st=3DHASH(0x1b9d48), HandleSetErr,
>> DBI::db=3DHASH(0x260a18)) undef (not defined)
>> dbih_setup_attrib(DBI::st=3DHASH(0x1b9d48), HandleError,
>> DBI::db=3DHASH(0x260a18)) undef (not defined)
>> initializing sth query timeout to 0
>> ignore named placeholders =3D 0
>> dbd_preparse scanned 1 distinct placeholders
>> SQLPrepare returned 0
>>=20
>> dbd_st_prepare'd sql f2787256, ExecDirect=3D0
>> INSERT INTO tblDecimalTest (FieldDecimal) VALUES (?)
>> <- prepare=3D DBI::st=3DHASH(0x260b20) at ./insert.pl line 14
>> >> bind_param DISPATCH (DBI::st=3DHASH(0x260b20) rc1/1 @4 g0 =
ima1
>> pid#5169) at ./insert.pl line 16
>> -> bind_param for DBD::ODBC::st =
(DBI::st=3DHASH(0x260b20)~0x1b9d48
1
>=20
>> 10 HASH(0x1209b8)) bind 1 <== '10' (attribs: HASH(0x1209b8)), =
type 3
>> <- bind_param=3D 1 at ./insert.pl line 16
>> >> execute DISPATCH (DBI::st=3DHASH(0x260b20) rc1/1 @1 g0
> ima1041
>> pid#5169) at ./insert.pl line 18
>> -> execute for DBD::ODBC::st (DBI::st=3DHASH(0x260b20)~0x1b9d48)
>> dbd_st_execute (outparams =3D 0)...
>> bind 1 <== 10 (size 2/3/0, ptype 6, otype 1, sqltype 3) bind 1 =
<===20
>> '10' (len 2/2, null 0)
>> bind 1: CTy=3D1, STy=3DDECIMAL, CD=3D2, Sc=3D2, VM=3D2.
>> SQLBindParameter: idx =3D 1: fParamType=3D1, name=3D1, =
fCtype=3D1,=20
>> SQL_Type =3D 3, cbColDef=3D2, scale=3D2, rgbValue =3D 13ddc0, =
cbValueMax=3D2,
> cbValue =3D 2
>> Param value =3D 10
>> rebind check char Param 1 (10)
>> dbd_st_execute (for hstmt 2787256 before)...
>> dbd_st_execute (for hstmt 2787256 after, rc =3D -1)...
>> dbd_error: err_rc=3D-1 rc=3D0 s/d/e: 2787256/2497728/2496296
>> dbd_error: SQL-22003 (native 0): [unixODBC][Microsoft][ODBC SQL
Server
>=20
>> Driver]Numeric value out of range (SQL-22003)
>> dbd_error: err_rc=3D-1 rc=3D0 s/d/e: 0/2497728/2496296
>> dbd_error: err_rc=3D-1 rc=3D0 s/d/e: 0/0/2496296 =
st_execute/SQLExecute=20
>> error -1 recorded: [unixODBC][Microsoft][ODBC SQL Server=20
>> Driver]Numeric value out of range (SQL-22003)(DBD:
>> st_execute/SQLExecute err=3D-1)
>> !! ERROR: -1 '[unixODBC][Microsoft][ODBC SQL Server
Driver]Numeric
>=20
>> value out of range (SQL-22003)(DBD: st_execute/SQLExecute err=3D-1)'
>> (err#0)
>> <- execute=3D undef at ./insert.pl line 18
>> >> DESTROY DISPATCH (DBI::st=3DHASH(0x260b20) rc1/1 @1 g0 =
ima4
>> pid#5169)
>> <> DESTROY(DBI::st=3DHASH(0x260b20)) ignored for outer handle
(inner
>> DBI::st=3DHASH(0x1b9d48) has ref cnt 1)
>> >> DESTROY DISPATCH (DBI::st=3DHASH(0x1b9d48) rc1/1 @1 g0 =
ima4
>> pid#5169)
>> -> DESTROY for DBD::ODBC::st (DBI::st=3DHASH(0x1b9d48)~INNER)
>> SQLFreeStmt called, returned 0.
>> ERROR: -1 '[unixODBC][Microsoft][ODBC SQL Server
Driver]Numeric
>=20
>> value out of range (SQL-22003)(DBD: st_execute/SQLExecute err=3D-1)'
>> (err#0)
>> <- DESTROY=3D undef
>> DESTROY (dbih_clearcom) (sth 0x260b20, com 0x2a8690, imp
>> DBD::ODBC::st):
>> FLAGS 0x180191: COMSET Warn RaiseError PrintError PrintWarn=20
>> PARENT DBI::db=3DHASH(0x260a18)
>> KIDS 0 (0 Active)
>> IMP_DATA undef
>> NUM_OF_FIELDS -1
>> NUM_OF_PARAMS 1
>> dbih_clearcom 0x260b20 (com 0x2a8690, type 3) done.
>>=20
>> -- DBI::END
>> >> disconnect_all DISPATCH (DBI::dr=3DHASH(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=20
>> ./insert.pl line 0
>> -> disconnect_all for DBD::ODBC::dr
>> (DBI::dr=3DHASH(0x1fa794)~0x25303c)
>> <- disconnect_all=3D '' at
>> /usr/perl5.8.7/lib/site_perl/5.8.7/sun4-solaris/DBI.pm line 677 via=20
>> ./insert.pl line 0
>> ! >> DESTROY DISPATCH (DBI::db=3DHASH(0x260a18) rc1/1 @1 g0 =
ima4
>> pid#5169) during global destruction
>> ! -> DESTROY for DBD::ODBC::db (DBI::db=3DHASH(0x260a18)~INNER)
>> ** auto-rollback due to disconnect without commit returned 1
>> DBD::ODBC Disconnected!
>> ERROR: -1 '[unixODBC][Microsoft][ODBC SQL Server
Driver]Numeric
>=20
>> value out of range (SQL-22003)(DBD: st_execute/SQLExecute err=3D-1)'
>> (err#0)
>> ! <- DESTROY=3D undef during global destruction
>> DESTROY (dbih_clearcom) (dbh 0x25300c, com 0x136f30, imp global
>> destruction):
>> FLAGS 0x180191: COMSET Warn RaiseError PrintError PrintWarn=20
>> PARENT DBI::dr=3DHASH(0x25303c)
>> KIDS 0 (0 Active)
>> IMP_DATA undef
>> dbih_clearcom 0x25300c (com 0x136f30, type 2) done.
>>=20
>> ! >> DESTROY DISPATCH (DBI::dr=3DHASH(0x25303c) rc1/1 @1 g0 =
ima4
>> pid#5169) during global destruction
>> ! -> DESTROY in DBD::_::common for DBD::ODBC::dr
>> (DBI::dr=3DHASH(0x25303c)~INNER)
>> ! <- DESTROY=3D undef during global destruction
>> DESTROY (dbih_clearcom) (drh 0x1fa794, com 0x136460, imp global
>> destruction):
>> FLAGS 0x100215: COMSET Active Warn PrintWarn AutoCommit=20
>> PARENT undef
>> KIDS 1 (1 Active)
>> IMP_DATA undef
>> dbih_clearcom 0x1fa794 (com 0x136460, type 1) done.
>>=20
>> ! >> DESTROY DISPATCH (DBI::dr=3DHASH(0x1fa794) rc1/1 @1 g0 =
ima4
>> pid#5169) during global destruction
>> ! <> DESTROY for DBI::dr=3DHASH(0x1fa794) ignored (inner handle =
gone)
>> ! >> DESTROY DISPATCH (DBI::db=3DHASH(0x25300c) rc1/1 @1 g0 =
ima4
>> pid#5169) during global destruction
>> ! <> DESTROY for DBI::db=3DHASH(0x25300c) ignored (inner handle =
gone)
>>=20
>>=20
>>=20
RE: Numeric value out of range error
am 29.09.2005 07:14:51 von jurlwin
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
=20
> -----Original Message-----
> From: Brian Becker [mailto:bbecker@Jaeb.org]=20
> 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
>=20
> Sorry you were missing the outcome on that whole issue. We=20
> decided to take off the odbc_default_bind_type that we had=20
> set to 12 and returned it back to 0 to let the DBD do the=20
> SQLDescribeParam as it was designed.
> We also applied the patch that you sent previously which does=20
> address the issue. =20
>=20
> The issue with dates is just that it has to be in the=20
> specific format (is 9075) which is a problem for us but=20
> unrelated to the issue at hand...we are simply describing it=20
> as varchar and letting the database figure it out on its own.=20
> This is however why we are describing our own params.
>=20
> The SQL Describe param ordinal thing has to do with Stored=20
> procedure calls for SQL Server. The order your proc defines=20
> it attributes is the order they are described in so for example
>=20
> Create procedure someproc as
> Field1 as int
> Field2 as varchar
> ...
>=20
> If you try and call someproc @Field2=3D?, @Field1=3D? -- the=20
> fields are described incorreclty by SQLDescribeParam (Field2=20
> as int and Field1 as varchar). This seems odd as they were=20
> given the attribute names so you would think it could be=20
> matched up by name -- but it simply does it by position. =20
> This was confirmed with Microsoft.
>=20
> So to bring us back we have applied both patches -- the=20
> Isnull from a while back and this most recent one. Both were=20
> described as "hacks" and we are concerned putting these into=20
> production not knowing if these will make it into the next=20
> version or if we will need to re-apply these "hacks" to the=20
> code and risk it breaking something else in the newer=20
> versions (some variable changes name or new condition block=20
> is added or who knows what else). =20
>=20
> Hope that cleared things up.
>=20
> Brian Becker
> Jaeb Center for Health Research
> 15310 Amberly Drive
> Suite 350
> Tampa, FL 33647
> tel: 813-975-8690
> fax: 813-975-8761
> email: bbecker@jaeb.org
> http://www.jaeb.org
>=20
>=20
>=20
> -----Original Message-----
> From: Martin J. Evans [mailto:martin.evans@easysoft.com]
> 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
>=20
>=20
> 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=20
> us. Also,
> > we are trying to avoid having everything default to varchar.
>=20
> 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.
>=20
> Brian did seem to indicate at the time you were setting
> odbc_default_bind_type
> - which also prevents DBD::ODBC from calling SQLDescribeParam.
>=20
> > Is it
> > possible the patch you are suggesting will be implemented=20
> in a future
> > release of the DBD::ODBC module?
>=20
> 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.
>=20
> Martin
> --
> Martin J. Evans
> Easysoft Ltd, UK
> Development
>=20
>=20
> > Lee Anne
> >=20
> > -----Original Message-----
> > From: Martin J. Evans [mailto:martin.evans@easysoft.com]=20
> > 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
> >=20
> > On 27-Sep-2005 Lee Anne Lester wrote:
> >> I am getting a "Numeric value out of range error" when=20
> specifically=20
> >> binding a placeholder for a decimal column as a decimal type. I'm=20
> >> 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=20
> as long as
> >> there are no numbers to the left of the decimal. Example: A value
> of
> >> .5 works.
> >>=20
> >> Can anyone advise how to make this work? Is there a way to define
> the
> >=20
> >> precision and scale when calling bind_param?
> >>=20
> >> Thanks in advance for any help.
> >>=20
> >> Lee Anne Lester
> >>=20
> >>=20
> > SQL> create table jaeb (FieldDecimal numeric(5,2)) insert into jaeb=20
> > SQL> values ('10.00') insert into jaeb values (10.00) select * from
> jaeb
> > +-------------+
> >| FieldDecimal|
> > +-------------+
> >| 10.00 |
> >| 10.00 |
> > +-------------+
> > SQLRowCount returns 2
> > 2 rows fetched
> >=20
> > So you can insert without binding OK but as you say:
> >=20
> >#!/usr/local/bin/perl -w
> > use DBI;
> > my $dbh =3D DBI->connect('dbi:ODBC:test', 'Martin_Evans', =
'easysoft');
> my
> > $sql =3D q/insert into jaeb values (?)/; my $sth =
> $dbh->prepare($sql);
> > $sth->bind_param(1, 10.00, {TYPE=3D>3}); $sth->execute();
> >=20
> > fails.
> >=20
> > Changing the bind_param to remove the forcing the type to=20
> SQL_NUMERIC
> >=20
> > $sth->bind_param(1, 10.00);
> >=20
> > appears to insert correctly but a string "10" is still=20
> 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.
> >=20
> > The actual problem appears to be:
> >=20
> >=20
> SQLBindParameter(0x8258ea0,1,1(Input::),1,3,5,5,0x82580f0,5, 0x82580d0)
> >=20
> > i.e. bind parameter 1 as input, ValueType=3DSQL_C_CHAR,
> > ParameterType=3DSQL_NUMERIC, ColumnSize=3D5, DecimalDigits=3D5. This
> instructs
> > MS SQL Server to convert your SQL_C_CHAR string into a numeric 5,5
> > before inserting - this will not fit.
> >=20
> > Interestingly, your trace shows "cbColDef=3D2, scale=3D2," and I =
cannot
> > reproduce this.
> >=20
> > 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.
> >=20
> > 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=20
> but it is
> a
> > bit of a hack:
> >=20
> > case SQL_TIME:
> > case SQL_TYPE_TIME:
> > /* fSqlType =3D SQL_VARCHAR;*/
> > break;
> > + case SQL_NUMERIC:
> > + case SQL_DECIMAL:
> > + =20
> > + if (phs->sv_buf && *phs->sv_buf) {
> > + char *cp;
> > + cp =3D strchr(phs->sv_buf, '.');
> > + if (cp) {
> > + ibScale =3D 0;
> > + cp++;
> > + while (*cp !=3D '\0' && isdigit(*cp)) {
> > + cp++;
> > + ibScale++;
> > + }
> > + }
> > + }
> > + break;
> > case SQL_TIMESTAMP:
> > case SQL_TYPE_TIMESTAMP:
> >=20
> >=20
> > Why not just pass your parameters in as strings and leave the
> parameter
> > type off the bind call.
> >=20
> > Martin
> > --
> > Martin J. Evans
> > Easysoft Ltd, UK
> > Development
> >=20
> >=20
> >> SAMPLE CODE
> >> =============3D
> >>#!/usr/local/bin/perl -Tw
> >>=20
> >> use strict;
> >> use DBI;
> >>=20
> >> my $dsn =3D "dbi:ODBC:dsn_IRB";
> >>=20
> >> our $dbh =3D DBI->connect($dsn, '', '', {AutoCommit=3D>0,=20
> RaiseError=3D>1,=20
> >> FetchHashKeyName=3D>'NAME_uc'});
> >>=20
> >> unlink('dbitrace.log') if(-e 'dbitrace.log');
> >>=20
> >> DBI->trace(9, 'dbitrace.log');
> >>=20
> >> my $sth =3D $dbh->prepare(q{INSERT INTO tblDecimalTest=20
> (FieldDecimal)=20
> >> VALUES (?)});
> >>=20
> >> $sth->bind_param(1, 10.00, {TYPE=3D>3} );
> >>=20
> >> $sth->execute();
> >>=20
> >> $sth->finish;
> >>=20
> >> $dbh->commit;
> >>=20
> >> $dbh->disconnect;
> >> ~
> >>=20
> >>=20
> >>=20
> >> VERSIONS
> >> =========3D
> >>=20
> >> Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)=20
> >> May 3 2005 23:18:38=20
> >> Copyright (c) 1988-2003 Microsoft Corporation
> >> Standard Edition on Windows NT 5.0 (Build 2195:=20
> Service Pack 4)
> >>=20
> >> unixODBC version: 2.2.7 using EasySoft ODBC bridge
> >>=20
> >> Perl Version =3D 5.008007
> >> DBI Version =3D 1.48
> >> DBD::ODBC Version =3D 1.13
> >>=20
> >>=20
> >> OUTPUT FROM TRACE - LEVEL 9
> >> =
==================== =====3D=
===3D
> >> DBI 1.48-nothread default trace level set to 0x0/9 (pid 5169)
> >> >> prepare DISPATCH (DBI::db=3DHASH(0x25300c) rc1/1 @2 g0
> > ima2201
> >> pid#5169) at ./insert.pl line 14
> >> -> prepare for DBD::ODBC::db (DBI::db=3DHASH(0x25300c)~0x260a18 =
> >> 'INSERT INTO tblDecimalTest (FieldDecimal) VALUES (?)')
> >> New DBI::st (for DBD::ODBC::st, =
parent=3DDBI::db=3DHASH(0x260a18),
> > id=3D)
> >> =20
> =
dbih_setup_handle(DBI::st=3DHASH(0x260b20)=3D>DBI::st=3DHASH (0x1b9d48),
> >> DBD::ODBC::st, 260b2c, Null!)
> >> dbih_make_com(DBI::db=3DHASH(0x260a18), 136f30,=20
> DBD::ODBC::st, 212,
>=20
> >> 0) thr#0
> >> dbih_setup_attrib(DBI::st=3DHASH(0x1b9d48), Err,
> >> DBI::db=3DHASH(0x260a18)) SCALAR(0x1face0) (already defined)
> >> dbih_setup_attrib(DBI::st=3DHASH(0x1b9d48), State,
> >> DBI::db=3DHASH(0x260a18)) SCALAR(0x1fad40) (already defined)
> >> dbih_setup_attrib(DBI::st=3DHASH(0x1b9d48), Errstr,
> >> DBI::db=3DHASH(0x260a18)) SCALAR(0x1fad10) (already defined)
> >> dbih_setup_attrib(DBI::st=3DHASH(0x1b9d48), TraceLevel,
> >> DBI::db=3DHASH(0x260a18)) 0 (already defined)
> >> dbih_setup_attrib(DBI::st=3DHASH(0x1b9d48), FetchHashKeyName,
> >> DBI::db=3DHASH(0x260a18)) 'NAME_uc' (already defined)
> >> dbih_setup_attrib(DBI::st=3DHASH(0x1b9d48), HandleSetErr,
> >> DBI::db=3DHASH(0x260a18)) undef (not defined)
> >> dbih_setup_attrib(DBI::st=3DHASH(0x1b9d48), HandleError,
> >> DBI::db=3DHASH(0x260a18)) undef (not defined)
> >> initializing sth query timeout to 0
> >> ignore named placeholders =3D 0
> >> dbd_preparse scanned 1 distinct placeholders
> >> SQLPrepare returned 0
> >>=20
> >> dbd_st_prepare'd sql f2787256, ExecDirect=3D0
> >> INSERT INTO tblDecimalTest (FieldDecimal) VALUES (?)
> >> <- prepare=3D DBI::st=3DHASH(0x260b20) at ./insert.pl line 14
> >> >> bind_param DISPATCH (DBI::st=3DHASH(0x260b20) rc1/1=20
> @4 g0 ima1
> >> pid#5169) at ./insert.pl line 16
> >> -> bind_param for DBD::ODBC::st=20
> (DBI::st=3DHASH(0x260b20)~0x1b9d48
> 1
> >=20
> >> 10 HASH(0x1209b8)) bind 1 <== '10' (attribs:=20
> HASH(0x1209b8)), type 3
> >> <- bind_param=3D 1 at ./insert.pl line 16
> >> >> execute DISPATCH (DBI::st=3DHASH(0x260b20) rc1/1 @1 g0
> > ima1041
> >> pid#5169) at ./insert.pl line 18
> >> -> execute for DBD::ODBC::st =
(DBI::st=3DHASH(0x260b20)~0x1b9d48)
> >> dbd_st_execute (outparams =3D 0)...
> >> bind 1 <== 10 (size 2/3/0, ptype 6, otype 1, sqltype 3) bind 1 =
<===20
> >> '10' (len 2/2, null 0)
> >> bind 1: CTy=3D1, STy=3DDECIMAL, CD=3D2, Sc=3D2, VM=3D2.
> >> SQLBindParameter: idx =3D 1: fParamType=3D1, name=3D1, =
fCtype=3D1,=20
> >> SQL_Type =3D 3, cbColDef=3D2, scale=3D2, rgbValue =3D 13ddc0, =
cbValueMax=3D2,
> > cbValue =3D 2
> >> Param value =3D 10
> >> rebind check char Param 1 (10)
> >> dbd_st_execute (for hstmt 2787256 before)...
> >> dbd_st_execute (for hstmt 2787256 after, rc =3D -1)...
> >> dbd_error: err_rc=3D-1 rc=3D0 s/d/e: 2787256/2497728/2496296
> >> dbd_error: SQL-22003 (native 0): [unixODBC][Microsoft][ODBC SQL
> Server
> >=20
> >> Driver]Numeric value out of range (SQL-22003)
> >> dbd_error: err_rc=3D-1 rc=3D0 s/d/e: 0/2497728/2496296
> >> dbd_error: err_rc=3D-1 rc=3D0 s/d/e: 0/0/2496296 =
st_execute/SQLExecute=20
> >> error -1 recorded: [unixODBC][Microsoft][ODBC SQL Server=20
> >> Driver]Numeric value out of range (SQL-22003)(DBD:
> >> st_execute/SQLExecute err=3D-1)
> >> !! ERROR: -1 '[unixODBC][Microsoft][ODBC SQL Server
> Driver]Numeric
> >=20
> >> value out of range (SQL-22003)(DBD: st_execute/SQLExecute =
err=3D-1)'
> >> (err#0)
> >> <- execute=3D undef at ./insert.pl line 18
> >> >> DESTROY DISPATCH (DBI::st=3DHASH(0x260b20) rc1/1=20
> @1 g0 ima4
> >> pid#5169)
> >> <> DESTROY(DBI::st=3DHASH(0x260b20)) ignored for outer handle
> (inner
> >> DBI::st=3DHASH(0x1b9d48) has ref cnt 1)
> >> >> DESTROY DISPATCH (DBI::st=3DHASH(0x1b9d48) rc1/1=20
> @1 g0 ima4
> >> pid#5169)
> >> -> DESTROY for DBD::ODBC::st (DBI::st=3DHASH(0x1b9d48)~INNER)
> >> SQLFreeStmt called, returned 0.
> >> ERROR: -1 '[unixODBC][Microsoft][ODBC SQL Server
> Driver]Numeric
> >=20
> >> value out of range (SQL-22003)(DBD: st_execute/SQLExecute =
err=3D-1)'
> >> (err#0)
> >> <- DESTROY=3D undef
> >> DESTROY (dbih_clearcom) (sth 0x260b20, com 0x2a8690, imp
> >> DBD::ODBC::st):
> >> FLAGS 0x180191: COMSET Warn RaiseError PrintError PrintWarn=20
> >> PARENT DBI::db=3DHASH(0x260a18)
> >> KIDS 0 (0 Active)
> >> IMP_DATA undef
> >> NUM_OF_FIELDS -1
> >> NUM_OF_PARAMS 1
> >> dbih_clearcom 0x260b20 (com 0x2a8690, type 3) done.
> >>=20
> >> -- DBI::END
> >> >> disconnect_all DISPATCH (DBI::dr=3DHASH(0x1fa794) rc1/4 @1 =
g0
> >> ima801 pid#5169) at
> >> /usr/perl5.8.7/lib/site_perl/5.8.7/sun4-solaris/DBI.pm=20
> line 677 via=20
> >> ./insert.pl line 0
> >> -> disconnect_all for DBD::ODBC::dr
> >> (DBI::dr=3DHASH(0x1fa794)~0x25303c)
> >> <- disconnect_all=3D '' at
> >> /usr/perl5.8.7/lib/site_perl/5.8.7/sun4-solaris/DBI.pm=20
> line 677 via=20
> >> ./insert.pl line 0
> >> ! >> DESTROY DISPATCH (DBI::db=3DHASH(0x260a18) rc1/1=20
> @1 g0 ima4
> >> pid#5169) during global destruction
> >> ! -> DESTROY for DBD::ODBC::db (DBI::db=3DHASH(0x260a18)~INNER)
> >> ** auto-rollback due to disconnect without commit returned 1
> >> DBD::ODBC Disconnected!
> >> ERROR: -1 '[unixODBC][Microsoft][ODBC SQL Server
> Driver]Numeric
> >=20
> >> value out of range (SQL-22003)(DBD: st_execute/SQLExecute =
err=3D-1)'
> >> (err#0)
> >> ! <- DESTROY=3D undef during global destruction
> >> DESTROY (dbih_clearcom) (dbh 0x25300c, com 0x136f30, imp global
> >> destruction):
> >> FLAGS 0x180191: COMSET Warn RaiseError PrintError PrintWarn=20
> >> PARENT DBI::dr=3DHASH(0x25303c)
> >> KIDS 0 (0 Active)
> >> IMP_DATA undef
> >> dbih_clearcom 0x25300c (com 0x136f30, type 2) done.
> >>=20
> >> ! >> DESTROY DISPATCH (DBI::dr=3DHASH(0x25303c) rc1/1=20
> @1 g0 ima4
> >> pid#5169) during global destruction
> >> ! -> DESTROY in DBD::_::common for DBD::ODBC::dr
> >> (DBI::dr=3DHASH(0x25303c)~INNER)
> >> ! <- DESTROY=3D undef during global destruction
> >> DESTROY (dbih_clearcom) (drh 0x1fa794, com 0x136460, imp global
> >> destruction):
> >> FLAGS 0x100215: COMSET Active Warn PrintWarn AutoCommit=20
> >> PARENT undef
> >> KIDS 1 (1 Active)
> >> IMP_DATA undef
> >> dbih_clearcom 0x1fa794 (com 0x136460, type 1) done.
> >>=20
> >> ! >> DESTROY DISPATCH (DBI::dr=3DHASH(0x1fa794) rc1/1=20
> @1 g0 ima4
> >> pid#5169) during global destruction
> >> ! <> DESTROY for DBI::dr=3DHASH(0x1fa794) ignored (inner=20
> handle gone)
> >> ! >> DESTROY DISPATCH (DBI::db=3DHASH(0x25300c) rc1/1=20
> @1 g0 ima4
> >> pid#5169) during global destruction
> >> ! <> DESTROY for DBI::db=3DHASH(0x25300c) ignored (inner=20
> handle gone)
> >>=20
> >>=20
> >>=20
>=20