java.sql.SQLException: Exhausted Resultset

[Bharat Agarwal]

Hi, I am trying to insert and retreive an Bfile into an oracle database and i get the error as stated above. Can someone help me out please, what does that error means. I am sending the code hereby :

        if ( evt.target == Button7 )
        {
            System.out.println( "Button Pressed" );
            txtfield.append( "Button submit pressed" );
            for ( int i = 0; i < num; i++ )
            {
                System.out.println( "Land Points are : " + land_points_xy[ i ] + " Number " + i );
            }
            for ( int j = 0; j < num1; j++ )
            {
                System.out.println( "Region points are : " + regi_points_xy[ j ] + " Number" + j );
            }
            try
            {
                FileWriter fw = new FileWriter( "land.txt" );
                FileWriter fw1 = new FileWriter( "Regi.txt" );
                PrintWriter pw = new PrintWriter( fw );
                PrintWriter pw1 = new PrintWriter( fw1 );
                //              int i=0, k=0;
                for ( int i = 0; i < num1; i = i + 2 )
                {
                    pw.print( land_points_xy[ i ] );
                    pw.print( "," );
                    i++;
                    pw.print( land_points_xy[ i ] );
                    pw.print( "\n" );
                }
                System.out.println( num1 );
                for ( int k = 0; k < num; k = k + 2 )
                {
                    pw1.print( regi_points_xy[ k ] );
                    pw1.print( "," );
                    k++;
                    pw1.print( regi_points_xy[ k ] );
                    pw1.print( "\n" );
                }
                System.out.println( num );
                pw.close();
                pw1.close();
            }
            catch ( IOException ioe )
            {
                System.out.println( "IO Error :" + ioe );
            }
            System.out.println( "HELLO" );
            try
            {
                DriverManager.registerDriver( new oracle.jdbc.driver.OracleDriver() );
                System.out.println( "Connecting to diglib2, please wait........" );
                Connection conn = DriverManager.getConnection( "url", "user_id", "paswd" );
                DriverManager.registerDriver( new oracle.jdbc.driver.OracleDriver() );
                conn.setAutoCommit( false );
                System.out.println( "connected" );
                Statement stmt = conn.createStatement();
                stmt.execute( "INSERT INTO TEST1 VALUES('hi', 'hello', bfilename('301', 'land.txt'), 'HI')" );
                //      stmt.execute ("INSERT INTO TEST1 VALUES('hi', 'hello', 'abc', 'HI')");
                System.out.println( "Inserted" );
            }
            catch ( Exception e )
            {
                e.printStackTrace();
            }
        }//end of submit button .................. 
        //      if (source==Button8)    
        if (evt.target == Button9)  
        {
            try
            {
                DriverManager.registerDriver( new oracle.jdbc.driver.OracleDriver() );
                System.out.println( "connedting to server, please wait........." );
                Connection conn = DriverManager.getConnection( "url", "usr", "paswd" );
                //          conn.setAutoCommit (false);
                Statement stmt = conn.createStatement();
                System.out.println( "connected" );
                ResultSet result = stmt.executeQuery( "SELECT draw_id1, i_id1, ma_desc1 FROM TEST1" );
                System.out.println( "Result set created " );
                System.out.println( result.next() );
                while ( !result.next() )
                {
                    System.out.println( "in the while loop" );
                    String x = result.getString( 1 );
                    String y = result.getString( 2 );
                    BFILE bfile = ( (OracleResultSet)result ).getBFILE( 3 );
                    String z = result.getString( 4 );
                    System.out.println( x + y + z );
                    dumpBfile( conn, bfile );
                }
                System.out.println( "Out of the loop" );
            }
            catch ( Exception e )
            {
                e.printStackTrace();
            }
        }
    public void dumpBfile (Connection conn, BFILE bfile) throws Exception 
    {
        System.out.println( "Dumping File " + bfile.getName() );
        System.out.println( "File exists " + bfile.fileExists() );
        System.out.println( "File open : " + bfile.isFileOpen() );
        System.out.println( "Opening File : " );
        bfile.openFile();
        System.out.println( "File open : " + bfile.isFileOpen() );
        long length = bfile.length();
        System.out.println( "File Length : " + length );
        int chunk = 10;
        InputStream instream = bfile.getBinaryStream();
        byte[] buffer = new byte[ chunk ];
        while ( ( length = instream.read( buffer ) ) != -1 )
        {
            System.out.println( "Read " + length + " bytes : " );
            for ( int i = 0; i < length; i++ )
            {
                System.out.println( buffer[ i ] + " " );
            }
            System.out.println();
        }
    }

I add the ubb code tags for easier reading. -- Carl

[This message has been edited by Carl Trusiak (edited August 14, 2001).]

[Bharat Agarwal]

The detailed error message is :
java.sql.SQLException: Exhausted Resultset
at java.lang.Throwable.fillInStackTrace(Throwable.java:native)
at java.lang.Throwable.<init>(Throwable.java:38)
at java.lang.Exception.<init>(Exception.java:24)
at java.sql.SQLException.<init>(SQLException.java:22)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:114)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:156)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:219)
at oracle.jdbc.driver.OracleStatement.prepare_for_new_get(OracleStatement.java:2289)
at oracle.jdbc.driver.OracleStatement.getStringValue(OracleStatement.java:2434)
at oracle.jdbc.driver.OracleResultSetImpl.getString(OracleResultSetImpl.java:285)
at TestProject.action(TestProject.java:393)
at java.awt.Component.handleEvent(Component.java:570)
at java.awt.Component.postEvent(Component.java:935)
at java.awt.Button.process(Button.java:205)
at java.awt.ActionEvt.dispatch(ActionEvt.java:16)
at java.awt.EventDispatchThread.run(EventDispatchThread.java:39)

[Parvinder Arora]

It must be because for the reason that when the resultset is used before that statement must have been closed
hth
~Parvinder

[Carl Trusiak]

That exception will occur whenever you get one row returned. You need to change

System.out.println( result.next() );
while ( !result.next() )
{                    
    System.out.println( "in the while loop" );
    String x = result.getString( 1 );
    String y = result.getString( 2 );
    BFILE bfile = ( (OracleResultSet)result ).getBFILE( 3 );
    String z = result.getString( 4 );
    System.out.println( x + y + z );
    dumpBfile( conn, bfile );
}

to

while ( result.next() )
{                    
    System.out.println( "I have result.next()" );
    System.out.println( "in the while loop" );
    String x = result.getString( 1 );
    String y = result.getString( 2 );
    BFILE bfile = ( (OracleResultSet)result ).getBFILE( 3 );
    String z = result.getString( 4 );
    System.out.println( x + y + z );
    dumpBfile( conn, bfile );
}

Andd that should eliminate the error. If you had one row returned, when you did the System.out.println( result.next() ); the ResultSet was navigated to that row and printed out "true" Now when you did the while ( !result.next() ), it navigated beyound the end of the ResultSet. result.next() returns false indicating that you are no longer within the rows but, since you did a ! you go ahead and try and access a null row. Hence the Exhausted Resultset Exception


------------------
Hope This Helps
Carl Trusiak, SCJP2

[Bharat Agarwal]

Thanx guys,
I did tried all this methods that you had advised before posting the question. I however got rid of that error but I am in the other error now.. I tried to make an binary file of all the pixel values and then try to insert that binary file into the BFILE column of the database. And when I try to read the name of the file entered, it gives some weird name as oracle.sql.bfile@anw34 something of this kind. Also, when I try to use the sql methods( .FileExists() or .FileOpen()), it says that the file of the directory doesnot exists for this operation.
Is this problem something that is related to the server setting. The server that I am working on is Linux Mandrake 7.2. i am able to insert all other type of datatype in the database, but when it comes of using of blob or Bfile, I am stuck as it gives me all kinds of error which I am not able to handle. If you guys or any one else have any suggestions, I would really appreciate. I am ready to send all the code, if that helps to answer my question....
Thanx a lot in advance....
Bharat.