Re: Passing Arrays to an SQL Call

On 17-Mar-2014 10:29 -0700, Michael Ryan wrote:

I want to do this:

Exec Sql
Call PRMTEST( :pInStore
, :pOtSKU
) ;

Where pInStore and pOtSKU are arrays defined like this:

D pInSKU 9A Dim(200)
D pInQuantity 3A Dim(200)

And PRMTEST is an RPG program.

I'm getting an SQL0314 error in my compile. It's definitely due to
the array; if I take off the DIM it compiles.

I thought this was an issue in the past (passing arrays via a call
statement), but had been resolved by V7R1.

What do I need to do differently? I thought there was a thread about
this some time ago, but I can't find it. Thanks!

SQL added an ARRAY data type [available for many scalar data types], but there is no support for\from embedded SQL; i.e. no support for host language array types, neither to be declared as SQL ARRAY type nor directly passed as parameters. The ARRAY data type is limited to use in SQL routines [PROCEDURE ¿FUNCTION? ¿TRIGGER?] and Java external procedures; the JDBC can invoke a routine declared with an ARRAY typed parameter. The additional functions UNNEST and ARRAY_AGG enable the transition the representation of the values between column data in rows and elements of an array. Some more information that may be of value; included at the end of the first link below, are more links; two of which should match those that I included further below:

<http://www.ibmsystemsmag.com/ibmi/administrator/db2/SQL-Arrays-in-DB2-for-i-7-1/>
"SQL Arrays in DB2 for i 7.1" July 2010 | by Tom Blamer

<http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzcrtarrayt.htm>
_CREATE TYPE_ (Array)

<http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/sqlp/rbafyarray.htm>
_Array support in SQL procedures_