Soap API Query

Jacob Winn

Hello,


I'm trying to execute a query against the SOAP api and I cannot figure out the correct escape syntax using the documentation.

My example has a single quote inside of the query string i.e. Select Id From Group Where Name = 'Jacob's Group'.


Can someone point me in the right direction for how to accomplish this?


Thanks

Stephen Richarme

The closest answer I can find in the documentation is here:

http://open.convio.com/webservices/#main.query_syntax.html

fragment
QUOTED_CHARACTER:
  ( ~( '\\'' | '\\\\' ) ) | '\\\\' ( ( '\\'' | '\\\\' ) );

STRING_LITERAL:
  '\\''! ( QUOTED_CHARACTER )* '\\''!;

From this, I would expect that you should escape the single quote with a backslash, such as:

Select Id From Group Where Name = 'Jacob\\'s Group'

I tried testing this out, however, and while I did not get an error, I also did not get any matching data in the response (after creating a group with the appropriate name). I also tried testing this with other data types, such as Constituents with a last name of O'Donnell, and found the same result - no error, but no records returned. Finally, I tried testing this with the Oracle SQL syntax for escaping single quote characters:

Select Id From Group Where Name = 'Jacob''s Group'

Unfortunately, while this also did not cause an error, it did not return the expected records. I fear you may have found a bug - if you can't get it to work with either of the suggestions I've described above, I would recommend contacting Support for more assistance on this.


Good luck!

Jacob Winn

Thanks for that response. I have also tried it both ways based on the Documentation, along with other members of my team trying different things. We have gotten the same results that you have and have been unable to solve it. Looks like I'll be reaching out to support.


Thanks!

Brian Mucha

I've run into this ages ago too. I thought it was the two single-quotes idea as well.

Select Id From Group Where Name = 'Jacob''s Group'

Since it's just a select, maybe try a wildcard?

Select Id From Group Where Name = 'Jacob_s Group'

Brian Mucha

Ever find an answer to this?


I got a chance to fool around in the console and couldn't get any luck with _ as a wildcard. Not part of CQL?

Jacob Winn

Brian,


Thanks for the response, for some reason I did not see a notification of your earlier reply. I've had no luck with wildcards either.

I'm going to open a ticket with support to see if they can shed any light on it.


Thanks!

Abner Ferreira

Do we have any updates on this?