how to take away the ' ' in the query statement?

[kttang]

I am using JBoss-2.2.1 and MySql 3.23.43.
I tried to put 'IN' into my query statement to optimize speed of the finder method, for example,
" select A, B from DBT where C in (x,y,z); " instead of doing mulitiply query statements as
" select A, B from DBT where C=x; ",
" select A,B from DBT where C=y; "...
In the finder method of jaws.xml, I put C in ({0}) instead of C={0}.
I passed the parameter as a string contains "x,y,z" where C 's type is int(11) in the DB.
In the log file of MySql, the statement becomes
"select A, B from DBT where C in ('x,y,z');" (with the ' ' ) and the query statments return empty set.
In the command line, I tried the same statement with the ' ' and it returned the empty set as expect. When I tried the statement without the ' ', it returned what I wanted.

I want to ask if there is any way that I can 'notify' JBoss not to put ' ' in a particular finder method. Or, any way to take away the ' ' in the statement that MySql receives. I'll appreciate your help.

Ken

[davidjencks]

I have never come across a parameterized query handler that works with "in". I think it highly unlikely that your method can possibly work -- if C is an int(11), I would expect the database to want int(11) parameters.

You might try
C in ({0}, {1}, {2})
and when that doesn't work

C={0} or C={1} or C={2} which should work with any reasonable db.

[abrasax]

I have the same problem...
Your solution is fine, but what when I don't know the number of the parameters? Do I really need to use BMP to solve that?

Regards
Martin

[fiftefora]

Seems like there ought to be a way to trick jaws into
passing in the string without quotes. After all, it passes in Integers and things like that without quotes. I tried
making a finder with parameter type Object, but it still
added the quotes. :-(

Or: It seems like there ought to be some tricky thing you could do in MySQL with substrings and concatenation and such. But I don't yet see how to do it. However, I heard a rumor that there's a plan to make Perl the stored procedure language of MySQL. Then this kind of trick would definately be possible, it seems to me. Ugly, maybe, but possible.

(I basically didn't say anything, but on the chance that it would send someone off into finding the answer, I thought I'd post these musings....)

[gigatoad]

I also agree that it would be very nice to make the ' ' optional in the query.

Where I would like to use it is in a case like this:

SELECT id from table WHERE myfield like {0} ORDER BY {1}

Where the {1} variable would be: upper(title) desc

That way I could do a case insensitive order by.

just my 2 cents...

[dsundstrom]

This is the way JDBC works. The parameters are turned directly into a ? in a prepared statement.

Use dynamic-ql in JBossCMP if you want this feature.