Re: ResultSet size limitation

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Joe Weinstein (joeNOSPAM_at_bea.com)
Date: 04/07/04 

Date: Wed, 07 Apr 2004 11:53:10 -0700
To: Sam Lebron <sam.lebron@dressbarn.com>

Sam Lebron wrote:

> Thanks for your prompt response.

sure.

> I have recreated the problem running in a wsad test
> environment.

WSAD? Does that mean you are running in a standalone program,
not weblogic, and you are getting a jdbc connection directly from
the MS driver?
thanks,
Joe

   I am reading the rows in the order that they
> are returned and I'm reading the columns left to right. I
> am also using this data to populate a collection of
> PreparedStatement using the addBatch(). The error occurs
> during a column read of the ResultSet. If it helps, I've
> pated the method below,
>
> private PreparedStatement processPhotographyDetail(
> Statement updateStmt,
>
> Connection queryConnection,
>
> Connection updateConnection,
>
> ArrayList newsroomPublishedStatuses)
> throws SQLException {
> ArrayList contentArea = publishDocumentType( 100,
> 100, newsroomPublishedStatuses );
>
> if ( contentArea.size() == 0 ) {
> return null;
> }
>
> String whereClause =
> getPhotographyDetailWhereClause( queryConnection,
> contentArea );
> String query = "SELECT sequenceId, photographyId,
> description, unSelectedThumbnailImage,
> selectedThumbnailImage, largeImage, highResolutionImage,
> sortOrder, createDate, lastUpdatedBy, modifiedDate FROM
> newsroomPhotographyCollectionDetail " + whereClause;
>
> Statement queryStmt =
> queryConnection.createStatement();
> StringBuffer insertStmt = new StringBuffer
> ( 512 );
>
> insertStmt.append( "INSERT INTO
> newsroomPhotographyCollectionDetail (" );
> insertStmt.append( "sequenceId,"
> );
> insertStmt.append( "photographyId,"
> );
> insertStmt.append( "description,"
> );
> insertStmt.append(
> "unSelectedThumbnailImage," );
> insertStmt.append(
> "selectedThumbnailImage," );
> insertStmt.append( "largeImage,"
> );
> insertStmt.append(
> "highResolutionImage," );
> insertStmt.append( "sortOrder,"
> );
> insertStmt.append( "createDate,"
> );
> insertStmt.append( "lastUpdatedBy,"
> );
> insertStmt.append( "modifiedDate"
> );
> insertStmt.append( ") VALUES ("
> );
> insertStmt.append( "?,"
> );
> insertStmt.append( "?,"
> );
> insertStmt.append( "?,"
> );
> insertStmt.append( "?,"
> );
> insertStmt.append( "?,"
> );
> insertStmt.append( "?,"
> );
> insertStmt.append( "?,"
> );
> insertStmt.append( "?,"
> );
> insertStmt.append( "?,"
> );
> insertStmt.append( "?,"
> );
> insertStmt.append( "?"
> );
> insertStmt.append( ")"
> );
>
> PreparedStatement pstmt = null;
> ResultSet rs = queryStmt.executeQuery( query );
>
> if ( rs.next() ) {
> updateStmt.addBatch( "DELETE FROM
> newsroomPhotographyCollectionDetail " + whereClause );
> pstmt = updateConnection.prepareStatement(
> insertStmt.toString() );
>
> do {
> pstmt.setInt(
> 1, rs.getInt( 1 ) );
> pstmt.setInt(
> 2, rs.getInt( 2 ) );
> pstmt.setString(
> 3, rs.getString( 3 ) );
>
> addImageToPreparedStatement(
> 4, rs.getBytes( 4 ), pstmt );
> addImageToPreparedStatement(
> 5, rs.getBytes( 5 ), pstmt );
> addImageToPreparedStatement(
> 6, rs.getBytes( 6 ), pstmt );
> addImageToPreparedStatement(
> 7, rs.getBytes( 7 ), pstmt );
>
> pstmt.setInt(
> 8, rs.getInt( 8 ) );
> pstmt.setTimestamp(
> 9, rs.getTimestamp( 9 ) );
> pstmt.setString(
> 10, rs.getString( 10 ) );
> pstmt.setTimestamp(
> 11, rs.getTimestamp(11 ) );
>
> pstmt.addBatch();
> } while ( rs.next() );
> }
> queryStmt.close();
> return pstmt;
> }
>
> private void addImageToPreparedStatement(int outPos, byte
> [] image, PreparedStatement pstmt) throws SQLException {
> pstmt.setBinaryStream( outPos, new
> ByteArrayInputStream( image ), image.length );
> }
>
>
>
>
>>-----Original Message-----
>>
>>
>>Sam Lebron wrote:
>>
>>
>>>Current technology used - I'm using the latest
>
> microsoft
>
>>>jdbc driver, windows 2000 server, SQLServer enterprise
>>>edition, weblogic running jvm version 1.3.
>>>
>>>What I'm trying to do - I'm using a Statement object
>>>to "select *" from a table that contains 64 rows, four
>>>image attributes per row, over 111 megabytes of total
>
> data.
>
>>>Problem - ResultSet.next() was true, I read 3 out of
>
> the
>
>>>four images and the fourth once was null. That
>
> attribute
>
>>>on that row has data.
>>>
>>>I currently rewrote the code to grab only the changed
>>>sections and it's working for now.
>>>
>>>Is there any limitation on the
>
> driver/connection/statement
>
>>>that would cause the resultset to get truncated?
>>>
>>>I could not find any SQLServer settings that would
>
> affect
>
>>>this.
>>>
>>>I'm using the default connection and statement settings.
>>
>>I don't think so. Can you reproduce the problem running
>
> the
>
>>same query using the same driver in a little standalone
>>program? I assume you are reading the rows in the order
>>they come from the query, correct? There is a driver
>
> limitation
>
>>that for text/image types, yo do have to read the data in
>>returned order...
>>Joe Weinstein at BEA
>>
>>.
>>