Re: [Tcl-tdbc] SQL LIKE '%test%' problem

[<to...@tu...>]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Wed, Jan 02, 2013 at 06:50:37PM +0000, Donal K. Fellows wrote:
> On 02/01/2013 18:03, to...@tu... wrote:
>> On Wed, Jan 02, 2013 at 02:48:21PM +0000, Donal K. Fellows wrote:
>>> you need to use something like this:
>>>
>>>    SELECT * FROM radcheck WHERE username LIKE '%' || :ffff || '%'
>>>
>>> (The “||” is the standard SQL string concatenation operator.)
>>
>> NOTE: the last MySQL I had contact with didn't know about ||.
>
> Not my fault they ignore the SQL specification (probably due to the
> widespread use of that operator for logical OR, but even so...).

I was surprised by that too. Needless to say, my opinion on MySQL didn't
improve after that finding. Just wanted to spare others the search.

>> You'd have to use concat there, like so:
>>
>>>    SELECT * FROM radcheck WHERE username LIKE concat('%', :ffff ||, '%')
> [...]
>
> You have an extra “||” in there. Otherwise, that's correct.

You're quite right, too much paste on my part.

> http://en.wikibooks.org/wiki/SQL_Dialects_Reference/Functions_and_expressions/String_functions

Yes, that was my place to go, after staring incredulously at the error
messages from MySQL.

>> Unless tdbc::mysql is smart and understands the || operator: then you
>> may ignore my talk above completely, that is.
>
> It definitely doesn't; the core principle of TDBC is to not make the
> client side over-smart, since that runs into real problems with more
> complex cases.

That makes a lot of sense -- and that was my expectation too. But I
didn't know for sure, therefore the caveat.

Thanks for enlightening and regards (and thanks for many things TCL,
btw. :-)

regards
- -- tomás
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFQ5KzIBcgs9XrR2kYRAsJTAJ9Dgvo79JHYtxaRtVLGrKb3b/Q32ACeI5s0
N+TM7RDEZuDJ+S5JaiiHM0c=
=Puqd
-----END PGP SIGNATURE-----