If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Informix > Select with tables of two different db

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Aug 2006
Posts: 6
Select with tables of two different db

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!!
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Aug 2006
Location: Riga, Latvia
Posts: 17
Try this one:


Code:
INSERT INTO database01:TableA(id, field01)
SELECT * FROM database02:TableB
WHERE database01:TableA.id=database02:TableB.id
__________________
www.database.lv
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Aug 2006
Posts: 6
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
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Aug 2006
Posts: 6
which program do you use??
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Aug 2006
Location: Riga, Latvia
Posts: 17
I'm using standard win32 SQL editor, produced by Informix and all works fine!
Select with tables of two different db-sqledit.jpg
__________________
www.database.lv
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Aug 2006
Posts: 6
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?
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Aug 2006
Location: Riga, Latvia
Posts: 17
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?
__________________
www.database.lv
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Jan 2006
Posts: 38
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?
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
Join Date: Aug 2006
Posts: 6
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;
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
Join Date: Aug 2004
Posts: 7
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.
Reply With Quote
  #11 (permalink)  
Old
Registered User
 
Join Date: Aug 2006
Posts: 6
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??
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On