What's the correct way to escape single quote character (') in a string literal for selectQuery SOAP API call? - Forum - Rollbase - Progress Community

What's the correct way to escape single quote character (') in a string literal for selectQuery SOAP API call?

 Forum

What's the correct way to escape single quote character (') in a string literal for selectQuery SOAP API call?

This question is not answered

Hi,

I seem to have a problem with using two single quote characters ('') to escape a single quote character (') in a string literal when calling selectQuery SOAP API function.

The escaped string used for query argument of selectQuery call is:

SELECT id FROM test1 WHERE externalReference1 = 'TEST''DATA' 

This produces a MySQL error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''DATA)' at line 1

Can you help, please? Thanks.

All Replies
  • Hi Marcin,

    The query is working fine for me. However, to investigate further, I need some information from your end. For that purpose, I am sharing a list of field types whose information would help me to deduce the problem that you are facing. If you have configured any fields that belong to the following list, please share all the details (especially Integration Code) of those fields.

    Group of Checkboxes

    Dependent Picklist

    Email Template

    Radio Buttons

    Picklist

    Picklist (multiple)  

    Workflow Status

    Document Template

    Along with the above details, please share the webapi.log file as well.

    I will get back with further information once I have the above details from you.

    Regards,

    Anvi

  • Hi mzorbis,

    You can espace single quotes using backslash.

    I.e 'TEST\'\'DATA'


    Regards,
    Ricardo

  • Hi Anvi,

    At the moment I've got the webapi.log only (replaced actual IP address with 0.0.0.0 intentionally):

    [2018-03-23 08:30:53,666] [RID=01edb035-00f8-44de-8140-0017cd16e3ba] [Comp=webapi] [CustId=630468] [IP=0.0.0.0] selectQuery query=SELECT id FROM locationAddress WHERE externalReference1 = 'BB''S BABY DONUTS' AND addressAccount = '949184'

    [2018-03-23 08:30:53,670] [RID=01edb035-00f8-44de-8140-0017cd16e3ba] [Comp=webapi] [CustId=630468] [IP=0.0.0.0] ===> Error in thread http-apr-8080-exec-9 at 03/23/2018 08:30 AM: selectQuery error: SELECT A.OBJ_ID FROM RB_OBJ_DATA A WHERE A.OBJ_DEF_ID=640129 AND (A.STR5 = 630488'S BABY DONUTS' AND A.INTG3 = '949184')

    com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''S BABY DONUTS' AND A.INTG3 = '949184')' at line 1

    Cheers,

    Marcin.

  • Can you check if you have a picklist in your locationAddress object which has the picklist item code as ''BB'? If yes, then this is a known issue at the moment.

  • Hi Manooj,

    Initially we though that this was an issue with the phrase BB as our SOAP calls continued working one we changed the picklist code from BB to BB1. However, we figured that this issue is related to any picklist codes, and dependant picklist codes and values.

    For example if you have a state called Victoria and do a SOAP selectQuery call (select id from address where reference='Victoria''s doughnuts'). That fails too.

    Like I mentioned, the issue is related picklist and dependant picklist codes and values too.

    Has this been fixed in any recent versions or do you have any plans to fix the same?

    Thanks,

    Hosny