This Bugzilla instance is a read-only archive of historic NetBeans bug reports. To report a bug in NetBeans please follow the project's instructions for reporting issues.
When attempting to delete from a DataProvider that is connected to a CachedRowSet associated with an Oracle table the DataProvider Commit changes throws a RuntimeException because of Synchronisation issues. The code used is similar to that specified in the VWP Tutorial http://www.netbeans.org/kb/55/vwp-inserts_updates_deletes.html. Simply building the attached project and deploying it will show the issue.
Created attachment 52735 [details] Project Zip
Created attachment 52736 [details] Database Definition
Created attachment 52737 [details] Service log
Which build are you using ? Please provide the IDE information from the Help->About box After beta2 there were some fixes made to CachedRowSet. Here is the latest build http://bits.netbeans.org/download/6.0/nightly/latest/
marking as INCOMPLETE until the attached project is verified with the latest build
Product Version: NetBeans IDE Dev (Build 20071026043710) Java: 1.6.0_03; Java HotSpot(TM) Client VM 1.6.0_03-b05 System: Windows XP version 5.1 running on x86; Cp1252; en_GB (nb) Userdir: C:\Development\UserDirs\OpenESB\nb6.0
Product Version: NetBeans IDE 6.0 RC1 (Build 200711090000) Java: 1.6.0_03; Java HotSpot(TM) Client VM 1.6.0_03-b02 System: Windows XP version 5.1 running on x86 I tried to reproduce this bug and used the following scenario (according to the tutorial "Performing Inserts, Updates, and Deletes"): 1. Connect to DB Oracle 10g (ojdbc14.jar, jdbc:oracle:thin:@jbrave-pc1.sfbay.sun.com:1521:ora10g, travel/travel), create the DB table "TRX" (see sql-script below): DROP TABLE TRX; CREATE TABLE TRX ( TRX_ID VARCHAR(50) NOT NULL, TRX_DESCRIPTION VARCHAR(250), LAST_UPDATE_TS DATE NOT NULL, TRX_TYPE VARCHAR(25) ); INSERT INTO TRX VALUES ('ID_0', 'DESCRIPTION_0', TO_DATE('05.11.2006', 'DD.MM.YYYY'), 'TYPE_0'); INSERT INTO TRX VALUES ('ID_1', 'DESCRIPTION_1', TO_DATE('09.07.2007', 'DD.MM.YYYY'), 'TYPE_1'); INSERT INTO TRX VALUES ('ID_2', 'DESCRIPTION_2', TO_DATE('14.11.2007', 'DD.MM.YYYY'), 'TYPE_2'); INSERT INTO TRX VALUES ('ID_3', 'DESCRIPTION_3', TO_DATE('27.12.2007', 'DD.MM.YYYY'), 'TYPE_3'); 2. Create Visual Web project (Glassfish V2, Java EE 5, Visual Web JavaServer Faces) 3. DnD JSF component Table (Palette "Basic") on Designer, DnD DB table "TRX" on it. 4. Right click on component "table1", use popup menu item "Table Layout". Add 1 new item to the list "Selected" (1 new column to table): - type of component: Button - text: Delete Click OK. 5. On Designer select any button "Delete" and in the window "Properties" change value of its property "id" from "button1" to "delete" 6. Define correct handler for the button "delete": public String delete_action() { try { RowKey rk = tableRowGroup1.getRowKey(); if (rk != null) { trxDataProvider.removeRow(rk); trxDataProvider.commitChanges(); trxDataProvider.refresh();} } catch (Exception ex) { ex.printStackTrace(System.err); log("Table row deleting: ", ex); error("Table row deleting: " + ex.getMessage()); } return null; } 7. Modify the method "prerender()": public void prerender() { trxDataProvider.refresh(); } 8. Run project and click the button "Delete" several times. Result (see screenshot_runtime_*.JPG): - there were no exception in the "messages.log" and in the "server.log" - table with data is displayed properly in a web-browser - the appropriate table row will be deleted if its button "Delete" is clicked Could this bug be closed? dante, John, Petr what do you think?
Created attachment 52803 [details] screenshot
Created attachment 52804 [details] screenshot
Created attachment 52805 [details] screenshot
Created attachment 52806 [details] screenshot
Created attachment 52807 [details] sql script
Created attachment 52808 [details] Project
Closing as WONTFIX per the example that works in desc8 above If there is still an error after referring to the example in desc8 then please provide your schema and sample code
Try the following: 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'); You still see the original error. This I suspect is due to the CLOB column. Why does the delete execute the following: [#|2007-11-12T13:51:59.796+0000|INFO|sun-appserver9.1|javax.enterprise.system.stream.out|_ThreadID=20;_ThreadName=httpSSLWorkerThread-28080-1;| Writer: pre-delete select SELECT ID, TEXT, CLOB_COL FROM CLOB_TABLE WHERE ID = ? AND TEXT = ? AND CLOB_COL = ? |#] This select will not work with a clob column.
If the table has a Primary Key why do we not use this in the pre delete select rather than the whole sql statement
My Delete Action Code: public String delete3Btn_action() { try { RowKey rk = tableRowGroup3.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; }
The mentioned bug is reproduced on the latest RC1 build if Oracle CLOB-column is used: Product Version: NetBeans IDE 6.0 RC1 (Build 200711120000) Java: 1.6.0_03; Java HotSpot(TM) Client VM 1.6.0_03-b02 System: Windows XP version 5.1 running on x86 & Oracle 10g (ojdbc14.jar, jdbc:oracle:thin:@jbrave-pc1.sfbay.sun.com:1521:ora10g, travel/travel). John, please try to reproduce this bug. You should use the scenario, described in the note #8 (from pal127), but instead of DB table "TRX" use, please, DB table "clob_table" (dante has added the appropriate sql-script). Result (the ): - when the button "Delete" is clicked (the project - WebApplication1_delete_clob.zip) a) an error-message will appear on the web-page (see the attached screenshot_delete_clob.JPG), but a table row won't be removed b) the RuntimeException will appear in the "server.log" (see the attached server_delete_clob.log) c) the SQLException will appear in the "message.log" (see the messages_delete_clob.log) Unknown text in .log files is Russian text "The type of column is forbidden".
Created attachment 52889 [details] screenshot
Created attachment 52890 [details] messages.log
Created attachment 52891 [details] server log
Created attachment 52892 [details] Project
Commit changes for delete was working in builds prior to RC1, but fails now in RC1 ?
The scenario "Delete" worked properly before RC1 and it works properly now, but for "normal" Oracle data types (not CLOB). For CLOB this scenario was performed now.
I see, originally this issue was filed schema used in the Inserts Updates Deletes tutorial. Now, the issue has changed - the schema has been modified to use a CLOB type
Yes, you're right. I beg your pardon - it's not my bug. Previous version of this bug (with table "TRX") was verified and its status was changed to WONTFIX, because there were no problems with suggested original scenario. dante owns this bug and he(she) reopened it after changing DB schema to CLOB.
To view CLOB data, you'll need a custom converter for the component that displays the clob data. 1) Use the attached ClobConverter class in your source packages (rename the package statement in the source file to the appropriate package) 2) Add a declaration in faces-config.xml (under web/WEB-INF of your project) Attached is faces-config.xml <converter> <converter-id>ClobConverter</converter-id> <converter-class> demo_clob.ClobConverter </converter-class> </converter> 3) Add a converter property in the component declaration in the jsp source converter="ClobConverter" <ui:tableColumn binding="#{Page1.tableColumn4}" headerText="CLOB_COL" id="tableColumn4" sort="CLOB_COL"> <ui:staticText binding="#{Page1.staticText4}" id="staticText4" text="#{currentRow.value['CLOB_COL']}" converter="ClobConverter" /> </ui:tableColumn>
Created attachment 52908 [details] custom converter
Created attachment 52909 [details] faces-config with converter declaration
Closing, works for me after applying the converter. I'll write some article on this
It may work for you but it does not for me. A couple of points to try: 1) Within the Table connected to the DB CLOB_TABLE deselect the CLOB_COL that is to say do not display it. We still need to select it so don't change the RowSet query. Now re-run and try deleting the row. At this point my app throws the the Exception again. 2) I have been using ClobConverters but when I try to implement as you describe below it throws the Exception : com.sun.rave.web.ui.appbase.ApplicationException: org.apache.jasper.JasperException: java.lang.IllegalArgumentException: Cannot convert ClobConverter of type class java.lang.String to interface javax.faces.convert.Converter The page will not even display in this situation. I have attached the Project and server.log. This case has not changed I simply said it was similar to the Tutorial the original CreateTables.sql had the table with the CLOB Columns. Before this is closed again can you also test the scenario where the DataProvider is not attached to a Table Object. I have a page that displays a single set of information from a DataProvider (this DP is guaranteed to return only one row) , it's the result of a view, when I press a delete button I attempt to delete the CursorRow using the DataProvider and receive the same error. Delete Code below private void deleteMsg() { try { try { RowKey row = deadMsgDataProvider.getCursorRow(); if (row != null && deadMsgDataProvider.canRemoveRow(row)) { log("*** APH-I2 : About to remove row"); deadMsgDataProvider.removeRow(row); deadMsgDataProvider.commitChanges(); } } catch (Exception e) { log("*** APH-I1 : Exception Attempting correct way to delete",e); // Only do this is the correct way fails getSessionBean1().getDeadMsgRowSetDelete().setString(1, getSessionBean1().getTechMsgId()); getSessionBean1().getDeadMsgRowSetDelete().execute(); getSessionBean1().getDeadMsgRowSetDelete().commit(); } getSessionBean1().clearMsgProperties(); } catch (Exception e) { Logger.getLogger(MessageRepairPage.class.getName()).log(Level.SEVERE, e.getMessage(), e); } } Version ======= Product Version: NetBeans IDE Dev (Build 200711050000) Java: 1.6.0_03; Java HotSpot(TM) Client VM 1.6.0_03-b05 System: Windows XP version 5.1 running on x86; Cp1252; en_GB (nb) Userdir: C:\Development\UserDirs\Netbeans\nb6.0
Created attachment 52916 [details] CLOB Table WebApp Project
Created attachment 52917 [details] CLOB Exception Server.log
John, I performed all your steps and after deployment I got an exception in the server.log (the same as dante mentioned): [#|2007-11-13T21:16:23.062+0300|SEVERE|sun-appserver9.1|javax.enterprise.system.container.web|_ThreadID=16;_ThreadName=httpSSLWorkerThread-8080-0;_RequestID=b77f94a5-b5af-4ad7-80bb-27cc26f3de3b;|StandardWrapperValve[Faces Servlet]: PWC1406: Servlet.service() for servlet Faces Servlet threw exception com.sun.rave.web.ui.appbase.ApplicationException: org.apache.jasper.JasperException: java.lang.IllegalArgumentException: Cannot convert ClobConverter of type class java.lang.String to interface javax.faces.convert.Converter No data were shown in a web-browser. May be, you forgot to describe any detail for project modification?
The converter is working for J2EE 1.4 but not Java EE 5 components. Attaching both projects for each version
Created attachment 52944 [details] J2EE 1.4 project
Created attachment 52945 [details] Java EE 5 project
Setup is Register new driver ojdbc14.jar Register new database connection for ojdbc14.jar URL is jdbc:oracle:thin:@jbrave-pc1.sfbay.sun.com:1521:ora10g user = travel password = travel
See attached projects http://www.netbeans.org/nonav/issues/showattachment.cgi/52944/j2ee14_project.zip and http://www.netbeans.org/nonav/issues/showattachment.cgi/52945/JavaEE5_project.zip
A possible workaround is to use Standard components in a Java EE5 project
Appears to be a woodstock bug since Standard components work with custom converters
This issue has been logged against project woodstock: https://woodstock.dev.java.net/issues/show_bug.cgi?id=922 To work around this issue, use a JavaServer Faces EL expression that resolves to a backing bean or bean property that implements the javax.faces.converter.Converter interface. For example: Add a property on your page: private Converter myClob = new ClobConverter(); public Converter getMyClob() { return myClob; } In the jsp, set the staticText's converter attribute to the property: converter="#{Page1.myClob}" Closing as will not fix since this issue has been migrated.
We still have an issue with this that has not been resolved. Can you please try the following because it still will not work for me. 1) Create a page with a Table based on the CLOB_TABLE. 2) Add the Delete Button and code as with the working examples. 3) Edit the table layout and remove the CLOB_COL from the display. It MUST still be in the Select Statement. 4) Run the Application and try the Delete button. Given the above steps I see the exception at runtime. Also given the above I can not add the ClobConverter because this is a display only feature but the predelete select will still attempt to use the CLOB_COL in the query. Additionally we have been simplifying this case so that you can see the problems and hopefully resolve them but in the real world case the functionality is slightly more complex because the RowSet / DataProvider is not used to populate a table. Instead it is used to populate an Edit page displaying just a single row (guaranteed because it queries on the Primary Key passed from the previous page). The deleteMsg method code was added to the code notes yesterday. None of the workarounds suggested have resolved this issue and although we have highlighted a problem with referencing Converters this is not the underlying problem.
In the last comment desc44 it's not clear what you're trying to do. Removing a column from being displayed in the Table does not exclude a column from the query. That's just the way it works. If you want to exclude a column in the query (select statement), use the Visual Query Editor by excluding a column. To run the Visual Query Editor, with the Page in Design view, look in the Navigator and expand SessionBean1. Then, double-click on the rowset node. Then in the top graph pane, uncheck the column to be excluded. See attached screenshots
Created attachment 52986 [details] Starting the Visual Query Editor
Created attachment 52987 [details] Excluding a column from the select statement
It sounds like more of a project design issue rather than a bug in NetBeans. Please explain how the application is expected to work or attach a sample project
John, dante (Andrew?), Lark, Petr and all other people, who are interested in this bug and this fruitful discussion. :-) Let me describe this problem again. Using of ClobConverter has no concern with this bug, because converter just displays CLOB data in a web-browser correctly. Workaround, which Lark suggested, - Page1.java: private Converter myClob = new ClobConverter(); public Converter getMyClob() { return myClob; } - Page1.jsp: <ui:tableColumn ... converter="#{Page1.myClob}" /> allows JSF component Table to show CLOB data (and this approach definitely works with JavaEE 5 project). Please, look at attached files: 1). screenshot_TableLayout_with_CLOB_and_converter_Glassfish_J2EE5.JPG Table Layout of JSF component Table contains CLOB column, which has converter, and all data are displayed properly. 2). screenshot_TableLayout_without_CLOB_Glassfish_J2EE5.JPG Table Layout of JSF component Table doesn't contain CLOB column (and converter, of course) But when these 2 different projects are deployed and the button "Delete" is clicked, we will get the same error and RuntimeException in server.log: [#|2007-11-14T18:50:27.437+0300|WARNING|sun-appserver9.1|javax.enterprise.system.stream.err| _ThreadID=19;_ThreadName=httpSSLWorkerThread-8080-0;_RequestID=e54fdfe3-9ddb-4d97-92bc-1b131eeecef8;| java.lang.RuntimeException: Number of conflicts while synchronizing: 1 SyncResolver.DELETE_ROW_CONFLICT row 1 Forbidden type of column at com.sun.data.provider.impl.CachedRowSetDataProvider.commitChanges(CachedRowSetDataProvider.java:992) at webapplication1.Page1.delete_action(Page1.java:311) The reason - the default SQL statement "SELECT * FROM TRAVEL.CLOB_TABLE" is used in both these cases for clob_tableRowSet (this SQL statement will be defined when DB table is bound to JSF component Table and instances of DataProvider/CachedRowSet are created). We will get RuntimeException until we exclude CLOB column from SQL statement related to clob_tableRowSet, for example: SELECT ALL TRAVEL.CLOB_TABLE.ID, TRAVEL.CLOB_TABLE.TEXT FROM TRAVEL.CLOB_TABLE
Created attachment 52991 [details] screenshot
Created attachment 52993 [details] screenshot
To avoid the runtime error, the default select statement just needs to be modified as I described in desc45
Applying the suggestions in desc43 and desc45 should resolve the runtime issues. There is a bug in converter as mentioned in desc43 and another issue tracks this bug https://woodstock.dev.java.net/issues/show_bug.cgi?id=922
I have just tested this again using the NB 6 GA build and still see an issue. I have attached Case121491WebApplication1.zip that demonstrates a simple implementation of a common development paradigm. If you run it against the CLOB Table previously defined the Delete button on the first page will cause the exception to be thrown. 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');
Created attachment 54078 [details] Case121491WebApplication1.zip
Created attachment 54088 [details] Case121491WebApplication2.zip
To preempt the case where you state that the previous example is bad architectural design I have attached Case121491WebApplication2.zip which is an alternative way of implementing the functionality. In this example you will need to view a row and then attempt to delete the record. When you try to delete you will see the exception raised in the log file. Just to reiterate the version I am using is: Product Version: NetBeans IDE 6.0 (Build 200711261600) Java: 1.6.0_03; Java HotSpot(TM) Client VM 1.6.0_03-b05 System: Windows XP version 5.1 running on x86; Cp1252; en_GB (nb) Userdir: C:\Development\UserDirs\Netbeans\nb6.0 I have also attached the ojdbc6.jar I am using which according to Oracle is the jdbc driver to use with JDK 1.6
Created attachment 54089 [details] Oracle ojdbc6.jar
Instead of trying an "alternative way," follow the suggestions mentioned in this issue desc43 and 45. If the suggested approach does not work in your case, then please explain what you are doing differently and why they don't work
Ok we have been around this loop before the reason the first example does not work is because of the way NB selects the record prior to deleting it. My table is backed by a query that still selects the clob from table but does not display it. This is exactly what the client wants. The reason is then when the user selects view the clob value can be passed to the view page as part of the RequestBean or SessionBean in this case. So given this scenario the delete on the table will not work because NB tries to us it in the select it executes before the delete (why it has to use all columns and not just the primary key I do not understand). Ok say basically desc43 &desc45 say if you are not going to display the clob column with a converter then don't select it. This leads onto the second example, alternative, now in this example the clob column is not selected as part of the table query and hence to display it in the details page I have to query the table again. Now when I try to delete in the details table the exception is thrown. Now both of these are perfectly adequate design paradigm and will be implement by various clients so what we need is for the delete to work in both cases. Now if you are going to tell me to user a clobConverter try pressing the Refresh button on the details page of the second example. The one supplied previously in this bug seems to cause an issue when the clob column is part of a transaction.
This is more of a JSF lifecycle issue not a database issue and probably not a bug. I'll find a solution, but maybe not until later this week
Now that I got the latest project attached setup and running, it looks very similar to the TwoPageCrud sample application. Have you looked at how TwoPageCrud is developed ? TwoPageCrud is located in the New Project dialog 1) Open the new project dialog 2) Expand Samples and Web and select Visual JSF 3) Select Two Page CRUD with Table A new Two Page CRUD with Table project is created
This issue seems to be urgent, but I would like to understand what this entails. dante, are you helping a Customer develop an application ? If immediate resolution is required then better support is available at this link: http://www.netbeans.org/kb/support.html
The latest issue doesn't appear to be a product issue. So, I'm lowering to P3 for now.
This issue originally started because I was on a large Proof of Concept that required some specific screen designs. The key issue with this case is the CLOB column in the table. If we use tables that do not contain CLOBs the issue never occurs. The example provided is similar to the Two Page CRUD with 2 key differences in that the second page doers the delete and the table contains a CLOB. If you run Case121491WebApplication1.zip you will notice the following in the log: [#|2007-12-11T10:31:43.250+0000|INFO|sun-appserver9.1|javax.enterprise.system.stream.out|_ThreadID=18;_ThreadName=httpSSLWorkerThread-28080-1;| Reader executing finished|#] [#|2007-12-11T10:32:42.203+0000|INFO|sun-appserver9.1|javax.enterprise.system.stream.out|_ThreadID=18;_ThreadName=httpSSLWorkerThread-28080-1;| INSERT INTO CLOB_TABLE (ID, TEXT, CLOB_COL) VALUES (?, ?, ?)|#] [#|2007-12-11T10:32:42.203+0000|INFO|sun-appserver9.1|javax.enterprise.system.stream.out|_ThreadID=18;_ThreadName=httpSSLWorkerThread-28080-1;| Writer: pre-delete select SELECT ID, TEXT, CLOB_COL FROM CLOB_TABLE WHERE ID = ? AND TEXT = ? AND CLOB_COL = ? |#] [#|2007-12-11T10:32:42.218+0000|WARNING|sun-appserver9.1|javax.enterprise.system.stream.err|_ThreadID=18;_ThreadName=httpSSLWorkerThread-28080-1;_RequestID=8ee78063-d7e7-4a40-bd5d-fd8f15e8700c;|java.lang.RuntimeException: Number of conflicts while synchronizing: 1 SyncResolver.DELETE_ROW_CONFLICT row 0 Invalid column type I suspect that the Exception is due to the where clause in the pre-delete select because it is trying to use the clob column directly which will not work, try the same in SQL*Plus, what I would like to know is why executing a delete first attempts an insert and then executes the Select with all columns in the Where clause when the table is defined with a primary key on Id ( I assume the select is actually a select for update to lock the row). This syntax will not work for tables containing CLOBs & BLOBs because Oracle with throw the Exception [Error Code: 932, SQL State: 42000] ORA-00932: inconsistent datatypes: expected - got CLOB. So if a table contains a primary key why not just use the primary key, this will also improve performance for large databases because it will be indexed.
As I mentioned earlier, this is not a product bug but an application design issue. For application design issues, Support can handle them after submitting a support request http://www.netbeans.org/kb/support.html
I agree that this implementation and the NetBeans SQL interface is a NetBeans application design issue but I would consider that this is a bug with the core NetBeans code.
Closing. A bug was filed in the internal bug tracking system 6643666 for Support to take care of
The workaround for this issue is described in the NetBeans FAQ "How can a Visual Web application delete a row in an Oracle table that has a CLOB column?" at http://wiki.netbeans.org/VwpOracleDeleteCLOB. See issue 129080 where this issue is now being tracked.