Example of a SQLRPGLE Program that Calls a SQL Stored Procedure Which is Written in Persistent Stored Modules (PSM)

Troubleshooting

Problem

The following will provide an example of a SQLRPGLE program that calls a SQL Stored Procedure which is written in Persistent Stored Modules (PSM).

Resolving The Problem

Disclaimer: This is only an example. IBM accepts no responsibility for its correctness.

To create the procedure, you can either use ACS Run SQL Scripts or the RUNSQLSTM command on the IBM i.

The SQL statement:

create procedure yourLIB.testproc (in char10 varchar(10), out char20 varchar(20))
language sql
begin
set char20 = char10 || char10;
end;

If you use the RUNSQLSTM command to create the procedure, paste the above create procedure code into a text member on the IBM i.

Then you would run the RUNSQLSTM command. For example, the RUNSQLSTM command would look like the following:

RUNSQLSTM SRCFILE(yourLIB/QSQL) SRCMBR(TESTPROC) COMMIT(*NONE) NAMING(*SQL)

Here is the SQLRPGLE program that calls the SQL Stored Procedure:

**FREE
 Dcl-S ParmIn          VarChar(10);
 Dcl-S ParmOut         VarChar(20);
 ParmIn = 'Test';
 EXEC SQL
  Call TESTPROC(:ParmIn, :ParmOut);
 Dsply ParmIn;
 Dsply ParmOut;
 *inlr = *on;

The results follow:

PARMIN = 'Test '
PARMOUT = 'TestTest '

[{"Type":"MASTER","Line of Business":{"code":"LOB68","label":"Power HW"},"Business Unit":{"code":"BU070","label":"IBM Infrastructure"},"Product":{"code":"SWG60","label":"IBM i"},"ARM Category":[{"code":"a8m0z0000000CHtAAM","label":"Programming ILE Languages"}],"ARM Case Number":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Versions"}]

Historical Number

510420188

Was this topic helpful?

Document Information

More support for:
IBM i

Component:
Programming ILE Languages

Software version:
All Versions

Operating system(s):
IBM i

Document number:
635097

Modified date:
05 December 2024

UID

nas8N1013295

IBM Support

Tips