Basics on stored procedures

[satya]

Hello boarders,

So,I have started writing procedures and this is completely new to me.so please don't kill me if i may ask a dummy questions.

I wrote a sql procedure and got it compiled after multiple retries.Now i am trying to test it/debug it.

it has 3 inp and 1 out parameter
this is what it looks like

declare c1 ccursor for
[B]select * from table where abc=def[/B];
open cursor 
fetch from c1;
if fld1 ='XYZ' --- [B]THIS IS  WHERE i am getting the error[/B] 

i am trying to fetch all the records


1.first thing how do i debug the stored proc i have put the debug option to *source but unable to understand how do i call it to debug.
tried runsql also

I am trying to call the stored proc using call syntax in isereis navigator, it calls the stored proc but gives an error saying feild not found in file.

2. Is there a particular way should i use the variable(how do i refer/use feilds in the file)

3. for the out parameter i am trying to pass a initial value i have tried the runsql command(as an alternative to run sql script) it fails saying that out parameter value not allowed for in,inout,out,if i don't put anything it says stored proc not found.

4.also do we something like datastructures in stored proc where i would fetch the data into the ds then i could use the variable.(that is how i do it in rpgle).

5.Can i use select * in stored proc cause i have seen many example using into clause. i wanted to have all the feilds accesible without using the into clause


Also i would like to ask any questions on this topic here as i don't want any new threads to open . hope admin have no issues with this.

[B.Hauser]

Re: Basics on stored procedures

When using a cursor in a stored procedure or embedded SQL, you need to specify in your FETCH Statement the variables where to put the read row values.
Since you used SELECT * (which is not the best idea though), you need to declare variables for all columns and list all those variables in the fetch statement in the same sequence as they are definied in your table.

SQL-PL does not support data structures.

If you add a SET OPTION Statement with option DBGVIEW=*SOURCE before the first Compound Statement (Begin ... End), you can debug the SQL Statements with the graphical debugger included in the IBM i Navigator's Run an SQL Script. (Open Debugger, SELECT your stored procedure, Set at leas one Break Point and call the procedure from the Run SQL Script.

For testing our procedures, you should run them through the IBM i Navigator, pass all OUTPUT Variables pass as question marks (?). The returned value can be seen in the IBM i Navigator's Message then.

Birgitta

[satya]

Re: Basics on stored procedures

When using a cursor in a stored procedure or embedded SQL, you need to specify in your FETCH Statement the variables where to put the read row values.
Since you used SELECT * (which is not the best idea though), you need to declare variables for all columns and list all those variables in the fetch statement in the same sequence as they are definied in your table.

SQL-PL does not support data structures.

If you add a SET OPTION Statement with option DBGVIEW=*SOURCE before the first Compound Statement (Begin ... End), you can debug the SQL Statements with the graphical debugger included in the IBM i Navigator's Run an SQL Script. (Open Debugger, SELECT your stored procedure, Set at leas one Break Point and call the procedure from the Run SQL Script.

For testing our procedures, you should run them through the IBM i Navigator, pass all OUTPUT Variables pass as question marks (?). The returned value can be seen in the IBM i Navigator's Message then.

Birgitta

TQVM hauser for taking time to answer the question however i must ask couple of more question.

I was able to debug now. Now when i am trying to eval a parameter or variable to change the value, i have to use eval *(%%parm) = 'xyx'.This changes the first character to 'x' .
1.I wanted to completely change the value though,I have tried this eval *(%%parm):c 4 ='xuxu' it doesn't works.
2.Is there another way i can check the variable instead using the above notation.
3.I Have declared an exit handler which sets couple of value before exiting the program. Now when i debugged the program the control did not go to compound statement written for exit handler,when the handler was called explicitly.however the values are changed.How do i debug the part after the handler declaration ? I AM ABLE TO DEBUG THIS NOW

[satya]

Re: Basics on stored procedures

hello everyone ,

i have started practising with global temp tables.

this is the code where i am preparing the temp table

set s4 = 'declare global temporary table qtemp.t2 as('||s3||')'||
' WITH DATA with replace ON COMMIT DELETE ROWS' ;
end if;

prepare stmt from s4;
execute stmt;

the statement executes fine

but when i wanted to use a for loop(practising for loop) for a declared temp table
inside a begin end block like below

begin
for each_record as c2 cursor for
select * from t2
end

it says file not found during compilation

i have tried session.t2, qtemp.t2

i have tried to create a dummy table in qtemp with some random structure( not like the above temp table)
The program compiled succesfully but while running its not able to execute the statement.(or somehow its not replacing the above table)

i used the declare,open fetch while loop method it works fine.

but with for i don't understand whts wrong.

or do i need to use the for loop within a calling application that calls the above stored proc.

[Michael Jones]

Re: Basics on stored procedures

Your code creates that TEMP table, but you have to incorporate a DECLARE CURSOR, OPEN, FETCH, and CLOSE statements in order to use a CURSOR.

FYI...CURSOR processing processes one row at a time, which occasionally is needed, but single row at a time processing should be avoided. SQL, and in particular SQL stored procedures, really shine when you avoid cursors and structure your design to perform SET based processing. SET based processing is typically MUCH faster and simpler than single row processing.

SQL stored procedures can return one or more result SETs to their caller, and even RPG can now consume those SETs (see SET RESULT SETS, ASSOCIATE LOCATORS, and ALLOCATE CURSOR).

[B.Hauser]

Re: Basics on stored procedures

Not sure what syntax you are using, but certainly not the SQL syntax.

Syntax for an FOR Loop is:

FOR Variable as CursorName CURSOR
   FOR SELECT-Statement
           DO SQL-Statement;
                additional SQL-Statements;
END FOR (Label);

For more details about SQL Programming look at:
SQL Control Statements

Birgitta

[satya]

Re: Basics on stored procedures

Not sure what syntax you are using, but certainly not the SQL syntax.

Syntax for an FOR Loop is:

FOR Variable as CursorName CURSOR
   FOR SELECT-Statement
           DO SQL-Statement;
                additional SQL-Statements;
END FOR (Label);

For more details about SQL Programming look at:
SQL Control Statements

Birgitta

Hello birgitta

The syntax is correct.However the issue is with the file not found ,i am trying to prepare the file by using prepare statement the file doesn't exist @ the for loop.

please find the code below

 set s3 =s1||s3||' and fld1 ='''||wwfld||'''';                  
 set s4 = 'declare global temporary table qtemp.t2 as('||s3||')'|| 
          ' WITH DATA with replace ON COMMIT DELETE ROWS' ;        
 end if;                                                           
                                                                   
 prepare stmt from s4;                                             
 execute stmt;                                                     
 get diagnostics condition 1 msgtxt = MESSAGE_TEXT;                
                                                                   
 begin                                                             
 --declare c2 cursor for                                           
 --select * from session.t2;                                       
                                                                   
 --open c2;                                                        
 --fetch from c2 into wwencd,wwtrno,wwtrdt,wwdact;                 
 for_loop:                                                         
 for each_record as c2 cursor                                      
 for[B] select * from t2  [/B]                                            
 do 
 set statement
end for; 

here t2 doesn't exist at compile time so i guess there should be a file with name as t2
now when i create a file t2 with a random structure it doesn't give the error at compile time.

now after i compile the program, its unable to execute the statement due to sqlcod -913 (UNSUCCESSFUL EXECUTION CAUSED BY DEADLOCK OR TIMEOUT. REASON CODE reason-code, TYPE OF RESOURCE resource-type, AND RESOURCE NAME resource-name).my prepared file name has different structure and fields when compared to the one i created above.

[Michael Jones]

Re: Basics on stored procedures

Most likely, table QTEMP.T2 is locked from a prior execution, and your subsequent execution is not being allowed to replace QTEMP.T2, due to the residual lock.

The default commitment control compile option is *CHG. If you truly don't need commitment control in this procedure, add "set option COMMIT = *NONE" in the header area of the source code. Doing that will improve performance, and decrease the chance for residual locks from one call to the next. Adding that compile option to the source doesn't prevent you from using commitment control in that procedure, it sets the default commitment control option for statements within that procedure. If you wish to selectively use commitment control on only some of the statements, you can do so by adding a WITH clause on a per statement basis. If you continue to use commitment control, when and where are you performing COMMIT or ROLLBACK? Rows and objects created under commitment control stay locked until you COMMIT or ROLLBACK.

You might also try using "for each_record as c2 cursor without hold".

A minor nitpick: it is more standard to use "declare global temporary table session." instead of "declare global temporary table qtemp.". Session tables will get created in QTEMP. I doubt that is causing you issues, but I don't know that for sure as I always use SESSION as a standard when using "declare global temporary table".

[satya]

Re: Basics on stored procedures

Most likely, table QTEMP.T2 is locked from a prior execution, and your subsequent execution is not being allowed to replace QTEMP.T2, due to the residual lock.

The default commitment control compile option is *CHG. If you truly don't need commitment control in this procedure, add "set option COMMIT = *NONE" in the header area of the source code. Doing that will improve performance, and decrease the chance for residual locks from one call to the next. Adding that compile option to the source doesn't prevent you from using commitment control in that procedure, it sets the default commitment control option for statements within that procedure. If you wish to selectively use commitment control on only some of the statements, you can do so by adding a WITH clause on a per statement basis. If you continue to use commitment control, when and where are you performing COMMIT or ROLLBACK? Rows and objects created under commitment control stay locked until you COMMIT or ROLLBACK.

You might also try using "for each_record as c2 cursor without hold".

A minor nitpick: it is more standard to use "declare global temporary table session." instead of "declare global temporary table qtemp.". Session tables will get created in QTEMP. I doubt that is causing you issues, but I don't know that for sure as I always use SESSION as a standard when using "declare global temporary table".

hello micheal i have tried to create session table.I am able to create the table in the sesssion /qtemp lib but the for loop says unable to locate the file in session lib when i try to compile my code.

however i do not have a session library.

for each_record as c2 cursor
for select * from session.t2

135 for v1 as c2 cursor without hold for
136 select * from session.t2
137 do

error:

136 Position 20 T2 in SESSION type *FILE not found.
Message Summary

if i remove the session keyword
gives the following error

136 Position 20 T2 in *LIBL type *FILE not found.
Message Summary

[B.Hauser]

Re: Basics on stored procedures

Is there any reason why you embedd the FOR LOOP in a compound statement (BEGIN ... END)?
If not try to remove the compound statement.

Birgitta

[satya]

Re: Basics on stored procedures

Is there any reason why you embedd the FOR LOOP in a compound statement (BEGIN ... END)?
If not try to remove the compound statement.

Birgitta

hello hauser

this is the reason i thought i need to embedd in a separate compound statement.however this doen't work also(please check out the last example)

https://www.toadworld.com/platforms/ibmdb2/w/wiki/7799.use-of-temporary-tables 

I beleive its a scoping issue,but i am not sure about it. i read somewhere whenever a dynamic table is declared it creates its own scope.again i am not sure. anyways i had to quit using forloop .i am using while and it works.

[satya]

Re: Basics on stored procedures

I have sarted with external stored proc with parameter style as SQL. and i wanted to test how sqlca is returned from a external stored procedure.so i wrote my external program in sqlrpgle and intentionally trying to create a "record not found error".so that i could set the sqlstate and sqlmsgtext to custom '38xxx' .HOWever the status are not reflected back in called program sqlca.it some how reset the sqlca.state to '0000' .i have put the called program in * caller actgrp and calling program in a named actgrp.both the program are sqlrpgle

I found that i have declared a select statement and that has its own sqlca and whenever i set the value of sqlstate. it is set for that particular select statement.when i return my called program sqlca.ds is not initialized with new value since i never initialized my called program ds,Is there any way around this. it seems calling an sqlrpgle external stored proc won't return user defined error codes.


Also there was this issue of passing the parameters to the calling program,i declared an output variable as decimal while creating a stored proc.but when i called the the external storedproc (parameter style sql) it passes a junk values in it.then i changed the out parameter to numeric and it works fine. correct value is passed.can anyone tell me why is the exact reason we have to declare a variable as numeric instead of decimal.

[satya]

Re: Basics on stored procedures

Hello boarders
still looking for any answer i could get.

I am calling an sqlrpgle program(doing a select into query which sets sqlcod to 100) from an external stored proc with parameter style sql.

i am not able to pass the sqlca to the calling sqlrpgle program.The values are reset everytime in the calling program.the called program is in activation group *caller.

[satya]

Re: Basics on stored procedures

Hello all,

I have started working with array result set external procedures. i am passing the result set to the caller with the following syntax

set result sets array :cursorarr for :count rows;

but how do i handle it in the caller(how can i see the values in the called program)

i am able to handle an cursor result set using associate locaters.

[Scott Klement]

Re: Basics on stored procedures

What language is the calling program written in?