Executing arbitrary SQL Query

[Mjdgard]

Maybe I don't quite understand the principles behind the programming style used in Visual Web.

One example: I would like to delet a row in a table.
In SQL this would look like: "DELETE FROM ismember WHERE iduser = ? and idgroup  = ?"
How can I do this in Visual Web?

I tried to use DataProvider, however they the seem to support SELECT statements only.
I could select the table and than use removeRow with the appropriate RowKey. This works, if the SELECT-statement does not include joins - which I need.

I guess, this is very dirty, however I tried to setup a SQLRowSet where I can run arbitrary SQL queries.

            getSessionBean1().gettmpSQLRowSet().setCommand("DELETE FROM ismember WHERE iduser = ? AND idgroup = ?");
            getSessionBean1().gettmpSQLRowSet().setTableName("ismember");                        
            getSessionBean1().gettmpSQLRowSet().setInt(1, usrID);
            getSessionBean1().gettmpSQLRowSet().setInt(2, groupID);
            getSessionBean1().gettmpSQLRowSet().execute();

It removes the entry from the database.
However, the execute()-call also produces an "HTTP Status 500 Error".

I'm really struggling with this.
I read the tutorials but they were not helpful for solving my problems.

Thanks for your help.

[John Baker]

Did you see this tutorial?
http://www.netbeans.org/kb/55/vwp-inserts_updates_deletes.html#04

This Delete section describes how to delete a row using DataProviders.
When you drag and drop a database table, a CachedRowSetDataProvider is created.

The instance name of the CachedRowSetDataProvider, view the Outline and expand the Page node.
There will be a node name something like tripDataProvider  (if trip is the table name)

[table_name]DataProvider

The example in the tutorial above, the code is in the action handler for a JavaServer Faces button component.

In the code below, tableRowGroup1 is an instance of TableRowGroup that is a Component that represents a group of table rows.

  public String delete_action() {
       form1.discardSubmittedValues("save");
       try {
           RowKey rk = tableRowGroup1.getRowKey();
           if (rk != null) {
               tripDataProvider.removeRow(rk);
               tripDataProvider.commitChanges();
               tripDataProvider.refresh();}
       } catch (Exception ex) {
           log("ErrorDescription", ex);
           error(ex.getMessage());
       }
       return null;
   }


for other tutorials, see http://www.netbeans.org/kb/55/vwp-index.html

hope this helps
John

Mjdgard wrote:

Maybe I don't quite understand the principles behind the programming style used in Visual Web.

One example: I would like to delet a row in a table.
In SQL this would look like: "DELETE FROM ismember WHERE iduser = ? and idgroup  = ?"
How can I do this in Visual Web?

I tried to use DataProvider, however they the seem to support SELECT statements only.
I could select the table and than use removeRow with the appropriate RowKey. This works, if the SELECT-statement does not include joins - which I need.

I guess, this is very dirty, however I tried to setup a SQLRowSet where I can run arbitrary SQL queries.

            getSessionBean1().gettmpSQLRowSet().setCommand("DELETE FROM ismember WHERE iduser = ? AND idgroup = ?");
            getSessionBean1().gettmpSQLRowSet().setTableName("ismember");                        
            getSessionBean1().gettmpSQLRowSet().setInt(1, usrID);
            getSessionBean1().gettmpSQLRowSet().setInt(2, groupID);
            getSessionBean1().gettmpSQLRowSet().execute();

It removes the entry from the database.
However, the execute()-call also produces an "HTTP Status 500 Error".

I'm really struggling with this.
I read the tutorials but they were not helpful for solving my problems.

Thanks for your help.