Change Database without changing Transaction Object

[Former Member]

Any ideas about change curent database without change (current) nor create a new transaction object.

I mean, exactly what I want is execute a simple ASE statement as:

use XXX ; where obviously XXX = database_name

Open table ....

Regards ...

Arnoldo Perozo

By the way I'm using ASE as target DBMS.

[Former Member · Accepted solution]

Hi Arnoldo;

  When we need to do this in ASE, I use either ...

1) A call to a Stored Procedure that can then cross DB boundaries.

  - OR -

2) Proxy tables that map to different DB areas.

HTH

Regards .. Chris

[Former Member · Accepted solution]

Thanks Chris ...

you think that's  the only option ...?  Can we try something with staging or dynamic areas ...? or those are exclusive for SQL and SP ..?

Regards ...

[Former Member · Accepted solution]

   Even Dynamic in-line SQL in PowerScript is tied to a Transaction Object and thus a specific DB. Unlike a DBScript where I can control the "USE DBName + GO" before I execute the DML statement, PB does not support that. However that being said, I have never tried issuing a Format1 SQL statement (EXECUTE IMMEDIATE) in PB to try and utilize the "USE" DML command to see what happens. My guess though is that that will not work .. but - I could be pleasantly surprised.  

[Former Member · Accepted solution]

Hi Chris ...

I was able to do it with "execute immediate" and "use database" commands.

I guess there always pleasant surprises ...

Regards ...

[Former Member · Accepted solution]

WOW ... "Kool" - I always wondered if that would work!   

BTW: Have you hugged your DataWindow today? 

[Former Member]

same SERVER, but different database?  As you suggested Arnoldo, the use command will work.

string ls_usedatabase

ls_usedatabase = "USE ThatDatabase"

execute immediate :ls_usedatabase using sqlca;

just make sure that the login has permissions in the database you are changing to.

note: this does not work in appeon

also, i didn't try this in ASE, only sql server.  should still work

[former_member1333806]

There's always the DISCONNECT, change transaction object attributes, re-CONNECT, but that's not necessarily performance friendly.

Depending on your DBMS and permissions, you may be able just to qualify your table names with the database (e.g. for ASE: ... from DBName..TableName ...).

Good luck.