[Gambas-user] mysql seach string contains single quote

[Tobias Boege]

On Sat, 10 Aug 2013, Johny Provoost wrote:
> 
> Op 10-08-13 09:02, bill-lancaster schreef:
> > Have seen a couple of posts that refer to this but can't quite understand the
> > answer
> >
> > I have a database which conntains a string  'Queen's Head'
> >
> > so
> >       hResult = hConn.Exec("SELECT contacts.* FROM contacts WHERE Title =
> > 'Queen's Head'")
> > produces an error.
> >
> > How best to solve this?
> >
> >
> >
> > --
> > View this message in context: http://gambas.8142.n7.nabble.com/mysql-seach-string-contains-single-quote-tp42768.html
> > Sent from the gambas-user mailing list archive at Nabble.com.
> >
> > ------------------------------------------------------------------------------
> > Get 100% visibility into Java/.NET code with AppDynamics Lite!
> > It's a free troubleshooting tool designed for production.
> > Get down to code-level detail for bottlenecks, with <2% overhead.
> > Download for free and get started troubleshooting in minutes.
> > http://pubads.g.doubleclick.net/gampad/clk?id=48897031&iu=/4140/ostg.clktrk
> > _______________________________________________
> > Gambas-user mailing list
> > Gambas-user at lists.sourceforge.net
> > https://lists.sourceforge.net/lists/listinfo/gambas-user
> >
> 
> It's because Queen's Head has a ' in the string.
> 
> I always use
>      Var="Queen's Head"
>      hResult=hConn.Exec("SELECT contacts.* FROM contacts WHERE Title=" 
> &"\"" & Var & "\"")
> or
>      hResult=hConn.Exec("SELECT contacts.* FROM contacts WHERE Title=" & 
> Chr(34) & Var & Chr(34))
> 

This is even shorter:

hResult = hConn.Exec("SELECT contacts.* FROM contacts WHERE Title='Queen\\'s Head'")

Since ' is meaningful in MySQL, you have to mask it with \ and since you do
this in a Gambas string constant, you have to double the backslash.

Regards,
Tobi