Quote character in mysql search string

Post Reply
bill-lancaster
Posts: 9
Joined: Tuesday 26th September 2017 3:17pm

Quote character in mysql search string

Post by bill-lancaster » Tuesday 27th February 2018 3:19pm

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

stevedee
Posts: 75
Joined: Monday 20th March 2017 6:06pm

Re: Quote character in mysql search string

Post by stevedee » Tuesday 27th February 2018 5:45pm

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?

bill-lancaster
Posts: 9
Joined: Tuesday 26th September 2017 3:17pm

Re: Quote character in mysql search string

Post by bill-lancaster » Tuesday 27th February 2018 9:14pm

Exactly!
Here is the offending string:-
John Lancaster's birthday

stevedee
Posts: 75
Joined: Monday 20th March 2017 6:06pm

Re: Quote character in mysql search string

Post by stevedee » Wednesday 28th February 2018 8:27am

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

bill-lancaster
Posts: 9
Joined: Tuesday 26th September 2017 3:17pm

Re: Quote character in mysql search string

Post by bill-lancaster » Wednesday 28th February 2018 9:27am

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.

User avatar
jornmo
Site Admin
Posts: 154
Joined: Wednesday 21st September 2016 1:19pm
Location: Norway

Re: Quote character in mysql search string

Post by jornmo » Wednesday 28th February 2018 10:06am

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 :)

Post Reply