aspose file tools*
The moose likes JDBC and the fly likes Executing query from java program Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Executing query from java program" Watch "Executing query from java program" New topic
Author

Executing query from java program

ashu srivastava
Greenhorn

Joined: Jun 25, 2011
Posts: 6
Hi All,

I am frustrated with a problem. I have created so many queries for DB2 and successfully executed it.
I am facing problem a query which uses IN keyword.

I have a text box on page where user can enter any number of comma separated values. I need to query DB using this parameter as

SELECT * FROM STATE WHERE STATE_ABBREVIATION IN (:stateAbb);

Here, stateAbb value I will pass from my java program.
Lets say user has entered in text box two values - DC, AL.

In my java program I am taking these values one by one and making it as 'DC' and 'AL' and again appending these as 'DC', 'AL' because in query it must be in single quotes.
So, my query became (printed in console):
SELECT * FROM STATE WHERE STATE_ABBREVIATION IN ('DC', 'AL');


Now, when I send it and execute this, I get the following error :
nested exception is java.sql.SQLException: An undefined column name was detected

The same query when I run directly on sql page ..it runs successfully.

Kindly help me as I am not understanding where things are wrong and I tried almost every option.
Thanks in advance.
Fatih Keles
Ranch Hand

Joined: Sep 01, 2005
Posts: 182
I suppose your query is executed with quotes missing somehow.
ashu srivastava
Greenhorn

Joined: Jun 25, 2011
Posts: 6
Fatih Keles wrote:I suppose your query is executed with quotes missing somehow.


Hi,

I am printing the query and it is coming with quotes in console and the same query is running fine when I am running it directly.

Any suggestion.

Thanks,
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30293
    
150

Are you passing "'DC', 'AL" as a single setString() in JDBC? If so, that doesn't work.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
ashu srivastava
Greenhorn

Joined: Jun 25, 2011
Posts: 6
Jeanne Boyarsky wrote:Are you passing "'DC', 'AL" as a single setString() in JDBC? If so, that doesn't work.


Hi,
Thanks for your reply.

If you have any idea then please suggest me that How I should frame my query so that it will fulfill my requirement.

Any reason why JDBC is not understanding parameters inside IN keyword when it is in the correct format, that is, IN ('DC', 'AL')

Thanks.
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30293
    
150

ashu srivastava wrote:
Jeanne Boyarsky wrote:Are you passing "'DC', 'AL" as a single setString() in JDBC? If so, that doesn't work.


Hi,
Thanks for your reply.

If you have any idea then please suggest me that How I should frame my query so that it will fulfill my requirement.

Any reason why JDBC is not understanding parameters inside IN keyword when it is in the correct format, that is, IN ('DC', 'AL')

Thanks.

I don't understand your answer so I'm going to repeat my post more descripively. Are you doing something like this:

If so, you need to change your code to use a loop to generate multiple ? binding variables one per value you wish to insert. In particular, use a loop to generate the SQL with more ? and another loop to call setString() multiple times.

If this is not the problem, please show a little more code.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Executing query from java program