ResultSet, problem with two rows.

[skarosi]

Hi all,
I am quite new to jdbc but i would like to use it for an application that i am making.
I have a table A with fields:
a, b, c, d, e.
i want to get two specific rows, for e=1 or 2(for example).
and i want fields a and b for the first one and c and d for the second one.

I tried to use 2 different resultSets, but i am getting an error, empty result set.

Then i tried to use a single result set, but i cant distinguish the fields that i want.

 rs = statement.executeQuery("select *  from A where e like '"+t+"' or e like '"+m+"'");
             rs.next();
             HD = rs.getString("a");
            HW = rs.getString("b");
          
             rs.next();
             
            AD = rs.getString("c");
            AW = rs.getString("d");

That would return the values of the first record, even though i use the next command.

I would prefare to have to results sets, since i could just use two different queries to get exactly what i want.

thanks in advance,ilias

 

[stefanwagner]

SELECT a1.a, a1.b, a2.c, a2.d 
FROM a a1, a a2 
WHERE a1.e = 1 
AND a2.e = 2

should do it, but your way isn't wrong; I don't see why it shouldn't work.

don't visit my homepage:

http://home.arcor.de/hirnstrom/minis/index.html

 

[skarosi]

the problem isnt at the sql code, but the java code, or more likely the jdbc code.

 

[Diancecht]

First of all, I think you should have a look at PreparedStatement.

Appart from that, maybe you're missing the % on the like statements, but I agree Stefan: the problem is in the SQL, not in the Java part.



Cheers,
Dian

 

[stefanwagner]

Well - the situation is unclear. First of all, we should look for an error - later we may point on best practices (preparedStatement, attribute- oder variablenames). Pointing first to preparedStatement which will not heal the code is confusing at best.

Here we read:

for e=1 or 2(for example).

which fits to Integers but the statement says:

e like '"+t+"' or e like '"+m+"'");

which is string comparision, first overseen by me.

I decided to go for the String-way and change the requirement to

for e LIKE '1%' or e LIKE '2%'

So we need a little db - let's build it:

CREATE TABLE A (a varchar, b varchar, c varchar, d varchar, e varchar); 
insert into a values ('a2', 'b2', 'c2', 'd2', '20');
insert into a values ('a1', 'b1', 'c1', 'd1', '10');
select * from A;

a | b | c | d | e
----+----+----+----+----
a2 | b2 | c2 | d2 | 20
a1 | b1 | c1 | d1 | 10
(2 Zeilen)

Running the code

		String t = "1%";
		String m = "2%";
		String HD, HW, AD, AW;
		
		ResultSet rs = statement.executeQuery ("SELECT * FROM A WHERE e LIKE '" + t + "' OR e LIKE '" + m + "'");
		rs.next();
		HD = rs.getString ("a");
		HW = rs.getString ("b");

		rs.next();

		AD = rs.getString ("c");
		AW = rs.getString ("d");
		
		System.out.println (HD + '\t' + HW + '\t' + AD + '\t' + AW);

gives me:
a2 b2 c1 d1
However:

		String sql = "SELECT a1.a, a1.b, a2.c, a2.d " +
			" FROM a a1, a a2 " + 
			" WHERE a1.e LIKE '" + t +  "'" + 
			" AND a2.e LIKE '" + m + "'";
		System.out.println (sql);
		ResultSet rs = statement.executeQuery (sql);
			
		rs.next();
		HD = rs.getString ("a");
		HW = rs.getString ("b");
		AD = rs.getString ("c");
		AW = rs.getString ("d");

leads to the same result.
a1 b1 c2 d2
Hm. Not the same? Well - just a switch somewhere.

And here with preparedStatement, which simplifies masking on the one hand, and protects you from sql-injection on the other.

		String sql = "SELECT a1.a, a1.b, a2.c, a2.d " +
			" FROM a a1, a a2 " + 
			" WHERE a1.e LIKE ? " + 
			" AND a2.e LIKE ? ";
		PreparedStatement pstmt =  conn.prepareStatement (sql);
		String t = "1%";
		String m = "2%";
		System.out.println (sql); 
		pstmt.setString (1, t);
		pstmt.setString (2, m);
		
		String HD, HW, AD, AW;		
		ResultSet rs = pstmt.executeQuery ();

don't visit my homepage:

http://home.arcor.de/hirnstrom/minis/index.html

 

[Diancecht]

Blah, blah, and you end doing it with a PS

Cheers,
Dian

 

[skarosi]

Thanks everybody. with ur hints i figured out an other way to do it. dont know if it is the correct way to go, but it works.
I just close the statement and reopen it with a second query.
That way i have to sets, each with just the information i want and not all the fields

 

[stefanwagner]

@Diancecht: The problem wasn't not to use prepared statements - was it?

I don't end doing it, because it's not my problem.

I often enough have a trusted source, so I don't have to take care about SQL injection.

Whether quoting is a problem here - we will maybe never know, since skarosi claims to have solved it ignoring our hints. He just closes the statement.

What about int and String, LIKE without pattern? But he will believe till the end of times closing the stmt will help in a magical way.



don't visit my homepage:

http://home.arcor.de/hirnstrom/minis/index.html

 

[Diancecht]

Stefan, I was just joking, no offence.





Cheers,
Dian

 

[stefanwagner]

Oh, pardon. Maybe I was too thin skinned yesterday.

don't visit my homepage:

http://home.arcor.de/hirnstrom/minis/index.html

 

[skarosi]

Hey Stefan! take it easy m8! I dont know if u got upset with me or with Dian, but i want to use things that i understand rather than things i just copied.
and since this worked i dont see a reason not to use it.

 

[stefanwagner]

So you didn't understand SQL-injection, never heard of it?

And you don't understand masking in SQL?

You didn't understand your problem and solved it by accident, since, as I showed, using "WHERE e LIKE '" + t + "' OR e LIKE '" + m + "'"); and two times "rs.next ()" works.

You found your new solution, but you don't know why it works, nor did you tell us whether you use LIKE ...% or changed it to "e = 1 OR e = 2" or something.

From a performance view it might be no problem to close the statement, reopen it, and send a second statement to the database.

If you have difficulties in understanding the hints, just tell us. But asking for hints, then ignoring them is not very polite - why did you ask at all?

don't visit my homepage:

http://home.arcor.de/hirnstrom/minis/index.html

 

[timw]

The next() method on a ResultSet returns a boolean that you SHOULD be checking to ensure you have a valid row and have not dropped off the end of the set. It may be that your SQL only returns one row and the second next() returns false to tell you that. The ResultSet implementor may have coded to preserve the last valid row after a call to next() goes off the end, hence your obeserved behaviour.

Tim

 

[Diancecht]

I would expect an exception when calling next() is the ResultSet has no more rows, but looking at the documentation it's not clear to me.

Cheers,
Dian

 

[timw]

Yeah, it's not 100% crystal is it? However, if he just had one row in the resultset, then the second next() should return false by definition. No exception is indicated. Attempting to then read the resultset is what is probably undefined behaviour in this case.

Tim