re: Embedded SQL lock record

[<groyle@xxxxxxxx> (Gordon S. Royle)]

When you declare your cursor, make it read only. This should prevent locking.

eg

DECLARE x CURSOR FOR table
SELECT a, b, c, etc.
FOR READ ONLY

---------- Original Text ----------

From: "Simmerlein, Stefan" <S.Simmerlein@guenther.de>, on 3/30/01 3:26 AM:

Hi,

i have some problems with sql. We have a rpg-servicepgm. with a
sql-procedure using the sqlda. With this procedure i can execute
SELECT, INSERT, UPDATE Statements.

If i call my procedure with a normal SELECT statement the sql
locks the record. That's a problem, because after
calling the sql-procedure i make a chain and update operation
on the same file.

How can i use my procedure without lock the record ?
Can i say unlock in the prepare or in the fetch statements ?

thx for help, stefan


some statements of my sql-procedure:
...
..
...


 c                   if        %subst(statement:1:6) = 'SELECT'

                            
 c/exec sql include sqlda

 c/end-exec

 c/exec sql prepare s0 from :statement

 c/end-exec

 c                   eval      sqln = 50

 c/exec sql describe s0 into :sqlda

 c/end-exec

 c                   if        sqln <= sqld

 c                   eval      sqln = sqld

 c/exec sql describe s0 into :sqlda

 c/end-exec

 c                   endif

 c/exec sql declare c0 cursor for s0

 c/end-exec

 c/exec sql open c0

 c/end-exec

 c                   eval      #PrepareError = (SQLCOD < 0)

 c                   endif   

...
...
...
...
...


c              if        %subst(statement:1:6) = 'SELECT' 
   
c/exec sql                                                             
c+ fetch c0 for 1 rows                                                 
c+ using descriptor :sqlda                                             
c+ into :satz_ds                                                       
c/end-exec                                                             
c                else                                               
        
c/exec sql prepare s0 from :statement                            
c/end-exec                                                        
c/exec sql                                                        
c+ execute s0                                                      
c/end-exec                                                         
c               eval      #PrepareError = (SQLCOD < 0)        
c               endif                                              











_______________________________________________________________

Cott - The Leader in Premium Retailer Brand Beverage Innovation. 
_______________________________________________________________
+---
| This is the RPG/400 Mailing List!
| To submit a new message, send your mail to RPG400-L@midrange.com.
| To subscribe to this list send email to RPG400-L-SUB@midrange.com.
| To unsubscribe from this list send email to RPG400-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---