different databases
Min Kim asked how to reference a second database (db2) in a PREPAREd INSERT ... SELECT when the program opened db1, using colon-qualified names. John Bejarano said it works if db2 is on the same Informix server; otherwise use db2@otherserver:table and check connectivity files (sqlhosts, /etc/services, .rhosts). Art S. Kagel suggested table aliases and his dbcopy.ec utility from the IIUG repository.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Hello,
I'm PREPARING a string to execute an insert statement into a table in a
database that I defined at the top of my program as, say, DATABASE db1.
However, I'm inserting values from another database, say db2. How can I
reference this other database in my sql string? Is the following ok?
insert into tableName select 0, db2:quote_items.part_no, db2:part_code.item_type from db2:quote_head ...... etc
basically, I'm specifying the 'scope' with colons everywhere.
Min
Min,
So long as db2 resides in the same Informix server, your SQL should work
fine. If it is in another Informix server (on the same host, or another
host), you'll need to specify that Informix server in the statement (e.g.
db2@otherserver:quote_items). If you have to use another server,
double-check that all of the connectivity files are set up to allow you to
do this (i.e. sqlhosts, /etc/services, .rhosts, etc.) Hope this helps.
Kind regards,
John Bejarano.
Min Kim wrote in message <374AF0E1.AA837899@icpma.com>...
>Hello,
>I'm PREPARING a string to execute an insert statement into a table in a
>database that I defined at the top of my program as, say, DATABASE db1.
>
>However, I'm inserting values from another database, say db2. How can I
>reference this other database in my sql string? Is the following ok?
>
>insert into tableName select 0, db2:quote_items.part_no, db2:part_co>de.item_type from db2:quote_head ...... etc
>
>basically, I'm specifying the 'scope' with colons everywhere.
>Min
>
Min Kim wrote:
>
> Hello,
> I'm PREPARING a string to execute an insert statement into a table in a
> database that I defined at the top of my program as, say, DATABASE db1.
>
> However, I'm inserting values from another database, say db2. How can I
> reference this other database in my sql string? Is the following ok?
>
> insert into tableName select 0, db2:quote_items.part_no, db2:part_co> de.item_type from db2:quote_head ...... etc
>
> basically, I'm specifying the 'scope' with colons everywhere.
Simplify:
insert into tableName
select 0, q2.part_no, q2.part_co, de.item_type
FROM db2:quote_head q2, ......
Just use the SQL alias capability. BTW check out my dbcopy.ec utility
for doing this sort of thing. It is blazingly fast and already
written. You can download it from the IIUG Software Repository in the
package utils2_ak.
Art S. Kagel
PS - Why do these questions seem to run in batches? This is the 5th
time today I find myself recommending dbcopy.ec. Two weeks ago I had
recommended myschema.ec several times a day. In March it was
dostats.ec Very odd, no?