aspose file tools*
The moose likes JDBC and the fly likes PreparedStatement's executeQuery() returning resultset with no rows Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "PreparedStatement Watch "PreparedStatement New topic
Author

PreparedStatement's executeQuery() returning resultset with no rows

Sourin K. Sen
Ranch Hand

Joined: May 02, 2006
Posts: 86
Hi All,

My problem is that when I fire a query directly into MySQl, it returns me the rows with proper data. But when I try to fire the same query via PreparedStatement, its fetching me a resultset with no rows.

Inorder to ensure that I'm firing the same query in the db directly, I wrote this code :



Once the query was printed onto the console, I copied it and ran it directly into the mysql console. It fetched me around 11,000 records.
But the resultset in the java app even though was not null, yet contained no records.

One thing, the java app is on one server and the database is on another server but there was no problem in connection settings as statement.executeQuery() was able to execute without any exceptions.

Please help me out as this problem is driving me crazy...
[ October 18, 2008: Message edited by: Sourin K. Sen ]

Regards,<br />Sourin.
Sourin K. Sen
Ranch Hand

Joined: May 02, 2006
Posts: 86
Ok..

I used Statement instead of PreparedStatement and it worked.

Can anyone tell me as to what went wrong with PreparedStatement??
Rene Larsen
Ranch Hand

Joined: Oct 12, 2001
Posts: 1179

We need to see your code, if we should have a chance of given you a clear answer.


Regards, Rene Larsen
Dropbox Invite
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18669
    
    8

And you're assuming from that line of code that the result set was empty? That's a bad assumption. The getFetchSize() method doesn't return anything like the number of rows which the result set will return if you ask it. Read its documentation again, more carefully this time. Also note that there's a setFetchSize() method; that would make no sense if getFetchSize() returned the number of rows.
Sourin K. Sen
Ranch Hand

Joined: May 02, 2006
Posts: 86
Originally posted by Paul Clapham:
And you're assuming from that line of code that the result set was empty? That's a bad assumption. The getFetchSize() method doesn't return anything like the number of rows which the result set will return if you ask it. Read its documentation again, more carefully this time. Also note that there's a setFetchSize() method; that would make no sense if getFetchSize() returned the number of rows.

You are absolutely right Paul.

I noticed that after putting a sysout statement in the while loop.
But still, the problem remains the same, PreparedStatement is fetching rows for my local system but not for the production environment whereas Statement is working everywhere.

Has this something to do with connection pooling?

Here's a piece of my code :

DBConnection class for getting connection :


And here's how I was using PreparedStatement :



Also, here's a piece of code from my context.xml :

However, in this piece of code, preparedstatement isnt using the pooled connection object. It is using a normal connection object as created in DBConnection class.

I simply dont understand as to why it is not working on the production server....

Please help...

[ October 21, 2008: Message edited by: Sourin K. Sen ]

[ October 21, 2008: Message edited by: Sourin K. Sen ]

[ October 22, 2008: Message edited by: Sourin K. Sen ]
[ October 22, 2008: Message edited by: Sourin K. Sen ]
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2503
    
    8

Have you tried to use PreparedStatement.setDate() in stead of setString?
[ October 21, 2008: Message edited by: Jan Cumps ]

OCUP UML fundamental and ITIL foundation
youtube channel
Sourin K. Sen
Ranch Hand

Joined: May 02, 2006
Posts: 86
Originally posted by Jan Cumps:
Have you tried to use PreparedStatement.setDate() in stead of setString?

[ October 21, 2008: Message edited by: Jan Cumps ]


Sorry, I printed out the wrong line over there.
Here is the actual line :


I had done this because I needed to convert the java.util.Date to java.sql.Date. So the only option I though was through milliseconds.

I suppose I have rectified the errors from my post now.
Can anyone now tell me as to what could be going wrong with PreparedStatement??
[ October 22, 2008: Message edited by: Sourin K. Sen ]
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2503
    
    8

I suppose I have rectified the errors from my post now.
You changed your post so much that we don't see the issue anymore. The SQL querythat you used for the Statement and Prepared Statement are gone.

You are using setTimeStamp. This will only return records that match dailyDetails.getDate() to the millisecond.
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30764
    
156

Sourin,
In the future, please "reply" rather than "edit" when changing code. This allows the conversation to still make sense later.


[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
Sourin K. Sen
Ranch Hand

Joined: May 02, 2006
Posts: 86
Originally posted by Jeanne Boyarsky:
Sourin,
In the future, please "reply" rather than "edit" when changing code. This allows the conversation to still make sense later.


Hi Jeanne,

It really doesnt matters with my current post as this is the exact same code after editing which is working on my local machine but not on the production server.

While trying to find a solution to my problem, I had changed my original to such an extent, that while posting my problematic code, I had to edit it lot many times. I mean, I had replaced PreparedStatement with Statement in my atual code and hadnt backed up my PreparedStatement code which is why I needed to edit it.

Once I reproduced the problem with the PreparedStatement (again it was working on my machine and not on production), then I posted that code over here via editing because the problem needs to be solved in this code only and not in the previously posted code.
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

Originally posted by Sourin K. Sen:


Hi Jeanne,

It really doesnt matters with my current post as this is the exact same code after editing which is working on my local machine but not on the production server.



Hi Sourin,

actually it matters, if you see your post from top to bottom you will notice that a few number of people have responded to your query. You may/may not have change your code as per their suggestion.

If you will keep on changing code they will also loose the track of exact problem.


You know when few days back when I came to answer this post i could not even find the actual problem because you have changed your code number of times.

If you change your code on your local machine, do post same code on forum. It can also be possible that your new code is causing some new problem and you will find people are answering with reference to initial problem.

Just imagine once your code is working and you edit your code with your latest working code, then all the answer to your post will become useless.

To make complete thread a sensible discussion it is always advisable to click on reply and post new code .


Shailesh


Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
Sourin K. Sen
Ranch Hand

Joined: May 02, 2006
Posts: 86
Originally posted by Shailesh Chandra:

You know when few days back when I came to answer this post i could not even find the actual problem because you have changed your code number of times.


Hi Shailesh,

I think I am stil not able to clear my point. I know I should have posted the actual problematic code in the first place but I failed to do so properly as I didnt have backup of my problematic code.

The last time I edited/reposted the code, it was the actual problematic code which I previously had. That is to say, the code that you see over here is the code where PStmt is failing to fetch results. So you dont need to think about what I had posted earlier as it would make no sense in trying to find the problem in an erratic code.

As for this code, I know for sure that there is no programatic errors or it wouldn't have run properly on my machine which makes it a lot difficult for me to find the actual cause of failure of this code on production server.

What I want to know is if there is some specific limitation of PStmt like, whether it requires specific connection timeout settings or something else while fetching large number of records for the only difference that is actually clear to me between my local machine and the production server is the number of records.

On my local system, I am using this code to fetch hardly 100 records whereas on production it fetches more than 10000 records.

So, now lets discuss on as to I shouldnt have edited my post and all that for I am conscious of my mistake but then "human means err" and I wont repeat it in future, so lets rather focus on "this" problematic code itself try to find out the cause and solution for this.(even finding the cause atleast would be of great help )
siva saran
Greenhorn

Joined: Mar 11, 2007
Posts: 17
Hi,

If the Backend is using a Stored procedure to return the records , maybe you should try the Callable Statement instead of Prepared Statement.
 
 
subject: PreparedStatement's executeQuery() returning resultset with no rows