SQLite3: unable to add the ' character to the database

[gvanassche]

Dear all,

attached a file to demonstrate the problem I observed. I have a couple of questions on this:
1. Does someone know how I can add the ' character to an SQLite database?
2. Is this character the only one causing the problem?
3. Is there a workarround or solution? (I was thinking about replacing all ' by something like µ$§ before uploading, and then use a query to replace µ$§ in the database, but I have no idea how to do this)
4. Last but not least: I try to capture the lasterror, but that does not seem to work. The feedback is "OK" while it should have been an error.

Or am I making a terrible mistake?

thanks for your help,

gert

[TopicTJ]

Hi

I'm getting the same result as you, i.e. the .. ' .. character is preventing the record being stored in the database but even if you get around this you may still find difficulty with .. é .. and other accented characters.

In limited testing I found that .. é .. did not prevent a record being saved but it did produce errors when querying the database.

I got around the difficulty by using String.Replace ().

As an example, to look for ..'.. and replace with ... $$$ ... in the file you uploaded, use

data = String.Replace(Paragraph.GetText("Paragraph2"), "'", "$$$", false);

When you query the database, all you need do is reverse this by replacing all occurences of ... $$$ ... with ..'.. and doing something similar for all accented characters.

Hopefully, one of the database experts will show us both a simpler way of doing it but at least the above works!


TTJ

[Sakuya]

Couldn't you escape the string?


insert into table values ('hey look a \' thing and another few \' \' \'.')

..or something like that.

Syntax might be wrong, I'm used to MySQL 5.

[gvanassche]

In the mean time I discovered the biggest problem with the search & replace bypass: the text (that may be UTF-8) is corrupted by the ANSI limitation of AMS. So, it may work for English, it certainly does not work for French, German, Arabic or Asian languages.

Argh...

I'm getting desperate.

Thanks for your thinking along anyway.

[Dermot]

You need to warp thye value in double quotes, not single quotes. You should always do this with SQLite.


db:exec("INSERT INTO TEST (id, text) VALUES (NULL, \""..data .."\")");

[reteset]

this is ' a reserved character
and you should escape this characters in your string

please try this modified example

[TopicTJ]

please try this modified example

Sometimes I just despair at how much I don't know :rolleyes

Many thanks for the explanation.


TTJ

[gvanassche]

Thank you all very much for the help & the example!
The only problem still to fix, is the unicode one ;-)

now my é shows up as �...

gert