Re: Soft coding SQL based on parameter file

[Charles Wilt <charles.wilt@xxxxxxxxx>]

Use dynamic SQL...

wSqlStmt = 'values (select ' + Column1_Name +',' + Column2_Name
<...>
                         + ') into ?';

exec sql prepare DynSQL from :wSQLStmt;

exec sql execute DynSQL using :myDataStruct;



Note the use of VALUES INTO instead of SELECT INTO you'd normally use with
static SQL.

That's the basics...

Charles


On Wed, May 31, 2017 at 5:05 PM, Roger Harman <roger.harman@xxxxxxxxxxx>
wrote:

> I have a need to soft-code SQL (embedded RPGLE) based on a parameter file
> for some auditing we need to do.  Record count and a couple of hash totals.
>
> Table name will be passed as a parm to the program and it then looks up
> the columns it needs to query.
>
> Configuration File Layout:     // Sample config
>   Table_Name   10a                // SLP15
>   Column1_Name   10a         // BBAL15
>   Column2_Name   10a         // PTMT15
>   Column1_Text  32a             // Customer Balance
>   Column1_Text  32a             // Payment Amount
>
> myDataStruct:         // Sample result data
>   Name1   10a           //  BBAL15
>   Name2   10a           //  PTMT15
>   Text1      32a           //  "Customer Balance"
>   Text2      32a           // "Payment Amount"
>   Value1   31p2         //  10000.00
>   Value2   31p2         // 2000.00
>   RcdCount   10i o    // 4000
>
> What I need is to be able to generate the equivalent of this based on and
> it is giving me fits:
>
> select 'BBAL15', 'PTMT15', 'Customer Balance', 'Amount Paid', sum(BBAL15),
> sum(PTMT15), count(*) into myDataStruct  from SLP15
>
> I've tried stringing it together with :HostVariable, using parameter
> markers, and nothing seems to work.   A lot of SQL0312 errors.
>
> Does anyone have a simple example of this or pointers to get me going?
> It's got to be the magic mix of literals and column names that is the
> problem.
>
> Thanks.
>
> Roger Harman
> COMMON Certified Application Developer - ILE RPG on IBM i on Power
>
>
>
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
> Please contact support@xxxxxxxxxxxx for any subscription related
> questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate
> link: http://amzn.to/2dEadiD