update table show results

[HarryDeBoer]

Hi,

what's the way (are the ways) to execute an SQL update statement and show how many rows are affected.

[Jon]

Harry,

In SQL: select count(*) from xxx where...

If using a database resource you need to assign an alias i.e. select count(*) as count from xxx where...

If reading the data into an Ebase table you can use the rowCount variable:

Code: Select all

tables.T1.fetchTable();
var count = tables.T1.rowCount;

Regards
Jon

[HarryDeBoer]

Jon,

it's not a SELECT, it's an update 'UPDATE tTable WHERE ...' statement. I wondered what was the way to execute it and return the affected rows after execution (and show it).

[Jon]

Harry,

You can't get this information from updateTable(). You could check if there are any modified rows before issuing updatetable i.e. something like:

Code: Select all

var t = tables.T1;
var rows = t.rows;
while (rows.next())
{
  if (t.isRowModified())
  { 
     ...
  }
}

The isModified() only checks for an altered row and doesn't include insertions or deletions - there are additional methods for these if you need them. After updatetable has been issued all rows will return false to isModified().

Regards
Jon

[Jon]

Harry,

Not sure I've answered your question. A sql update statement executed via JDBC returns the number of updated records, but doesn't tell you which ones have been changed.

Regards
Jon

[HarryDeBoer]

Hi Jon,

Thanks. isRowModified() may do the trick. Is there also a way to do this with a stored proc resource?

[Jon]

No, at the moment Stored Procedure Resources do not support reading/writing data to or from Ebase tables. So you would need to execute your stored procedure using JDBC then loop through the result set and insert data into the table.

[Dave]

...or just run a select statement first using the same where clause, e.g.

Code: Select all

select count(*) cnt from ... where....

Obviously there's a small overhead in doing this.

Dave