Quote character in mysql search string

Post your Gambas programming questions here.
Post Reply
bill-lancaster
Posts: 190
Joined: Tuesday 26th September 2017 3:17pm
Location: NW England

Quote character in mysql search string

Post 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
User avatar
stevedee
Posts: 518
Joined: Monday 20th March 2017 6:06pm

Re: Quote character in mysql search string

Post 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?
bill-lancaster
Posts: 190
Joined: Tuesday 26th September 2017 3:17pm
Location: NW England

Re: Quote character in mysql search string

Post by bill-lancaster »

Exactly!
Here is the offending string:-
John Lancaster's birthday
User avatar
stevedee
Posts: 518
Joined: Monday 20th March 2017 6:06pm

Re: Quote character in mysql search string

Post 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
bill-lancaster
Posts: 190
Joined: Tuesday 26th September 2017 3:17pm
Location: NW England

Re: Quote character in mysql search string

Post 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.
User avatar
jornmo
Site Admin
Posts: 224
Joined: Wednesday 21st September 2016 1:19pm
Location: Norway

Re: Quote character in mysql search string

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