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*. 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 _______________________________________________ use-revolution mailing list [email protected] Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
