• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

PreparedStatement's executeQuery() returning resultset with no rows

 
Ranch Hand
Posts: 86
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 ]
 
Sourin K. Sen
Ranch Hand
Posts: 86
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Ok..

I used Statement instead of PreparedStatement and it worked.

Can anyone tell me as to what went wrong with PreparedStatement??
 
Ranch Hand
Posts: 1179
Mac OS X Eclipse IDE
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
We need to see your code, if we should have a chance of given you a clear answer.
 
Marshal
Posts: 28193
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 86
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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 ]
 
Bartender
Posts: 2661
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Have you tried to use PreparedStatement.setDate() in stead of setString?
[ October 21, 2008: Message edited by: Jan Cumps ]
 
Sourin K. Sen
Ranch Hand
Posts: 86
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 2661
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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.
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Sourin,
In the future, please "reply" rather than "edit" when changing code. This allows the conversation to still make sense later.
 
Sourin K. Sen
Ranch Hand
Posts: 86
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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.
 
Ranch Hand
Posts: 1087
Oracle Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
 
Sourin K. Sen
Ranch Hand
Posts: 86
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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 )
 
Greenhorn
Posts: 17
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi,

If the Backend is using a Stored procedure to return the records , maybe you should try the Callable Statement instead of Prepared Statement.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic