SQL Confusion

[Bart Pietercil]

On 9-okt-07, at 14:08, Ruslan Zasukhin wrote:

> On 9/10/07 3:00 PM, "Bart Pietercil" <bart.pietercil at gmail.com> wrote:
>
>>
>> INSIDE a stored procedure we cannot say
>>
>> put a lock on this record
>> doUpdatestatement
>> release lock
>
> But why you need this ?
>
>
> Note also, in procedures exists
>     DECLARE CURSOR
>
> But they not allow control locks.
>
>
> Can you just explain your TASK and your PROBLEM  ?
>
I am trying, I am trying.....


you may know we are trying to make 2 API work on the same server :-)

On one side vRB and on the other side vPHP

RB and PHP are frontends to the same vServer application. In order to  
keep the development cost on the frontends as low as possible we want  
to place as much business logic in the database as possible.
Otherwise said the aim is to have no longer any sqlstatement inside  
the front-ends.
I know this is not the first time I say this, but it is the driving  
force after all our questions :-)

Concerning our update question, the aim here is to have a procedure  
that can do the update statement based on arguments send to the  
procedure;

It would look like this (you may notice that several things are not  
possible now)

create or replace sp_Update(tblName String,aRecid Long,UpdateFields  
as Array, UpdateValues as Array)

Begin
	build a cursor to lock the record(aRecID) of table tblName
	for i = 0 to UpdateFields.Count
		cursor.UpdateFields(i) = UpdateValue(i)
	next
	cursor.UpdateRecord()
	cursor = 0 ---> release locks
Exception
	record_is_already_locked

End

So what we TRY to do is updating a record through a stored procedure
The first PROBLEM is that we cannot obtain a lock inside a stored  
procedure so we just must try an update statement that will fail  
(generate an exception) if somebody else already has a writelock on  
this record.
There is currently no way to SET a lock on a record from within the  
procedure.
So the only way of updating a record from within the procedure seems  
to be trial and error. And that is something I don't like....

Bart