This discussion is archived
4 Replies Latest reply: Aug 10, 2012 11:09 AM by rp0428 RSS

Resultset issue

955052 Newbie
Currently Being Moderated
Hi,

I am trying to execute a query and use the resultset as below:
Connection con = ConnectionManager.getConnection();
st = (Statement)con.createStatement();

rs = st.executeQuery("SELECT DISTINCT ENV_DETAILS1.* , RELEASE_STATUS.RELEASE_NAME, BUILD_DETAILS.RELEASE_ID FROM ENV_DETAILS1,RELEASE_STATUS,BUILD_DETAILS WHERE (ENV_DETAILS1.ENV_ID='5' AND ENV_DETAILS1.RELEASE_ID='2') AND (ENV_DETAILS1.STATUS='active') AND (ENV_DETAILS1.RELEASE_ID=BUILD_DETAILS.RELEASE_ID) AND (RELEASE_STATUS.RELEASE_ID=BUILD_DETAILS.RELEASE_ID)"); 


//ResultSetMetaData rsmd = rs.getMetaData();
//int numberOfColumns = rsmd.getColumnCount(); //this correctly gives the expected number of columns

while (rs.next()) {
//not reaching here

//get values from resultset
int envIdQ=rs.getInt("Env_id");
..
..
}
When I test the same query in SQLDeveloper(1.5.4), it is executing fine and I get the result. But in the code above "rs.next()" is returning false. I am using Weblogic 10.3, ojdbc6.jar and Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production. I am using the Weblogic datasource to get the connection and there seem to be no issue with that as I can execute other queries properly and get expected results. I am not able to figure out that what could be wrong in the above code. It would be great help if anyone can suggest something.

Thanks in advance.
  • 1. Re: Resultset issue
    gimbal2 Guru
    Currently Being Moderated
    Different database/schema is a common mistake made.
  • 2. Re: Resultset issue
    955052 Newbie
    Currently Being Moderated
    Thanks for the reply but can you please elaborate little more? I am using same database connection parameters in the weblogic datasource and in SQL Develeoper where the same query works fine.
  • 3. Re: Resultset issue
    gimbal2 Guru
    Currently Being Moderated
    Simple.

    - in one place you get result A
    - in another place you get result B

    If the query is -exactly- the same, apparently the dataset is different. There is no magic going on. So yeah? Different database? Different user with different rights?
  • 4. Re: Resultset issue
    rp0428 Guru
    Currently Being Moderated
    Welcome to the forum!
    >
    When I test the same query in SQLDeveloper(1.5.4), it is executing fine and I get the result. But in the code above "rs.next()" is returning false.
    >
    Then, as already suggested, that means there is no result set 'for that query, by that user, on that database'.

    So since you know there must be something different you need to find out what the difference is.
    To identify the user add code to query the user from the database
    SELECT USER FROM DUAL;
    If the user is the same then add code to query the database information
    select name, created, platform_id, platform_name, db_unique_name from v$database
    One of those queries will give you a difference.

    You can also use the 'getmetaData' method on the connection instance to get a DatabaseMetaData instance and then query it to get database specific information.
    http://docs.oracle.com/javase/1.5.0/docs/api/java/sql/DatabaseMetaData.html

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points