On 1 Nov 2007, at 18:57, Jim Ault wrote:
On 11/1/07 11:01 AM, "Dave" <[EMAIL PROTECTED]> 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
_______________________________________________
use-revolution mailing list
use-revolution@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription
preferences:
http://lists.runrev.com/mailman/listinfo/use-revolution
