Re: RPG: Embedded Dynamic SQL using Descriptors.

I build individual subprocedures to set the data, I build a snippet for each type that I use, and insert it where needed. Here is one of them.

// -----------------------------
// Insert Character into SQL Descriptor
// -----------------------------
dcl-proc DescriptorInsertChar;
dcl-pi *n;
descrip Varchar(128) const options(*varsize);
entryno Int(10) const;
data Char(256) const;
end-pi;

dcl-s type Int(10) Inz(SQL_TYPE_CHAR);
dcl-s len Int(10) Inz(0);

len = %size(data);
exec sql set sql descriptor global :descrip
value :entryno
type = :type,
length = :len,
data = :data;

end-proc;

I was trying to create a single sub-procedure for each type, but that didn't work out so well as the database expects data length and type to match the eventual field in the database as well as the field being passed in. Something like that. I decided the best way was to have a subprocedure for each field I needed to add to a descriptor, and just call the appropriate subprocedure with the descriptor name I wanted to load it into. Less dynamic, but it worked. I believe that you can use SQLDA the way you want to though it is way more complicated.


Mark Murphy
STAR BASE Consulting, Inc.
mmurphy@xxxxxxxxxxxxxxx


-----MARK GOLDEN <mark_golden@xxxxxxxxxxxxxxx> wrote: -----
To: "MIDRANGE-L@xxxxxxxxxxxx" <MIDRANGE-L@xxxxxxxxxxxx>
From: MARK GOLDEN <mark_golden@xxxxxxxxxxxxxxx>
Date: 05/09/2016 01:34PM
Subject: RPG: Embedded Dynamic SQL using Descriptors.


Help needed. I have researched this but cannot find a definitive answer.
I am trying to generate a dynamic SQL INSERT using SQL descriptors.

I have built the SQL string with parameter markers, prepared the statement, allocated and described the SQL descriptor.
Now I want to use the SET DESCRIPTOR to define the fields within the SQL.

exec SQL
set descriptor :Desc_n
value :parmNo
type = :Data_Type,
length = :Data_Len,
data = :XXXXXX;

The part I am stuck on is the 'data' parameter. This seems to insist on a local variable and will not accept an array element. The reason I need the array is that is contains all field names that should be contained within the SQL INSERT Statement. If I have to specifically name a field the dynamic seems to have disappeared.

Hopefully I'm wrong!!