Avatar billede fredand Forsker
03. marts 2004 - 12:53 Der er 11 kommentarer og
2 løsninger

Problems with ResultSet from CallableStatement

Hello!

I have a tricky question.

I execute the following storedprocedure to a Oracle database:
CREATE OR REPLACE PROCEDURE getImages (p_cursor OUT SYS_REFCURSOR) AS BEGIN OPEN p_cursor FOR SELECT ID, FILNAMN FROM bild; END;

with this Java code:
callableStatement = connection.prepareCall(storedProcedure, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
callableStatement.registerOutParameter(i+1, OracleTypes.CURSOR);

To get hold of the ResultSet I do:
callableStatement.executeQuery();
resultSet = (ResultSet) callableStatement.getObject(1);

That seems to work fine, but later on I have to do this:
resultSet.beforeFirst();

But It gives me an exception, that beforeFirst is not allowed:
java.sql.SQLException: Ogiltig Õtgõrd f÷r endast vidarebefordrad resultatuppsõtt
ning: beforeFirst
        at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
        at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
        at oracle.jdbc.driver.BaseResultSet.beforeFirst(BaseResultSet.java:72)
        at p6b4005.DatabaseAccessor.executeOracleStoredProcedure(DatabaseAccesso
r.java:231)
        at p6b4005.DatabaseAccessor.main(DatabaseAccessor.java:806)

Evidently the prepareCall method only effects the returned ResultSet delivered by the callableStatement.executeQuery();
And not the resulset delivered by the resultSet = (ResultSet) callableStatement.getObject(1);

But how should I do to make this ResultSet scrollable so I can call resultSet.beforeFirst();???

Best regards
Fredrik
Avatar billede erikjacobsen Ekspert
03. marts 2004 - 13:06 #1
Your driver does not support repositioning to a previous position in the
result-set, and that's ok - it doesn't have to. You may do one of two:

1) Put results in an array (arraylist...)
2) Reissue you select statement
Avatar billede rasmusbg Nybegynder
03. marts 2004 - 13:11 #2
You should do something like this:

Statement stmt = con.createStatement(
                  ResultSet.TYPE_SCROLL_INSENSITIVE,
                  ResultSet.CONCUR_UPDATABLE);
Avatar billede arne_v Ekspert
03. marts 2004 - 13:22 #3
callableStatement = connection.prepareCall(storedProcedure, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
callableStatement.executeQuery();

returns a scrollable result set.

resultSet = (ResultSet) callableStatement.getObject(1);

returns a not scrollable result set.

I assume.
Avatar billede arne_v Ekspert
03. marts 2004 - 13:22 #4
BTW, many (all ?) JDBC drivers that supports scrollable result sets do it by
reading everything into memory => never use scrolleable result set on large data.
Avatar billede fredand Forsker
03. marts 2004 - 14:00 #5
To erikjacobsen

Hello!
How do mean when you say put the result into a ArraList? Perhaps you can show me some code?

Best regards
Fredrik
Avatar billede arne_v Ekspert
03. marts 2004 - 14:09 #6
Simple move the data from the result set to an ArrayList or something else
the first time you loop through the result set.

The second you just go through the ArrayList instead.
Avatar billede fredand Forsker
03. marts 2004 - 14:22 #7
Of course, stupied me!

BTW mates, give me some "svar" so I could give you your well earned points!

Regards
Fredrik
Avatar billede arne_v Ekspert
03. marts 2004 - 14:26 #8
All 3 or ?
Avatar billede rasmusbg Nybegynder
03. marts 2004 - 14:28 #9
svar
Avatar billede fredand Forsker
03. marts 2004 - 14:45 #10
Yes, all 3 of you!

/Fredrik
Avatar billede arne_v Ekspert
03. marts 2004 - 14:48 #11
svar
Avatar billede fredand Forsker
08. marts 2004 - 16:31 #12
Thanks for all help!

Best regards
Fredrik
Avatar billede fredand Forsker
09. marts 2004 - 15:50 #13
BTW her is your well earned points!
Best regards
Fredrik
Avatar billede Ny bruger Nybegynder

Din løsning...

Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.

Loading billede Opret Preview
Kategori
Kurser inden for grundlæggende programmering

Log ind eller opret profil

Hov!

For at kunne deltage på Computerworld Eksperten skal du være logget ind.

Det er heldigvis nemt at oprette en bruger: Det tager to minutter og du kan vælge at bruge enten e-mail, Facebook eller Google som login.

Du kan også logge ind via nedenstående tjenester