db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mamta Satoor <msat...@gmail.com>
Subject Re: [PATCH] Updatable Resultset JDBC api (update and delete for forward only updatable resultsets) support for Network Server
Date Sat, 21 May 2005 09:44:57 GMT
Hi,
 Please find attached another patch for updatable resultset support under 
Network Server. What changed in this patch compared to the first patch is 
Derby Net Client while constructing update/delete sql goes through all the 
columns(rather than just the first column) in the select list to determine 
the target table. There is still a problem when the select list does not 
include any base table columns at all. Embedded Derby does not face the 
problem because it does not rely on the select column's metadata to 
determine the target table, rather it looks at the language layer's 
activation directly. 
 So, the functionality for Network Server is similar to what Embedded Derby 
supports, with the exception of following updated list of differences
1)DNC requires that there be at least one column in the select list from the 
target table.
eg select 1, 2 from t1 for update of c11 will fail in Network Server because 
driver looks at both the columns in the select list and it can't determine 
the target table for update/delete looking at those columns' metadata. This 
is not required by embedded driver. There is test for this in the attached 
patch(Positive Test2 in updatableResultSet.java).
2)Embedded driver allows Statement name change when there is an open 
resultset on that statement object. DNC driver does not supported that. 
(Positive Test8a and Test8b in updatableResultSet.java )
3)(Positive Test20) Embedded driver allows updateString on SMALLINT, 
INTEGER, BIGINT, DECIMAL datatypes. DNC doesn't.
Embedded driver support updateBytes on CHAR, VARCHAR, LONG VARCHAR 
datatypes. DNC doesn't
Embedded driver support updateTime on TIMESTAMP datatypes. DNC doesn't 
Embedded driver supports updateObject with null value. DNC doesn't
DNC does not support updateClob and updateBlob
 Also, I am running the derbyall suite to confirm nothing has broken. The 
updatable resultset test runs fine.
 thanks,
Mamta
 On 5/20/05, Mamta Satoor <msatoor@gmail.com> wrote: 
> 
> Hi,
>  For the first difference (related to updatable resultset) that I 
> mentioned earlier between embedded and network server(copied below for easy 
> reference)
> 1)DNC requires that the first column in the select list be from the target 
> table.
> eg select 1, c11 from t1 for update of c11 will fail in Network Server 
> because driver looks at first column to determine the target table for 
> update/delete. This is not required by embedded driver. There are tests for 
> this in the attached patch(Positive Test2 in updatableResultSet.java).
>  I am thinking of changing the Derby Net Client driver to see if it can 
> look at the subsequent columns to determine the target table for 
> update/delete rather than give up after trying to get that information from 
> the first column in the select list. Unless somone has objection to this 
> approach, I will look into implementing that so we can bring down the 
> differences between embedded and network server behavior. 
>  Mamta
> 
>  On 5/20/05, Mamta Satoor <msatoor@gmail.com> wrote: 
> 
> >  Hi David,
> >  I have not yet entered JIRA bugs for these differences. Once the patch 
> > is committed, I can go ahead and make JIRA entries. Also, yes, I hope that 
> > these differences can be listed in maybe Server and Admin Guide's existing 
> > section "Differences between running Derby in embedded mode and using the 
> > network server". 
> >  Mamta
> > 
> >   On 5/19/05, David Van Couvering < david@vancouvering.com> wrote: 
> > 
> > >  Hi Mamta. These are some pretty significant incompatibilities between
> > > DNC and Embedded drivers. Are all these logged as JIRA bugs? Are 
> > > they/will they be called out in the documentation? 
> > > 
> > > Thanks,
> > > 
> > > David
> > > 
> > > Mamta Satoor wrote:
> > > 
> > > > Hi,
> > > >
> > > > Sometime back, I submitted patches for update and delete on forward 
> > > only
> > > > updatable resultsets using JDBC apis for Embedded Derby. With this 
> > > mail, 
> > > > I would like to submit similar support for Network Server using 
> > > Derby
> > > > Net Client.
> > > >
> > > > The functionality for Network Server is similar to what Embedded 
> > > Derby
> > > > supports, with the exception of following differences 
> > > > 1)DNC requires that the first column in the select list be from the
> > > > target table.
> > > > eg select 1, c11 from t1 for update of c11 will fail in Network 
> > > Server
> > > > because driver looks at first column to determine the target table 
> > > for 
> > > > update/delete. This is not required by embedded driver. There are 
> > > tests
> > > > for this in the attached patch(Positive Test2 in 
> > > updatableResultSet.java).
> > > > 2)Embedded driver allows Statement name change when there is an open 
> > > 
> > > > resultset on that statement object. DNC driver does not supported 
> > > that.
> > > > (Positive Test8a and Test8b in updatableResultSet.java )
> > > > 3)(Positive Test20) Embedded driver allows updateString on SMALLINT, 
> > > 
> > > > INTEGER, BIGINT, DECIMAL datatypes. DNC doesn't.
> > > > Embedded driver support updateBytes on CHAR, VARCHAR, LONG VARCHAR
> > > > datatypes. DNC doesn't
> > > > Embedded driver support updateTime on TIMESTAMP datatypes. DNC 
> > > doesn't 
> > > > Embedded driver supports updateObject with null value. DNC doesn't
> > > > DNC does not support updateClob and updateBlob
> > > >
> > > > Following files are changed by this patch
> > > > **************svn stat************ 
> > > > M
> > > > 
> > > java\testing\org\apache\derbyTesting\functionTests\tests\lang\updatableResultSet.java
> > > > M
> > > > 
> > > java\testing\org\apache\derbyTesting\functionTests\master\DerbyNetClient\updatableResultSet.out
> > > >
> > > > M
> > > > 
> > > java\testing\org\apache\derbyTesting\functionTests\master\updatableResultSet.out
> > > > M
> > > > 
> > > java\testing\org\apache\derbyTesting\functionTests\master\jdk14\updatableResultSet.out
> > > > M
> > > > 
> > > java\testing\org\apache\derbyTesting\functionTests\suites\DerbyNet.exclude
> > > > M java\client\org\apache\derby\client\am\ResultSet.java
> > > > **********************************
> > > >
> > > > Following is a brief description of the actual changes that are 
> > > going 
> > > > into the patch
> > > > 1)After updateRow(provided updateXXX were issued on the row before
> > > > updateRow) and deleteRow, position the resultset to right before the
> > > > next row. This matches the embedded driver behavior. 
> > > > 2)If ResultSet is not positioned on a row and user issues updateRow 
> > > (w/o
> > > > prior updateXXX), then throw an exception that it is not a valid
> > > > operation. This matches the embedded driver behavior.
> > > > 3)Made changes to DNC so that it does not require all the updatable 
> > > > columns in the sql to be modified with updateXXX prior to updateRow.
> > > > This matches the embedded driver behavior.
> > > > 4)To preserve case sensitivity and spaces in the names, put quotes
> > > > around database object names like table name, column name, cursor 
> > > name 
> > > > etc. Similar to embedded driver.(Positive Test31a, Test31b)
> > > > 5)For a select sql like, select 2, c11 from t1, Derby has null for 
> > > table
> > > > name and schema name for column 2. DNC needs to check for these 
> > > nulls to 
> > > > avoid null pointer exception.
> > > >
> > > > As always, please review the patch and let me know of any comments,
> > > > Mamta
> > > >
> > > >
> > > >
> > > > 
> > > ------------------------------------------------------------------------ 
> > > >
> > > > Index: 
> > > java/testing/org/apache/derbyTesting/functionTests/tests/lang/updatableResultSet.java
> > > > ===================================================================
> > > > --- 
> > > java/testing/org/apache/derbyTesting/functionTests/tests/lang/updatableResultSet.java

> > > (revision 170535) 
> > > > +++ 
> > > java/testing/org/apache/derbyTesting/functionTests/tests/lang/updatableResultSet.java

> > > (working copy)
> > > > @@ -253,13 +253,7 @@
> > > > System.out.println("Negative Testl - request for scroll insensitive 
> > > updatable resultset will give a read only scroll insensitive resultset"); 
> > > > conn.clearWarnings();
> > > > stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, 
> > > ResultSet.CONCUR_UPDATABLE);
> > > > - warnings = conn.getWarnings ();
> > > > - while (warnings != null)
> > > > - {
> > > > - JDBCDisplayUtil.ShowWarnings(System.out, warnings);
> > > > - warnings = warnings.getNextWarning();
> > > > - }
> > > > - conn.clearWarnings();
> > > > + JDBCDisplayUtil.ShowWarnings(System.out, conn);
> > > > System.out.println ("requested TYPE_SCROLL_INSENSITIVE, 
> > > CONCUR_UPDATABLE but that is not supported");
> > > > System.out.println("Make sure that we got TYPE_SCROLL_INSENSITIVE? " 
> > > + (stmt.getResultSetType () == ResultSet.TYPE_SCROLL_INSENSITIVE));
> > > > System.out.println("Make sure that we got CONCUR_READ_ONLY? " + (
> > > stmt.getResultSetConcurrency() == ResultSet.CONCUR_READ_ONLY));
> > > > @@ -292,12 +286,7 @@ 
> > > >
> > > > System.out.println("Negative Test2 - request for scroll sensitive 
> > > updatable resultset will give a read only scroll insensitive resultset");
> > > > stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, 
> > > ResultSet.CONCUR_UPDATABLE);
> > > > - while (warnings != null)
> > > > - {
> > > > - System.out.println ("warnings on connection = " + warnings);
> > > > - warnings = warnings.getNextWarning();
> > > > - }
> > > > - conn.clearWarnings();
> > > > + JDBCDisplayUtil.ShowWarnings(System.out, conn);
> > > > System.out.println("requested TYPE_SCROLL_SENSITIVE, 
> > > CONCUR_UPDATABLE but that is not supported");
> > > > System.out.println("Jira issue Derby-154 : When client connects to 
> > > Network Server using JCC, it incorrectly shows support for scroll sensitive

> > > updatable resultsets"); 
> > > > System.out.println("Make sure that we got TYPE_SCROLL_INSENSITIVE? " 
> > > + (stmt.getResultSetType() == ResultSet.TYPE_SCROLL_INSENSITIVE));
> > > > @@ -377,20 +366,13 @@
> > > > //have to close the resultset because by default, resultsets are 
> > > held open over commit 
> > > > rs.close();
> > > >
> > > > -
> > > > System.out.println("Negative Test5 - request updatable resultset for 
> > > sql with no FOR UPDATE clause");
> > > > stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, 
> > > ResultSet.CONCUR_UPDATABLE);
> > > > rs = stmt.executeQuery("select * from t1");//notice that we forgot 
> > > to give mandatory FOR UPDATE clause for updatable resultset 
> > > > System.out.println("Make sure that we got CONCUR_READ_ONLY? " + (
> > > rs.getConcurrency() == ResultSet.CONCUR_READ_ONLY));
> > > > System.out.println("Jira issue Derby-159 : Warnings raised by Derby 
> > > are not getting passed to the Client in Network Server Mode"); 
> > > > System.out.println("Will see the warnings in embedded mode only");
> > > > - warnings = rs.getWarnings();
> > > > - while (warnings != null) 
> > > > - {
> > > > - System.out.println("Expected warnings on resultset = " + 
> > > warnings);
> > > > - warnings = warnings.getNextWarning ();
> > > > - }
> > > > - rs.clearWarnings();
> > > > + JDBCDisplayUtil.ShowWarnings(System.out, rs);
> > > > rs.next();
> > > > System.out.println ("Now attempting to send a delete on a sql with 
> > > no FOR UPDATE clause.");
> > > > try {
> > > > @@ -410,7 +392,6 @@
> > > > System.out.println("SQL State : " + e.getSQLState());
> > > > System.out.println("Got expected exception " + e.getMessage());
> > > > }
> > > > -
> > > > //have to close the resultset because by default, resultsets are 
> > > held open over commit 
> > > > rs.close();
> > > >
> > > > @@ -420,13 +401,7 @@
> > > > System.out.println("Make sure that we got CONCUR_READ_ONLY? " + (
> > > rs.getConcurrency() == ResultSet.CONCUR_READ_ONLY ));
> > > > System.out.println("Jira issue Derby-159 : Warnings raised by Derby 
> > > are not getting passed to the Client in Network Server Mode");
> > > > System.out.println ("Will see the warnings in embedded mode only");
> > > > - warnings = rs.getWarnings();
> > > > - while (warnings != null)
> > > > - {
> > > > - System.out.println("Expected warnings on resultset = " + 
> > > warnings);
> > > > - warnings = warnings.getNextWarning();
> > > > - }
> > > > - rs.clearWarnings ();
> > > > + JDBCDisplayUtil.ShowWarnings(System.out, rs);
> > > > rs.next();
> > > > System.out.println("Now attempting to send a delete on a sql with 
> > > FOR READ ONLY clause."); 
> > > > try {
> > > > @@ -463,14 +438,12 @@
> > > > System.out.println("Got expected exception " + e.getMessage());
> > > > }
> > > > System.out.println("Now attempt a updateRow without first doing next 
> > > on the resultset.");
> > > > - System.out.println("In embedded mode, updateRow will check if it 
> > > is on a row or not even though no changes have been made to the row using 
> > > updateXXX"); 
> > > > - System.out.println("In Network Server mode, if no updateXXX were 
> > > issued before updateRow, then updateRow is a no-op and doesn't check if it

> > > is on a row or not");
> > > > + System.out.println("updateRow will check if it is on a row or not 
> > > even " +
> > > > + "though no changes have been made to the row using updateXXX");
> > > > try { 
> > > > rs.updateRow();
> > > > - if (TestUtil.isEmbeddedFramework())
> > > > - System.out.println("FAIL!!! In embedded mode, this updateRow 
> > > should have failed because resultset is not on a row"); 
> > > > - else
> > > > - System.out.println("PASS!!! In Network Server mode, this updateRow 
> > > is a no-op because no updateXXX were issued before the updateRow"); 
> > > > + System.out.println("FAIL!!! updateRow should have failed because " 
> > > +
> > > > + "resultset is not on a row");
> > > > } 
> > > > catch (SQLException e) {
> > > > System.out.println("SQL State : " + e.getSQLState());
> > > > @@ -664,12 +637,7 @@
> > > >
> > > > System.out.println("Positive Test1a - request updatable resultset 
> > > for forward only type resultset");
> > > > stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, 
> > > ResultSet.CONCUR_UPDATABLE );
> > > > - warnings = conn.getWarnings();
> > > > - while (warnings != null)
> > > > - {
> > > > - System.out.println("Unexpected warnings = " + warnings); 
> > > > - warnings = warnings.getNextWarning();
> > > > - }
> > > > + JDBCDisplayUtil.ShowWarnings(System.out, conn);
> > > > System.out.println("requested TYPE_FORWARD_ONLY, CONCUR_UPDATABLE"); 
> > > 
> > > > System.out.println("got TYPE_FORWARD_ONLY? " + (
> > > stmt.getResultSetType() == ResultSet.TYPE_FORWARD_ONLY));
> > > > System.out.println("got CONCUR_UPDATABLE? " + (
> > > stmt.getResultSetConcurrency () == ResultSet.CONCUR_UPDATABLE));
> > > > @@ -679,17 +647,15 @@
> > > > System.out.println("column 1 on this row before deleteRow is " + 
> > > rs.getInt(1));
> > > > System.out.println("column 2 on this row before deleteRow is " + 
> > > rs.getString(2));
> > > > rs.deleteRow();
> > > > - System.out.println("Since after deleteRow(), in embedded mode, 
> > > ResultSet is positioned before the next row, getXXX will fail");
> > > > - System.out.println("In Network Server mode, the ResultSet stays on 
> > > the deleted row after deleteRow and hence no error for getXXX");
> > > > + System.out.println("Since after deleteRow(), in embedded mode and 
> > > Network "+ 
> > > > + "Server mode using Derby Net Client, ResultSet is positioned 
> > > before " +
> > > > + "the next row, getXXX will fail");
> > > > try { 
> > > > System.out.println("column 1 on this deleted row is " + rs.getInt
> > > (1));
> > > > }
> > > > catch (SQLException e) {
> > > > - if ( TestUtil.isEmbeddedFramework()) {
> > > > - System.out.println("SQL State : " + e.getSQLState());
> > > > - System.out.println("Got expected exception " + e.getMessage());
> > > > - } else
> > > > - System.out.println("Got unexpected exception " + e.getMessage());
> > > > + System.out.println("SQL State : " + e.getSQLState());
> > > > + System.out.println("Got expected exception " + e.getMessage());
> > > > }
> > > > System.out.println("calling deleteRow again w/o first positioning 
> > > the ResultSet on the next row will fail");
> > > > try {
> > > > @@ -707,16 +673,10 @@
> > > > //have to close the resultset because by default, resultsets are 
> > > held open over commit 
> > > > rs.close();
> > > >
> > > > - if (TestUtil.isEmbeddedFramework()) {
> > > > System.out.println("Positive Test1b - request updatable resultset 
> > > for forward only type resultset"); 
> > > > reloadData();
> > > > stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, 
> > > ResultSet.CONCUR_UPDATABLE);
> > > > - warnings = conn.getWarnings(); 
> > > > - while (warnings != null)
> > > > - {
> > > > - System.out.println("Unexpected warnings = " + warnings);
> > > > - warnings = warnings.getNextWarning();
> > > > - }
> > > > + JDBCDisplayUtil.ShowWarnings(System.out, conn);
> > > > rs = stmt.executeQuery("SELECT * FROM t1 FOR UPDATE"); 
> > > > rs.next();
> > > > System.out.println("column 1 on this row before updateInt is " + 
> > > rs.getInt(1));
> > > > @@ -725,8 +685,9 @@
> > > > System.out.println ("column 2 on this row before updateString is " + 
> > > rs.getString(2));
> > > > System.out.println("now updateRow on the row");
> > > > rs.updateRow();
> > > > - System.out.println("Since after updateRow(), in embedded mode, 
> > > ResultSet is positioned before the next row, getXXX will fail");
> > > > - System.out.println("In Network Server mode, the ResultSet stays on 
> > > the updated row after updateRow and hence no error for getXXX"); 
> > > > + System.out.println("Since after updateRow(), in embedded mode and 
> > > Network "+
> > > > + "Server mode using Derby Net Client, ResultSet is positioned 
> > > before " + 
> > > > + "the next row, getXXX will fail");
> > > > try {
> > > > System.out.println("column 1 on this updateRow row is " + rs.getInt
> > > (1));
> > > > }
> > > > @@ -751,14 +712,33 @@
> > > > //have to close the resultset because by default, resultsets are 
> > > held open over commit
> > > > rs.close();
> > > >
> > > > - System.out.println("Positive Test2 - even if no columns from table 
> > > specified in the column list, we should be able to get updatable 
> > > resultset");
> > > > + System.out.println("Positive Test2 - even if no columns from table 
> > > " +
> > > > + "specified in the column list, we should be able to get updatable 
> > > " +
> > > > + "resultset"); 
> > > > reloadData();
> > > > + System.out.println("Will work in embedded mode because target 
> > > table is "+
> > > > + "not derived from the first column in the select list"); 
> > > > + System.out.println("Will not work in network server mode because 
> > > it " +
> > > > + "derives the target tabke from the first column in the select 
> > > list"); 
> > > > System.out.println("total number of rows in T1 ");
> > > > dumpRS(stmt.executeQuery("select count(*) from t1"));
> > > > rs = stmt.executeQuery("SELECT 1, 2 FROM t1 FOR UPDATE"); 
> > > > rs.next();
> > > > System.out.println("column 1 on this row is " + rs.getInt(1));
> > > > - rs.deleteRow();
> > > > + try {
> > > > + rs.deleteRow();
> > > > + if (TestUtil.isNetFramework())
> > > > + System.out.println("FAIL!!! should have failed in network 
> > > server");
> > > > + else 
> > > > + System.out.println("PASS!!! passed in embedded mode");
> > > > + }
> > > > + catch (SQLException e) {
> > > > + if ( TestUtil.isNetFramework()) {
> > > > + System.out.println("SQL State : " + e.getSQLState());
> > > > + System.out.println("Got expected exception " + e.getMessage());
> > > > + } else
> > > > + System.out.println("Got unexpected exception " + e.getMessage());
> > > > + }
> > > > //have to close the resultset because by default, resultsets are 
> > > held open over commit 
> > > > rs.close();
> > > > System.out.println("total number of rows in T1 after one deleteRow 
> > > is ");
> > > > @@ -908,7 +888,7 @@
> > > > System.out.println("deletesAreDetected( ResultSet.TYPE_FORWARD_ONLY)?

> > > " + dbmt.deletesAreDetected(ResultSet.TYPE_FORWARD_ONLY));
> > > > reloadData();
> > > > stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, 
> > > ResultSet.CONCUR_UPDATABLE );
> > > > - rs = stmt.executeQuery("SELECT 1, 2 FROM t1 FOR UPDATE of c1");
> > > > + rs = stmt.executeQuery("SELECT * FROM t1 FOR UPDATE of c1");
> > > > rs.next();
> > > > System.out.println("The JDBC program should look at rowDeleted only 
> > > if deletesAreDetected returns true");
> > > > System.out.println("Since Derby returns false for detlesAreDetected 
> > > for FORWARD_ONLY updatable resultset,the program should not rely on 
> > > rs.rowDeleted() for FORWARD_ONLY updatable resultsets");
> > > > @@ -971,21 +951,46 @@
> > > > rs.close();
> > > > stmt.executeUpdate("DROP TABLE SESSION.t3");
> > > >
> > > > - System.out.println("Positive Test8a - change the name of the 
> > > resultset and see if deleteRow still works");
> > > > + System.out.println("Positive Test8a - change the name of the 
> > > statement " + 
> > > > + "when the resultset is open and see if deleteRow still works");
> > > > + System.out.println("This test works in embedded mode since Derby 
> > > can " + 
> > > > + "handle the change in the name of the statement with an open 
> > > resultset");
> > > > + System.out.println("But it fails under Network Server mode because 
> > > JCC " + 
> > > > + "and Derby Net Client do not allow statement name change when 
> > > there " +
> > > > + "an open resultset against it");
> > > > reloadData(); 
> > > > stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, 
> > > ResultSet.CONCUR_UPDATABLE);
> > > > System.out.println("change the cursor name(case sensitive name) with 
> > > setCursorName and then try to deleteRow"); 
> > > > stmt.setCursorName("CURSORNOUPDATe");//notice this name is case 
> > > sensitive
> > > > - rs = stmt.executeQuery("SELECT 1, 2 FROM t1 FOR UPDATE of c1");
> > > > + rs = stmt.executeQuery("SELECT * FROM t1 FOR UPDATE of c1");
> > > > rs.next();
> > > > rs.deleteRow();
> > > > System.out.println("change the cursor name one more time with 
> > > setCursorName and then try to deleteRow"); 
> > > > - stmt.setCursorName("CURSORNOUPDATE1");
> > > > - rs.next();
> > > > - rs.deleteRow();
> > > > + try {
> > > > + stmt.setCursorName("CURSORNOUPDATE1");
> > > > + rs.next();
> > > > + rs.deleteRow();
> > > > + if (TestUtil.isNetFramework())
> > > > + System.out.println("FAIL!!! should have failed in network 
> > > server");
> > > > + else
> > > > + System.out.println ("PASS!!! passed in embedded mode");
> > > > + }
> > > > + catch (SQLException e) {
> > > > + if (TestUtil.isNetFramework()) {
> > > > + System.out.println("SQL State : " + e.getSQLState());
> > > > + System.out.println("Got expected exception " + e.getMessage());
> > > > + } else 
> > > > + System.out.println("Got unexpected exception " + e.getMessage());
> > > > + }
> > > > rs.close();
> > > >
> > > > - System.out.println("Positive Test8b - change the name of the 
> > > resultset and see if updateRow still works");
> > > > + System.out.println("Positive Test8b - change the name of the 
> > > statement " + 
> > > > + "when the resultset is open and see if updateRow still works");
> > > > + System.out.println("This test works in embedded mode since Derby 
> > > can " + 
> > > > + "handle the change in the name of the statement with an open 
> > > resultset");
> > > > + System.out.println("But it fails under Network Server mode because 
> > > JCC " + 
> > > > + "and Derby Net Client do not allow statement name change when 
> > > there " +
> > > > + "an open resultset against it");
> > > > reloadData(); 
> > > > System.out.println("change the cursor name one more time with 
> > > setCursorName and then try to updateRow");
> > > > stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY , 
> > > ResultSet.CONCUR_UPDATABLE);
> > > > @@ -994,21 +999,53 @@
> > > > rs = stmt.executeQuery("SELECT * FROM t1 FOR UPDATE of c1");
> > > > rs.next();
> > > > rs.updateLong(1,123);
> > > > - stmt.setCursorName("CURSORNOUPDATE1");
> > > > - rs.updateRow();
> > > > + try {
> > > > + stmt.setCursorName("CURSORNOUPDATE1");
> > > > + rs.updateRow();
> > > > + if (TestUtil.isNetFramework())
> > > > + System.out.println ("FAIL!!! should have failed in network 
> > > server");
> > > > + else
> > > > + System.out.println("PASS!!! passed in embedded mode");
> > > > + } 
> > > > + catch (SQLException e) {
> > > > + if (TestUtil.isNetFramework()) {
> > > > + System.out.println("SQL State : " + e.getSQLState ());
> > > > + System.out.println("Got expected exception " + e.getMessage());
> > > > + } else
> > > > + System.out.println ("Got unexpected exception " + e.getMessage());
> > > > + }
> > > > rs.close();
> > > >
> > > > - System.out.println("Positive Test9a - using correlation name for 
> > > the table in the select sql is not a problem"); 
> > > > + System.out.println("Positive Test9a - using correlation name for 
> > > the " +
> > > > + "table in the select sql works in embedded mode and Network Server 
> > > " + 
> > > > + "using Derby Net Client driver");
> > > > + System.out.println("Correlation name for table does not work in 
> > > Network "+
> > > > + "Server mode (using JCC) because the drivers construct the delete 
> > > sql "+ 
> > > > + "with the correlation name rather than the base table name");
> > > > reloadData();
> > > > stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY , 
> > > ResultSet.CONCUR_UPDATABLE);
> > > > - rs = stmt.executeQuery("SELECT 1, 2 FROM t1 abcde FOR UPDATE of 
> > > c1");
> > > > + rs = stmt.executeQuery("SELECT * FROM t1 abcde FOR UPDATE of c1");
> > > > rs.next();
> > > > System.out.println("column 1 on this row is " + rs.getInt(1));
> > > > System.out.println("now try to deleteRow");
> > > > - rs.deleteRow();
> > > > + try {
> > > > + rs.deleteRow();
> > > > + if (TestUtil.isJCCFramework())
> > > > + System.out.println("FAIL!!! should have failed in network 
> > > server");
> > > > + else
> > > > + System.out.println("PASS!!! passed in embedded mode"); 
> > > > + }
> > > > + catch (SQLException e) {
> > > > + if (TestUtil.isJCCFramework()) {
> > > > + System.out.println ("SQL State : " + e.getSQLState());
> > > > + System.out.println("Got expected exception " + e.getMessage());
> > > > + } else
> > > > + System.out.println("Got unexpected exception " + e.getMessage());
> > > > + }
> > > > rs.close();
> > > > -
> > > > - System.out.println("Positive Test9b - using correlation name for 
> > > updatable column name is not allowed"); 
> > > > +
> > > > + System.out.println("Positive Test9b - using correlation name for " 
> > > +
> > > > + "updatable columns is not allowed.");
> > > > reloadData(); 
> > > > stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, 
> > > ResultSet.CONCUR_UPDATABLE);
> > > > System.out.println("Table t1 has following rows");
> > > > @@ -1055,22 +1092,6 @@ 
> > > > System.out.println("Table t1 after updateRow has following rows");
> > > > dumpRS(stmt.executeQuery("select * from t1"));
> > > >
> > > > - System.out.println("Positive Test9c - try to updateXXX on a 
> > > readonly column. Should get error");
> > > > - reloadData();
> > > > - rs = stmt.executeQuery("SELECT c1, c2 FROM t1 abcde FOR UPDATE of 
> > > c1"); 
> > > > - rs.next();
> > > > - try {
> > > > - rs.updateString(2,"bbbb");
> > > > - System.out.println("FAIL!!! updateString on readonly column should 
> > > have failed"); 
> > > > - }
> > > > - catch (SQLException e) {
> > > > - System.out.println("SQL State : " + e.getSQLState());
> > > > - System.out.println("Got expected exception " + e.getMessage());
> > > > - }
> > > > - rs.close();
> > > > - System.out.println("Table t1 has following rows"); 
> > > > - dumpRS(stmt.executeQuery("select * from t1"));
> > > > -
> > > > System.out.println("Positive Test9d - try to updateXXX on a readonly 
> > > column with correlation name. Should get error"); 
> > > > reloadData();
> > > > rs = stmt.executeQuery("SELECT c1, c2 as col2 FROM t1 abcde FOR 
> > > UPDATE of c1");
> > > > @@ -1092,9 +1113,9 @@
> > > > reloadData(); 
> > > > stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, 
> > > ResultSet.CONCUR_UPDATABLE);
> > > > stmt1 = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, 
> > > ResultSet.CONCUR_UPDATABLE );
> > > > - rs = stmt.executeQuery("SELECT 1, 2 FROM t1 FOR UPDATE");
> > > > + rs = stmt.executeQuery("SELECT * FROM t1 FOR UPDATE");
> > > > rs.next();
> > > > - rs1 = stmt1.executeQuery("SELECT 1, 2 FROM t1 FOR UPDATE");
> > > > + rs1 = stmt1.executeQuery("SELECT * FROM t1 FOR UPDATE");
> > > > rs1.next();
> > > > System.out.println("delete using first resultset");
> > > > rs.deleteRow();
> > > > @@ -1119,7 +1140,7 @@
> > > > stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY , 
> > > ResultSet.CONCUR_UPDATABLE);
> > > > stmt.executeUpdate("call 
> > > SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
> > > > stmt.setFetchSize(200);
> > > > - rs = stmt.executeQuery ("SELECT 1, 2 FROM t1 FOR UPDATE of c1");
> > > > + rs = stmt.executeQuery("SELECT * FROM t1 FOR UPDATE of c1");
> > > > System.out.println("Notice the Fetch Size in run time statistics 
> > > output."); 
> > > > showScanStatistics(rs, conn);
> > > > System.out.println("statement's fetch size is " + stmt.getFetchSize
> > > ());
> > > > @@ -1205,6 +1226,7 @@
> > > > dumpRS( stmt.executeQuery("select * from table2WithTriggers"));
> > > > //have to close the resultset because by default, resultsets are 
> > > held open over commit
> > > > rs.close ();
> > > > + conn.rollback();
> > > >
> > > > System.out.println("Positive Test14a - make sure self referential 
> > > delete cascade works when deleteRow is issued");
> > > > dumpRS( stmt.executeQuery("select * from selfReferencingT1"));
> > > > @@ -1252,7 +1274,7 @@
> > > > reloadData();
> > > > conn.setAutoCommit(false);
> > > > stmt = conn.createStatement (ResultSet.TYPE_FORWARD_ONLY, 
> > > ResultSet.CONCUR_UPDATABLE);
> > > > - rs = stmt.executeQuery("SELECT 1, 2 FROM t1 FOR UPDATE");
> > > > + rs = stmt.executeQuery("SELECT * FROM t1 FOR UPDATE"); 
> > > > rs.next();
> > > > System.out.println("Opened an updatable resultset. Now trying to 
> > > drop that table through another Statement");
> > > > stmt1 = conn.createStatement();
> > > > @@ -1503,13 +1525,15 @@
> > > > }
> > > > }
> > > > }
> > > > + conn.rollback(); 
> > > > conn.setAutoCommit(true);
> > > >
> > > > System.out.println("Positive Test21 - Test all updateXXX(excluding 
> > > updateObject) methods on all the supported sql datatypes"); 
> > > > conn.setAutoCommit(false);
> > > > - stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, 
> > > ResultSet.CONCUR_UPDATABLE);
> > > > - stmt1 = conn.createStatement ();
> > > > + PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM 
> > > AllDataTypesForTestingTable FOR UPDATE", ResultSet.TYPE_FORWARD_ONLY, 
> > > ResultSet.CONCUR_UPDATABLE);
> > > > + PreparedStatement pstmt1 = conn.prepareStatement("SELECT * FROM 
> > > AllDataTypesNewValuesData");
> > > > for (int sqlType = 1, checkAgainstColumn = 1; sqlType <= 
> > > allSQLTypes.length; sqlType++ ) {
> > > > + conn.rollback();
> > > > System.out.println("Next datatype to test is " + 
> > > allSQLTypes[sqlType-1]);
> > > > for (int updateXXXName = 1; updateXXXName <= 
> > > allUpdateXXXNames.length; updateXXXName++) {
> > > > checkAgainstColumn = updateXXXName;
> > > > @@ -1519,9 +1543,9 @@
> > > > System.out.println (" Using column position as first parameter to " 
> > > + allUpdateXXXNames[updateXXXName-1]);
> > > > else
> > > > System.out.println(" Using column name as first parameter to " + 
> > > allUpdateXXXNames[updateXXXName-1]);
> > > > - rs = stmt.executeQuery("SELECT * FROM AllDataTypesForTestingTable 
> > > FOR UPDATE"); 
> > > > + rs = pstmt.executeQuery();
> > > > rs.next();
> > > > - rs1 = stmt1.executeQuery ("SELECT * FROM 
> > > AllDataTypesNewValuesData");
> > > > + rs1 = pstmt1.executeQuery();
> > > > rs1.next();
> > > > try { 
> > > > if (updateXXXName == 1) {//update column with updateShort methods
> > > > @@ -1642,7 +1666,8 @@
> > > > rs.updateRef(ColumnNames[sqlType-1], null);
> > > > }
> > > > rs.updateRow();
> > > > - if 
> > > (updateXXXRulesTableForEmbedded[sqlType-1][updateXXXName-1].equals("ERROR"))

> > > { 
> > > > + if ((TestUtil.isNetFramework() && 
> > > updateXXXRulesTableForNetworkServer[sqlType-1][updateXXXName-1].equals("ERROR"))

> > > ||
> > > > + ( TestUtil.isEmbeddedFramework() && 
> > > updateXXXRulesTableForEmbedded[sqlType-1][updateXXXName-1].equals("ERROR")))

> > > {
> > > > System.out.println("FAILURE : We shouldn't reach here. The test 
> > > should have failed earlier on updateXXX or updateRow call"); 
> > > > return;
> > > > }
> > > > @@ -1652,7 +1677,8 @@
> > > > return; 
> > > > }
> > > > } catch (Throwable e) {
> > > > - if 
> > > (updateXXXRulesTableForEmbedded[sqlType-1][updateXXXName-1].equals("ERROR"))

> > > 
> > > > + if ((TestUtil.isNetFramework() && 
> > > updateXXXRulesTableForNetworkServer[sqlType-1][updateXXXName-1].equals("ERROR"))

> > > ||
> > > > + ( TestUtil.isEmbeddedFramework() && 
> > > updateXXXRulesTableForEmbedded[sqlType-1][updateXXXName-1].equals("ERROR")))
> > > > System.out.println(" Got expected exception : " + e.getMessage());
> > > > else {
> > > > if ((sqlType == 14 || sqlType == 15 || sqlType == 16) && //we
are 
> > > dealing with DATE/TIME/TIMESTAMP column types 
> > > > @@ -1669,14 +1695,14 @@
> > > > rs1.close();
> > > > }
> > > > }
> > > > + conn.rollback();
> > > > conn.setAutoCommit(true);
> > > >
> > > > System.out.println("Positive Test22 - Test updateObject method");
> > > > conn.setAutoCommit(false);
> > > > - stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, 
> > > ResultSet.CONCUR_UPDATABLE);
> > > > - stmt1 = conn.createStatement();
> > > > String displayString;
> > > > for (int sqlType = 1; sqlType <= allSQLTypes.length; sqlType++ ) {
> > > > + conn.rollback();
> > > > System.out.println("Next datatype to test is " + 
> > > allSQLTypes[sqlType-1]);
> > > > for (int updateXXXName = 1; updateXXXName <= 
> > > allUpdateXXXNames.length; updateXXXName++) {
> > > > for (int indexOrName = 1; indexOrName <= 2; indexOrName++) {
> > > > @@ -1684,9 +1710,9 @@
> > > > displayString = " updateObject with column position &"; 
> > > > else
> > > > displayString = " updateObject with column name &";
> > > > - rs = stmt.executeQuery("SELECT * FROM AllDataTypesForTestingTable 
> > > FOR UPDATE");
> > > > + rs = pstmt.executeQuery();
> > > > rs.next ();
> > > > - rs1 = stmt1.executeQuery("SELECT * FROM 
> > > AllDataTypesNewValuesData");
> > > > + rs1 = pstmt1.executeQuery();
> > > > rs1.next();
> > > > try {
> > > > if (updateXXXName == 1){ //updateObject using Short object 
> > > > @@ -1787,15 +1813,34 @@
> > > > rs.updateObject(ColumnNames[sqlType-1], new Boolean(rs1.getBoolean
> > > (1)));
> > > > } else if (updateXXXName == 19){ //update column with updateNull 
> > > methods 
> > > > System.out.println(displayString + " null as parameters");
> > > > + try {
> > > > if (indexOrName == 1) //test by passing column position 
> > > > rs.updateObject(sqlType, null);
> > > > else //test by passing column name
> > > > rs.updateObject(ColumnNames[sqlType-1], null);
> > > > + } catch (Throwable e) {
> > > > + if (TestUtil.isNetFramework ()) {
> > > > + System.out.println(" Got expected exception:" + e.getMessage());
> > > > + &nb
> > > 
> > ...
> 
> [Message clipped]

Mime
View raw message