On Wed, 21 Jun 2006, Peter wrote:
Ok, I've been having problems with trying to use Matrix options on my IC 5.2 core/4.9 foundation based catalogs (with a fair bit of customization). Anyways, I'll list the symptoms of the problem first, then show you what else I've discovered in my attempts at solving the problem...I can change a given item to Matrix Options but can't generate the variants with the "Create all possible combinations" button in the UI. Also items that are set to matrix options cannot be added to a shopping cart (this last symptom may just be because there are no variants for the item and so IC can't find a varyant to add to the cart).Checking the error log I get the following when I click on the "Create all possible combinations" button:203.173.163.5 jqJXiIfA:203.173.163.5 - [21/June/2006:18:38:03 -0700] mr-s /cgi-bin/mr-s/admin/item_option_old.html Safe: DBD::Pg::st execute failed: ERROR: null value incolumn "sku" violates not-null constraint...followed by a long block of code which is the [perl] block contained in the [if cgi explode] block in the file include/Options/old/item_option_matrix.If I then click on the "Create all possible combinations" button again I get no errors at all and the item gets set back to simple options.So I turned debugging on, added in/uncommented a number of Debug() and ::logDebug() lines and eventually narrowed the problem down to the Vend::Table::DBI::set_row sub. I uncommented all the ::logDebug() lines in the set_row sub and got the following output in my debug log (when I click on "Create all possible combinations"):Vend::Table::DBI:debug: set_row fields='HT148-NoChoice-NoChoice,,,,,,,,,'Vend::Table::DBI:debug: set_row query=INSERT INTO options VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)Vend::Table::DBI:debug: set_row fields='HT148-NoChoice-NoChoice,,,,,,,,,'Safe: DBD::Pg::st execute failed: ERROR: null value in column "sku" violates not-null constraint...same long block of code as before here.From what I can tell, there is just one field in the @fields array which marks all the rest as undefined and DBI interprets the undefined value as NULL, so DBI is setting all those ? placeholders (barring the first) to NULL in an insert statement. This clashes with the NOT NULL field constraints in the options table.I realise that I can probably work around this by removing the NOT NULL constraints from the fields, but I'd really rather fix this problem properly. Unfortunately it looks like this may be a problem (bug?) in the set_row sub and it will require a significant rewrite to fix it. I simply do not know the code well enough to be able to rewrite it without worrying about breaking some other compatibility elsewhere.
Peter,It is a known deficiency in some Interchange database routines that when a new row is created, it's first created with only the primary key and the remaining fields NULL, and then updated to populate the fields. Obviously quite bad if you're using a well-designed database that doesn't allow NULL all over the place.
I can't remember if that has been fixed in newer versions of Interchange yet or not, but it's worth taking a look at the latest set_row and any dependent routines in CVS.
I will also note that it's pretty pointless to try to keep a tight data model in the options table when you're using the old 4.8-style options, because the table is not even remotely normalized: There are essentially three different kinds of rows crammed into the same table, and it just isn't a sane data model.
So you'd might as well just consider dropping the NOT NULL constraints on options, as the table is fairly messy anyway. If you want to do things a better way, look at the newer options introduced in (I think) Interchange 5.2, using a separate variants table.
Jon -- Jon Jensen End Point Corporation http://www.endpoint.com/ _______________________________________________ interchange-users mailing list suppressed http://www.icdevgroup.org/mailman/listinfo/interchange-users
Mail converted by mhonarc 2.6.15
This archive provided courtesy of JSW4.NET, Internet Hosting Services for Small Business.