sqlrpgle dynamic query in as/4000

Question

I have employee1 table with field student_id,name,marks and table with field name,suppose NEHA is present in name field,I have a table with name NEHA .In name table marks,id and subject field is there.I want to fetch subject from name table(neha) where marks >10. I need to write sqlrpgle program to fetch name from employee1 by dynamic query as name is changing each time.I have share the code. In this code each time different name is fetched from emoployee1 but the problem in this code is not able to fetch SUBJECT column from NEHA table.In debug its's coming subject=' '. but the same query run perfectly in strsql.could anyone suggest any solution?

D stmt1           s            500A   inz                                   
D subject         s             10A                                         
D @subject        s             10A                                         
D name            s             20A                                         
D @Name           s             20A                                         
D STUdent_ID      s             10P 0                                       
d marks           s             10P 2                                       

/Free                                                                      
EXEC Sql                                                                  
declare c1 scroll cursor  for                                            
select name,student_id,marks from employee1;                             

EXEC Sql                                                                  
open c1; 

EXEC Sql                                                              
 fetch first from c1 into :@name,:student_id,:marks;                  

dow  sqlcod=0;                                                       
stmt1='select subject from ' + %Trim(@name)+ ' where marks>10';    

EXEC Sql                                                     
declare c2 scroll cursor for                                
select subject from @name where marks>10;   

EXEC Sql                                                   
open c2;                                                  

EXEC sql                                                   
fetch first from c2 into :@SUBJECT;    
 If SQLCOD = 0;                                            
  dsply subject;           
 EndIf; 

EXEC Sql                    
 Prepare s2 from :stmt1;    
EXEC Sql                                                                       
execute s2;                                                                   

EXEC SQL                                                                       
 Fetch next from c1 into :name,:student_id,:marks;                             

EXEC SQL                                                                       
 Fetch next from c2 into :subject;                                                  
enddo;  

EXEC Sql                                                                       
 close c1;                                                                     
EXEC Sql                                                                       
 close c2;     

 *inlr = *on;                                                                  
/END-FREE                                                                    

Answer 1

So given that you have a table of names that refer to the name of other tables, you have an issue with the order of your dynamic sql statements and how they are called.

exec sql
  declare C1 cursor for
    select name, student_id, marks from employee1;
exec sql
  declare S2 statement;
exec sql
  declare C2 cursor for S2;

exec sql
  open C1;
exec sql
  fetch first from C1 into :@name, :student_id, :marks;
dow sqlcode >= 0;

  stmt1 = 'select subject from ' + %Trim(@name)+ ' where marks>10';      
  exec sql
    prepare S2 from :stmt1;
  exec sql
    open C2;

  exec sql                                                   
    fetch first from c2 into :@SUBJECT;    
  if sqlcod = 0;                                            
    dsply subject;           
  endif;
  exec sql
    close C2;

  exec sql
    fetch next from C1 into :@name, :student_id, :marks;
enddo;

exec sql
  close C1;

I put all my declares up at the front. they are not executable, and do not need to be called repeatedly as they are totally commented out and generate no code in the program. The sequence for using a dynamically prepared statement in a cursor is:

1. build the statement
2. prepare the statement
3. open the cursor
4. fetch from the cursor in a loop
5. close the cursor

A few notes on execution:

• No executes, the open of the cursor does that
• You declare the cursor with the statement name in the for clause of the declare cursor
• The declaration of the cursor never changes

BUT

Rather than separating the subject (course) data for each student into it's own table, it is better to keep it all together, and add a name column to a single courses table to identify which student the course data is associated with. And, since names can change, it is even better to use the student_id as the common element between the tables.

Here is a better way. I have not normalized the database, that is a complete topic not for SO. But, I have renamed the NEHA table as courses, and replaced name in the courses table with student_id, just so you can follow:

// Table formats
// EMPLOYEE1
//   student_id      Integer
//   name            Varchar(255)
//   marks           Integer
//
// COURSES
//   id              Integer
//   student_id      Integer
//   marks           Integer
//   subject         Varchar(255)
//   

dcl-ds employee_t    ExtName('EMPLOYEE1') Template;
end-ds;
dcl_ds courses       ExtName('COURSES') Template;
end-ds;

dcl-ds rec       Qualified;
  name           Like(employee_t.name);
  student_id     Like(employee_t.student_id);
  marks          Like(employee_t.marks);
  subject        Like(courses_t.subject);
endds;

C1_OpenCursor();
dow C1_FetchCursor(rec);
  dsply rec.subject;
enddo;
C1_CloseCursor();

// --------------------------------
// Open the cursor
// This includes the cursor declaration just in case
// I need to use a local variable such as a parameter
// in the SQL.
// --------------------------------
dcl-proc C1_OpenCursor;

  exec sql
    declare C1 cursor for
      select a.name, a.student_id, a.marks, b.subject
      from employee1 a
      join courses b on a.student_id = b.student_id
      where b.marks > 10;

  exec sql open C1;
  // Check SQLSTATE here
end-proc;

// --------------------------------
// Fetch a record from the cursor
// returns *On when a record is found
//   or *Off when no record is found.
// --------------------------------    
dcl-proc C1_FetchCursor;
  dcl-pi *n Ind;
    recout    LikeDs(rec);
  end-pi;

  exec sql
    fetch C1 into :recout;
  // Check SQLSTATE here for errors
  if sqlstate = '02000';  // record not found
    clear recout;
    return *Off;
  endif;
  return *On;
end-proc;

// --------------------------------
// Close the cursor
// --------------------------------
dcl-proc C1_CloseCursor;

  exec sql close C1;
end-proc;