db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Geoff hendrey <geoff_hend...@yahoo.com>
Subject Re: Portability issue for 'Like-clause' on non-string types ?
Date Thu, 26 Jun 2008 13:56:41 GMT
Hi Rick,

Is this optimizable?

LIKE '%' ||  ?  ||  '%'

Let me explain: I need to use prepared statements for security, but I also need the  ability
to use wildcards characters  like '%'. I found that the above style, using "||" for concatenation
worked, whereas this did not:

LIKE '%?%'

In the version above, the '?' simply got treated literally by the JDBC driver, and was not
recognized as a query parameter, since it is really just part of the string itself. If the
use of "||" slows down the query significantly, than that's a real problem, since there appears
to be no other way to "inject" jdbc ? into the string.


----- Original Message ----
From: Rick Hillegas <Richard.Hillegas@Sun.COM>
To: Derby Discussion <derby-user@db.apache.org>
Sent: Thursday, June 26, 2008 5:45:55 AM
Subject: Re: Portability issue for 'Like-clause' on non-string types ?

Hi Albert,

As Dyre and Donald point out, the LIKE operator can only be applied to 
strings. You can write your own casting function to turn your integers 
into strings (or to extract other information which you may be encoding 
in integers). Something like the following should work:

select * from t
where intToString( intCol ) like '34%'

Note that this kind of query fragment won't be optimizable. That is, the 
optimizer won't be able to take advantage of useful indexes which you've 
put on intCol.

Hope this helps,

Albert Kam wrote:
> Hello again Apache Derby,
> I'm currently porting my little webapp from using mysql to apache derby.
> One of the issue i'm having right now is the like clause being used 
> for non-string types.
> I tried the ij, issuing simple sql statement like :
> ij> select sc.id <http://sc.id> from sms_command sc where sc.id 
> <http://sc.id> like '%';
> ERROR 42884: No authorized routine named 'LIKE' of type 'FUNCTION' 
> having compatible arguments was found.
> But it works fine for the string type :
> ij> select sc.id <http://sc.id>, sc.dbpool_name from sms_command sc 
> where sc.dbpool_name like 'd%';
> ID         |DBPOOL_NAME
> ---------------------------
> 6          |demo
> 14         |demo
> 21         |demo
> 23         |test
> I tried describe the table, and here's the output :
> ij> describe sms_command;
> COLUMN_NAME        
> ------------------------------------------------------------------------------
> ID                                |INTEGER  |0   |10  |10    
> |NULL      |NULL      |NO
> DBPOOL_NAME         |VARCHAR  |NULL|NULL|15    |NULL      |30        |NO
> ....
> I'm quite sure that there's no point in this case to use like-clause 
> for an integer typed column, but there are several cases that the like 
> clause can be useful for integer typed column, like when trying to 
> find out an records that contains such and such part of a number.
> When using mysql, using like-clause on non-string types works in my 
> proggie prior to porting it to derby.
> Anyway, is there a configuration that can be use to be 'friendly' for 
> using like-clause on non-string types ? Or perhaps anything else that 
> i'm missing out from the docs ? :)
> Regards,
> Albert Kam
> -- 
> Do not pursue the past. Do not lose yourself in the future.
> The past no longer is. The future has not yet come.
> Looking deeply at life as it is in the very here and now,
> the practitioner dwells in stability and freedom.
> (Thich Nhat Hanh) 
View raw message