wood burning stoves 2.0*
The moose likes JDBC and the fly likes Having problem while retrieving data from the resultset Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Having problem while retrieving data from the resultset" Watch "Having problem while retrieving data from the resultset" New topic
Author

Having problem while retrieving data from the resultset

Priya Prasad
Ranch Hand

Joined: Jun 11, 2013
Posts: 31
Somehow


is not working. Any idea why?
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 41114
    
  45
What does "not working" mean - does something blow up?

Does the SQL that is created work if you use it directly against the DB? Have you checked that it is what you think it is?


Ping & DNS - my free Android networking tools app
Priya Prasad
Ranch Hand

Joined: Jun 11, 2013
Posts: 31
Yes. The same statement works perfectly well and gives me the code when I use it directly against the DB. I need to use this value of Code for further processing. But somehow its not working. I need to see the value of the code in the System.out.println. But the resultset is not retrieving the data. How do I go about it?
Priya Prasad
Ranch Hand

Joined: Jun 11, 2013
Posts: 31
It shows illegal operation on empty set.
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 41114
    
  45
Before accessing a ResultSet you always need to check whether it has data by calling rs.next().

The same statement works perfectly well

How do you know this? I don't see any code that would print out the full query - given that, how did you test it? Post the fully-generated query here.
Priya Prasad
Ranch Hand

Joined: Jun 11, 2013
Posts: 31
Yes you are right. I used this..



and it shows Nothing in the resultset.

WHich means I am unable to retrieve the code from the table. Now how do i do it?
On the other hand if i run the same query directly in the SQL editor it works fine.
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 41114
    
  45
On the other hand if i run the same query directly in the SQL editor it works fine.

Again: how do you know this? How have you made sure that the SQL generated by your code is what you think it is?
Priya Prasad
Ranch Hand

Joined: Jun 11, 2013
Posts: 31
Yes I am sure about that. See this.

select Code from combinationtable where Gender='Male' AND Category='T-Shirt' AND SubCategory='V-Neck' AND StyleType='Stripes';

This is what i run in SQL editor.
and the same things I run while in the Java query.

Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 41114
    
  45
and the same things I run while in the Java query.

Yes, I understand that you THINK that that is the query that is being run, but how have you made sure that it actually IS the query that runs? Printing out the query that is being generated is so easy that it should be the next thing you do. And then compare that with what you think is getting generated.
Priya Prasad
Ranch Hand

Joined: Jun 11, 2013
Posts: 31
Resolved, Thank you!
Priya Prasad
Ranch Hand

Joined: Jun 11, 2013
Posts: 31
insert into attributetable values Color='Red',DesignName='Shiva',ReferenceCode='ncd01',S=12,M=12,L=12,XL=12, XXL='' ,FreeSize='',Undefined='',S32='',S34='',S36='',S38='', S40='',S42='',S44='',CostPrice=212.22,InValue=12.22,MRP=212.22,CodeValue='006';

gives me this error

Error code 1064, SQL state 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Color='Red',DesignName='Shiva',ReferenceCode='ncd01',S=12,M=12,L=12,XL=12, XXL='' at line 1

Color, DesignName and Reference code =varchar(100)
rest all integer
CostPrice, in value, mrp and outvalue= real
CodeValue is again varchar
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 41114
    
  45
That's not the right syntax for an INSERT statement. If you run that in your SQL tool you should get the same error.

By the way, have you noticed that the you're not using the CODE tags correctly? The way you're applying them they don't accomplish anything - the opening/closing tags go before/after the code. I had corrected those before, but since this is now the third time you're using them like this, please take a moment to check out how your last post looks like. You can edit it to correct the code tags.
Swastik Dey
Rancher

Joined: Jan 08, 2009
Posts: 1449
    
    6

Why not use PreparedStatement? I guess it makes the syntax bit easier and not prone to sql injection. Correct me if I am wrong.


Swastik
Priya Prasad
Ranch Hand

Joined: Jun 11, 2013
Posts: 31
Ulf Dittmer wrote:That's not the right syntax for an INSERT statement. If you run that in your SQL tool you should get the same error.

By the way, have you noticed that the you're not using the CODE tags correctly? The way you're applying them they don't accomplish anything - the opening/closing tags go before/after the code. I had corrected those before, but since this is now the third time you're using them like this, please take a moment to check out how your last post looks like. You can edit it to correct the code tags.


there you go!

Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 41114
    
  45
That's better. Still better would have been to edit your previous post instead of writing a new one... maybe next time

Have you figured out the correct syntax for INSERT statements? Any SQL guide covers that.
Priya Prasad
Ranch Hand

Joined: Jun 11, 2013
Posts: 31
Yes I figured out my INSERT statement was wrong and now I have replaced it with-



But my table is not taking null values. I have used Netbeans to create the table and i have check the box which says Nulls Allowed. If the user is not entering any value in the text field I want no value to get inserted in the table. How do I do that?
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 41114
    
  45
Something like this:

insert into attributetable values ('Red','Shiva','ncd01',12,23,12,23,12,null,null,null,null,null,12.33,12.33,23.33,43.22,'006')
Priya Prasad
Ranch Hand

Joined: Jun 11, 2013
Posts: 31
Which means in the code i need to write

String s34 = jTextField5.getText();
if(s34=='')
s34="null";

??

or it automatically stores null?
Priya Prasad
Ranch Hand

Joined: Jun 11, 2013
Posts: 31
And it shows this error now

java.sql.SQLException: Before start of result set

I am tired.
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 41114
    
  45
Which means in the code i need to write

String s34 = jTextField5.getText();
if(s34=='')
s34="null";

or it automatically stores null?

Sort of. But strings are not compared using the "==" operator, they're compared using the equals method. And yes, you do not need to write null - null is different from the empty string.

And it shows this error now

java.sql.SQLException: Before start of result set

Are you calling rs.next() before accessing the ResultSet? I'm just guessing because I don't know what code you're now working with. Software development is all about paying attention to detail. Working through a good introductory book (assuming you're not doing that anyway) would help explain things, and you wouldn't have figure .out everything on your own.
Greg Bag
Ranch Hand

Joined: Dec 25, 2010
Posts: 52
If I were you, I'd use PreparedStatement instead of Statement. They are better cached by the database and they protect you against SQL injection.
Priya Prasad
Ranch Hand

Joined: Jun 11, 2013
Posts: 31
Thank you for answering
 
Don't get me started about those stupid light bulbs.
 
subject: Having problem while retrieving data from the resultset
 
Similar Threads
How to display only selected amount of data from database in two dropdown lists?
Hibernate many-to-one association does not work properly
A question in dynamic dropdown box
object design
Show menu and submenu on homepage from database using servlet and jsp