General access problem

[=?ISO-8859-15?Q?Andr=E9_Rothe?= <cyberdyne_systems@xxxxxx>]

Hi,

I think in the mailing list could be a lot of JDBC experienced users, so 
  it could be, that I get an answer for a general problem:

I select some data from a table ADDRESS. The selection is sorted by 
different columns. The table has a primary key column id.

------------------------------------------
id  name    street      city      postcode
------------------------------------------
1   bob     mainstreet  leipzig   04107
2   alice   2nd street  berlin    01200
3   nate                munich
------------------------------------------

The application displays the records in a JTable and the user can change 
the order of the records, like name, street and so on. The order is 
processed by McKoi, I create a dynamic SQL statement and exchange the 
ORDER BY part.

If the user selects a row, the JTable returns an index, which I can use 
to get data from the Resultset by absolute(index).

Now I try to insert a new record. The id is taken from a sequence.

---------------------------------------
4   dave    jump st.   hamburg    30567
---------------------------------------

I refresh the Resultset and the JTable to display the new record. But I 
need to select the record in the table. The table expects an index - I 
have the database id. And now?

McKoi doesn't have a ROWNUM feature like Oracle, which I could use to 
execute the following:

select *
   from (
     select rownum tmp, id, name, city, postcode
       from address
      order by name asc
   )
  where id = 4;

The column tmp would hold the index. The previous example has also a 
mistake. I select new data from the database, another client could 
already have update it and the index doesn't match with the old data in 
the JTable.

So the only way to find the index is to search through the complete 
Resultset. I cannot use a fast search algorithm, because the id is not 
sorted (order by name!). This is a long time job, if the table has a lot 
of records.

In the McKoi examples I saw a possibility to sync multiple clients: the 
triggers. If a client changes the table, the other client will be 
informed by the triggers. So all clients "see" the same data and I can 
calculate the row index with an additional query without a table lock.

How can I solve the ROWNUM problem? I think about the following algorithm:

* get a unique transaction id from a sequence (CYCLE)
* try to create a temporary sequence with a static name plus the
   transaction-id
* if you get an exception, get another transaction-id and try it again
* if the temporary sequence was created, try the following query:

   select *
     from (
        select nextval('tempseq' || trans-id) rownum,
               id, name, street, city, postcode
          from address
         order by name asc
          )
    where id = 4;

* drop the temporary sequence
* get the index from the column rownum

I did not implemented it yet, what are your ideas? Could it work? Any 
other solutions out there?

Regards
Andre