This week's book giveaway is in the Clojure forum.
We're giving away four copies of Clojure in Action and have Amit Rathore and Francis Avila on-line!
See this thread for details.
Win a copy of Clojure in Action this week in the Clojure forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Connection and PreparedStatement Relation...??

 
Somnath Mallick
Ranch Hand
Posts: 482
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1510
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You should use the same connection object. Verify that the mainTable resultset actually has data
 
Somnath Mallick
Ranch Hand
Posts: 482
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 482
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Also mainTable.getRow() doesn't throw an exception so which means there must be at least one row in there!
 
Jan Cumps
Bartender
Posts: 2577
11
C++ Linux Netbeans IDE
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 482
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2577
11
C++ Linux Netbeans IDE
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
    Posts: 482
    • 0
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    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
    Posts: 2577
    11
    C++ Linux Netbeans IDE
    • 0
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    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
    Posts: 2577
    11
    C++ Linux Netbeans IDE
    • 0
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    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
    Posts: 482
    • 0
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    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
    Posts: 1510
    • 0
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    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
    Posts: 2577
    11
    C++ Linux Netbeans IDE
    • 0
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    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
    Posts: 482
    • 0
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Well here's my java method which retrieves the connection object:


    I have also attached my connection details as a screen-shot!
    DB Details.JPG
    Connection Details.
    [Thumbnail for DB Details.JPG]
     
    Jan Cumps
    Bartender
    Posts: 2577
    11
    C++ Linux Netbeans IDE
    • 0
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    They are the same
     
    amit punekar
    Ranch Hand
    Posts: 544
    • 0
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    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
    Bartender
    Posts: 2577
    11
    C++ Linux Netbeans IDE
    • 0
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    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
    Posts: 544
    • 0
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator


    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
    Posts: 482
    • 0
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Thanks Amit, Jan and all for the help. Using two prepared statements worked for me!
     
    It is sorta covered in the JavaRanch Style Guide.
    • Post Reply
    • Bookmark Topic Watch Topic
    • New Topic