Results 1 to 11 of 11
  1. #1
    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!!

  2. #2
    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

  3. #3
    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

  4. #4
    Join Date
    Aug 2006
    Posts
    6
    which program do you use??

  5. #5
    Join Date
    Aug 2006
    Location
    Riga, Latvia
    Posts
    17
    I'm using standard win32 SQL editor, produced by Informix and all works fine!
    Click image for larger version. 

Name:	sqledit.JPG 
Views:	39 
Size:	41.8 KB 
ID:	6295

  6. #6
    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?

  7. #7
    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?

  8. #8
    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?

  9. #9
    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;

  10. #10
    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.

  11. #11
    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??

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •