Task 1
In doConnect() after connecting, get the DatabaseMetaData in dbmd; create a scrollable ResultSet using ResultSet.TYPE_SCROLL_INSENSITIVE and ResultSet.CONCUR_READ_ONLY; to ensure that the ResultSet does not inadvertently become unavailable, add code to turn off autocommit.
dbmd = con.getMetaData();
stmt = con.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY );
con.setAutoCommit( false );
|
Task 2
In doRetrieval(), using the rsMetaData ResultSet, get Primary Key information for sTable and append it to the JTextArea jtaMD; close the ResultSet.
rsMetaData = dbmd.getPrimaryKeys( null, null,
sTable.toUpperCase() );
bFirst = true;
while( rsMetaData.next() )
{
if( bFirst )
{
jtaMD.setText( "Primary Keys: \n" );
bFirst = false;
}
jtaMD.append( rsMetaData.getString(4) +
", Sequence: " + rsMetaData.getShort(5) +
"\n" );
} // end while
if( bFirst )
{
jtaMD.setText( "No Primary Key information " +
"returned, \nassume no Primary Keys.\n" );
bFirst = false;
}
rsMetaData.close();
|
Task 3
In doRetrieval(), add code to execute the query, using sQuery, sTable and sOrderBy, retrieving the ResultSet rsScroll; determine that at least one row was returned. If not, inform the user, close rsScroll and return to the display; determine the scroll type. If ResultSet.TYPE_FORWARD_ONLY, close everything, inform the user that the program can not proceed and return to the display.
rsScroll = stmt.executeQuery( sQuery +
sTable + " " +
sOrderBy );
// determine that at least one row was returned
if( !rsScroll.next() )
{
jta.append( "No data Returned.\n" );
rsScroll.close();
con.commit();
return;
}
ndx = rsScroll.getType();
switch( ndx )
{
case ResultSet.TYPE_FORWARD_ONLY:
jtaMD.append(
"\nResultSet.TYPE_FORWARD_ONLY\n" );
jtaMD.append(
"\nProgram can not proceed.\n" );
rsScroll.close();
stmt.close();
con.commit();
con.close();
return;
case ResultSet.TYPE_SCROLL_INSENSITIVE:
jtaMD.append(
"\nResultSet.TYPE_SCROLL_INSENSITIVE\n" );
break;
default:
jtaMD.append(
"\nOther type of ResultSet.\n" );
break;
}
|
Task 4
In doRetrieval(), add code to get ResultSetMetadata using rsmd and display Catalog, Schema and Table names. -- After this code is a large amount of formatting code using ResultSetMetadata, which is worth the reader's time for a brief review.
rsmd = rsScroll.getMetaData();
jtaMD.append( "\nCatalog: " +
rsmd.getCatalogName(1) + "\n" );
jtaMD.append( "Schema: " +
rsmd.getSchemaName(1) + "\n" );
jtaMD.append( "Table: " +
rsmd.getTableName(1) + "\n" );
jtaMD.append( "\nColumns: \n" );
|
Task 5
In doNext(), add code to determine whether the "Prev" button should be enabled/disabled and also set the page tracker bHasPrevPage; get and load iPAGE_SIZE rows; add code to determine whether the "Next" button should be enabled/disabled and also set the page tracker bHasNextPage; ensure the proper cursor positioning for the next page request.
if( rsScroll.isBeforeFirst() ||
rsScroll.isFirst() )
{
bHasPrevPage = false;
jbPrev.setEnabled( false );
}
else
{
bHasPrevPage = true;
jbPrev.setEnabled( true );
}
for( iPAGE_COUNT = 0;
iPAGE_COUNT < iPAGE_SIZE;
iPAGE_COUNT++
)
{
if( rsScroll.next() )
{
loadRow();
}
else { break; }
} // end for
jbNext.setEnabled( false );
bHasNextPage = false;
if( iPAGE_COUNT == iPAGE_SIZE )
{
if( rsScroll.next() )
{
jbNext.setEnabled( true );
bHasNextPage = true;
}
}
rsScroll.previous();
|
Task 6
In doPrev(), insert code to set the cursor properly for the next set. The suggested code is explained in detail in the help section and should be reviewed.
if( !bHasNextPage ) // on last page
{
if( iPAGE_COUNT < iPAGE_SIZE )
{
rsScroll.relative(
-( iPAGE_SIZE + iPAGE_COUNT ) );
}
else // first or other page
{
rsScroll.relative( -(iSET_PREV) );
}
} // end if !bHasNextPage
else // first or other page
{
i = rsScroll.getRow();
if( i != 0 && ( i - iSET_PREV ) < 1 )
{
rsScroll.beforeFirst();
}
else
{
rsScroll.relative( -(iSET_PREV) );
}
} // end else
|
If bHasNextPage is false, then the last page is displayed. There can be two conditions: a full page or less than a full page. If the page was full, go back iSET_PREV rows. If there was less than a full page, go back by ( iPAGE_SIZE + the number of rows on the page ) rows.
If bHasNextPage is true, then the position can be any other page, including the first.
In general, for this condition, the line rsScroll.relative( -(iSET_PREV) ); should handle things properly. However, Cloudscape has apparently implemented ResultSet.relative() to calculate an absolute row number and then invoke ResultSet.absolute(). Sometimes the row number is zero, at which point an SQLException is thrown, and the ResultSet is closed. To avoid this situation, the calculations resulting in the invocation of ResultSet.beforeFirst() are performed, using getRow().
So, you might ask, why not use this calculation all of the time? Well, not every driver always returns a row when ResultSet.getRow() is called. In fact the API allows a zero return and DB2, and very specifically DB2/400, frequently returns zero. Therefore the else code, which works in that case.
Given this situation, the code will not work if a driver implements ResultSet.relative() to hand off ( possibly a zero ) to ResultSet.absolute() and does not return a row number on a ResultSet.getRow() call.
Print-friendly Version
