Page 1 of 1
Quote character in mysql search string
Posted: Tuesday 27th February 2018 3:19pm
by bill-lancaster
It's a long time since I did this:-
sNew is a string variable that may contain a "'" character.
hResult = hConn.Exec("SELECT * FROM gCal WHERE EventText = &1", sNew)
But I always get a result with no records.
No doubt I'm missing something obvious but any ideas would be welcome
Re: Quote character in mysql search string
Posted: Tuesday 27th February 2018 5:45pm
by stevedee
I hate this quoting problem. It comes up from time to time and I never seem to remember what I did last time.
In an example where I was running a Gimp script I did this:-
strExt = Quote("*.[jJ][pP][gG]")
strCommand = "cd " & DirView1.Current & " && gimp -i -b '(scale-batch " & strExt & ")' -b '(gimp-quit 0)'"
lblStatus.Text = "Please wait..... " & index & " image files found"
Wait
Shell strCommand Wait
So you may need to build the contents of sNew using Quote(sSomething) for the quoted part. Can we see an example of contents of sNew?
Re: Quote character in mysql search string
Posted: Tuesday 27th February 2018 9:14pm
by bill-lancaster
Exactly!
Here is the offending string:-
John Lancaster's birthday
Re: Quote character in mysql search string
Posted: Wednesday 28th February 2018 8:27am
by stevedee
OK, so it looks like your quoting problem is with SQL not Gambas (i.e. you need to quote to suit SQL rules).
Take a look at this post and let us know if it helps:
https://stackoverflow.com/questions/676 ... ophe-in-it
Re: Quote character in mysql search string
Posted: Wednesday 28th February 2018 9:27am
by bill-lancaster
Ah yes!
The problem was with a record in the db that contained a single (').
Using Gambas Replace function in the SQL statement solves the problem.
SELECT * FROM gCal WHERE EventText = '" & Replace(sNew, "'", "''") & "'"
Thanks for the help.
Re: Quote character in mysql search string
Posted: Wednesday 28th February 2018 10:06am
by jornmo
You can use this:
http://gambaswiki.org/wiki/comp/gb.db/_connection/quote
I think there's a bind parameters, or something like that too, or I'm confusing with PHP
