ResultSet progresses after any command

[Meir Gold]

Hi,

I'm using the following code to retrieve and parse data from my Microsoft SQL database:

try {
		  statement=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
		 
		rs=statement.executeQuery("SELECT id from library_table where library='"+library+"' and cell='"+book+"'" );
		
		rs.first();
		System.out.println("p1");
		System.out.println("p2");
		System.out.println("p3");
		
		int id=rs.getInt(1);
} catch (SQLException e) {
		
		e.printStackTrace();
	}
	   

The query I'm executing returns 1 result, as expected, however, it seems that each command that appears after the rs=statement.executeQuery line of code, causes the resultset to progress forward to the next result.
By the time I reach the int id=rs.getInt(1); command, the resultset has moved past the query result and returns an error.

I could not find any documentation for such an issue.
Can anyone explain why this is happening ?

Thanks,

Meir

[Rob Spoor]

Welcome to the Ranch!

Those System.out.println don't affect your ResultSet in any way. The problem is, you don't have any results at all. You don't know it because you ignore the return value, but rs.first() returns false. Just check it.


Also, you should use PreparedStatement to prevent SQL injection. Right now, the values for library and book are used unescaped, which allows a user to put some nasty stuff in there that could go as far as delete your entire table. Ouch.

[Meir Gold]

Actually, I do have a result.
If I place the int id=rs.getInt(1); command immediately after running the query, I get the result I'm expecting.
I also ran the same query using Microsoft SQL Management Studio and could clearly see the result.

I tried the same on a query that returns 22 results and could clearly see that I wasn't getting the expected result in the id=rs.getInt(1).

Meir

[Bill Johnston]

Meir Gold wrote:Actually, I do have a result.
If I place the int id=rs.getInt(1); command immediately after running the query, I get the result I'm expecting.
I also ran the same query using Microsoft SQL Management Studio and could clearly see the result.

I tried the same on a query that returns 22 results and could clearly see that I wasn't getting the expected result in the id=rs.getInt(1).

Meir

But that's different than a query returning one result, which you stated in your initial post. So different code would be needed.

[Meir Gold]

The code is similar, it's just a different query:

rs=statement.executeQuery("SELECT book_id from borrowing_table where borrower_id="+id );
rs.first();
System.out.println("Meir");
System.out.println("Meir");
System.out.println("Meir");
id=rs.getInt(1);

As the query now returns more results, I would have expected the id=rs.getInt(1); to return the ID number of the first result. Instead it returns the number of the 3rd or 4th result.
I also tracked this with the debugger and can clearly see the currentrow value of the rs variable progressing after each command.

Meir

[Meir Gold]

So apparently I ran the code again this morning and everything seems to be working fine.
The currentrow of the resultset in the debugger window still progresses after each line of code, but it looks like I'm getting the results I expected !

Thanks everyone for trying to help.

Meir

[Wendy L Gibbons]

sometimes this happens, i think the compiler somehow just get confused.
I hate to say it but sometimes the helpdesk standby of "have you rebooted" is just the best answer

[Wendy L Gibbons]

Meir Gold wrote:The code is similar, it's just a different query:

rs=statement.executeQuery("SELECT book_id from borrowing_table where borrower_id="+id );
rs.first();
System.out.println("Meir");
System.out.println("Meir");
System.out.println("Meir");
id=rs.getInt(1);

As the query now returns more results, I would have expected the id=rs.getInt(1); to return the ID number of the first result. Instead it returns the number of the 3rd or 4th result.
I also tracked this with the debugger and can clearly see the currentrow value of the rs variable progressing after each command.

Meir

but if you are writing serious code you really should be checking the result of rs.first() before you use it.

[Meir Gold]

Thanks Wendy.
I do have a check for rs.first(), I left it out just to make my question less complicated.

Meir

[Bill Johnston]

Meir Gold wrote:Thanks Wendy.
I do have a check for rs.first(), I left it out just to make my question less complicated.

Meir

But the truth is that is doesn't make it less complicated; it makes it more complicated. And similar code isn't the same as the exact code. Please don't read this the wrong way - I'm not being sarcastic or accusative but instructional ... at least that is how I intend this. You should always post the exact code snippet, byte for byte that you run, and the exact errors and exceptions you are getting as well. If the code would be too much to post, then you should make a smaller version of the code that demonstrates the problem and post that (in this instance apparently that isn't the case though). Again, just a friendly reminder.

Now as to the problem; I have seen very strange things happen in C regarding printf statements changing logic flow, and in one case I never did figure it out. But never in Java. I'd be desirous of following up on this if it was me. Regards,

[Martin Vashko]

I had experienced the weirdest errors in Java caused by the project not being correctly recompiled. It had happened to me more than once in NetBeans. I don't remember debugging a project in such a state, so I cannot comment on these observations. Nowadays I tend to always use Clean and Build to compile the project, which takes a long time, but does recompile all source files.

NetBeans also sometimes report errors in perfectly good code, in which case it usually helps to erase the NetBeans cache from user profile. These two are probably unrelated problems, though.

Nasty surprises in C can easily arise as a result of buffer overruns or passing wrong data type (not corresponding to the format string) into printf/scanf functions (even more easily for scanf). I'm really glad Java prevents this kind of errors from happening.

[Bill Johnston]

Martin Vajsar wrote:I had experienced the weirdest errors in Java caused by the project not being correctly recompiled. It had happened to me more than once in NetBeans. I don't remember debugging a project in such a state, so I cannot comment on these observations. Nowadays I tend to always use Clean and Build to compile the project, which takes a long time, but does recompile all source files.

NetBeans also sometimes report errors in perfectly good code, in which case it usually helps to erase the NetBeans cache from user profile. These two are probably unrelated problems, though.

Some excellent points there about project builds.

[Wendy L Gibbons]

Bill Johnston wrote:

Meir Gold wrote:Thanks Wendy.
I do have a check for rs.first(), I left it out just to make my question less complicated.

Meir

But the truth is that is doesn't make it less complicated; it makes it more complicated. And similar code isn't the same as the exact code. Please don't read this the wrong way - I'm not being sarcastic or accusative but instructional ... at least that is how I intend this. You should always post the exact code snippet, byte for byte that you run, and the exact errors and exceptions you are getting as well. If the code would be too much to post, then you should make a smaller version of the code that demonstrates the problem and post that (in this instance apparently that isn't the case though). Again, just a friendly reminder.

Now as to the problem; I have seen very strange things happen in C regarding printf statements changing logic flow, and in one case I never did figure it out. But never in Java. I'd be desirous of following up on this if it was me. Regards,

also remember printf will format varaibles for you, that is why debugging changed the flow.

[Bill Johnston]

Wendy Gibbons wrote:also remember printf will format varaibles for you, that is why debugging changed the flow.

I don't follow you, Wendy. In the case I mentioned this was a very rare instance where adding a printf statement changed the program flow. As I recall, I printed nothing; was just something like

printf(" ");

.

[Wendy L Gibbons]

Bill Johnston wrote:

Wendy Gibbons wrote:also remember printf will format varaibles for you, that is why debugging changed the flow.

I don't follow you, Wendy. In the case I mentioned this was a very rare instance where adding a printf statement changed the program flow. As I recall, I printed nothing; was just something like

printf(" ");

.

I was thinking about strings with funny stuff in them, they would crash unless you output them. But also any code changes memory addresses so if you were accessing a dodgy pointer it now looked somewhere else for it's dodgy data.

[Bill Johnston]

Wendy Gibbons wrote:

Bill Johnston wrote:

Wendy Gibbons wrote:also remember printf will format varaibles for you, that is why debugging changed the flow.

I don't follow you, Wendy. In the case I mentioned this was a very rare instance where adding a printf statement changed the program flow. As I recall, I printed nothing; was just something like

printf(" ");

.

I was thinking about strings with funny stuff in them, they would crash unless you output them. But also any code changes memory addresses so if you were accessing a dodgy pointer it now looked somewhere else for it's dodgy data.

If it was anything at all like that it wasn't explicit. It was a few years ago now, so I can't be sure by memory what was happening. I don't even recall what exactly changed in the functionality. I mentioned it only to highlight that stuff like this can blindside one in C but is rather less common in Java I would think (or rather from my experience) But I thought Martin's point about project builds can be one way that that it could. I also know - now that I am thinking about it more - that name space / classpath issues have caused me numerous problems over the years as well - especially when building quick + dirty applications where I don't package them carefully (or at all, and let them default).

[Rob Spoor]

Meir Gold wrote:As the query now returns more results, I would have expected the id=rs.getInt(1); to return the ID number of the first result. Instead it returns the number of the 3rd or 4th result.

Unless you use an explicit ORDER BY clause, you can make no assumptions at all about the order of the results. The RDBM is allowed to return the results in any random value when no ORDER BY is defined. My experiences with MS SQL Server have shown that it sometimes indeed does that.