quickbooks-online - 使用 UTF-8 字符查询 QuickBook Online

Question

我正在尝试使用 API 按姓名查询客户。具有非 ASCII 字符的名称(例如 민준 或 Włodarski)会导致查询失败。这是一个示例查询:

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

我按照 https://developer.intuit.com/hub/blog/2014/03/20/advanced-sql-queries 的建议对其进行 URL 编码并得到:

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

但是当我提交该请求时，我会返回:

<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>

我需要做什么才能找到这个客户？

我将问题提交给 Intuit 的支持并提供以下内容:

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.

我认为他们使用 å 提供的示例被选中是因为它是扩展 ASCII 字符集的一部分。我已经要求他们澄清 UTF-8 字符是否可以用作搜索字符串。

Answer 1

简短的回答是它不受支持。

我向 Intuit 的支持人员提交了一个问题，这是他们的初步答复。

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.

做了一点测试。仔细观察，他们选择的示例似乎有效，因为它是一个扩展的 ASCII 字符。我要求澄清如何处理像 민준 这样的 UTF-8 字符，我可以通过 API 设置这些字符并在 UI 中查看。

我的回复是:

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".

昨天他们终于回复了我:

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.

那真是令人愉快。