SQL Cannot write to db using a apstrophy

[jfxwave]

by using
ReadMessage = Input.GetText("WriteInput");

WriteInput = I'm here

it will not write to the db.

It will write everthing else even ? " [ = + @ and so on but will not write a apstrophy.

Please help

[Wonderboy]

Are you trying to write the input from and input object to a db file?

[jfxwave]

If i understand you correct then yes.


I type in a Input box and submit it

and it tries to write what i typed to the db but if i type in a apstrophy it will error.

I can type anything else and it works fine.

[Wonderboy]

I thouth you were writing it to a DatabaseFile with strings,
like this example

ReadMessage = Input.GetText("WriteInput");
TextFile.WriteFromString("Autoplay\\Docs\\MyDatabase.db", ""..ReadMessage, false);

cause that option works with apostrophe,
i havent used experimented with sqlite very much and so in that way i dont know if it works or not, hoppefully someone more knowlegable in this area will help you out,

[Jim Smiley]

To quote the all-knowing Worm:

"You need to enclose the string that contains the ' in quotes (")
I like to use a function to do it to keep it a little cleaner"

function Enclose(sIn)
return "\""..sIn.."\"";
end

Enclose("You're string here...")

[bule]

You need to use StringReplace() to replace all instances of the ' with two ' of a same kind:

I'm happy. -----> I''m happy.

This is because of the SQL syntax.

You see, if you don't replace ' with two 's, your query would end up like this, for example:

UPDATE table SET myvalue='I'm happy';

...which is not good. This is the correct syntax:

UPDATE table SET myvalue='I''m happy.';

...which will save I'm happy. string into your SQLite database.

[jfxwave]

I don't understand

Name = Label.GetText("Login");
Date = Label.GetText("Date");
Time = Label.GetText("Time");
ReadMessage = Input.GetText("WriteInput");
ToUser = ComboBox.GetText("ComboBox");

OpenMessageDatabase();

Light = 0;

sQuery = "INSERT into Message values(NULL, '" .. Name .. "', '" .. Date .. "', '" .. Time .. "', '" .. ToUser .. "', '" .. ReadMessage .. "', '" .. Light .. "')";

AddRow = WriteToDB(db, sQuery);
if AddRow == "OK" then
end

CloseMessageDatabase();

How would i write that?

[jfxwave]

ok

so i just
Replace

ReadMessage = Input.GetText("WriteInput");

for

ReadFirst = Input.GetText("WriteInput");

And add this

ReadMessage = String.Replace(ReadFirst, "'", "''", false);

Works Nice, thanks

Is there any others i should know about?

[bule]

I myself use this function to overcome this:

function Q(s)
	return String.Replace(s.."", "'", "''", false);
end

I use it like this in the SQL queries:

sQuery = "INSERT into Message values(NULL, '" .. Q(Name) .. "', '" .. Q(Date) .. "', '" .. Q(Time) .. "', '" .. Q(ToUser) .. "', '" .. Q(ReadMessage) .. "', '" .. Q(Light) .. "')";

[azmanar]

Hi,

function Enclose(sValue)
return String.Replace(s.."", "'", "''", false);
end

I find it is easy to Enclose the data that has Single Quotes when INSERTing into the SQLite DB. It is also easy to SELECT......and dump it on the screen.

BUT when making that data as the primary SELECT variable, it will not work.

For example:

SELECT Hobby WHERE Name=sName

If variable sName has been Enclose() earlier , there will be SQL error when making it as the primary SELECT variable.

Any thoughts?