SQL/Java Problem

Ziycon

The below code works fine for the first 3 queries but on the last query it throws an exception just before the if(rset4.next()), i cant figure it out! Anyone have any ideas?

if(value.equals("pk1"))
			{
				stmt1 = conn.createStatement();
				rset1 = stmt1.executeQuery("SELECT id, package, available FROM room1 WHERE package = 'June 5th - June 18th'");
				if (rset1.next())
				{
					sqlAvailID1 = rset1.getInt(1);
					sqlAvailPK1 = rset1.getString(2);
					sqlAvail1 = rset1.getInt(3);
					if(sqlAvail1 == 1)
					{
						sqlAvailID1 = 0;
						sqlAvailPK1 = null;
					}
				}
				stmt2 = conn.createStatement();
				rset2 = stmt2.executeQuery("SELECT id, package, available FROM room2 WHERE package = 'June 5th - June 18th'");
				if (rset2.next())
				{
					sqlAvailID2 = rset2.getInt(1);
					sqlAvailPK2 = rset2.getString(2);
					sqlAvail2 = rset2.getInt(3);
					if(sqlAvail2 == 1)
					{
						sqlAvailID2 = 0;
						sqlAvailPK2 = null;
					}
				}
				stmt3 = conn.createStatement();
				rset3 = stmt3.executeQuery("SELECT id, package, available FROM room3 WHERE package = 'June 5th - June 18th'");
				if (rset3.next())
				{
					sqlAvailID3 = rset3.getInt(1);
					sqlAvailPK3 = rset3.getString(2);
					sqlAvail3 = rset3.getInt(3);
					if(sqlAvail3 == 1)
					{
						sqlAvailID3 = 0;
						sqlAvailPK3 = null;
					}
				}
				stmt4 = conn.createStatement();
				rset4 = stmt4.executeQuery("SELECT id, package, available FROM room4 WHERE package = 'June 5th - June 18th'");
				System.out.println("1");
				if (rset4.next())
				{
					System.out.println("2");
					sqlAvailID4 = rset4.getInt(1);
					sqlAvailPK4 = rset4.getString(2);
					sqlAvail4 = rset4.getInt(3);
					System.out.println("3");
					if(sqlAvail4 == 1)
					{
						sqlAvailID4 = 0;
						sqlAvailPK4 = null;
					}
					System.out.println("4");
				}
				System.out.println("5");
			}

lynchie

What exception does it throw? You should post your stack trace as well, otherwise we are not gonna be able to figure out why its failing..

Ziycon

i've tried everything and cant get it to print out the error, its like its just stopping before the fourth if(rset4.next()) line! So confused!

lynchie

Put a try catch block around it

try
{
...
}catch(Exception e)
{
e.printStackTrace();
}

That should print out the exception then

TabulaRasa

Could be that rset4 has no elements in it. Try getting an SQL window and issuing the statements there.

Also put try catches around the code.

bonkey

My gues is that the first two queries each return 0 or 1 records. The third query returns more than 1 query, and thus the data in rset3 is not fully retrieved, and this is causing problems when you try opening a new recordset against the same connection.

You might need something like this in each "if block" :

if (rset3.next())
{
	sqlAvailID3 = rset3.getInt(1);
	sqlAvailPK3 = rset3.getString(2);
	sqlAvail3 = rset3.getInt(3);
	if(sqlAvail3 == 1)
	{
		sqlAvailID3 = 0;
		sqlAvailPK3 = null;
	}
	while (rset3.next()); // loop to end of recordset
	rset3.close();        // can't remember if the close method exists in java. 
	                      // Too lazy to check.
	rset3 = null;         
}