Re: Stored procedure column names

Justin,

I tried this humble code. Maybe you could try it. It will give you a hint
of what you could do further with a data structure array if that is what
you wanted to mean.
The trick here is to populate a single data structure or an array. You
will have to use your imagination then. What this does is build a CTE from
a data structure and return it as a cursor to the caller.

1. Please try to compile this program.
2. Create the corresponding SQL wrapper stored procedure.
3. Run it from iACS.

Here is the code:

ctl-opt dftactgrp(*no) actgrp('MRTST03');
ctl-opt decedit('0.') expropts(*usedecedit: *alwblanknum);
ctl-opt option(*srcstmt: *nodebugio);

dcl-pi MRTST03;
// no parms
END-PI;

dcl-ds resultSet qualified inz;
custNum zoned(9);
custFName varchar(32);
custMName varchar(32);
custLName varchar(32);
END-DS;

//** Program Start **//
*inlr = *on;

exec sql declare csrResultSet cursor for
with retCsrResultSet (custNum,
custFName,
custMName,
custLName) AS
(select :resultSet.custNum as "custnum",
:resultSet.custFName as "custfname",
:resultSet.custMName as "custmname",
:resultSet.custLName as "custlname"
from sysibm.sysdummy1 t1)
select custNum as "custnum",
custFName as "custfname",
custMName as "custmname",
custLName as "custlname"
from retCsrResultSet;

// Populate data structure fields
resultSet.custNum = 25248; // An arbitrary number
resultSet.custFName = 'Javier';
resultSet.custMName = 'A';
resultSet.custLName = 'Sanchez';

exec sql open csrResultSet;
//*** Program End ***//

Here are the instructions on how to build up the SP.

CREATE OR REPLACE PROCEDURE MYLIB.SP_MRTST03()
RESULT SETS 1
LANGUAGE RPGLE
NOT DETERMINISTIC
PARAMETER STYLE GENERAL
EXTERNAL NAME 'MYLIB/MRTST03';


Then call it from iACS and let me know your thoughts.

CALL MYLIB.SP_MRTST03();

HTH.

Javier Sanchez.


El jue, 29 dic 2022 a las 14:39, Javier Sanchez (<
javiersanchezbarquero@xxxxxxxxx>) escribió:

Try forcing them to lowercase, i.e.:
SELECT COL_A as "col_a"...
Make sure to enclose them in doble quotes.

Try that and let us know.

JS


El jue, 29 de dic. de 2022 1:53 p. m., Justin Taylor <
jtaylor.0ab@xxxxxxxxx> escribió:

I have an RPGLE external stored procedure that returns a data structure as
a result set. The column names are those defined in the Definition spec,
but are all upper case. Is there a way to to get the result set to not
force all upper case?

TIA
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com