[Citrusdb-users] Citrusdb-users Digest, Vol 22, Issue 9

[Rich Cloutier]

Message: 3
Date: Wed, 30 Apr 2008 10:24:12 -0400
Subject: Re: [Citrusdb-users] Billing Module Query Failure
To: "For users of CitrusDB discussion"
Content-Type: text/plain; charset=ISO-8859-1
That's odd, the schema should allow the creditcard_number,
creditcard_expire, and rerun_date to be inserted as null values and
mysql puts in zeroes for you. Did you happen to use any particular
windows gui tool to import the default citrus database schema? It
looks like there may be some kind of bug that may not create the
proper schema, http://bugs.mysql.com/bug.php?id=36250
`creditcard_number` bigint(16) default NULL,
`creditcard_expire` smallint(4) unsigned zerofill default NULL,
`rerun_date` date default NULL,
Paul

The schema is correct as created with the Windows command line client
for MySQL.

To recreate the bug, do the following:

Edit the Default Billing for the Example customer and erase the contents
of the "Credit Card Expire" field.

Try to Save Changes.

I did this in yesterday's CVS version and got the following error message:

1366: Incorrect integer value: '' for column 'creditcard_expire' at row 1

Query:

UPDATE billing SET name = 'Example Customer', company = 'Example
Company', street = 'Example St', city = 'ExampleCity', state = 'ABC',
zip = '12345', country = 'USA', phone = '555-555-5555', fax =
'555-555-5556', billing_type = '7', creditcard_number =
'5000000000000001', creditcard_expire = '', billing_status = '',
next_billing_date = '2007-10-07', from_date = '2007-10-07',
payment_due_date = '2007-10-07', rerun_date = '0000-00-00', notes = '',
pastdue_exempt = 'n', po_number = '', contact_email = '' WHERE id = 1

As you can see, the program is trying to update the creditcard_expire
field with an empty string, not a zero. However note that I could not
set the variable '$creditcard_expire' to null or unset it and get the
query to work. I'm not sure why.

Since the form fields always submit strings, it seems you need to
convert them to the proper data type before executing the query. (I
tried doing this in the initialization phase, and it didn't work,
because on load, the field had a value, so was not initialized.)

Rich C.