Query QuickBook Online with UTF-8 characters

Question

I'm trying to use the API to query for customers by name. The names with non-ASCII characters (e.g. 민준 or Włodarski) cause the queries fail. Here's an example query:

SELECT * FROM Customer WHERE FamilyName = 'Włodarski'

I URL encode it as suggested by https://developer.intuit.com/hub/blog/2014/03/20/advanced-sql-queries and get:

https://sandbox-quickbooks.api.intuit.com/v3/company/193514472385819/query?query=SELECT%20*%20FROM%20Customer%20WHERE%20LastName%20=%20'W%C5%82odarski'%20STARTPOSITION%201%20MAXRESULTS%201000

But when I submit that request I get back:

<IntuitResponse xmlns="http://schema.intuit.com/finance/v3" time="2017-03-03T15:14:26.774-08:00">
  <Fault type="ValidationFault">
    <Error code="4000">
      <Message>Error parsing query</Message>
      <Detail>QueryParserError: Invalid content. Lexical error at line 1, column 43.  Encountered: "\u0142" (322), after : "\'W"</Detail>
    </Error>
  </Fault>
</IntuitResponse>

What do I need to do to be able to find this customer?

I'd put the question to Intuit's support and the provided the following:

If you want to query the whole string, you can URL encode the whole query without the non-ASCII character and and then add the URL encoded character to this query. An example of this is: select%20%2A%20from%20Customer%20where%20GivenName%3D%27m%C3%A5na

select * from Customer where GivenName='måna'

%C3%A5 is URL encoding of my non-ascii character å.

You can also do something like this: select * from Customer where GivenName like 'm%na' and then encode the whole query which will ignore the second character and search for the rest of the characters in that order.

I think the example they provided with å was chosen because it's part of the extended ASCII character set. I've asked them to clarify if UTF-8 characters can be used as search strings.

Answer 1

Short answer is it's not supported.

I submitted a question to Intuit's support and here's their initial response.

If you want to query the whole string, you can URL encode the whole query without the non-ASCII character and and then add the URL encoded character to this query. An example of this is: select%20%2A%20from%20Customer%20where%20GivenName%3D%27m%C3%A5na select * from Customer where GivenName='måna' %C3%A5 is URL encoding of my non-ascii character å.

You can also do something like this: select * from Customer where GivenName like 'm%na' and then encode the whole query which will ignore the second character and search for the rest of the characters in that order.

Did a little testing. Looking at it more it seems like the example they chose works because it's an extended ASCII character. I asked for clarification on how to handle UTF-8 characters like 민준 which I'm able to set via the API and view in the UI.

I'd replied with:

I'm able to to replicate the 'å' character you suggested but it looks like it's part of the extended ASCII set and therefore encoded differently. Could you provide an example with a UTF-8 character like the one in my initial query? Or perhaps '민준'? I'm able to submit those values in the XML and API but have not been able to query for them.

The suggestion to replace the character with the % wildcard is interesting but would return additional records such as "Mona" or "Mina".

And yesterday they finally got back to me:

Hello Andrew, We don't support queries with these characters. You will have to encode on your end and then compare characters in a for loop for all customers.

So that's delightful.