Connection and PreparedStatement Relation...??

[Somnath Mallick]

Hi Everyone,

I would like to know that for executing different PreparedStatement do we need different Connection object? I am using one single connection object and trying to execute three SQL Queries. When i run the fragment of code below, its not going into the while loop. I thought the SQL queries where incorrect. But when i executed them on SQL Developer they returned proper rows!

Connection connection = getDBConnection();

PreparedStatement preparedStatement1 = connection.prepareStatement("select a.* from PORTAL_CP_DETAILS_CHANGE_LOG a, PORTAL_CP_DETAILS b WHERE CHANGE_DATE between sysdate-7 and sysdate and a.ENVIRONMENT=b.ENVIRONMENT and a.CP_NAME= b.CP_NAME");
PreparedStatement preparedStatement2 = connection.prepareStatement("select * from PORTAL_CP_DETAILS");

ResultSet changeLog = preparedStatement1.executeQuery();
ResultSet mainTable = preparedStatement2.executeQuery();
ResultSet checkTable = preparedStatement2.executeQuery();

while (mainTable.next())
{
.....
.....
}

[Bosun Bello]

You should use the same connection object. Verify that the mainTable resultset actually has data

[Somnath Mallick]

I ran the debug on it and i saw the values of ResultSet change from null to OracleResultSetImpl (id=58). So that would mean they are getting filled up with the rows doesn't it?

[Somnath Mallick]

Also mainTable.getRow() doesn't throw an exception so which means there must be at least one row in there!

[Jan Cumps]

Hi. It only throws an exception if a database access error occurs. It does not throw an exception if no row exists. Please verify the javadocs (see link above).

If your code returns a ResultSet, and you do not step into the while loop, this really means that your query "select * from PORTAL_CP_DETAILS" returns 0 (zero) records.

Regards, Jan

[Somnath Mallick]

Well when i execute the same query in SQL Developer it returns 79 rows! So dont you think there's something wrong here!

[Jan Cumps]

Yes. Possibilities are:

you are using a different db user in java.

you are connecting to a different database instance in java (say: a different server?).

you are connecting to a different schema in java (say: same server, different db schema?).

you haven't committed the insert of the 79 records in your other tool.

some other reason

possibility 1 to 3 are the most likely.

Regards, Jan

[Somnath Mallick]

The db which i am connecting to has only one schema called the WLI_SCHEMA. I am logging into that. I checked the IP now and its the same which i used for other coding tasks on the same table! I just copied the code from another class. So i must be connecting to the correct db!

[Jan Cumps]

I am clueless at this point.

Some questions to start debugging the issue:
Did you leave some lines out of the code you gave in post #1?
How do you know it does not go in the while loop?
Can you put a log as first line of the while (like: System.out.println("I am in the whileloop"); )?
Can you replace the query by "select count(*) from PORTAL_CP_DETAILS" , and in the while loop, print the result?

[Jan Cumps]

Can you also show (WITHOUT PASSWORDS) the connection settings you use in SQL Developer , and in your java code?
Complete: server, username, any other things used to connect).

[Somnath Mallick]

Well i was doing a step by step debug. So i saw the control jump from the while statement to the the line after the while loop! and none of the method calls within the while loop got executed because i had 2 breakpoints on two methods within that while loop! Will post the details as soon as possible!

[Bosun Bello]

Also, make sure you check the value of the resultset right before entering the loop to make sure it did not somehow get set to null. You should probably post the whole code.

Somnath Mallick wrote:I ran the debug on it and i saw the values of ResultSet change from null to OracleResultSetImpl (id=58). So that would mean they are getting filled up with the rows doesn't it?

[Jan Cumps]

That would throw an exception in stead of jumping over the while.

Somnath, are you shure that you used the step into button, in stead of the step over button while debugging? (edit: although your breakpoint should catch that...)

[Somnath Mallick]

Well here's my java method which retrieves the connection object:

private static Connection getDBConnection()
	{
		Connection connection = null;

		try 
		{			
			String db_URL = "jdbc:oracle:thin:@10.81.33.6:1521:orb2bvs";
			String db_UserName = "wli_schema";
			String db_Password = "wli_schema";

			Class.forName("oracle.jdbc.xa.client.OracleXADataSource").newInstance();

			connection = DriverManager.getConnection(db_URL, db_UserName, db_Password);
			connection.setAutoCommit(false);
		} 
		catch (InstantiationException e) 
		{
			System.out.println("Unable to instantiate the driver");
			e.printStackTrace();
		}
		catch (IllegalAccessException e) 
		{
			System.out.println("Illegal Access to database");
			e.printStackTrace();
		}
		catch (ClassNotFoundException e) 
		{
			System.out.println("Unable to load driver.");
			e.printStackTrace();
		}
		catch (SQLException e) 
		{
			System.out.println("Unable set Auto Commit to false");
			e.printStackTrace();
		}

		return connection;
	}

I have also attached my connection details as a screen-shot!

[Jan Cumps]

They are the same

[amit punekar]

Hi,
You are using same connection object to create two PreparedStatements and then one by one you are executing "executeQuery" method on them. This will result in only executing second query in your code i.e. "select * from PORTAL_CP_DETAILS".
Moreover because you are executing "preparedStatement2.executeQuery()" twice , so instead of ResultSet reference "mainTable" , you should be using the ResultSet reference "checkTable" to read the data.
Anyways this is not the correct use of re-using the connection. You should execute one query and consumer the ResultSet(might fill up the ArrayList with records) and then re-use the connection to execute another query. This is kind of a crude way to do this.
It would be even better if you would use a Connection pool to re-use the JDBC connections.

Regards,
Amit

[Jan Cumps]

You are using same connection object to create two PreparedStatements and then one by one you are executing "executeQuery" method on them. This will result in only executing second query in your code i.e. "select * from PORTAL_CP_DETAILS".

No. This should not be an issue.

Moreover because you are executing "preparedStatement2.executeQuery()" twice , so instead of ResultSet reference "mainTable" , you should be using the ResultSet reference "checkTable" to read the data.

Yes!! That's it. You found it. How could we have overlooked that.

[amit punekar]

You are using same connection object to create two PreparedStatements and then one by one you are executing "executeQuery" method on them. This will result in only executing second query in your code i.e. "select * from PORTAL_CP_DETAILS".

No. This should not be an issue.

It would not be an issue if we execute the first PreparedStatement and work with the ResultSet and then create another PreparedStatement using the same connection object.

[Somnath Mallick]

Thanks Amit, Jan and all for the help. Using two prepared statements worked for me!