Problem with revExecuteSQL - More

[Dave]

On 1 Nov 2007, at 18:57, Jim Ault wrote:

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

This has nothing to do with SQL, this is before the data is written,  
in fact I can insert the data ok. The problem is that some of the  
data has these weird characters. I am dealing with a "typical"  
MusicBase. From looking at the track that have these characters in  
then I'd say they came from a PC. I seem to remember somewhere now  
that said you have to do something to the data you wrote to ensure it  
will work on a Mac and PC. Does anyone remember??

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


All the Best
Dave