dcsimg
Query ...WHERE IN(...) and CMP 1.1 finder
1 posts in topic
Flat View  Flat View
TOPIC ACTIONS:
 

Posted By:   Alex_S
Posted On:   Monday, June 21, 2004 04:17 AM

Hi, I need to write some finder - SELECT * FROM table1 WHERE id IN (id1,id2,...,idN) id is of type long and the number of N is beforehand unknown. I creete somewhere list of id's - String ids = "1,2,3,4"; this string is a parameter fo my finder - SELECT * FROM table WHERE id IN (?) after generation deploy code looks like - public EJSFinder findByIds(java.lang.String ids) { ... PreparedStatement pstmt = getPreparedStatement("SELECT * FROM table1 WHERE id IN (?)"); pstmt.setString(1,ids); ... this code does not work; following error occurs - - ORA-01722: invalid number.    More>>

Hi,
I need to write some finder -

SELECT * FROM table1 WHERE id IN (id1,id2,...,idN)

id is of type long and the number of N is beforehand unknown.
I creete somewhere list of id's -

String ids = "1,2,3,4";
this string is a parameter fo my finder -

SELECT * FROM table WHERE id IN (?)

after generation deploy code looks like -

public EJSFinder findByIds(java.lang.String ids) {
...
PreparedStatement pstmt =
getPreparedStatement("SELECT * FROM table1 WHERE id IN (?)");
pstmt.setString(1,ids);
...

this code does not work; following error occurs -

- ORA-01722: invalid number.

I think because String s in result query is within apostrophe -
...IN('1,2,3,4')
instead of ...IN(1,2,3,4).
Is there any ideas for my problem? Thanks.

   <<Less

Re: Query ...WHERE IN(...) and CMP 1.1 finder

Posted By:   Bhagyashree_Jayaram  
Posted On:   Tuesday, June 22, 2004 03:44 AM

Hi,



what you are thinking is write you are getting an error as the ids passed contains '1,2,3,4' instead of 1,2,3,4.


while defining the EJB-QL take the parameter type for each parameter to be of type long and pass the values for those ids.So now the genereated finder would containing the setting of each attribute as


pstmt.setLong(1,id1)


pstmt.setLong(2,id2)
.
..
.
.


pstmt.setLong(N,idN)


and your problem will be solved.


Hope this helps....
Bhgaya

About | Sitemap | Contact