Original status: 3-Accepted; Suggested Status: NEW


Description:
The delete generates a pre select :

SELECT ID, TEXT, CLOB_COL FROM CLOB_TABLE WHERE ID = ? AND TEXT = ? AND CLOB_COL = ?

This is invalid SQL given that CLOB_COL is a CLOB

This is actually a NetBeans 6.0 issue as well but there does not seem to be an
option in Product Release for this.


For a full description of all details associated with this please see the
associated NetBeans bug 121491
http://www.netbeans.org/issues/show_bug.cgi?id=121491 this also has all
associated attachments.

Description (Entry 2):
Repro Case Build Steps
======================

1) Create Clob Table as follows :

DROP TABLE clob_table;

CREATE TABLE clob_table (
    id NUMBER PRIMARY KEY, 
    text VARCHAR(30),
    clob_col CLOB
);

INSERT INTO clob_Table VALUES (0, 'RECORD 0', 'COMMENT FOR RECORD 0');
INSERT INTO clob_Table VALUES (1, 'RECORD 1', 'COMMENT FOR RECORD 1');
INSERT INTO clob_Table VALUES (2, 'RECORD 2', 'COMMENT FOR RECORD 2');
INSERT INTO clob_Table VALUES (3, 'RECORD 3', 'COMMENT FOR RECORD 3');

2) Create a new Web Application
3) Drag VWP Table onto Page1 Canvas
4) Drag clob_table from DB Connection and drop on VWP Table
5) Edit Table Layout and deselect CLOB_COL ie move to Available
6) Add new column and change type to button (Delete).
7) Add following code to delete button action

    public String deleteBtn_action() {
        try {
            RowKey rk = tableRowGroup1.getRowKey();
            if (rk != null) {
                clob_tableDataProvider.removeRow(rk);
                clob_tableDataProvider.commitChanges();
                clob_tableDataProvider.refresh();
            }
        } catch (Exception ex) {
            ex.printStackTrace(System.err);
            log("Table row deleting: ", ex);
            error("Table row deleting: " + ex.getMessage());
        }
        return null;
    }

8) Select the Session Bean from the Navigation Pane and then the auto generated
clob_tableRowSet select statement. Set the printStatements flag
9) Clean / Build / Deploy / Run
10) Pick any row in the displayed table and select the delete button. The delete
will fail and the foillowing will be didplayed in the server.log:

[#|2008-01-18T14:14:58.031+0000|INFO|sun-appserver9.1|javax.enterprise.system.stream.out|_ThreadID=17;_ThreadName=httpSSLWorkerThread-8080-0;|
Reader executing finished|#]

[#|2008-01-18T14:19:46.031+0000|INFO|sun-appserver9.1|javax.enterprise.system.stream.out|_ThreadID=18;_ThreadName=httpSSLWorkerThread-8080-2;|
INSERT INTO CLOB_TABLE (ID, TEXT, CLOB_COL) VALUES (?, ?, ?)|#]

[#|2008-01-18T14:19:46.031+0000|INFO|sun-appserver9.1|javax.enterprise.system.stream.out|_ThreadID=18;_ThreadName=httpSSLWorkerThread-8080-2;|
Writer:  pre-delete select SELECT ID, TEXT, CLOB_COL FROM CLOB_TABLE WHERE ID =
? AND TEXT = ? AND CLOB_COL = ? |#]

[#|2008-01-18T14:19:46.125+0000|WARNING|sun-appserver9.1|javax.enterprise.system.stream.err|_ThreadID=18;_ThreadName=httpSSLWorkerThread-8080-2;_RequestID=6af00c62-29be-4bb2-8ba7-422b3d54d2a5;|java.lang.RuntimeException:
Number of conflicts while synchronizing: 1 SyncResolver.DELETE_ROW_CONFLICT row
0  Invalid column type
	at
com.sun.data.provider.impl.CachedRowSetDataProvider.commitChanges(CachedRowSetDataProvider.java:992)
	at case6649031.Page1.deleteBtn_action(Page1.java:326)

Work Around
===========

To add a work around for this delete follow the steps below:

1) Edit the VWP Table and add an extra column of type Button.
2) Drag the Clob_table and drop it on the canvas and in the dialog create a new
Session RowSet call deleteClobTableRowSet
3) Select the new RowSet and turn on printStatements
4) Edit the RowSet and change the statement to :

DELETE FROM SCOTT.CLOB_TABLE
WHERE SCOTT.CLOB_TABLE.ID = ?

5) In the Page edit the new button action and add the following code.

    public String workaroundBtn_action() {
        try {
            BigDecimal id = (BigDecimal)getValue("#{currentRow.value['ID']}");
            getSessionBean1().getDeleteClobTableRowSet().setBigDecimal(1, id);
            getSessionBean1().getDeleteClobTableRowSet().execute();
            getSessionBean1().getDeleteClobTableRowSet().commit();
        } catch (Exception e) {
            Logger.getLogger(Page1.class.getName()).log(Level.SEVERE,
e.getMessage(), e);
        }
        clob_tableDataProvider.refresh();
        return null;
    }

6) Add the following to the prerender method

    public void prerender() {
        try {
            getSessionBean1().getDeleteClobTableRowSet().setBigDecimal(1, new
BigDecimal(-1));
        } catch (SQLException ex) {
            Logger.getLogger(Page1.class.getName()).log(Level.SEVERE, null, ex);
        }

    }

7) Clean / Build / Run
8) Select the second delete button and you will see the expected behaviour.

Alternative #1
--------------
Instead of using the above (which I did because I wanted the CLOB selected but
not displayed) you can modify the clob_tableRowSet so that the CLOB_COL is not
selected. If this is done then the delete works as expected.


Key Points
==========

1) The query specified in the RowSet associated with the table is :

SELECT ALL SCOTT.CLOB_TABLE.ID, 
                    SCOTT.CLOB_TABLE.TEXT, 
                    SCOTT.CLOB_TABLE.CLOB_COL 
FROM SCOTT.CLOB_TABLE

When the DataProvider issues its Delete it executes the following :

SELECT ID, TEXT, CLOB_COL FROM CLOB_TABLE WHERE ID = ? AND TEXT = ? AND CLOB_COL = ?

It appears that the devloper has no control over this query and I suspect the
value passed to the CLOB_COL is a String rather than a Clob type.

2) Why when the table has a primary key are we specifying all the columns in the
pre-delete select when we only need to use the primary key.

3) I attempted to display the CLOB_COL and add a Converter (supplied from the
original IssueZilla case) but the delete then throws a Runtime Error, if the
type of the column is StaticText, or does nothing, if the type is TextArea.

Workaround:
To add a work around for this delete follow the steps below:

1) Edit the VWP Table and add an extra column of type Button.
2) Drag the Clob_table and drop it on the canvas and in the dialog create a new
Session RowSet call deleteClobTableRowSet
3) Select the new RowSet and turn on printStatements
4) Edit the RowSet and change the statement to :

DELETE FROM SCOTT.CLOB_TABLE
WHERE SCOTT.CLOB_TABLE.ID = ?

5) In the Page edit the new button action and add the following code.

    public String workaroundBtn_action() {
        try {
            BigDecimal id = (BigDecimal)getValue("#{currentRow.value['ID']}");
            getSessionBean1().getDeleteClobTableRowSet().setBigDecimal(1, id);
            getSessionBean1().getDeleteClobTableRowSet().execute();
            getSessionBean1().getDeleteClobTableRowSet().commit();
        } catch (Exception e) {
            Logger.getLogger(Page1.class.getName()).log(Level.SEVERE,
e.getMessage(), e);
        }
        clob_tableDataProvider.refresh();
        return null;
    }

6) Add the following to the prerender method

    public void prerender() {
        try {
            getSessionBean1().getDeleteClobTableRowSet().setBigDecimal(1, new
BigDecimal(-1));
        } catch (SQLException ex) {
            Logger.getLogger(Page1.class.getName()).log(Level.SEVERE, null, ex);
        }

    }

7) Clean / Build / Run
8) Select the second delete button and you will see the expected behaviour.

Alternative #1
--------------
Instead of using the above (which I did because I wanted the CLOB selected but
not displayed) you can modify the clob_tableRowSet so that the CLOB_COL is not
selected. If this is done then the delete works as expected.