Database join - Foreign key allowed as null - Fabrik form does not work properly

[Patrickios]

Fabrik 2.1, Joomla 1.5.24, Windows XP(dev machine), MySql 5.1

The database design supports a Foreign key from TableA into TableB, that also allows it to have a NULL value.

The Fabrik Form is configured to render as <drop-down> the FKey, and works well when the field does have a value.
When there is NO value for the Fkey field, there is an error with the SQL statement -- upon SAVE -- and saving does not work.
The generated SQL statement is

UPDATE tableA SET fieldAtoFK = ''
instead of ...
UPDATE tableA SET fieldAtoFK = null

An empty string appears in the SQL stmt instead of the field with a NULL value.
Is there a work-around for this?

Thanks

 

[cheesegrits]

What is the actual MySQL error you are seeing?

Sounds like a MySQL "strict mode" issue to me, which is barfing about trying to set an INT field to an empty string.

Fabrik almost certainly won't work in "strict mode". Try turning that off. Find your my.ini file (usually somewhere like C:\Program Files\MySQL\MySQL Server 5.1\my.ini, unless you are running XAMPP or similar, in which case it'd be in your XAMPP root, somewhere like mysql/bin), and see if you have any lines that look like this:

# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

... and if so, comment out the sql-mode line (by inserting a # in front of it), and restart your MySQL service.

For some reason, MySQL sets strict mode by default when installing on Windows, but not when installing on Linux. And strict mode really doesn't buy you anything, unless you are a Fortune 500 company handling batched transactions, etc.

-- hugh

 

[Patrickios]

Thanks for the response.
I have tried removing all settings from SQL-MODE ? no help.
Looking carefully I have identified two issues, related to each other:
A> When we have in a table a column that can be NULL and is also UNIQUE, within a Fabrik Form, upon an INSERT or UPDATE the underlying sql code sets that field value to ?? (empty string).
This is both incorrect and causes problems on the 2nd attempt (since in the first one the column contains the empty string ? on the second the UNIQUE constraint is violated)
B> When we have a table with a field that can be NULL, but it is also a FOREIGN key to another table ? When that field has NO VALUE, the Fabrik Form INSERT/UPDATE generates an SQL that sets the value to ?? (empty string).
SOLUTIONS
==========
I. Either allow at ELEMENT level to set the field as NULLABLE, and instead of generating the value of ??replace with NULL
II. Let Fabrik generate the empty string, but make MySql simulate such behavior that an empty string to be translated to a NULL (this is what ? for better or for worse ? the Oracle database does).
Now I tried to find work arounds to Fabrik by either using Form -> Submission plugins (and tried to replace the ?? to null), or
Element -> Validation ? Match or Replace
In both cases with no luck ? not sure if it is possible
The last alternative is to write my own SAVE code ? but I am not sure how this is done and from where.