1. Michael Hartnett
  2. PowerServer Mobile (Obsolete)
  3. Friday, 7 February 2020 11:14 AM UTC

Hi All,

Hoping someone can help me with this...

Our Appeon Mobile App is being used by over 100 users within a global organisation.  All users are using iOS devices.

Within the app we provide the ability for users to search records based on Name.  In some cases the names will contain apostrophes, or single quote. In or SQL Query we simply escaped the apostrophe with a second apostrophe, which works fine on Android and had been working on iOS, until iOS 11!

Sample of Code used to escape the apostrophe for SQL Filtering:

ls_value = ln_stringclass.replaceall(ls_value,"'","''")
ls_filter_horse_name = " AND HorseName LIKE '%" + ls_value + "%'"

It has been flagged by users that their searches are no longer working on iOS when including an apostrophe in their search string.  This seemingly is a result of Apple changing the character used for the apostrophe to a curly single quote to be more aesthetically pleasing!

This feature can be turned off in the keyboard settings on the iOS device (Smart Punctuation), but is not exactly the easiest thing to do with upward of 100 users, many of whom have multiple devices.

Does anyone know how I can trap the curly quote code to replace it with an escaped apostrophe similar to my code above?

Thanks

Michael

Michael Hartnett Accepted Answer Pending Moderation
  1. Wednesday, 12 February 2020 09:12 AM UTC
  2. PowerServer Mobile (Obsolete)
  3. # 1

Hi All,

Thanks for replies.

Since the Asc/Char functions were not returning the correct character for this, I tried something different.

I have managed to resolve this by writing the character to a settings table in my Database using a once off piece of script.

Each time the App loads, I am reading this value into a string variable and this works for the comparison and replace.

It would have been nice to trap the Char value but this solution works nicely.

Michael

Comment
  1. Miguel Leeuwe
  2. Wednesday, 12 February 2020 09:33 AM UTC
Well, great to hear you can work around it. So.... the character must be out of bounds of what fits in one byte, is my conclusion. If you write it to a file on disk and then edit with a hexadecimal editor you might be able to find out.
  1. Helpful
There are no comments made yet.
Michael Hartnett Accepted Answer Pending Moderation
  1. Tuesday, 11 February 2020 09:49 AM UTC
  2. PowerServer Mobile (Obsolete)
  3. # 2

OK, 

Here is another test I have run...unsuccessful!

  1. If I enter a string which I purposely know has the 'curly quote' as the last character, I use RIGHT(string,1) to get the curly quote character.
  2. I use AscA or Asc to get the ASCII value of the character - this returns 146
  3. When I try to output this as a character (Char(146) or CharA(146)) it shows no character, also using it in the replaceall() function does not work.

Obviously using step 1. above to allocate the character to a string variable and comparing that or using that in the replaceall() works.  This is no good as I have prior knowledge of the string for testing in the above example and I need the character value to be able to store and use for comparative purposes.

Hope someone can help.

Thanks

Comment
There are no comments made yet.
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Monday, 10 February 2020 23:17 PM UTC
  2. PowerServer Mobile (Obsolete)
  3. # 3

another way of doing it would be by excluding the single quote character and replacing the search for 'any' character, using '_' (which would represent any character in a Like in Oracle for example, or '?' in Access, not sure about other databases).

 

ls_value = ln_stringclass.replaceall(ls_value,"'","''")
ls_value = ln_stringclass.replaceall(ls_value,"ʼ","_")
ls_value = ln_stringclass.replaceall(ls_value,"ʽ","_")
ls_filter_horse_name = " AND HorseName LIKE '%" + ls_value + "%'"

 

Comment
There are no comments made yet.
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Monday, 10 February 2020 23:03 PM UTC
  2. PowerServer Mobile (Obsolete)
  3. # 4

I'd say you can do that the same way you're replacing the single quote with a double quote:

instead of

"ls_value = ln_stringclass.replaceall(ls_value,"'","''")"

 

do

"ls_value = ln_stringclass.replaceall(ls_value,"'","''")"

AND followed by (for IOS):

ls_value = ln_stringclass.replaceall(ls_value,"","''")

(hard to perceive, but in red is the normal single quote and in green the curly single quote.

not sure if that's exactly the same curly quote - in the second parameter - which you're getting in IOS ...)

Comment
  1. Miguel Leeuwe
  2. Tuesday, 11 February 2020 09:26 AM UTC
Sorry to hear that. What if ...to find out which code it is getting exactly:

What I would do is type in "A'", so the second character is a single quote and then in your code do something like #

Messagebox("second character value", string( Asc( Mid( ls_value, 2, 1) ) ) )

That way it'll show you which character you get exactly on IOS. (which you can replace in your strings using "Char(#)"

# being the code returned by Asc().
  1. Helpful
  1. Michael Hartnett
  2. Wednesday, 12 February 2020 08:59 AM UTC
Thanks Miguel,

For some reason the Char(#) does not work as PB Asc value returns an integer value that does not match.



I have managed to get this working, by writing the value to the DB and retrieving it from there into a string variable for comparison. This seems to work!

Michael
  1. Helpful
  1. Miguel Leeuwe
  2. Wednesday, 12 February 2020 09:34 AM UTC
Great!
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.