Query error: Column count doesn't match value count at row 1

Queries and Resources
1

Hello, I try to bulk insert data to a table and I'm getting en error: message:"ER_WRONG_VALUE_COUNT_ON_ROW: Column count doesn't match value count at row 1". I don't know why? I got the error even in case I remove the last column and value.

Regarding the table, it has another two columns (id and created_at) with defaults.

Here is my query:

INSERT INTO shop_product_information (
  shop_project_id,
  shop_product_id,
  product_information_type_id,
  product_information_value
) VALUES (
{{
table_shop_feed_item.selectedRow.data.map (row => ({
  "shop_project_id": get_project_details.data.id[0],
  "shop_product_id": row.id,
  "product_information_type_id": 19,
  "product_information_value": { PARAM: input_add_variable_params.value }
}))

}})

Thanks for help.
H.

2

Hey @honzapav! If you hardcode the VALUES section, does it work? Similarly, if you create a new JS transformer query type and return the VALUES section, does it return what you'd expect?

e.g.

return {{ table_shop_feed_item.selectedRow.data.map (row => ({ "shop_project_id": get_project_details.data.id[0], "shop_product_id": row.id, "product_information_type_id": 19, "product_information_value": { PARAM: input_add_variable_params.value }}

3

Hello, yes, the hardcoded values work. The transformer also contained four properties as expected.
Screenshot 2022-10-03 at 20.07.34

I also tried to remove the last property product_information_value that contains the object to be sure it is not the problem. Even the prepared statement looks as expected...

Screenshot 2022-10-03 at 20.11.08

Thanks for any advice.

4

@victoria hello, any update on this? Thank you :pray:

5

Looking at this right now, actually :sweat_smile:

Trying to see if I can replicate your query

6

I also looked up your error and found this post:

https://stackoverflow.com/questions/18369252/column-count-doesnt-match-value-count-at-row-1

The error means that you are providing not as much data as the table wp_posts does contain columns. And now the DB engine does not know in which columns to put your data.

To overcome this you must provide the names of the columns you want to fill. Example:

insert into wp_posts (column_name1, column_name2)
values (1, 3)

Does that seem relevant to your specific case?

7

@victoria lovely, thank you – this is part of an important feature I need to implement in our app...

Regarding the stack overflow thread, I think this doesn't solve my problem because I already provide the column names. Here's my query from the original post:

INSERT INTO shop_product_information (
  shop_project_id,
  shop_product_id,
  product_information_type_id,
  product_information_value
) VALUES (
{{
table_shop_feed_item.selectedRow.data.map (row => ({
  "shop_project_id": get_project_details.data.id[0],
  "shop_product_id": row.id,
  "product_information_type_id": 19,
  "product_information_value": { PARAM: input_add_variable_params.value }
}))

}})

This query works:

INSERT INTO shop_product_information (
  shop_project_id,
  shop_product_id,
  product_information_type_id,
  product_information_value
) VALUES (
  79,
  728242,
  19,
  'PARAM'
)

This query does not work:

INSERT INTO shop_product_information (
  shop_project_id,
  shop_product_id,
  product_information_type_id,
  product_information_value
) VALUES (
{{
table_shop_feed_item.selectedRow.data.map (row => ({
  "shop_project_id": get_project_details.data.id[0],
  "shop_product_id": row['Lister product ID'],
  "product_information_type_id": 19,
  "product_information_value": "PARAM" // I tryied to send a string because I thought that the object could be a problem.
}))

}})

I think it might be because of the map function which is correct from a javascript point of view, but Retool might execute things in a different order (I came across this issue before – maybe it is similar). What do you think?

Thank you.

8

Ok, the problem was the object in product_information_value when using SQL mode. If I switch to GUI mode and also create a string from the product_information_value object, it works. Here's my final solution (actually for my use case, it is a workaround – generally not a good idea to store this data as string :wink:).

  • GUI mode enabled
  • Type: Bulk insert records

Array code

{{
table_shop_feed_item.selectedRow.data.map (row => ({
  "product_information_type_id": 19,
  "product_information_value": "{ PARAM: [" + input_add_variable_params.value + "]}",
  "shop_product_id": row['Lister product ID'],
  "shop_project_id": get_project_details.data.id[0]
}))

}}

Hope it helps anyone :slight_smile:
Thanks @victoria for help!
H.

9

:tada:that’s great! So glad to hear it. Thank you for sharing your solution here + hopefully I can be of more help next time :muscle: