datastore create() function help

[kidmar]

Hi.
I would like to create a datastore from an SQL SELECT.
This is the code i wrote:

/*******************************************/
// here is my SELECT
ls_select = "select * from dba.b04_doc"

// create datastore syntax
ls_syntax = sqlca.syntaxfromsql( as_select, 'style(type=grid)', ls_error_syntax)

// create datastore
li_error_create = this.create(ls_syntax, ls_error_create)

// set his transObject
lds.settransobject( sqlca_2 )

// retrieve
lds.retrieve()

/*******************************************/

And here is my question:
How can I set which TransactionObject should the datastore use BEFORE the create() function?
I am connected to two databases through two transaction objects (SQLCA and SQLCA_2) and I want to create a datastore from a table of SQLCA_2.
In the example I wrote the datastore uses SQLCA (powerbuilder system's trans object) and returns an error because he can't find the table b04_doc (which only exist in SQLCA_2).
If i try to call the SetTransObject(sqlca_2) before the create() the datastore just ignores it.
this looks like create() can only work on SQLCA. is this true?

thank you very much!
Marco

 

[kidmar]

Oh i forgot..
I'm using PB v 9.01 build 7119

 

[reevda]

Try it this:

1) create a datawindow and save it (dw_test)

2)
datastore lds
lds = create datastore
lds.dataobject = "dw_test"
lds.SetTransObject( SQLCA_2 )
lds.Retrieve()

3) don't forget to destroy it when finished
destroy lds

 

[kidmar]

I needed to create a dw dynamically, so I can't use a "dw_test".
Anyway, I just found an error:

// create datastore syntax
ls_syntax = sqlca.syntaxfromsql( as_select, 'style(type=grid)', ls_error_syntax)

here i used sqlca.syntaxfromsql()
that's why the ds always used sqlca to make the syntax
i changed it to sqlca_2 and it worked fine.

thank you.