Guys,

Is the question below really silly?  I have tried various approaches to the
problem mentioned without any success - the only thing that I haven't tried
is to have a method whereby I go back to pure jdbc and scrap the framework.
I don't want to do this.

An approach I did try was to use a stored procedure that takes in a varchar2
but I hit the maximum length when using large strings, which is why I need
clobs.  I am pretty sure that I need to use the character stream to write
the data - I am not sure how to set the auto commit to false before I call
the select for update then reset to true afterwards.

I hope this isn't too obvious - I have read and re-read the book.  I can see
ways I can solve this problem - I have a working solution in another
application - I just can't seem to get it to work within the framework.

Kind regards,

Paul Feaviour
p.s. I have cc'd the springframework as I am not sure if the wrox site is
still active.

----- Original Message -----
From: "Paul Feaviour" <paulfeaviour@h...>
To: "ExpertJ2EE with RodJohnson" <expertj2ee_with_rodjohnson@p...>
Sent: Friday, March 21, 2003 5:08 PM
Subject: [expertj2ee_with_rodjohnson] Retrieving clob problem


> Hi,
>
> I am trying to retrieve a clob for update using the following code:
>
>  class ClobQuery extends ManualExtractionSqlQuery {
>   public ClobQuery(DataSource ds) {
>    super(ds, "select my_clob from temp where id = ? for update");
>       declareParameter(new SqlParameter(Types.NUMERIC));
>    compile();
>   }
>
>   protected Object extract(ResultSet rs, int rownum) throws SQLException {
>      return ((OracleResultSet)rs).getCLOB(1);
>   }
>
> The calling method casts the single object in the returned list to an
oracle.sql.CLOB locator, which I then try and use to write data to the
database via the java.io.Writer returned via getCharacterOutputStream().
When I run this I get the following error:
>
> 1)
testClob(com.phoenix.business.support.test.BusinessDAOTester)com.interface21
.jdbc.core.UncategorizedSQLException: (JdbcTemplate.query(psc) with
PreparedStatementCreator
[PreparedStatementCreatorFactory.PreparedStatementCreatorImpl: sql={select
my_clob from temp where id = ? for update}: params={1}]): encountered
SQLException [ORA-01002: fetch out of sequence]; nested exception is:
java.sql.SQLException: ORA-01002: fetch out of sequence
> ...
>
> I am using 'for update' in my sql so the cursor should remain open?  I am
thinking that I need to setAutoCommit to false when making this call - I
don't see how I am able to do this?
>
> Having re-read page 356 of the book I am sure that what I am doing is at
least in the spirit of the data access classes - can anyone shed any
light/provide a solution to my problem?
>
> Thanks in advance.
>
> Paul Feaviour
>
> p.s. I am aware I am using non-portable code
>
>