Thread: FW: ERROR: Bad numeric input format '.' on insert or update of numeric column with a zero value
FW: ERROR: Bad numeric input format '.' on insert or update of numeric column with a zero value
From
"Steve Johnson"
Date:
ODBC driver version: 07.03.0100 (ODBC 3 driver)
PostgreSQL database version: 7.3.2
Error message: ERROR: Bad numeric input format '.'
When I attempt to add or update a a row with a numeric(15,2)
column with a value of 0 (zero) or null, I get this error
message. If I change the value to anything else (.01, 1000), the
insert and update work.
Using psql, I am able to insert and update rows with numeric
values of 0 without an error.
If I drop back to the psqlodbc legacy 2.5 driver, I do not get
the error.
I get the error when using the Unicode (beta) driver and the ODBC
3 driver.
My application uses VB6 ADODB recordsets with MDAC 2.7.
For a test case, it should be pretty easy to duplicate. Create a
table with one numeric(15,2) numeric column. Use psql to insert
a row with a value of zero for the column. Now try to do the
same thing through psqlODBC ODBC 3.0 driver and you'll get the
error. If you try to update that row in that table with a value
of zero, you'll get the error as well.
The partial ODBC trace log starting with the problem column:
midb 144-1a0 ENTER SQLBindParameter
HSTMT 055C2C70
UWORD 4
SWORD 1 <SQL_PARAM_INPUT>
SWORD 2 <SQL_C_NUMERIC>
SWORD 2 <SQL_NUMERIC>
SQLULEN 15
SWORD 2
PTR 0x05074BA8
SQLLEN 19
SQLLEN * 0x053A970C
midb 144-1a0 EXIT SQLBindParameter with return code
0 (SQL_SUCCESS)
HSTMT 055C2C70
UWORD 4
SWORD 1 <SQL_PARAM_INPUT>
SWORD 2 <SQL_C_NUMERIC>
SWORD 2 <SQL_NUMERIC>
SQLULEN 15
SWORD 2
PTR 0x05074BA8
SQLLEN 19
SQLLEN * 0x053A970C (19)
midb 144-1a0 ENTER SQLSetDescFieldW
SQLHDESC 055C2DD4
SQLSMALLINT 4
SQLSMALLINT 1005 <SQL_DESC_PRECISION>
SQLPOINTER 0x0000000F
SQLINTEGER 0
midb 144-1a0 ENTER SQLSetDescField
SQLHDESC 055C2DD4
SQLSMALLINT 4
SQLSMALLINT 1005 <SQL_DESC_PRECISION>
SQLPOINTER 0x0000000F
SQLINTEGER 0
midb 144-1a0 EXIT SQLSetDescField with return code 0
(SQL_SUCCESS)
SQLHDESC 055C2DD4
SQLSMALLINT 4
SQLSMALLINT 1005 <SQL_DESC_PRECISION>
SQLPOINTER 0x0000000F (BADMEM)
SQLINTEGER 0
midb 144-1a0 EXIT SQLSetDescFieldW with return code
0 (SQL_SUCCESS)
SQLHDESC 055C2DD4
SQLSMALLINT 4
SQLSMALLINT 1005 <SQL_DESC_PRECISION>
SQLPOINTER 0x0000000F (BADMEM)
SQLINTEGER 0
midb 144-1a0 ENTER SQLSetDescFieldW
SQLHDESC 055C2DD4
SQLSMALLINT 4
SQLSMALLINT 1006 <SQL_DESC_SCALE>
SQLPOINTER 0x00000002
SQLINTEGER 0
midb 144-1a0 ENTER SQLSetDescField
SQLHDESC 055C2DD4
SQLSMALLINT 4
SQLSMALLINT 1006 <SQL_DESC_SCALE>
SQLPOINTER 0x00000002
SQLINTEGER 0
midb 144-1a0 EXIT SQLSetDescField with return code 0
(SQL_SUCCESS)
SQLHDESC 055C2DD4
SQLSMALLINT 4
SQLSMALLINT 1006 <SQL_DESC_SCALE>
SQLPOINTER 0x00000002 (BADMEM)
SQLINTEGER 0
midb 144-1a0 EXIT SQLSetDescFieldW with return code
0 (SQL_SUCCESS)
SQLHDESC 055C2DD4
SQLSMALLINT 4
SQLSMALLINT 1006 <SQL_DESC_SCALE>
SQLPOINTER 0x00000002 (BADMEM)
SQLINTEGER 0
midb 144-1a0 ENTER SQLSetDescFieldW
SQLHDESC 055C2DD4
SQLSMALLINT 4
SQLSMALLINT 1010 <SQL_DESC_DATA_PTR>
SQLPOINTER 05074BA8
SQLINTEGER 0
midb 144-1a0 ENTER SQLSetDescField
SQLHDESC 055C2DD4
SQLSMALLINT 4
SQLSMALLINT 1010 <SQL_DESC_DATA_PTR>
SQLPOINTER 05074BA8
SQLINTEGER 0
midb 144-1a0 EXIT SQLSetDescField with return code 0
(SQL_SUCCESS)
SQLHDESC 055C2DD4
SQLSMALLINT 4
SQLSMALLINT 1010 <SQL_DESC_DATA_PTR>
SQLPOINTER 05074BA8
SQLINTEGER 0
midb 144-1a0 EXIT SQLSetDescFieldW with return code
0 (SQL_SUCCESS)
SQLHDESC 055C2DD4
SQLSMALLINT 4
SQLSMALLINT 1010 <SQL_DESC_DATA_PTR>
SQLPOINTER 05074BA8
SQLINTEGER 0
midb 144-1a0 ENTER SQLBindParameter
HSTMT 055C2C70
UWORD 5
SWORD 1 <SQL_PARAM_INPUT>
SWORD 1 <SQL_C_CHAR>
SWORD 12 <SQL_VARCHAR>
SQLULEN 4096
SWORD 0
PTR 0x05074BBC
SQLLEN 4096
SQLLEN * 0x053A9714
midb 144-1a0 EXIT SQLBindParameter with return code
0 (SQL_SUCCESS)
HSTMT 055C2C70
UWORD 5
SWORD 1 <SQL_PARAM_INPUT>
SWORD 1 <SQL_C_CHAR>
SWORD 12 <SQL_VARCHAR>
SQLULEN 4096
SWORD 0
PTR 0x05074BBC
SQLLEN 4096
SQLLEN * 0x053A9714 (0)
midb 144-1a0 ENTER SQLBindParameter
HSTMT 055C2C70
UWORD 6
SWORD 1 <SQL_PARAM_INPUT>
SWORD 1 <SQL_C_CHAR>
SWORD 12 <SQL_VARCHAR>
SQLULEN 15
SWORD 0
PTR 0x05075BC0
SQLLEN 15
SQLLEN * 0x053A971C
midb 144-1a0 EXIT SQLBindParameter with return code
0 (SQL_SUCCESS)
HSTMT 055C2C70
UWORD 6
SWORD 1 <SQL_PARAM_INPUT>
SWORD 1 <SQL_C_CHAR>
SWORD 12 <SQL_VARCHAR>
SQLULEN 15
SWORD 0
PTR 0x05075BC0
SQLLEN 15
SQLLEN * 0x053A971C (5)
midb 144-1a0 ENTER SQLBindParameter
HSTMT 055C2C70
UWORD 7
SWORD 1 <SQL_PARAM_INPUT>
SWORD 1 <SQL_C_CHAR>
SWORD 11 <SQL_TIMESTAMP>
SQLULEN 23
SWORD 0
PTR 0x05075BD0
SQLLEN 23
SQLLEN * 0x053A9724
midb 144-1a0 EXIT SQLBindParameter with return code
0 (SQL_SUCCESS)
HSTMT 055C2C70
UWORD 7
SWORD 1 <SQL_PARAM_INPUT>
SWORD 1 <SQL_C_CHAR>
SWORD 11 <SQL_TIMESTAMP>
SQLULEN 23
SWORD 0
PTR 0x05075BD0
SQLLEN 23
SQLLEN * 0x053A9724 (19)
midb 144-1a0 ENTER SQLBindParameter
HSTMT 055C2C70
UWORD 8
SWORD 1 <SQL_PARAM_INPUT>
SWORD 1 <SQL_C_CHAR>
SWORD 11 <SQL_TIMESTAMP>
SQLULEN 23
SWORD 0
PTR 0x05075BE8
SQLLEN 23
SQLLEN * 0x053A972C
midb 144-1a0 EXIT SQLBindParameter with return code
0 (SQL_SUCCESS)
HSTMT 055C2C70
UWORD 8
SWORD 1 <SQL_PARAM_INPUT>
SWORD 1 <SQL_C_CHAR>
SWORD 11 <SQL_TIMESTAMP>
SQLULEN 23
SWORD 0
PTR 0x05075BE8
SQLLEN 23
SQLLEN * 0x053A972C (19)
midb 144-1a0 ENTER SQLExecute
HSTMT 055C2C70
midb 144-1a0 EXIT SQLExecute with return code -1
(SQL_ERROR)
HSTMT 055C2C70
DIAG [S1000] ERROR: Bad numeric input format '.' (7)
midb 144-1a0 ENTER SQLGetDiagRecW
SQLSMALLINT 3
SQLHANDLE 055C2C70
SQLSMALLINT 1
SQLWCHAR * 0x0012F074 (NYI)
SQLINTEGER * 0x0012F098
SQLWCHAR * 0x0012EC74 (NYI)
SQLSMALLINT 512
SQLSMALLINT * 0x0012F09C
midb 144-1a0 EXIT SQLGetDiagRecW with return code 0
(SQL_SUCCESS)
SQLSMALLINT 3
SQLHANDLE 055C2C70
SQLSMALLINT 1
SQLWCHAR * 0x0012F074 (NYI)
SQLINTEGER * 0x0012F098 (7)
SQLWCHAR * 0x0012EC74 (NYI)
SQLSMALLINT 512
SQLSMALLINT * 0x0012F09C (36)
midb 144-1a0 ENTER SQLGetDiagRecW
SQLSMALLINT 3
SQLHANDLE 055C2C70
SQLSMALLINT 2
SQLWCHAR * 0x0012F074 (NYI)
SQLINTEGER * 0x0012F098
SQLWCHAR * 0x0012EC74 (NYI)
SQLSMALLINT 512
SQLSMALLINT * 0x0012F09C
midb 144-1a0 EXIT SQLGetDiagRecW with return code
100 (SQL_NO_DATA_FOUND)
SQLSMALLINT 3
SQLHANDLE 055C2C70
SQLSMALLINT 2
SQLWCHAR * 0x0012F074 (NYI)
SQLINTEGER * 0x0012F098
SQLWCHAR * 0x0012EC74 (NYI)
SQLSMALLINT 512
SQLSMALLINT * 0x0012F09C
This bug may be fixed in cvs.
Please try the snapshot dll at
http://www.geocities.jp/inocchichichi/psqlodbc/ .
regards,
Hiroshi Inoue
http://www.geocities.jp/inocchichichi/psqlodbc/
Steve Johnson wrote:
>
> ODBC driver version: 07.03.0100 (ODBC 3 driver)
> PostgreSQL database version: 7.3.2
> Error message: ERROR: Bad numeric input format '.'
>
> When I attempt to add or update a a row with a numeric(15,2)
> column with a value of 0 (zero) or null, I get this error
> message. If I change the value to anything else (.01, 1000), the
> insert and update work.
>
> Using psql, I am able to insert and update rows with numeric
> values of 0 without an error.
>
> If I drop back to the psqlodbc legacy 2.5 driver, I do not get
> the error.
>
> I get the error when using the Unicode (beta) driver and the ODBC
> 3 driver.
>
> My application uses VB6 ADODB recordsets with MDAC 2.7.
>
> For a test case, it should be pretty easy to duplicate. Create a
> table with one numeric(15,2) numeric column. Use psql to insert
> a row with a value of zero for the column. Now try to do the
> same thing through psqlODBC ODBC 3.0 driver and you'll get the
> error. If you try to update that row in that table with a value
> of zero, you'll get the error as well.
>
> The partial ODBC trace log starting with the problem column:
>