Re: Row Locking and ODBC

If your only requirement is to have unique keys (where it is not so important exactly what they are) then why not create a unique constraint (or even a primary key constraint) on the column in the destination table. Then if you create a sequence within Oracle and then a before update or insert trigger then you can get Oracle to use its own sequence generator (will not give out the same number twice unless you tell it to CYCLE). Moreover, the trigger and or the unique key / primary key constraint can keep the id column in the destination table 'safe'.

Not necessary simple, but if you do the above, Oracle will take care of your 'counter' columns for you.

Anyone who's used Access / MySQL etc. will be disappointed that there is no simple way to replicate this functionality in Oracle but it's just a fact of life.

At least, once you get started writing triggers and see the usefulness of them, you'll never look back!

David P.

utlrarunner_at_my-deja.com wrote:
>
> I have a table consisting of a name and a counter
> that I wish to use to generate unique ids. I want
> to use the following sql to lock the row I am
> working on so that another user cannot select the
> row while I am updating the id.
>
> select record_id from sequences where
> sequence_name = 'mytablename' for update of
> record_id;
>
> Sending the sql via sql plus works fine but when I
> send it through ODBC I am getting an ORA-01002
> error which I believe means a fetch across commit
> boundries. What confuses me is that the error I am
> getting back is on the SQLExecute Call.
>
> In attempting to figure it out I tried doing a
> commit on the connection but this did not seem to
> help. I believe that the something in the state of
> the connection is preventing me from locking the
> row.
>
> Grant Hickey
> ghickey_at_northplains.com
> North
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
  Received on Tue Mar 28 2000 - 00:00:00 CST