Buy Now
Sign in

SQL error 1054 on saving data when repeater area was used to build array of multiple file uploads

rsearle , July 13 at 05:07
Answered
rsearle
I built a form for a customer to accept submissions of one or more file uploads. Fields are name, email, email confirm, phone (optional), message (text area), and file uploads. The file uploads are built as a single file upload field within a repeater area.
Details of the relevant design elements are:
Repeater Area: ID = area_repeater24, Data provider = 0 (because I want the list of file upload fields to start with only one of them), Enable content multiplier = Yes, Max multiplies = 8, all other values are empty or defaults.
File Field (in body of repeater area): Multi file select = No, name = file_upload[{var:area_repeater24.key}], ID = file_upload, Include value in email = Yes, Save to database = Yes, Upload to server = Yes, all other values are empty or defaults. The name includes the repeater area key as the index counter for the array, and this is doing the right thing in terms of uploading files and encoding them into the file_upload array.
Details of the relevant Setup actions are:
I set up a Modify Data action in an effort to produce a model that I could feed to the Save data action, because Save data just spat the dummy when I let is use {data:} as its data provider. The Modify data values are: Data provider = (empty), Var name = save_data, Data override =
name:{data:name}​
​email:{data:email}​
​email_confirm:{data:email_confirm}​
​phone:{data:phone}​
​message:{data:message}​
​file_upload:{data:file_upload}
I know the Modify Data action is producing the right result, because when I add a custom action to dump {var:save_data} to the form output, it gives me the array I expect:
{"name":"Joe Blow","email":"jblow@gmail.com","email_confirm":"jblow@gmail.com","phone":"","message":"test","file_upload":["20180713043240_file1.jpg","20180713043240_file2.jpg"]}
Save Data action: name_ save_data5, Enabled = Yes, Auto manage the data table = No, Model name = Data5, Database table = #_chronoforms_data_upload (structure is unremarkable), Data provider = {var:save_data}, Auto save fields = No.
I tried using {data:} as the data provider and Auto save fields = Yes, but that is giving more or less the same results as doing it this way. But I've been through a number of methods to attack this issue and the one I've described here is designed to given me maximum focus on the problem area.
When I run the form and feed it sample data, it gives me a white page with the error:
Error: 1054​
​Unknown column 'Array' in 'field list'
Observations:
From the numerous attempts I've made to solve this problem, I believe that the Save Data action is not parsing the column list for the SQL INSERT correctly. I believe that it is actually including either the Repeater Area key field, or an expression involving the repeater area key, in the list of values to be written to the database, and I think it's doing that because it is not handling the value of the File Field name correctly (i.e. file_upload[{var:area_repeater24.key}].
In some of my trials, the Save Data step failed with the error shown above, and I believe that is because it has parsed a value from the form data to end up as 'Array ...' something. In other trials, the Save Data step has failed with the same error code, but it complains that there is an unknown column '0' in 'field list', and in these tests I've caught the SQL INSERT command as attempting to write to columns named '0', 'name' 'email' ... etc. So the '0' appears to come from the Data Provider for the Repeater Area element (= 0, as noted above).
There is further support for my vague theories in the fact that the SQL errors change when I actually add columns named 'Array' and '0' to the database table. The SQL INSERT command still fails but it does not complain about either of these column names.
So I reckon the problem is in the Save Data action, which is not parsing the form design elements correctly in order to produce the SQL INSERT statement.
Can you please check this out and give me some clues?
Thanks ...
rsearle
Stop looking ... I found an answer to the issue in threads related to error 1054. I need to add file_upload/json to the Special Fields box on the Save Data action.