[Openroad-users] Dynamic SQL for conversion of COPYINTOstatements

[Colin Hay]

Yep - thats what I ended up doing - doco was a bit confusing but here is the solution for anyone interested
 
    val_arr             = ARRAY OF copy_column_uc;
    ss                  = SQLSelect;
 
        // Build columns and associate with targets
        lv_columns = '';
        FOR i = 1 TO lv_num_cols
        DO
            lv_columns = lv_columns + p_columns[i].column_name + ',';
            ss.Columns[i].Targets[1].Expression = 'val_arr[' + VARCHAR(i) + '].column_value';
            ss.Columns[i].Targets[1].IsSelectTarget = TRUE;
        ENDFOR;
 
        lv_columns = LEFT(lv_columns,LENGTH(lv_columns)-1);
 
        // Set Query
        ss.Query.Value = 'SELECT ' + lv_columns + ' FROM ' + p_tablename;
 
        lv_file.FileHandle = p_filename;
 
        // Open DataStream
        lv_status = ss.Open
            (
                QueryMode = QY_DIRECT,
                CheckCols = FALSE,
                Scope     = CurProcedure.Scope
            );
 
        IF lv_status != ER_OK
        THEN
            p_mess = 'DataStream Failed !';
            RETURN ER_FAIL;
        ENDIF;
 
        lv_status = ss.NextRow();
        WHILE lv_status = 0
        DO
            lv_status = ss.Load();
 
            MESSAGE HC_NEWLINE + 'Record ' + CHAR(ss.CurRoW);
 
            FOR i = 1 TO lv_num_cols
            DO
                MESSAGE val_arr[i].column_value;
            ENDFOR;
 
            
            lv_status = ss.NextRow();
        ENDWHILE;
 
        ss.Close();
 
Colin Hay
Database Administrator
Fintechnix
Level 3,
55 Clarence Street,
Sydney

colin.hay at fintechnix.com <mailto:colin.hay at fintechnix.com>  
Phone: +61 2 8234 8276 
 

________________________________

From: openroad-users-bounces at peerlessit.com [mailto:openroad-users-bounces at peerlessit.com] On Behalf Of Bodo Bergmann
Sent: Thursday, 7 February 2008 6:30 PM
To: International OpenROAD Users; International OpenROAD Users
Subject: Re: [Openroad-users] Dynamic SQL for conversion of COPYINTOstatements


If you need more flexibility on the "INTO part",
e.g. if you don't know the count of columns until runtime,
you can use an SQLSelect object to retrieve the data,
as it allows to specify both the query and the target variables at runtime.
 
Bodo.
 
Bodo Bergmann
Senior Software Engineer
OpenROAD Worldwide Development
Ingres Corp.


________________________________

Von: openroad-users-bounces at peerlessit.com im Auftrag von Kim Ginnerup
Gesendet: Do 07.02.2008 01:03
An: International OpenROAD Users
Betreff: Re: [Openroad-users] Dynamic SQL for conversion of COPY INTOstatements



Hi Colin,

How about using: execute immediate into.
It can take a dynamic SQL select statement.
But you might end up with a similar problem on the into part depending on how flexible you need to be.

btw. ABF cannot handle the into part ;-)

Kim
________________________________
Fra: openroad-users-bounces at peerlessit.com [openroad-users-bounces at peerlessit.com] På vegne af Colin Hay [colin.hay at fintechnix.com]
Sendt: 7. februar 2008 05:16
Til: International OpenROAD Users
Emne: [Openroad-users] Dynamic SQL for conversion of COPY INTO statements

We are currently converting our COPY INTO statements to run against DB2

I need to select the data into a String to write to a file

When getting the data, the columns and table names are dynamic

So I would like to do something like this -

SELECT :lv_columns
FROM :lv_table
{
    // Process each row of data into a String
};


Unfortunately (and not surprisingly) OpenROAD does not handle :lv_columns as desired

Is there a simple answer or do I need to drop into 3GL or (gulp!) ABF -

e.g.

data_array = select * from :lv_table;


Colin Hay
Database Administrator
Fintechnix
Level 3,
55 Clarence Street,
Sydney

colin.hay at fintechnix.com<mailto:colin.hay at fintechnix.com>

Phone: +61 2 8234 8276



Specialist providers of back and front office systems for the financial services industry.
Featuring: Fintechnix(r)
Disclaimer:
Notice: This message contains privileged and confidential information intended only for the use of the addressee named above. If you are not the intended recipient of this message you are hereby notified that you must not disseminate, copy or take any action in reliance on it.
Any views expressed in this message are those of the individual sender,except where the sender specifically states them to be the views of Fintechnix Pty Ltd.

________________________________________________________________
OpenROAD-Users mailing list

You can maintain your subscription here:
http://www.peerlessit.com/mailman/listinfo/openroad-users

To unsubscribe click on this link
mailto:openroad-users-unsubscribe at peerlessit.com&subject=unsubscribe

To subscribe click on this link
mailto:openroad-users-subscribe at peerlessit.com&subject=subscribe



______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email 
______________________________________________________________________


Specialist providers of back and front office systems for the financial services industry.
Featuring:  Fintechnix(r)
Disclaimer: 
Notice: This message contains privileged and confidential information intended only for the use of the addressee named above. If you are not the intended recipient of this message you are hereby notified that you must not disseminate, copy or take any action in reliance on it.
Any views expressed in this message are those of the individual sender,except where the sender specifically states them to be the views of Fintechnix Pty Ltd.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://www.peerlessit.com/pipermail/openroad-users/attachments/20080208/163a508f/attachment.html