Problem with revExecuteSQL - More

[Jim Ault]

On 11/1/07 11:01 AM, "Dave" <dave at looktowindward.com> wrote:

> This works ok with the addition of a line to check for embedded
> apostrophes:
> 
> put replace(myText,"'","''") into myText
> 
> However I now have a another weird problem, I have a field that
> represents a file path, in this case the path is:
> 
> /Users/Dave/Music/iTunes/iTunes Music/Blank & Jones/Addicted To
> Trance (Disc 1)/11 DJs, Fans And Freaks.mp3
> 
> However a "if there is a file" fails on this path. When I look I
> can't see the & in the file name. I'm guessing it's something to
> do with UTF16 vs UTF8 or something, but I'm not sure how to resolve
> it. The database I am writing is set to UTF8 which AFAIK is the only
> option for SQLite.
> 
> Any ideas what how to overcome this problem?
> 

Basically, you are including control characters in the data you are trying
to store, so Sql does not see those characters as *data*.

You need to read the docs carefully to identify these characters and take
action
One method as given earlier in this thread is to escape them (tell the Sql
engine to use them as data, NOT control characters)

Another method as given earlier in this thread is to use Rev to replace
these characters with non-control characters before sending the data to the
Sql engine

A third method is to encode the data (especially file paths, network
locations, and web urls).  This is not the same as  UTF8, which is merely a
superset of ASCII. 

>From Wikipedia: <quote>
UTF-8 is a superset of ASCII. Since a plain ASCII string is also a valid
UTF-8 string, no conversion needs to be done for existing ASCII text.
Software designed for traditional non-extended ASCII character sets can
generally be used with UTF-8 with few or no changes. <end quote>

In short, UTF8 encoding does not change normal ASCII characters and control
characters will still appear as control characters to Sql.  They need to be
changed or escaped.

<I am not an expert in this area, so there could be several corrections on
my post in the coming days (or minutes) >

Encoding can be done different ways and converts a data string into
alternate character symbols that allow decoding to get back the original
string.  Your example of (&) is an html entity which makes sense to a
web browser, but not Sql.  The ampersand is a control character for Sql, and
in this case, the Sql engine must be told to treat it as a data character
(or encoded to some other character)

Also remember that spaces in file names can be problematic, depending on the
platform and how you are using them.  You may have to make adjustments and
quote them appropriately for the complete path name to be understood.

Do a google search for "sql escape characters escaping encode decode" to
find work done by others.  One difficulty with quick easy answers is that
different databases and languages use different control characters.

You have landed squarely in the world of character conversion depending on
the environments you wish to use.  You need to create your own map and
methods to get from point A to B to C and back again.

If you are trying to do multi-lingual and unicode, things get a bit more
complicated.

I could be wrong on some of this, as I have not built an Sql database, but I
have spent time working through string conversions with Rev, text file, file
paths, Html, Applescript, PHP, and Apache protocols.  Same game, just
different team colors.

Hope this helps

Jim Ault
Las Vegas