[Citrusdb-users] Billing Module Query Failure

[Rich Cloutier]

Can you show me the query it prints? Maybe with fake data if necessary.
Paul

No, because I fixed it. It was definitely data-related. We are still
fleshing out our data and many fields are blank still.

The initialization of many empty numeric and date fields to "" is what
caused the failure. I saw that in the CVS version you had error
reporting turned on, but for the release 1.2.2.1 version, all the
database errors are masked. Perhaps a field in config to set the debug
level would be nice in future versions. :)

Anyway, I added code in the billing module as follows, starting at line
92 of /modules/billing/edit.php:

$creditcard_number = $base->input['creditcard_number'];
if ($creditcard_number==""){$creditcard_number = 0;} // Initialization
of empty values to proper data type
$creditcard_expire = $base->input['creditcard_expire'];
if ($creditcard_expire==""){$creditcard_expire = 0;} // Initialization
of empty values to proper data type
$billing_status = $base->input['billing_status'];
$next_billing_date = $base->input['next_billing_date'];
$from_date = $base->input['from_date'];
$payment_due_date = $base->input['payment_due_date'];
$rerun_date = $base->input['rerun_date'];
if ($rerun_date==""){$rerun_date = "0000-00-00"; // Initialization of
empty values to proper data type

It may not be the most elegant way to fix this, but I'm still learning
my way around citrusdb. :)

Rich C.

[Paul Yasi]

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

Post by Rich Cloutier

Can you show me the query it prints? Maybe with fake data if necessary.
Paul

No, because I fixed it. It was definitely data-related. We are still
fleshing out our data and many fields are blank still.
The initialization of many empty numeric and date fields to "" is what
caused the failure. I saw that in the CVS version you had error
reporting turned on, but for the release 1.2.2.1 version, all the
database errors are masked. Perhaps a field in config to set the debug
level would be nice in future versions. :)
Anyway, I added code in the billing module as follows, starting at line
$creditcard_number = $base->input['creditcard_number'];
if ($creditcard_number==""){$creditcard_number = 0;} // Initialization
of empty values to proper data type
$creditcard_expire = $base->input['creditcard_expire'];
if ($creditcard_expire==""){$creditcard_expire = 0;} // Initialization
of empty values to proper data type
$billing_status = $base->input['billing_status'];
$next_billing_date = $base->input['next_billing_date'];
$from_date = $base->input['from_date'];
$payment_due_date = $base->input['payment_due_date'];
$rerun_date = $base->input['rerun_date'];
if ($rerun_date==""){$rerun_date = "0000-00-00"; // Initialization of
empty values to proper data type
It may not be the most elegant way to fix this, but I'm still learning
my way around citrusdb. :)
Rich C.
-------------------------------------------------------------------------
This SF.net email is sponsored by the 2008 JavaOne(SM) Conference
Don't miss this year's exciting event. There's still time to save $100.
Use priority code J8TL2D2.
http://ad.doubleclick.net/clk;198757673;13503038;p?http://java.sun.com/javaone
_______________________________________________
Citrusdb-users mailing list
https://lists.sourceforge.net/lists/listinfo/citrusdb-users

[Paul Yasi]

I think this may have to do with the sql_mode setting, it's may
default to something different when running under windows vs. linux.
In linux it's empty by default, and in windows it's strict.

Can you check that in the my.ini file, or by running the query: SELECT
@@global.sql_mode, @@session.sql_mode, @@sql_mode;

I thought this worked in strict, but I guess not. I'll have to change
my sql_mode and find out.

Paul

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.
Edit the Default Billing for the Example customer and erase the contents
of the "Credit Card Expire" field.
Try to Save Changes.
1366: Incorrect integer value: '' for column 'creditcard_expire' at row 1
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.
-------------------------------------------------------------------------
This SF.net email is sponsored by the 2008 JavaOne(SM) Conference
Don't miss this year's exciting event. There's still time to save $100.
Use priority code J8TL2D2.
http://ad.doubleclick.net/clk;198757673;13503038;p?http://java.sun.com/javaone
_______________________________________________
Citrusdb-users mailing list
https://lists.sourceforge.net/lists/listinfo/citrusdb-users