[Openroad-users] Dynamic SQL for conversion of COPY INTOstatements

[Bodo Bergmann]

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


-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://www.peerlessit.com/pipermail/openroad-users/attachments/20080207/295014cb/attachment.html