473,573 Members | 2,210 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

cursor in procedure

Hello Folks,

I tried to create a stored procedure on the AS/400 - but it doesn't work ;o(

At first I will validate a specific value by a Param-value and after that
update it.
At least I want to return a resultset. But the DB2 tells me "[SQL0104] Token
C1 not valid"

When I compile the sp without the both first querys and without the
if-statement
or without the courser - it still works and it cannot be a syntaxerror.

I still guess that coursers only work as standalone!? Hopefully not!
Perhaps I could write i into two SP, but I want it in one SP.

Who can help me?
Regards - Bas
#### SOURCE ############### ############### #############

CREATE PROCEDURE myfunc(in iUSTID integer)
DYNAMIC RESULT SETS 1
LANGUAGE SQL
CALLED ON NULL INPUT
MODIFIES SQL DATA
INHERIT SPECIAL REGISTERS

BEGIN
DECLARE iUSRID integer;

select USRID into iUSRID
from session
where USTID = iUSTID
and CTRLSTATUS = 1
and SESS_START between CURRENT TIMESTAMP-1 MINUTE and CURRENT
TIMESTAMP;

update session
set SESS_END = CURRENT TIMESTAMP,
CTRLSTATUS = 0
where USTID = iUSTID;

if iUSRID>0 then
declare c1 cursor for
select USRID, NAME
from user
where USRID = iUSRID;
OPEN c1;
end if;
END;
Sep 20 '06 #1
5 4389
Hello.
Try this:

#### SOURCE ############### ############### #############

CREATE PROCEDURE myfunc(in iUSTID integer)
DYNAMIC RESULT SETS 1
LANGUAGE SQL
CALLED ON NULL INPUT
MODIFIES SQL DATA
INHERIT SPECIAL REGISTERS

BEGIN
DECLARE iUSRID integer;

declare c1 cursor with return for
select USRID, NAME
from user
where USRID = iUSRID;

select USRID into iUSRID
from session
where USTID = iUSTID
and CTRLSTATUS = 1
and SESS_START between CURRENT TIMESTAMP-1 MINUTE and CURRENT
TIMESTAMP;

update session
set SESS_END = CURRENT TIMESTAMP,
CTRLSTATUS = 0
where USTID = iUSTID;

if iUSRID>0 then
OPEN c1;
end if;
END;

Sincerely,
Mark B.
Hello Folks,

I tried to create a stored procedure on the AS/400 - but it doesn't work ;o(

At first I will validate a specific value by a Param-value and after that
update it.
At least I want to return a resultset. But the DB2 tells me "[SQL0104] Token
C1 not valid"

When I compile the sp without the both first querys and without the
if-statement
or without the courser - it still works and it cannot be a syntaxerror.

I still guess that coursers only work as standalone!? Hopefully not!
Perhaps I could write i into two SP, but I want it in one SP.

Who can help me?
Regards - Bas
#### SOURCE ############### ############### #############

CREATE PROCEDURE myfunc(in iUSTID integer)
DYNAMIC RESULT SETS 1
LANGUAGE SQL
CALLED ON NULL INPUT
MODIFIES SQL DATA
INHERIT SPECIAL REGISTERS

BEGIN
DECLARE iUSRID integer;

select USRID into iUSRID
from session
where USTID = iUSTID
and CTRLSTATUS = 1
and SESS_START between CURRENT TIMESTAMP-1 MINUTE and CURRENT
TIMESTAMP;

update session
set SESS_END = CURRENT TIMESTAMP,
CTRLSTATUS = 0
where USTID = iUSTID;

if iUSRID>0 then
declare c1 cursor for
select USRID, NAME
from user
where USRID = iUSRID;
OPEN c1;
end if;
END;
Sep 20 '06 #2
<4.****@mail.ru wrote:
Hello.
Try this:

#### SOURCE ############### ############### #############
<SNIP />

Thank you - it works!

But can you tell me why I have to set up the cursor at the top?
Is it caused on to set a declare at the top of a sp?

Bas
Sep 20 '06 #3
Check the strucure description of sql compound statement at
http://publib.boulder.ibm.com/infoce...mpoundstmt.htm

But can you tell me why I have to set up the cursor at the top?
Is it caused on to set a declare at the top of a sp?
Sep 20 '06 #4
4.****@mail.ru wrote:
Check the strucure description of sql compound statement at
http://publib.boulder.ibm.com/infoce...mpoundstmt.htm

>But can you tell me why I have to set up the cursor at the top?
Is it caused on to set a declare at the top of a sp?
You may be able to do what you want by wrapping the DECLARE and OPEN
into it's own block:
.... THEN BEGIN
DECLARE ...
OPEN ...
END

Not sure if DB2 iSeries supports nested compounds.
keep in mind that cursors opened in a nested compound are implicitly
closed when their scope is left unless they are defined WITH RETURN

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Sep 20 '06 #5
Yes, iSeries (at least V5R3) supports nested compounds.
You may be able to do what you want by wrapping the DECLARE and OPEN
into it's own block:
... THEN BEGIN
DECLARE ...
OPEN ...
END

Not sure if DB2 iSeries supports nested compounds.
keep in mind that cursors opened in a nested compound are implicitly
closed when their scope is left unless they are defined WITH RETURN

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Sep 20 '06 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
2217
by: Philip Mette | last post by:
Does anyone have any good references they could recommend on Cursor based SQL writing? I have to create SQL that can loop though records simular to VB loops and I have been told that this is the way to go. Any recommendations would be helpful.
15
3808
by: Philip Mette | last post by:
I am begginner at best so I hope someone that is better can help. I have a stored procedure that updates a view that I wrote using 2 cursors.(Kind of a Inner Loop) I wrote it this way Because I couldn't do it using reqular transact SQL. The problem is that this procedure is taking longer and longer to run. Up to 5 hours now! It is...
3
6097
by: DarthMacgyver | last post by:
Hello, I recently wrote a survey application. Each question is very similar. The first questions gives me a problem when there are multiple people taking the survey (The Database connection Timed out) I am using the Data Access Application Blocks as ASP.NET (using VB.NET) and SQL 2000. In there first question there can be up to 27...
10
1688
by: Neil | last post by:
I need to get two values from a complex SQL statement which returns a single record and use those two values to update a single record in a table. In order to assign those two values to variables and then use those variables in the UPDATE statement, I created a cursor and used Fetch Next.... Into. This way, I only have to call the complex SQL...
2
9483
by: ansonee | last post by:
I wrote a stored procedure that contains some COMMIT logic, but keep runnign into an odd problem. As soon as the procedure gets to the COMMIT statement, the CURSOR closes. Here's the procedure: CREATE PROCEDURE AIM.UPDATEARCHIVERETRIEVALSTATUS ( ) SPECIFIC AIM.UPDATEARCRETSTS LANGUAGE SQL P1: BEGIN DECLARE RECORDCOUNT INTEGER;
8
13989
by: Yusuf INCEKARA | last post by:
I have a stored procedure : CREATE PROCEDURE STP_GETSTORELIST @RETCUR CURSOR VARYING OUTPUT AS set @RETCUR = CURSOR FORWARD_ONLY STATIC FOR SELECT ID,STORE_NAME FROM T_INF_STORE ORDER BY STORE_NAME OPEN @RETCUR
12
30991
by: Lucky | last post by:
Hi guys! i want to create one cursor in the t-sql. the problem is i want to use stored procedure instead of select command in cursor. can anyone tell me how can i use stored procedure's o/p to create cursor? i'm using sql 2000 and .net 2.0 thanks,
10
3245
by: 4.spam | last post by:
Hello. v8.2.7, windows xp. --- create procedure cursor_test() language sql dynamic result sets 1 begin declare c1 cursor
2
3862
by: BilalGhazi | last post by:
Hi All, I have this strange problem. I am user of two different database (both are same version 9i). I created a procedure and within this procedure i used a cursor to select the values, this is working very fine. When i copy this procedure in other database and try to run the procedure, the procedure runs successful, but the cursor does not...
1
1785
by: love2livedislife | last post by:
I have a requirement as follows: while executing the procedure for first time i'll open a cursor and read the first row from it and return the value through OUT parameter of procedure without closing the cursor... during the second time execution of the same procedure i need the cursor to be opened so that i can read the second row during...
0
7710
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8039
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
1
7800
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
6437
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
3738
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3745
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2230
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1332
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1049
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.