How to perform bulk delete

[pure lantern]

I have a Single-line text field (userData) to receive the comma separated input values(primary keys) from user and used the following query in Queries section,
UPDATE tablename SET columnName = 2 WHERE FIND_IN_SET(id,{{userData.text}});

This was working fine before, and after that, no modifications had been made to the app. But now I'm getting an error:
Error: java.lang.IllegalStateException: Parameter at position 0 is not set

How can I perform bulk delete?

[heavy oasis]

Hey There,

We've registered your query, and our team will get back to you soon.

Regards,
Team Appsmith

[heavy oasis]

Please help us with the following information for faster debugging.

Which is your current Appsmith version?
Provide screenshots/screen recordings of the original error/code snippet.
Add details about your use case and provide steps to reproduce the issue, if
possible.

[pure lantern]

We are using the latest AppSmith version V1.9.10. The issue was noticed in v1.9.9 also

[pure lantern]

Create a form and add a single-line text field to it. Then on Submit button click action execute the query snippet mentioned below: UPDATE tablename SET columnName = 2 WHERE FIND_IN_SET(id,{{userData.text}});

[heavy oasis]

Could you try turning off Use Prepared Statements and try again?

[pure lantern]

Turned off Use Prepared Statements and getting error. Please see the attached image.

[pure lantern]

Can anyone help me to fix this issue?

[heavy oasis]

Hey Akhila,

We're still looking into this issue. Thank you for your patience! :)

[heavy oasis]

Hello Akhilia,

So, I see two things here, lets address them separately.

1. Error: java.lang.IllegalStateException: Parameter at position 0 is not set

I have been trying to reproduce this without success (picture attached). If this
is a public instance (or cloud) could you please share access to the workspace
with [email protected]?

If you try to run a simple query, like the one on the attached picture do you
still get the error? Can you run that same query on a different client like
MySQL Workbench or DBeaver? How many records do you usually update on a query
like that one?

If this is a selfhosted instance, could you also attach the logs by following
this guide:
https://docs.appsmith.com/learning-and-resources/how-to-guides/how-to-get-container-logs
[https://docs.appsmith.com/learning-and-resources/how-to-guides/how-to-get-container-logs]

2. Secondary Stale Connection Error

This is a known issue that we are currently working on resolving here:
https://github.com/appsmithorg/appsmith/issues/17324
[https://github.com/appsmithorg/appsmith/issues/17324].

Since we are working on that one, it seems like it would be better to focus on
identifying what's causing the java.lang.IllegalStateException: Parameter at position 0 is not set and fixing it, or getting a workaround.

[heavy oasis]

Akhilia, I was able to reproduce the first error... Seems to be something
intermittent... In your case, does it always fail? If you create a copy of the
same query and run it does it succeed or does it fail?

[pure lantern]

Yes, am getting that error for all the times. It failed for the copy of the
same query as well.

[heavy oasis]

Could you please share the remaining requested information?

1. How many rows are you trying to update with this query?

2. Can you add [email protected] to your workspace?

3. Does this work on other clients like MySQL Workbench or DBeaver?

[pure lantern]

1. How many rows are you trying to update with this query?
   From 1 to 4 rows were tried to update. For 1 row it is working fine. For multiple comma separated values it is not working.

2. Can you add [email protected] to your workspace?
   We need to discuss with our leads and let you know.

3. Does this work on other clients like MySQL Workbench or DBeaver?

The below query is executing successfully in AppSmith itself.

UPDATE Sample SET status = 2 WHERE FIND_IN_SET(id,'2,3,4');

Tried the following queries and they are not working:
UPDATE Sample SET status = 2 WHERE FIND_IN_SET(id,'{{appsmith.store.ids}}');
UPDATE Sample SET status = 2 WHERE FIND_IN_SET(id,{{appsmith.store.ids}});

[heavy oasis]

Thank you for this information.

[heavy oasis]

Hello, I followed the steps you indicated to replicate your problem but I didn't
get the main error you mentioned: "Parameter at position 0 is not set". However,
I am getting another error message which says: " Incorrect parameter count in
the call to native function 'FIND_IN_SET' " This error occurs when passing the
IDs through the Appsmith store, but it is not consistent.

[heavy oasis]

To clarify my previous message, I started receiving this error: Incorrect
parameter count in the call to native function 'FIND_IN_SET' when configuring
row updates with {{appsmith.store.ids}} as you described. Is this the error you
are currently experiencing?

[pure lantern]

Thanks for checking. But am still receiving the same error message which I have mentioned earlier.

Executed Query: UPDATE tablename SET columnName = 2 WHERE FIND_IN_SET(id,"{{appsmith.store.ids}}");

[pure lantern]

I am able to reproduce the error which you have got, by turning OFF the Use Prepared Statements and also by removing the " " in the query.
Old query: UPDATE tablename SET columnName = 2 WHERE FIND_IN_SET(id,"{{appsmith.store.ids}}");
New query: UPDATE tablename SET columnName = 2 WHERE FIND_IN_SET(id,{{appsmith.store.ids}});