Sanjay soni wrote : > >I have written one stored procedure. >What I am trying to do is: >1. get the data from base table. >2. modify the data of one column(FOLDERNAME) > Well here, value of foldername looks like >"c:\folder\Myfolder". But I want only Myfolder from this data. So we >have written another stored procedure which extract the data and >store it in output parameter, because there is no inbuild support >from sapdb for doing all this. > >3. Copy the data got from base table + modified foldername in to temp >table. > >4. Sort the temp table on foldername >5. Return it as a cursor > >It is must that I dont touch the original data, but also data must be >sorted on the modified foldername and returned it as cursor. > >When I compile this procedure, it compiles properly, and when I run >it, I get the error message, > >---- Error ------------------------------- >Auto Commit: On, SQL Mode: Internal, Isolation Level: Committed >General error;-4024 . >call UPDATERESULTSET_CURSOR3 > >I guess something is wrong with temp table, but dont know what?? > >Here is the stored procedure > >CREATE DBPROC UPDATERESULTSET_CURSOR3 returns CURSOR AS >VAR > JINDEX INT; > JID VARCHAR(64); > CUSERID INT; > AUSERID INT; > FLEINDEX INT; > FLDRNAME VARCHAR(255); > FLENAME VARCHAR(1024); > JNAME VARCHAR(255); > TMPFLDRNAME VARCHAR(1024); > WRKFLOWFILENAME VARCHAR(1024); > ARCHIEVEFLDRNAME VARCHAR(1024); > TRGTNAME VARCHAR(255); > USRNAME VARCHAR(255); > JNO INT; > CHANGEDFLDRNAME VARCHAR(255); > >BEGIN > CREATE TABLE TEMP.TEMPJOBS(TMPJOBINDEX INT, > TMPJOBID VARCHAR >(64), > TMPCREATEDUSERID >INT, > TMPASSIGNEDUSERID >INT, > TMPFILEINDEX INT, > TMPFLDRNAME >VARCHAR(255), > TMPFILENAME >VARCHAR(1024), > TMPJOBNAME VARCHAR >(255), > TMPTEMPFLDRNAME >VARCHAR(1024), > >TMPWRKFLOWFILENAME VARCHAR(1024), > >TMPARCHIEVEFLDRNAME VARCHAR(1024), > TMPTARGETNAME >VARCHAR(255), > TMPUSERNAME >VARCHAR(255), > TMPJOBNO INT); > > DECLARE MYCURSOR CURSOR FOR > SELECT * FROM PMP_OWNER.JOBS; > > WHILE $RC = 0 DO BEGIN > FETCH NEXT MYCURSOR >INTO :jIndex, :jID, :cUserID, :aUserID, :fleIndex, :fldrName, > :fleName, :jName, :tmpFldrName, :WrkFlowFileName, :ArchieveFld >rName, > :TrgtName, :UsrName, :jNo; > > IF($rc = 100) THEN break; > CALL GETFOLDERNAME2(:fldrName, :changedFldrName); > > INSERT INTO TEMP.TEMPJOBS(TMPJOBINDEX , > TMPJOBID, > TMPCREATEDUSERID, > TMPASSIGNEDUSERID, > TMPFILEINDEX, > TMPFLDRNAME, > TMPFILENAME, > TMPJOBNAME, > TMPTEMPFLDRNAME, > >TMPWRKFLOWFILENAME, > >TMPARCHIEVEFLDRNAME, > TMPTARGETNAME, > TMPUSERNAME, > TMPJOBNO) > VALUES(:jIndex, > :jID, > :cUserID, > :aUserID, > :fleIndex, > :changedFldrName, > :fleName, > :jName, > :changedFldrName, > :WrkFlowFileName, > :ArchieveFldrName, > :TrgtName, > :UsrName, > :jNo); > END; > > DECLARE ReturnCursor CURSOR FOR > Select * from TEMP.TEMPJOBS order by TMPFLDRNAME; > > DECLARE ReturnCursor2 CURSOR FOR > Select * from PMP_OWNER.ReturnCursor; > END; > Please change
DECLARE ReturnCursor2 CURSOR FOR Select * from PMP_OWNER.ReturnCursor; to $CURSOR = 'RETURNCURSOR2'; DECLARE :$CURSOR CURSOR FOR Select * from PMP_OWNER.ReturnCursor; HTH, Thomas -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
