4 Replies Latest reply: Aug 10, 2012 1:09 PM by rp0428 RSS

    Resultset issue

    955052

      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
          Different database/schema is a common mistake made.
          • 2. Re: Resultset issue
            955052
            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
              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
                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