Execute SQL statement using an RPG variable

352565 pts.
Tags:
RPG
SQL statements
I'm trying to pass a cursor and data structure to a procedure. That procedure should populate the structure with the next row from a SQL cursor. Here's what I'm trying to do:
 *****************************************************
  *
  * Fetch the next row from a cursor
  *
  * @param cursor - the name of the cursor
  * @param structure - the data structure to hold the fields
  *****************************************************
 pfetchNextRow     B
 DfetchNextRow     PI              N
 d cursor                     32767A   varying const
 d structure       DS                  ???????               
  /free 
     exec sql
       fetch next from :cursor  into :structure 
     ;

     if (sqlstate = SQL_SUCCESS);
       return *on;
     else;
       exec sql
         close :cursor;
       return *off;
     endif;

  /end-free
 pfetchNextRow     E
How can I pass the cursor? How would I define the data structure parameter?

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TomLiotta

    How can I pass the cursor?

    You can't. From the SQL Reference documentation for the DEFINE CURSOR statement:

    • It is an executable statement that cannot be dynamically prepared.

    How would I define the data structure parameter?

    If the process could be done in this way, you would define a different structure to match each possible table that might be accessed and code FETCH statements for each table and structure. You would have logic to choose the matching FETCH statement.

    You can't dynamically prepare a DEFINE CURSOR statement. Such a statement must be coded in the program and compiled, not passed for dynamic execution.

    Any structure that receives column data must be defined to match data types and sizes for each column it receives. It can be a single definition if all tables have the same column definitions.

    If this overall type of process is needed, the programming will be more complex. Processing similar to what you ask for will require use of detailed SQL APIs defined in the SQL call level interface (CLI) topic in the Information Center.

    For example, your program would examine a user request to see what table was involved. It would query the database to retrieve definitions for each column reference, and it would call one of the SQL CLI APIs to set each column's definition in an internal SQL data structure. Then it would call APIs to execute the request and to begin returning rows. As each row was returned, you would use whatever APIs are needed to pull the column values from the internal structures and to format them in however it's needed to satisfy the user request.

    The SQL CLI APIs are essentially the same ones that would be used in an ODBC application that did the same thing, i.e., that received an undefined request and returned an undefined result. In short, it would determine both the request and the result and dynamically define and manage the memory to do the work.

    Tom

    125,585 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following