aspose file tools*
The moose likes JDBC and the fly likes Connection and PreparedStatement Relation...?? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Connection and PreparedStatement Relation...??" Watch "Connection and PreparedStatement Relation...??" New topic
Author

Connection and PreparedStatement Relation...??

Somnath Mallick
Ranch Hand

Joined: Mar 04, 2009
Posts: 477
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!

Bosun Bello
Ranch Hand

Joined: Nov 06, 2000
Posts: 1510
You should use the same connection object. Verify that the mainTable resultset actually has data


Bosun (SCJP, SCWCD)
So much trouble in the world -- Bob Marley
Somnath Mallick
Ranch Hand

Joined: Mar 04, 2009
Posts: 477
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
Ranch Hand

Joined: Mar 04, 2009
Posts: 477
Also mainTable.getRow() doesn't throw an exception so which means there must be at least one row in there!
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2516
    
  10

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


OCUP UML fundamental and ITIL foundation
youtube channel
Somnath Mallick
Ranch Hand

Joined: Mar 04, 2009
Posts: 477
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
Bartender

Joined: Dec 20, 2006
Posts: 2516
    
  10

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
    Ranch Hand

    Joined: Mar 04, 2009
    Posts: 477
    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
    Bartender

    Joined: Dec 20, 2006
    Posts: 2516
        
      10

    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
    Bartender

    Joined: Dec 20, 2006
    Posts: 2516
        
      10

    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
    Ranch Hand

    Joined: Mar 04, 2009
    Posts: 477
    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
    Ranch Hand

    Joined: Nov 06, 2000
    Posts: 1510
    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
    Bartender

    Joined: Dec 20, 2006
    Posts: 2516
        
      10

    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
    Ranch Hand

    Joined: Mar 04, 2009
    Posts: 477
    Well here's my java method which retrieves the connection object:


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



    [Thumbnail for DB Details.JPG]

    Jan Cumps
    Bartender

    Joined: Dec 20, 2006
    Posts: 2516
        
      10

    They are the same
    amit punekar
    Ranch Hand

    Joined: May 14, 2004
    Posts: 516
    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


    Regards,
    Amit
    Jan Cumps
    Bartender

    Joined: Dec 20, 2006
    Posts: 2516
        
      10

    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
    Ranch Hand

    Joined: May 14, 2004
    Posts: 516


    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
    Ranch Hand

    Joined: Mar 04, 2009
    Posts: 477
    Thanks Amit, Jan and all for the help. Using two prepared statements worked for me!
     
    I agree. Here's the link: http://aspose.com/file-tools
     
    subject: Connection and PreparedStatement Relation...??