CachedRowSet executing twice?
Hi, I'm using Java Studio Creator 2, and generating a CachRowSet along with a dataprovider to the database. I'm using a customized SQL Query for it rather then the standard select statements. Here's an example of the query:
DELETE FROM table
WHERE table.table_column IN ( ? )
I call that data provider by using this Java code:
try{
getRequestBean1().getDataProviderName().setObject(1, value);
getRequestBean1().getDataProviderName().execute();
}catch (Exception e){
}
The error I get is that there is a null point exception the value I tried to delete is empty. I checked the database and the record is removed.
So based on what I saw it's executing the query twice, I stepped through the code and it throwed an exception after it ran the execute due to a null pointer exception.
Any help regarding this matter would be appreciated, such as being able to make it only execute once... It's doing it's job it's just I dislike the fact it will throw an exception in the process.
[1229 byte] By [
r3cca] at [2007-11-26 8:59:34]
# 1
In what page/link/button event are you running this code?
I usually have a SELECT statement in the dataProvider and use DataProvider.removeRow(rowKey) to remove the row. Then I call the commitChanges to commit it. Gurus, which one of the two is the preffered way?
Message was edited by:
Sabir
Sabir at 2007-7-6 23:03:07 >
# 2
I select a user from a drop down list, display the information and remove it from the database via that way. I don't use a data table for this and attempted the removeRow and that didn't really work for. Could you show me an example of removing a row without using a datatable?
r3cca at 2007-7-6 23:03:07 >
# 3
See if this can be of any help: http://developers.sun.com/prodtech/javatools/jscreator/learning/tutorials/2/ins erts_updates_deletes.html
Sabir at 2007-7-6 23:03:07 >
# 4
I had already tried that, but that's more or less dependant on the datatable. I attempted to apply it to a non datatable scenario and didn't work. So I attempted to use a delete statement which works... it just executes twice.
r3cca at 2007-7-6 23:03:07 >
# 5
> I usually have a SELECT statement in the dataProvider
> and use DataProvider.removeRow(rowKey) to remove the
> row. Then I call the commitChanges to commit it.
> Gurus, which one of the two is the preffered way?
>
Depends on your goals.
If you want to maximize performance, or need to know how many rows were deleted, you need to use plain old jdbc.
If you want to have a consistent coding style - which is a good thing - you can do what you do with CachedRowSet.
I'm surprised the OP got the DELETE to work at all with a CachedRowSet, although I'm not that surprised.
RowSets/ResultSets are not a byproduct of a DELETE statement, so from a theoritical view it doesn't make sense.
http://blogs.sun.com/roller/page/jfbrown?entry=using_rowsets_for_crud_or
-Tub
# 6
I figure it was just executing a stored procedure in the database, I never knew it had to force a result set. However it does work with MySQL as does using custom MySQL commands like:
SELECT DISTINCT last_insert_id() AS lastID
FROM table
I guess that would work due it being a select statement. So you're saying this won't work because it's not a select statement and it can't simply create a delete stored procedure for it?
r3cca at 2007-7-6 23:03:07 >
# 7
> I figure it was just executing a stored procedure in
> the database, I never knew it had to force a result
> set.
Based on the rowset specs/javadocs and Joel's blog, my understanding
is that a CachedRowSet is not designed to work with anything other than an sql command that returns a ResultSet.
But as you've discovered, it does execute your DELETE statement.
> However it does work with MySQL as does using
> custom MySQL commands like:
>
> SELECT DISTINCT last_insert_id() AS lastID
> FROM table
>
As a side note, be very careful doing this. Your app server or web server likely uses a connection pool, and if it does then each execution of this may be on a different db connection. And I understand that the last_insert_id() only works on the exact same connection where you did the last insert.
> I guess that would work due it being a select
> statement. So you're saying this won't work because
> it's not a select statement and it can't simply
> create a delete stored procedure for it?
If it work's for you, go right ahead - just test test test.
I just don't like using any stuff for a purpose other than what it's designed for, as these things tend to break when you upgrade your software.
-Tub
# 8
Well it's semi working. As I stated it executes the DELETE statement however it executes it twice which is what I'm trying to figure out as to why, this will cause an issue in my program as I need to test success and display it to the user.
As for hte last_insert_id() thanks for the advice, I havn't really gotten to test the application with multi-users yet but as of now it's working normally. But I will watch out for it and thanks for letting me know of this.
Stated above semi working, still looking for a solution to this, any help would be appreciated still.
r3cca at 2007-7-6 23:03:07 >
# 9
Still looking for a way to solve this if anyone has any idea on what to do.
r3cca at 2007-7-6 23:03:07 >
