RE: SQL - File staying locked.

["Leland, David" <dleland@xxxxxxxxxx>]

Rob:

Is there a reason you don't explicitly declare your SQL cursors and then
explicitly close them when done?  Works for me.

c/exec sql                                               
c+ declare     c1 cursor for                             
c+  select     itnbr                                         
c+  from       imhist a                                  
c+  where      a.trndt between :fromdate and :todate and 
c+             a.tcode = 'IT'                            
c+  order by   1                                      
c+ for read only                                         
c/end-exec                                               

c/exec sql       
c+ open c1       
c/end-exec       

c/exec sql                          
c+ fetch next from c1 into :itnbr 
c/end-exec                          

... do loop here
c/exec sql                          
c+ fetch next from c1 into :itnbr 
c/end-exec                          
... enddo

c/exec sql   
c+ close c1  
c/end-exec   

Dave

-----Original Message-----
From: rob@xxxxxxxxx [mailto:rob@xxxxxxxxx]
Sent: Thursday, April 24, 2003 11:07 AM
To: Midrange Systems Technical Discussion
Subject: SQL - File staying locked.


I've very recently gotten into the habit of using 
Commitment control . . . . . . . . . . . . . . :   *NONE
Close SQL cursor . . . . . . . . . . . . . . . :   *ENDMOD
Hoping it would no longer leave certain files locked when I do simple 
statements like:
select substr(a.upaccd,10,5), a.uptext into :EmpNum, :UserText
  from userlist a
  where a.upuprf=:USERNAME

However it does on occasion.  This same program and this same sql.  I did 
some reading on *ENDMOD and came up with:
*ENDMOD: SQL cursors are closed and SQL prepared statements are implicitly 
discarded when the module is exited. LOCK TABLE locks are released when 
the first SQL program on the call stack ends.

Then I thought, "Aha, sometimes I execute this program from somewhere 
below another SQL program in the call stack and that is what the issue 
is".  So I created a simple sqlrpgle program:
      /DEFINE DSpec
     D TEST            s             10a
     D qcmd            pr                  extpgm('QCMD')
      /UNDEFINE DSpec

     C/EXEC SQL
     C+ Set Option
     C+     Naming    = *Sys,
     C+     Commit    = *None,
     C+     UsrPrf    = *User,
     C+     DynUsrPrf = *User
     C/END-EXEC

     C/EXEC SQL
     C+ Select adfile into :test
     C+ from acctdel
     C+ where adfile='APTRAN'
     C/END-EXEC

      /free
       qcmd();
       *inlr=*on;
       return;
      /end-free

But, when I execute the qcmd() and try calling my test program it doesn't 
leave it locked.

What do I have to do, wait until it locks again and see if that program 
does something differently?
Isn't there some (expletive deleted) way to have the program that locks 
the table in the first place not lock the table?  Yo!  I'm done with it!

V5R2
cume=TL03077
WRKPTFGRP
PTF Group             Level  Status 
SF99519                  47  Installed
SF99502                   6  Installed
SF99271                   7  Installed
SF99190                  10  Installed
SF99169                   8  Installed
SF99149                   3  Installed
SF99148                   3  Installed
SF99098                   8  Installed
SF99085                   3  Installed
SF99039                   2  Installed


Rob Berendt
-- 
"They that can give up essential liberty to obtain a little temporary 
safety deserve neither liberty nor safety." 
Benjamin Franklin 
_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo.cgi/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.