roy-n
February 26, 2016, 12:27pm
1
I am receiving the following error when performing a bulk Delete by querying on the user table for names that match a pattern:
{“code”:3027,“message”:“Unknown error: Invalid data query parameter: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘LEFT JOIN 41996AF7-6B1E-89AC-FF35-F16AE9D23C00.acl on (`41996AF7-6B1E-89AC-FF3’ at line 1”}
The URL I have is: https://api.backendless.com/v1/data/bulk/Users?where=name%20'Phil_Simms_%'
My whereclause before percent encoding is: name LIKE ‘Phil_Simms_%’
Should the single quotes also be URL encoded?
There is no word “LIKE” in your “where” argument
roy-n
February 27, 2016, 2:17pm
3
I double checked the URL and I am using https://api.backendless.com/v1/data/bulk/Users?where=name%20LIKE%20'Phil_Simms_%'
I still get the error with the “LIKE” statement
roy-n
February 27, 2016, 3:20pm
4
name%20LIKE%20%27Phil_Simms_%25%27 <<— Works on Console table SQL search field , the query is returned
In the REST Console this: name LIKE ‘Phil_Simms_%’ is URL encoded to where=name%20LIKE%20’Phil_Simms_%25’
So, single quotes are not encoded in certain cases?
It looks like console misses quotes - they should be URL encoded as well. I opened an internal ticket
roy-n
February 27, 2016, 9:58pm
6
To recap:
name LIKE ‘Phil_Simms_4%’
Backendless console SQL field encodes this to: name%20LIKE%20’Phil_Simms_4%25’
Backendless REST console encodes this to:https://api.backendless.com/v1/data/Users?where=name%20LIKE%20’Phil_Simms_4%’
When I form the URL prior to calling the Backendless Delete in bulk Query API, the URL is: https://api.backendless.com/v1/data/bulk/Users?where=name%20LIKE%20%27Phil_Simms_%25%27
To encode my where clause I am using the curl_escape(…) function. What is in question here is the single quotes and whether or not the backend receiving should or shouldn’t require or support both type of notations (single quotes or %27)
roy-n
February 27, 2016, 10:04pm
7
Depending on the outcome of requiring single quotes to not be encoded, the documentation should be updated to note this information.
roy-n
February 27, 2016, 10:22pm
8
I checked my other query statements and they are URL encoded the same way and the queries work. The problem is only in the bulk processing API.
Could you show a complete REST request you send where you get an error? Also, please include the error you’re getting.
roy-n
February 28, 2016, 3:47pm
10
When the delete in bulk query contains an equal instead of LIKE, the query with the URL encoded single quotes works.
Below is the URL and headers for the delete in bulk query with an ‘=’ that works.
https://api.backendless.com/v1/data/bulk/MyTable?where=ColumnName%3D'165C1B82-4348-9FFD-FF60-C80235547B00'
HTTP headers
roy-n
February 28, 2016, 6:11pm
11
Failure request and HTTP Headers
Below is the URL and headers for the delete in bulk query that fails
https://api.backendless.com/v1/data/bulk/Users?where=name%20LIKE%20'Phil_Simms_%'
HTTP headers
Response41996AF7-6B1E-89AC-FF35-F16AE9D23C00.acl on (`41996AF7-6B1E-89AC-FF3’ at line 1”}
mark-piller
February 28, 2016, 10:55pm
12
Thanks. I assigned it to a dev
Hi, Roy! The error you receive is not related to quotes encoding. I was able to perform successful bulk delete operations with url:https://api.backendless.com/v1/data/bulk/Users?where=name%20LIKE%20'Phil_Simms_%'
But I’ve managed to reproduce your error when I have some ACL rules for users (pressing the key icon in ACL column of Users table). That’s what were are looking into right now.
The issue (bulk delete for records with asset permissions) is fixed and will be available in the next release.
roy-n
March 1, 2016, 3:50pm
15
Thanks, when is the next release?
It should be later this week.
Hi Roy,
we already fixed an issue with URL encoding and released new version of app.
Regards,
Hi, Roy!
The fix for this issue is already released. Give it a try!
roy-n
March 27, 2016, 1:53am
19
Thanks, it works just fine.