Changing environments in 4GL
Dana needed one 4GL program to collect data from three mirrored databases in different environments. Art S. Kagel gave three options: separate named connections using WITH CONCURRENT TRANSACTIONS and SET CONNECTION, sequential DATABASE statements, or remote-database queries. Andrej Falout suggested CONNECT; Jonathan Leffler noted CONNECT cannot be prepared, is built into 7.3, and add-on code exists for 6.x/7.2.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Does anyone know how I can change environments from within a 4gl program? I have 3 mirrored databases in 3 different enviroments that I need to collect data from for a financial report. Can I do a TBCONFIG inside the 4gl without loosing my program thread? If I change environments can I use a cursor with hold. Thanks, Dana Hunsaker FPIC mailto borilar@home.com
In article <38423A5E.CA2E6726@home.com>,
dana <borilar@home.com> wrote:
> Does anyone know how I can change environments from within a 4gl
> program? I have 3 mirrored databases in 3 different enviroments that I
> need to collect data from for a financial report. Can I do a TBCONFIG
> inside the 4gl without loosing my program thread? If I change
> environments can I use a cursor with hold.
>
> Thanks, Dana Hunsaker
> FPIC
> mailto borilar@home.com
You have three options:
1) you can open three separate CONNECTIONS, assuming that 4GL 7.xx now
supports the CONNECT TO and SET CONNECTION statements
2) you can collect data from one server then execute a new DATABASE
statement and reconnect to the second server, etc
3) you can access the other two servers as remote databases and query
their data while connected to the first engine instance.
Quick and dirty how to:
1) Separate CONNECTIONS:
a) Create three named connections, one for each server, with the WITH
CONCURRENT TRANSACTIONS clause so cursors do not close when you switch
connections:
CONNECT TO "databs@server_1" AS Serv1 WITH CONCURRENT TRANSACTIONS;
CONNECT TO "databs@server_2" AS Serv2 WITH CONCURRENT TRANSACTIONS;
CONNECT TO "databs@server_3" AS Serv3 WITH CONCURRENT TRANSACTIONS;
Note that all of the servernames (ie server_1, etc) must be non-shared
memory as you are only allowed one shared memory connection per process.
b) Switch connections with:
SET CONNECTION TO Serv1;
c) Create and manage cursors on each connection and switch as needed to
format the report. This may be the fastest method as the connection
changes are very cheap and are direct to each instance avoiding
overhead of passing each communication packet through the local engine
as the third method does.
2) Sequential connections:
a) Use CONNECT or DATABASE to connect to the first instance
b) Fetch any data into data structures, temp files or reports if you do
not need to merge data from the three servers.
c) Use CONNECT or DATABASE to connect to the second instance
d) Fetch data as in b)
e) Connect to the third instance as in c)
f) Fetch data as in b)
g) Report
3) Remote databases:
a) Connect to one instance
b) SELECT data from other instances using the full ANSI addressing
syntax: database@server:table.column (I've left out the owner names
as they just confuse the issue, if you use an ANSI mode database the
owners are required.) So:
SELECT * FROM databs@server_2:table_1 WHERE .....;
All of these methods will work if the sqlhosts file lists all three
servers (for method one you need a pipe or network connection ALIAS
for each).
Art S. Kagel
Sent via Deja.com http://www.deja.com/
Before you buy.
In article <38423A5E.CA2E6726@home.com>, borilar@home.com says... > I do a TBCONFIG No, you can not, but you can use CONNECT statemant (since you are still using TBCONFIG, I susspect you will need to prepare it, since older versions of 4gl did not directly support CONNECT) to connect to any IDS environment that is defined in your current sqlhosts file. HTH, Yours, Andrej Falout, http://www.falout.com ICQ 7628616 ++64.21.607517 #----------------------------------------------------------------- globals "std_disclaimer.4gl" Ask yourself just one question: ' Qu' m's se puede hacer y aprender ? - Propellerhead ReBirth RB-338 manual
Andrej Falout wrote: > In article <38423A5E.CA2E6726@home.com>, borilar@home.com says... > > I do a TBCONFIG > > No, you can not, but you can use CONNECT statemant (since you are still > using TBCONFIG, I susspect you will need to prepare it, since older > versions of 4gl did not directly support CONNECT) to connect to any IDS > environment that is defined in your current sqlhosts file. Except that CONNECT is not a preparable statement... There is code in the IIUG archives which provides CONNECT functionality to I4GL 6.x or 7.2 programs. 7.3 has it built in; 4.x can't use CONNECT period (neither ESQL/C 4.x nor 5.x supported CONNECT). -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.62 -- see http://www.perl.com/CPAN #include <disclaimer.h> PS: A moment's thought shows why CONNECT cannot be prepared; you need a database to prepare a statement, but CONNECT is what establishes a connection to the database.