Transaction API with conditional "text" does not accept special characters

Bug
1

Application id: B80B1EC9-8C65-ABD2-FF0A-437AB4D8E700
I found a consistent bug from /transaction/unit-of-work when I was trying to update One To Many relationship table with Column “Mac” and Schema type “text” with POST content like … “conditional”:“macid = ‘AC:23:3F:A8:07:D4’”

  1. “conditional”:“firecardnumber = ‘44.45.55’”, says “message”:"Where clause is invalid for
  2. “conditional”:“firecardnumber = ‘44:45:55’”, says “message”:"Where clause is invalid for
  3. “conditional”:“firecardnumber = ‘44-45-55’”, creates new parent item but does not create child and says {“operationType”:“SET_RELATION”,“result”:0}
  4. most likely many others!, please verify with script

My POST:
curl -X POST -H ‘Content-Type:application/json’ ‘https://eu-api.backendless.com/B80B1EC9-8C65-ABD2-FF0A-437AB4D8E700/AAAAAAAAAAAAAAAAAAAAA/transaction/unit-of-work’ -d’{“operations”:[{“operationType”:“CREATE”,“table”:“henkilo”,“opResultId”:“please”,“payload”:{“firstname”:“Katja”,“lastname”:“Kettunen”}},{“operationType”:“SET_RELATION”,“table”:“henkilo”,“payload”:{“parentObject”:{"___ref":true,“opResultId”:“please”,“propName”:“objectId”},“relationColumn”:“fireworkcard”,“conditional”:“firecardnumber = ‘44.45.55’”}}]}’
Response:
{“success”:false,“error”:{“operation”:{“operationType”:“SET_RELATION”,“table”:“henkilo”,“opResultId”:“set_relationhenkilo1”,“payload”:{“conditional”:“firecardnumber = 44:45:55”,“unconditional”:null,“parentObject”:“7A1E6FA5-07E3-4C89-8A90-CEB356263E84”,“relationColumn”:“fireworkcard”}},“message”:“Where clause ‘firecardnumber = 44:45:55’ is invalid”},“results”:null}

My POST:
curl -X POST -H ‘Content-Type:application/json’ ‘https://eu-api.backendless.com/B80B1EC9-8C65-ABD2-FF0A-437AB4D8E700/AAAAAAAAAAAAAAAAAAAAAAA/transaction/unit-of-work’ -d’{“operations”:[{“operationType”:“CREATE”,“table”:“henkilo”,“opResultId”:“please”,“payload”:{“firstname”:“Katja”,“lastname”:“Kettunen”}},{“operationType”:“SET_RELATION”,“table”:“henkilo”,“payload”:{“parentObject”:{"___ref":true,“opResultId”:“please”,“propName”:“objectId”},“relationColumn”:“fireworkcard”,“conditional”:“firecardnumber = ‘44.45.55’”}}]}’
Response:
{“success”:false,“error”:{“operation”:{“operationType”:“SET_RELATION”,“table”:“henkilo”,“opResultId”:“set_relationhenkilo1”,“payload”:{“conditional”:“firecardnumber = 44.45.55”,“unconditional”:null,“parentObject”:“D75ED086-D2A5-48FD-AC4D-1BB40FCCE843”,“relationColumn”:“fireworkcard”}},“message”:“Where clause ‘firecardnumber = 44.45.55’ is invalid”},“results”:null}

My POST:
curl -X POST -H ‘Content-Type:application/json’ ‘https://eu-api.backendless.com/B80B1EC9-8C65-ABD2-FF0A-437AB4D8E700/AAAAAAAAAAAAAAAAAAAAAAAA/transaction/unit-of-work’ -d’{“operations”:[{“operationType”:“CREATE”,“table”:“henkilo”,“opResultId”:“please”,“payload”:{“firstname”:“Katja”,“lastname”:“Kettunen”}},{“operationType”:“SET_RELATION”,“table”:“henkilo”,“payload”:{“parentObject”:{"___ref":true,“opResultId”:“please”,“propName”:“objectId”},“relationColumn”:“fireworkcard”,“conditional”:“firecardnumber = ‘44-45-55’”}}]}’
Response:
{“success”:true,“error”:null,“results”:{“set_relationhenkilo1”:{“operationType”:“SET_RELATION”,“result”:0},“please”:{“operationType”:“CREATE”,“result”:{“firstname”:“Katja”,“created”:1644789771118,“phonenumber”:null,"___class":“henkilo”,“ownerId”:null,“phonenumbervalid”:null,“updated”:null,“objectId”:“A9FD8E27-A806-4FD0-AE34-F71B7970C5BB”,“lastname”:“Kettunen”}}}}

2

Hello @Jani_v

Please replace the symbol ‘ with a symbol ' in your conditional.

My requests:

  1. Request:
{
	"operations":
	[
		{
			"operationType":"CREATE",
			"table":"henkilo",
			"opResultId":"please",
			"payload":
			{
				"firstname":"Test1",
				"lastname":"Test1"
			}
		},
		{
			"operationType":"SET_RELATION",
			"table":"henkilo",
			"payload":
			{
				"parentObject":{"___ref":true,"opResultId":"please","propName":"objectId"},
				"relationColumn":"fireworkcard",
				"conditional":"firecardnumber = '44.45.55'"
			}
		}
	
	]
}

Response:

{
    "success": true,
    "error": null,
    "results": {
        "set_relationhenkilo1": {
            "operationType": "SET_RELATION",
            "result": 1
        },
        "please": {
            "operationType": "CREATE",
            "result": {
                "firstname": "Test1",
                "created": 1644831771587,
                "phonenumber": null,
                "___class": "henkilo",
                "ownerId": null,
                "phonenumbervalid": null,
                "updated": null,
                "objectId": "7DE4E221-06E8-44EB-8FB2-DCB3C74AE24A",
                "lastname": "Test1"
            }
        }
    }
}
  1. Request:
{
	"operations":
	[
		{
			"operationType":"CREATE",
			"table":"henkilo",
			"opResultId":"please",
			"payload":
			{
				"firstname":"Test2",
				"lastname":"Test2"
			}
		},
		{
			"operationType":"SET_RELATION",
			"table":"henkilo",
			"payload":
			{
				"parentObject":{"___ref":true,"opResultId":"please","propName":"objectId"},
				"relationColumn":"fireworkcard",
				"conditional":"firecardnumber = '44.45.55'"
			}
		}
	]
}

Response:

{
    "success": true,
    "error": null,
    "results": {
        "set_relationhenkilo1": {
            "operationType": "SET_RELATION",
            "result": 1
        },
        "please": {
            "operationType": "CREATE",
            "result": {
                "firstname": "Test2",
                "created": 1644832148931,
                "phonenumber": null,
                "___class": "henkilo",
                "ownerId": null,
                "phonenumbervalid": null,
                "updated": null,
                "objectId": "2959BCEB-E7C4-4753-9DA5-7D3F7826BE48",
                "lastname": "Test2"
            }
        }
    }
}
  1. Request:
{
	"operations":
	[
		{
			"operationType":"CREATE",
			"table":"henkilo",
			"opResultId":"please",
			"payload":
			{
				"firstname":"Test3",
				"lastname":"Test3"
			}
		},
		{
			"operationType":"SET_RELATION",
			"table":"henkilo",
			"payload":
			{
				"parentObject":{"___ref":true,"opResultId":"please","propName":"objectId"},
				"relationColumn":"fireworkcard",
				"conditional":"firecardnumber = '44-45-55'"
			}
		}
	]
}

Response:

{
    "success": true,
    "error": null,
    "results": {
        "set_relationhenkilo1": {
            "operationType": "SET_RELATION",
            "result": 1
        },
        "please": {
            "operationType": "CREATE",
            "result": {
                "firstname": "Test3",
                "created": 1644830820339,
                "phonenumber": null,
                "___class": "henkilo",
                "ownerId": null,
                "phonenumbervalid": null,
                "updated": null,
                "objectId": "A893F5CC-2A44-4DFC-BC19-C40F655E84E9",
                "lastname": "Test3"
            }
        }
    }
}

Regards,
Vladimir

3

This is a test that I have used single quote that is U+0027 : APOSTROPHE {single quote; APL quote} indentified in service What Unicode character is this ?

“conditional”:“firecardnumber = ‘44.45.55’”

4

It seems that your web page converts it to U+2018 : LEFT SINGLE QUOTATION MARK {single turned comma quotation mark}

5

I use WIN10 Bash shell to make curl POST request. To remove spaces in data payload I use online service JSON Editor Online - view, edit and format JSON online but still my problem remains. a) I take copy paste from your 1. Request, b) put it into jsoneditoronline and remove spaces c) put it into command line d) make sure that my command line has singlequote U+0027 : APOSTROPHE {single quote; APL quote} e) make POST request and get same errors that states “Where clause ‘firecardnumber = 44.45.55’ is invalid”

6

Hi Jani,

If you use a code block here (like the one below), it eliminates the possibility that any characters are replaced:

this is a sample text with single quotes: 'test'

This is how I formatted this very message:

Transaction API with conditional "text" does not accept special characters - Bug - Backendless Support 2022-02-14 14-39-44
Transaction API with conditional "text" does not accept special characters - Bug - Backendless Support 2022-02-14 14-39-44750×463 42.2 KB

7 
curl -X POST -H 'Content-Type:application/json' 'https://eu-api.backendless.com/B80B1EC9-8C65-ABD2-FF0A-437AB4D8E700/
aaaaaaaaaaaaaa/transaction/unit-of-work' -d'{"operations":[{"operationType":"CREATE","table":"henkilo","opResultId":"please","payload":{"firstname":"ffTest1","lastname":"llTest1"}},{"operationType":"SET_RELATION","table":"henkilo","payload":{"parentObject":{"___ref":true,"opResultId":"please","propName":"objectId"},"relationColumn":"fireworkcard","conditional":"firecardnumber = '44.45.55'"}}]}'
9

I find the following SO post to be quite helpful:
bash - CURL escape single quote - Stack Overflow

The single quotes should be escaped as follows:

'\''

As a result, the modified curl request that works looks like this:

curl -X POST -H 'Content-Type:application/json' 'https://eu-api.backendless.com/B80B1EC9-8C65-ABD2-FF0A-437AB4D8E700/aaaaaaaaaaaaaaa/transaction/unit-of-work' -d'{"operations":[{"operationType":"CREATE","table":"henkilo","opResultId":"please","payload":{"firstname":"ffTest1","lastname":"llTest1"}},{"operationType":"SET_RELATION","table":"henkilo","payload":{"parentObject":{"___ref":true,"opResultId":"please","propName":"objectId"},"relationColumn":"fireworkcard","conditional":"firecardnumber = '\''44.45.55'\''"}}]}'
10

Awesome! This worked! Thank you!