Re: PgNumeric and NULL values

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Ivar Zarans wrote:
| On Sun, Dec 14, 2003 at 08:53:33PM -0500, Billy G. Allie wrote:
|
|
|>| Updating numeric field with values of '0.00' generates query, trying
|>| update with 'NULL'. Since column is defined as 'NOT NULL', update fails
|>
|>Can you post sample code that exhibits this behavior please.
|
|
| Creaed table as:
| create table testtable (
|     ident int not null,
|     price numeric not null
| );
|
| Inserted test values:
| insert into testtable (ident, price) values (10, 0.00);
|
| Running follofing short test:
| ====
| #!/usr/bin/env python
|
| from pyPgSQL import PgSQL
|
| connstr = '::DBname:DBuser:DBpass:'
| DBConn = PgSQL.connect(connstr)
| Cursor = DBConn.cursor()
|
| price = PgSQL.PgNumeric('0.00')
| ident = 10
|
| qry = """
| UPDATE
|     testtable
| SET
|     price = %s
| WHERE
|     ident = %s
| """
|
| Cursor.execute(qry, price, ident)
| ====
|
| throws exception:
|
| ====
| Traceback (most recent call last):
|   File "./numtest.py", line 21, in ?
|       Cursor.execute(qry, price, ident)
|   File "/usr/lib/python2.3/site-packages/pyPgSQL/PgSQL.py", line 3072,
|                 in execute
|       raise OperationalError, msg
|       libpq.OperationalError: ERROR:  ExecUpdate:
|                 Fail to add null value in not null attribute price
| ====
|
| PostgreSQL log has following query:
|
| ====
| postgres[23448]: [240-1] LOG:  query:
| postgres[23448]: [240-2] UPDATE
| postgres[23448]: [240-3]     testtable
| postgres[23448]: [240-4] SET
| postgres[23448]: [240-5]     price = NULL
| postgres[23448]: [240-6] WHERE
| postgres[23448]: [240-7]     ident = 10
| postgres[23448]: [241] ERROR:  ExecUpdate: Fail to add null value in
not null attribute price
| postgres[23448]: [242] DEBUG:  AbortCurrentTransaction
| postgres[23448]: [243] DEBUG:  StartTransactionCommand
| postgres[23448]: [244] LOG:  query: ROLLBACK WORK
| ====
|
| PostgreSQL version 7.3.4
| PyPgSQL version 2.4
| OS is Debian Woody
|

The following patch will correct the problem.  I also updated CVS on
sourceforge.

*** pyPgSQL/PgSQL.py    22 Nov 2003 05:35:47 -0000      1.38
- --- pyPgSQL/PgSQL.py    17 Dec 2003 04:49:08 -0000
***************
*** 1674,1680 ****
~            return PgNumeric(-self.__v, self.__p, self.__s)

~      def _quote(self, forArray=0):
!       if self.__v:
~            if forArray:
~                return '"%s"' % self.__fmtNumeric()
~            else:
- --- 1674,1680 ----
~            return PgNumeric(-self.__v, self.__p, self.__s)

~      def _quote(self, forArray=0):
!       if self.__v != None:
~            if forArray:
~                return '"%s"' % self.__fmtNumeric()
~            else:



- --
____       | Billy G. Allie    | Domain....: [email protected]
|  /|      | 7436 Hartwell     | MSN.......: [email protected]
|-/-|----- | Dearborn, MI 48126|
|/  |LLIE  | (313) 582-1540    |
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQE/3+JmnmIkMXoVVdURAtU8AJ9wBHD1WPT+M5ulypbBCCbgR1FLBACgkmRp
h+sD0X1dRRnYXGkTYcFkV/c=
=1mm6
-----END PGP SIGNATURE-----



-------------------------------------------------------
This SF.net email is sponsored by: IBM Linux Tutorials.
Become an expert in LINUX or just sharpen your skills.  Sign up for IBM's
Free Linux Tutorials.  Learn everything from the bash shell to sys admin.
Click now! http://ads.osdn.com/?ad_id=1278&alloc_id=3371&op=click