RE: passing a DS to SQL Stored Proc

Birgitta,

Thanks. That is what I suspected. So, I have already begun the re-design to CALLP.

The reason for this call method is that this program can return a cursor for the caller to consume. I could find no way, but via the SQL stored proc, to get this to work. Also, the SQL Stored Proc method allows for use by non-native processes (ie - .net)

Steve Needles

-----Original Message-----
From: rpg400-l-bounces+sneedles=northlandins.com@xxxxxxxxxxxx [mailto:rpg400-l-bounces+sneedles=northlandins.com@xxxxxxxxxxxx] On Behalf Of Birgitta Hauser
Sent: Friday, October 28, 2011 12:27 PM
To: 'RPG programming on the IBM i / System i'
Subject: AW: passing a DS to SQL Stored Proc

Hi,

SQL cannot handle data structures.
I'd wrap the original procedure with another procedure that does nothing
else than moving the data structure into a character field and call the
original procedure after.

What I don't understand, why would you call an ILE procedure as SQL
registered Stored Procedure instead of calling it directly via CALLP.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im
Auftrag von Needles,Stephen J
Gesendet: Friday, 28. October 2011 17:46
An: rpg400-l@xxxxxxxxxxxx
Betreff: passing a DS to SQL Stored Proc

Hey all,

I'm trying to pass a data structure to a service program's subprocedure via
an SQL Store procedure without a great deal of luck.

In the primary RPG program, the data structure is an externally described
table. This program will populate the data structure and then pass the data
to a service program's subprocedure responsible for writing the row to the
table. The parameter is the data structure.

The Stored procedure is defined with a single parameter defined as the
compiler's length of the data structure. In this case: 720.

The call from the Primary: exec sql call SetTable(:TBLDTA);

The data structure is defined:

D TBLDTA e DS extname(TBLDTA)
D prefix(TBLDTA_)

The receiver in the SETTBLDTA subprocedure of the service program:

dSetTBLDTA pr like(sqlcod)
d parm_TBLDTA likeds(TBLDTA)
.
.
.
pSetTBLDTA b export
dSetTBLDTA pi like(sqlcod)
d parm_TBLDTA likeds(TBLDTA)



The SQL Stored Proc:

CREATE PROCEDURE SETTBLDTA (
IN PARM_TBLDTA CHAR(720) )
LANGUAGE RPGLE
SPECIFIC SETTBLDTA
NOT DETERMINISTIC
MODIFIES SQL DATA
CALLED ON NULL INPUT
PROGRAM TYPE SUB
EXTERNAL NAME servicepgm(SETTBLDTA)
PARAMETER STYLE SQL

I am getting a SQL0206 error when the primary RPG program attempts to run
the stored proc.

Is there a restriction that I missed in using a data structure as the
parameter?


Steve Needles

============================================================================
==
This communication, including attachments, is confidential, may be subject
to legal privileges, and is intended for the sole use of the addressee. Any
use, duplication, disclosure or dissemination of this communication, other
than by the addressee, is prohibited. If you have received this
communication in error, please notify the sender immediately and delete or
destroy this communication and all copies.