Dynamic Data pipeline?

[mikeflynn]

I'm trying to export data from a Sybase or Oracle directly
to an ODBC data source (ex: MS Access) without retrieving it
first. These queries could return hundreds of thousands of
rows.

The issue is that the SQL is completely dynamic. Any table,
any columns, different joins, etc...

What I'm trying to do is create a generic pipeline object
and alter it's syntax prior to executing it's start.

Has anyone done anything similar? Right now, this is what
I'm doing:
1. Connect to my ODBC data source.
2. Create an Error datastore
3. Create an instance of an nvo pipeline object
4. Set the the DataObject of the pipeline to a dummy pipe
5. Override the pipeline's syntax
6. Start the pipe

Right now, the start returns with a -16 error (error in
source database). The target table is created but no rows
are transferred.

Powerbuilder 9, Oracle 9i, Sybase 12.5

[Jerry Siegel]

The pipeline user object has a string property Syntax. Once a pipeline
object has been assigned you should be able to get it, massage it, and
replace it. The syntax is not documented anywhere that I know of, but is
very similar to DW syntax. It's not that tough to figure out. Export a
pipeline or snag the property in debugger and you'll see what I mean.
Caveat: I haven't actually done this trick since PB5, but I would expect it
to still work.

<Mike Flynn> wrote in message news:4107c246.6aa...@sybase.com...

[mikeflynn]

Thanx for the response. That's exactly what I'm doing.

I create a pipeline (p_dummy) that moves one column from one
table from Oracle to Access, then exported p_dummy and
grabbed the syntax.

In my pipeline nvo, I created a function to set the syntax.
The idea is that it will compose the syntax based on the
query. For now, I've got it hard coded based on p_dummy's
syntax.

this.syntax = 'PIPELINE(source_connect=DEV2 -
SODEV5X,destination_connect=MS Access
Database,type=create,commit=100,errors=100)
SOURCE(name="CLAIMS_CNTL",COLUMN(type=varchar,name="INV_TYPE"
,dbtype="VARCHAR2(2)",nulls_allowed=no))
RETRIEVE(statement="PBSELECT( VERSION(400)
TABLE(NAME=~"claims_cntl~" )
COLUMN(NAME=~"claims_cntl.inv_type~")) ")
DESTINATION(name="CLAIMS_CNTL",COLUMN(type=varchar,name="INV_TYPE"
,dbtype="VARCHAR(2)",nulls_allowed=no,initial_value="spaces"))'

My issue is that when I start the pipe, it creates the
target table fine, but the gives me a -16 error (error with
source database). I enabled trace and the error is

(31e2f30): PBSELECT( VERSION(400) TABLE(NAME=claims_cntl )
COLUMN(NAME=claims_cntl.inv_type)) (0 MilliSeconds)
(31e2f30): DESCRIBE: (344 MilliSeconds)
(31e2f30):
Error 24333 (rc -1) : ORA-24333: zero iteration count

TIA.

[mikeflynn]

Got it. The issue was my retrieve statement. Changed to this
and it worked:

' RETRIEVE(statement="SELECT claims_cntl.inv_type FROM
claims_cntl")'

Thanx.

[Jerry Siegel]

As usual, having a second pair of eyes makes yours focus better :-)
And now I know that the trick will work in PB9. Thanks right back!

<Mike Flynn> wrote in message news:41080058.6ea...@sybase.com...