1. Fabrik 3.9 has been released. If you have updated Joomla to 3.9, this is a required update.
    Dismiss Notice

Store row failed: ERROR

Discussion in 'Community' started by Bauer, Jan 29, 2013.

  1. Bauer

    Bauer Well-Known Member

    Level: Community
    I posted this a few weeks back and had no response - then now realize I posted it in the wrong (2.x) forum.

    I?m getting an error whenever I update a form that contains 3 joined groups.

    The system error message that is generated is something like...
    Store row failed: Update blah blah blah WHERE
    (i.e. The sql command ends at WHERE)

    Looking into this I see that problem is in components/com_fabrik/models/list.php - function updateObject()

    The 1st if statement continues the foreach loop if the field is an Internal or NA field. That is why the error occurs. Because if the current $v is an array field type (like internalid or databasejoin), BUT is also the keyName needed for updating the record, then the $where value is never initialized correctly.

    You need to add another check in that 1st if statement to check if it is the keyName, and if so, store the value of $v[0] to $v and allow the next if statement to do its thing (initialize $where), else "continue" ONLY if it?s not the keyName.

    I (again) updated from GitHub just before posting this.

    I may be missing something, but I changed that 1st "if" condition as shown below and it seems to work ok.

    PHP:
                if (is_array($v) or is_object($v) or $k[0] == '_')
                {
                    // Internal or NA field
                    if ($k == $keyName)
                    {                
                      $v = $v[0];
                    }
                    else
                    {
                        continue;
                    }  
            if ($k == $keyName)
            {
                // PK not to be updated
                $where = $keyName . '=' . $db->quote($v);
                continue;
            }  
    ETC....
                }
     
  2. Bauer

    Bauer Well-Known Member

    Level: Community
    I still had issues with this 3-group form. I'll explain.

    The form was from a list named `fb_member_details`. The forms are all linked on user_id (in this example '68'). The 2 joins are as such?

    left JOIN cly3w_akeebasubs_users ON fb_member_details.user_id = cly3w_akeebasubs_users.user_id

    left JOIN cly3w_users ON fb_member_details.user_id = cly3w_users.id

    The first join was not updating correctly when the form was submitted.
    All the rest of the changed form data was included in the query BUT the primary key was being overwritten with the primary key value (id) of the original `fb_member_details` table.

    By dumping the actual values of the query being set in line 6476 of in list.php updateObject() function

    $db->setQuery(sprintf($fmtsql, implode(",", $tmp), $where));

    I was able to see the queries as they were created. Here are the actual UPDATE queries that were being set ?

    UPDATE `fb_member_details` SET `zero`='0',`user_id`='68',`facility_checkboxes`='[\"1\",\"2\"]',`hospital_name`='Deaconas Hospital',`hospital_address`='1234 Pearl Rd.',`hospital_city`='Cleveland',`hospital_state`='Ohio',`hospital_zip`='44109',`hospital_autofill`='0',`hospital_cloneaddress`=NULL,`hospital_ftes`='321.0',`hospital_beds`='768',`hospital_gross_income`='4323456.00',`hospital_ownership`='Investor Owned For Profit',`is_management`='1',`hospital_type`='Specialty Hospital',`hospital_specialty_type`='Cancer',`hospital_teaching`='0',`management`='1',`nursing_name`='Fabitz Nursing Home',`nursing_ftes`='33',`nursing_beds`='1',`nursing_cloneaddress`=NULL,`nursing_address`='1234 Pearl Rd.',`nursing_city`='Cleveland',`nursing_state`='Ohio',`nursing_zip`='44109',`nursing_autofill`='',`practice_name`='JF Medical Practice',`practice_physician_count`='5',`practice_ftes`='65',`practice_cloneaddress`=NULL,`practice_address`='',`practice_city`='',`practice_state`='Ohio',`practice_zip`='',`practice_autofill`='',`practice_specialties`='[\"Family Practice\",\"Pediatrics\"]',`homehealth_name`='Jfabitz Hospice',`homehealth_services`='Home health/Aide services only',`homehealth_ftes`='44',`homehealth_cloneaddress`=NULL,`homehealth_address`='1234 Pearl Rd.',`homehealth_city`='Cleveland',`homehealth_state`='Ohio',`homehealth_zip`='44109',`homehealth_autofill`='',`notes`='',`date_time`='2013-01-29 14:55:00' WHERE id='4'

    UPDATE `cly3w_users` SET `name`='Joe Fabitz',`username`='jfabitz',`email`='jfabitz@ec.rr.com',`sendEmail`='0',`registerDate`='',`lastvisitDate`='',`activation`='',`survey_id`='1',`akeebasubs_level_id`='2',`survey_title`='Comp Circles ',`survey_description`='The CompCircles Survey',`akeebasubs_level_title`='CompCircles 6' WHERE id='68'

    UPDATE `fb_member_details` SET `zero`='0',`user_id`='68',`facility_checkboxes`='[\"1\",\"2\"]',`hospital_name`='Deaconas Hospital',`hospital_address`='1234 Pearl Rd.',`hospital_city`='Cleveland',`hospital_state`='Ohio',`hospital_zip`='44109',`hospital_autofill`='0',`hospital_cloneaddress`=NULL,`hospital_ftes`='321.0',`hospital_beds`='768',`hospital_gross_income`='4323456.00',`hospital_ownership`='Investor Owned For Profit',`is_management`='1',`hospital_type`='Specialty Hospital',`hospital_specialty_type`='Cancer',`hospital_teaching`='0',`management`='1',`nursing_name`='Fabitz Nursing Home',`nursing_ftes`='33',`nursing_beds`='1',`nursing_cloneaddress`=NULL,`nursing_address`='1234 Pearl Rd.',`nursing_city`='Cleveland',`nursing_state`='Ohio',`nursing_zip`='44109',`nursing_autofill`='',`practice_name`='JF Medical Practice',`practice_physician_count`='5',`practice_ftes`='65',`practice_cloneaddress`=NULL,`practice_address`='',`practice_city`='',`practice_state`='Ohio',`practice_zip`='',`practice_autofill`='',`practice_specialties`='[\"Family Practice\",\"Pediatrics\"]',`homehealth_name`='Jfabitz Hospice',`homehealth_services`='Home health/Aide services only',`homehealth_ftes`='44',`homehealth_cloneaddress`=NULL,`homehealth_address`='1234 Pearl Rd.',`homehealth_city`='Cleveland',`homehealth_state`='Ohio',`homehealth_zip`='44109',`homehealth_autofill`='',`notes`='',`date_time`='2013-01-29 14:55:00' WHERE id='4'

    UPDATE `cly3w_akeebasubs_users` SET `user_id`='4',`isbusiness`='1',`businessname`='Fabitz Hospital',`occupation`='Controller',`vatnumber`='',`viesregistered`='0',`taxauthority`='',`address1`='1333 Apple Court',`city`='Appleville',`state`='NC',`zip`='28433',`address2`='test',`country`='US',`params`='1',`notes`='' WHERE akeebasubs_user_id='21'

    The `cly3w_akeebasubs_users` UPDATE is the one that was messed up. It was replacing the `user_id`(the linked key) with the id from the `fb_member_details` list.

    I also thought it was strange that the list itself (`fb_member_details`) was being updated twice.( But then I thought maybe that is being done in case it contained a keysfrom one of the other forms that might have been changed.)???

    Anyhow ? looking over the code, I tried doing some debugging and came up with this?.

    In form.php processToDB() at line 1938...

    /*if ($oJoin->join_from_table . '.' . $oJoin->table_key == $origTableKey)
    {*/

    I removed the remarks to include the? if? condition and it worked.

    The cly3w_akeebasubs_users` query is now?

    UPDATE `cly3w_akeebasubs_users` SET `user_id`='68',`isbusiness`='1',`businessname`='Fabitz Hospital',`occupation`='Controller',`vatnumber`='',`viesregistered`='0',`taxauthority`='',`address1`='1333 Apple Court',`city`='Appleville',`state`='NC',`zip`='28433',`address2`='test',`country`='US',`params`='1',`notes`='' WHERE akeebasubs_user_id='21'

    AS IT SHOULD BE.
     
  3. Jaanus

    Jaanus Super Moderator

    Level: Community
    Well, my opinion is that it should stay commented out as it prevented an important feature to work:
    Just in case - take a look at http://fabrikar.com/wiki/index.php/Table_joins
    Perhaps it will help to find answer
     
  4. Bauer

    Bauer Well-Known Member

    Level: Community
    What "important feature" is that? Because if I don't use that "important feature", I will continue to tweak the code to make it work for me.

    Per your request in the reply to the visitor message that I sent to get your attention about this topic, I have attached some screen shots showing the joined lists settings - and the form itself.

    Without the change I mentioned, the "Subscriber Info" group in the form would be blank after form submission because the cly3w_akeebasubs_users___akeebasubs_user_id field was being replaced with the fb_member_details___id
     

    Attached Files:

  5. Jaanus

    Jaanus Super Moderator

    Level: Community
    I don't know anything else to suggest - try so:
    1) create list&form on cly3w_users
    2) go to join part in this list settings and create there 2 table joins:

    - from cly3w_users to fb_member_details, from id to user_id
    - from cly3w_users to cly3w_akeebasubs_users, from id to user_id

    In this way you shouldn't have problems with submissions.
     
  6. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Community
    As the comment trail on that code indicates, it's a pretty deep and twisted issue, whereby fixing one corner case breaks another corner case, and vice versa. To fix both would require some refactoring and redesigning of that chunk of code, which is one of the core functions that has grown with Fabrik for 5 years, and really needs tidying up.

    But, it's such core code, that fixing it is going to have to wait until the next major version is forked from 3.1. I'd guesstimate it'd take 3 to 6 months to fully bed in a complete redesign of that code.

    Anyway, just be aware that uncommenting those lines MAY lead to some subtle problems with other join setups. So if you do run in to other issues, see if running the stock code cures those as the first step in debugging.

    -- hugh
     
  7. Bauer

    Bauer Well-Known Member

    Level: Community
    Thanks Jaanus. The order of the grouping is the way it is for historical (hysterical) reasons.

    I'll copy out the 3 tables and try it the way you suggest. Because if it still doesn't work I will have be making a lot of changes to other related php and js code for nothing. That is only worth the effort if your suggestion fixes the issue. I'll post here the results after I make the changes.

    And cheesegrits - I understand how complex this project is. (Too complex for an old man like me to get involved.) I just wanted to explain and show proof of the issue I was having. Thanks for your reply too.
     
  8. Bauer

    Bauer Well-Known Member

    Level: Community
    That did it. Januus' suggestion worked. I guess it's a matter of learning that you have to play by the rules.:cool:

    Now to change the javascript and css to match the new form element names.:(

    PS. The reason I didn't have the user table as the parent table in the joins was because I had already created a joined Fabrik List intended just for the related Joomla Users tables (#__users, #__user_group_map, and #__usergroups). Also I was using the ExtendedReg extension, but switched last week to allow AkeebaSubs to handle new user registration.

    This seems to now be working perfect, even with 5 tables joined (but only 3 being updated). I was still able to use the same Fabrik 'Users' list - but just hide the user_group_map and usergroups groups (that was a mouthful) from normal users. A few months back that wasn't working.

    All is well - thanks!
     
  9. Bauer

    Bauer Well-Known Member

    Level: Community
    I just updated from Github. The original issue that I mentioned in the first post of this thread is still not fixed. :(
     
  10. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Community
    Well, I haven't applied that yet as I have a feeling that would simply mask a deeper issue. I can't see why a key value would still be an array by the time we get there. All actual table data should have been converted to database ready formats, via storeDatabaseFomat().

    And as I haven't seen the error you are reporting before, and nobody else has reported it, I'm reluctant to change one of the most important core functions, in case it has unintended side effects.

    So I was waiting to see if you had an Ah HAH! moment about why that key data is still an array.

    Anyway ... go ahead and run with your change for a week or so. See if you get any nasty side effects. I can't offhand see any potential gotchas, but that's why they are called "unexpected side effects". :)

    Let me know how it goes.

    Remember you can always create a private branch for your tweaked code, and merge our github updates into your branch, until I've applied it to our code. Save you the bother of having to re-apply your fix every time you update from github.

    --hugh
     

Share This Page