How do you refresh row (ie make trigger changes visible) after ResultSet.insertRow()?

How do you refresh row (ie make trigger changes visible) after ResultSet.insertRow()?

Post by Randall J. Par » Fri, 06 Apr 2001 04:35:16



How do you refresh the "insertRow" of an updatable ResultSet (particularily in
Oracle 8.1.7 JDBC)?

Example: I use an updatable ResultSet to insert an Invoice table record (which I
know has a trigger to assign the InvoiceNumber). I then need to refresh the
insertRow so that I may see the assigned InvoiceNumber and use that to insert
InvoiceItems.

If my code has to assign the InvoiceNumber first (ie I can't refresh the row), I
am, at best duplicating the trigger code (maintenance headaches, etc.) or worse,
circumventing the trigger and possibly violating database constraints.

Thanks
R.Parr

 
 
 

How do you refresh row (ie make trigger changes visible) after ResultSet.insertRow()?

Post by AV » Fri, 06 Apr 2001 20:55:20


-- get name of sequence from which trigger is taking NEXTVAL.
(you can easily do it by:
"select sequence_for_invoice from our_dba where
our_dba is not in_lunch")
-- write the row giving any (or empty) ID, it will be rewritten.
-- get ID from "select seq_invoice_id.curval from dual".
this is concurrency-safe only from "synchronized" java method
and NO OTHER processes insert into this table.

Concurrency-safe methods even without synchronization would be:
(1)
-- No insert trigger.
-- read "select seq_invoice_id.nextval from dual"
-- set this id for new row and insert row
-- be sure all OTHER processes read nextval first before insert
(2)
-- use interesting oracle feature "select returning"
-- at least in oracle 816 this syntax was not yet supported by
oracle's  jdbc driver.
(2a)
-- one can easily make stored procedure taking invoice data,
inserting and returning id, (with or without "select returning")
But store procedures will move you away from
insertRow...

AlexV



Quote:> How do you refresh the "insertRow" of an updatable ResultSet
(particularily in
> Oracle 8.1.7 JDBC)?

> Example: I use an updatable ResultSet to insert an Invoice table record
(which I
> know has a trigger to assign the InvoiceNumber). I then need to refresh
the
> insertRow so that I may see the assigned InvoiceNumber and use that to
insert
> InvoiceItems.

> If my code has to assign the InvoiceNumber first (ie I can't refresh the
row), I
> am, at best duplicating the trigger code (maintenance headaches, etc.) or
worse,
> circumventing the trigger and possibly violating database constraints.

> Thanks
> R.Parr


 
 
 

How do you refresh row (ie make trigger changes visible) after ResultSet.insertRow()?

Post by Uwe Plonu » Fri, 06 Apr 2001 21:04:47



> -- get name of sequence from which trigger is taking NEXTVAL.
> (you can easily do it by:
> "select sequence_for_invoice from our_dba where
> our_dba is not in_lunch")
> -- write the row giving any (or empty) ID, it will be rewritten.
> -- get ID from "select seq_invoice_id.curval from dual".
> this is concurrency-safe only from "synchronized" java method
> and NO OTHER processes insert into this table.

This is concurrency-safe. The curval is only set if the same transaction before
used the nextval. And as long as your transaction lasts the curval is the same
(I've tested this on ORACLE with version 7.3, 8.0 and 8.1).

Uwe
[snip]

 
 
 

How do you refresh row (ie make trigger changes visible) after ResultSet.insertRow()?

Post by AV » Sat, 07 Apr 2001 08:33:54


really?   Thanks, this is good to know.

AlexV



> > -- get name of sequence from which trigger is taking NEXTVAL.
> > (you can easily do it by:
> > "select sequence_for_invoice from our_dba where
> > our_dba is not in_lunch")
> > -- write the row giving any (or empty) ID, it will be rewritten.
> > -- get ID from "select seq_invoice_id.curval from dual".
> > this is concurrency-safe only from "synchronized" java method
> > and NO OTHER processes insert into this table.

> This is concurrency-safe. The curval is only set if the same transaction
before
> used the nextval. And as long as your transaction lasts the curval is the
same
> (I've tested this on ORACLE with version 7.3, 8.0 and 8.1).

> Uwe
> [snip]

 
 
 

How do you refresh row (ie make trigger changes visible) after ResultSet.insertRow()?

Post by Uwe Plonu » Sat, 07 Apr 2001 15:33:36


Simple test to prove the fact:
open two sqlplus
select sq_?.nextval from dual in first sqlplus
select sq_?.nextval from dual in second sqlplus

You will receive two different numbers.

btw, if i use a database to have constraints checked this is the minimum what i
expect from the database :)

Uwe


> really?   Thanks, this is good to know.

> AlexV




> > > -- get name of sequence from which trigger is taking NEXTVAL.
> > > (you can easily do it by:
> > > "select sequence_for_invoice from our_dba where
> > > our_dba is not in_lunch")
> > > -- write the row giving any (or empty) ID, it will be rewritten.
> > > -- get ID from "select seq_invoice_id.curval from dual".
> > > this is concurrency-safe only from "synchronized" java method
> > > and NO OTHER processes insert into this table.

> > This is concurrency-safe. The curval is only set if the same transaction
> before
> > used the nextval. And as long as your transaction lasts the curval is the
> same
> > (I've tested this on ORACLE with version 7.3, 8.0 and 8.1).

> > Uwe
> > [snip]

 
 
 

1. resultset - sensitive or insensitive cursor - changes not visible

Hi all,

I have declared a senstive, updatable cursor but if I update the updateable
resultset the changes are not visible to the application but are committed
to the database. I am using MS SQL 2000 driver. Unfortunately this does not
support rowsets.

Is the behaviour of an updateable resultset such that changes made locally
are not visible? In particular I am trying to display an editable jtable.

thanks
Mark

2. OpenClient Alternatives

3. Resultset Row Refetch / Refresh Problem

4. Case sensitivity in SQL 6.5

5. Adding a row to a ResultSet without populating the changes to the database

6. Configuration Management (Updates) of Sybase Databases

7. Refresh subscriber when row filter changed?

8. Large Databases. What Database?

9. Making changes to merge triggers

10. ResultSet with few rows invisible to ResultSet.next()

11. Making Components Visible to Multiple Forms

12. Making Access DB visible to the world

13. Trigger doing UNLOAD to file OR calling Store Procedure doing the UNLOAD to file