Re: WHERE CURRENT OF and dynamic SQL

>All:
>
>I just read that you cannot use the CURRENT OF clause when using dynamic
>SQL. Also, the documentation says you cannot have a FOR UPDATE OF
>without a CURRENT OF, so I assume that neither of these clauses can be
>used when doing dynamic SQL. This means that we are not able to open a
>cursor for update of some columns in the select statement, scan through
>each, modify and replace them using the CURRENT OF in dynamic SQL. Is
>there some other dynamic SQL to support this process? I can't imagine
>that there is no support for this as it seems an awfully common thing to
>do. I have thought about implementing my own sort of cursor, which
>doesn't seem terribly hard. However, if you leave off the FOR UPDATE,
>then the rows don't get locked.
>
>Anyone know of a way to do this type of procedure using dynamic SQL?
>
>Thanks in advance,
>Scott DeWitt

You can create the dynamic sql statement with the FOR UPDATE, but recall that you parsed the string and stored the cursor_id into a variable. You would have to parse a new string with the update statement, and it would be a different cursor_id.

Two things come to mind...
MOST of the time, if you can write a dynamic select statement, yuo can just as well write a dynamic update statement.

That is not always the case...in that case, you have your dynamic select statement. If you are going to update the row, create a dynamic statement to do the update, based on the PK. If it is imperative that the copy you selected be untouched by another process, include a bunch of where [select cursor value]=tablevalue conditions in the update. If someone modified the data, the condition will fail and the row will not be updated.

Christian Smith said he uses the 'FOR UPDATE' clause in his dynamic sql. That supposedly puts a lock on the row; that might eliminate the need for all the checks above. That actually might work since I woudl think the lock is for your process; one cursor selects and locks the row for your PROCESS, and another one updates it. Once you commit, the locks are released.

Good luck

Dan Hekimian-Williams Received on Thu Dec 09 1999 - 00:00:00 CET