Hi, i am having headaches since yesterday trying to solve this problem. I dont have much experience with informix so maybe the answer is easy.

I am trying to do this query in Informix:

INSERT INTO database01.dbo.TableA(id, field01)
SELECT * FROM database02.dbo.TableB
WHERE TableA.id=TableB.id

in sql server is similar to this query bus i have no idea how to do it in informix. i need your help.

Thanks In Advance!!

Try this one:

Thank you for your quick answer. i have tried it and now it says:

Not implemented yet.

but because i am running a beta version of server studio 6 y suppose that means that i will need the full version

which program do you use??

I'm using standard win32 SQL editor, produced by Informix and all works fine!

I have asked about this problem to Server Studio and this was their answer:

This error comes from IBM Informix JDBC driver - not from Server Studio.

It usually means that your table has LVARCHAR column and Informix JDBC driver does not support cross database queries against LVARCHAR columns.

So i think what i need is another driver... any suggestion?

I checked ODBC driver reaction on syntax, all works fine!

That what you try to do is for once or you trying create some permanent code solution?

If there is a problem on the JDBC driver side (which I am not sure about) - then you could wrap the query into a stored procedure and call it from the client code, so that the actual execution/syntax check whatever happens on the db server side. But I think this shouldn't be a driver issue. What Informix version are you running?

i think it is 9.40. i already have created a procedure. here is a sample of it:

CREATE FUNCTION historicos(ep VARCHAR)
RETURNING BOOLEAN;

DEFINE d VARCHAR(30);
DEFINE f DATE;
DEFINE ok BOOLEAN;

INSERT INTO lyra_historicos:uhd_actividadesvisita
SELECT AV.* FROM uhd_actividadesvisita AV, uhd_visita VI
WHERE AV.visita IN (
SELECT VI.idvisita FROM uhd_visita VI
WHERE VI.episodio = ep);
...

RETURN ok;

END FUNCTION;

We have two databases on two differenet Servers. We do take the unload of database1:table1 and then insert into database2:table2 as below

Server1
--------
unload to "data1.unl"
select * from database1:table2

Server2
--------
copy "data1.unl" to Server2

load from "data1.unl"
insert into database2:table2

Make sure that table1 and table2 are of same db structure.

i have change the function a little bit and now it will be like this:

CREATE FUNCTION "IxAdministrador-sec".historicos(ep VARCHAR)
RETURNING INTEGER;

DEFINE i1, i2, i3 INTEGER;
DEFINE d1 DECIMAL(16,2);
DEFINE b1 BOOLEAN;
DEFINE lvc1, lvc2, lvc3, lvc4, lvc5, lvc6, lvc7, lvc8, lvc9, lvc10 LVARCHAR;
DEFINE dt1, dt2, dt3, dt4, dt5 DATETIME YEAR TO SECOND;

DEFINE ok INTEGER;
LET ok = 0;

FOREACH SELECT idactividad, actividad, visita
INTO lvc1, i1, lvc2
FROM uhd_actividadesvisita AV
WHERE AV.visita IN (
SELECT VI.idvisita FROM uhd_visita VI
WHERE VI.episodio = ep)
INSERT INTO lyra_historicos:uhd_actividadesvisita
VALUES (lvc1, i1, lvc2);
LET ok = ok + 1;
END FOREACH;

RETURN ok;

END FUNCTION;

but the problem now is that it never goes inside the foreach. i have even try with the debugger but i was unlike. any idea about what is wrong??