ODBC - insert/updating two databases with one insert/update

Hello - I have the need to perform inserts and updates to tables in two separate databases, but the program code (using ODBC) currently only writes to one of the databases. What options do I have to implement this, short of changing all our program code to perform two updates where it currently performs only one? The inserts and updates only need to go one-way. Ideally, we'd use some sort of near-real-time replication implemented by the database itself, but that is apparently not an option with Informix 9.1.4. Is there some sort of "ODBC multiplexer" available? Or do you suggest implementing this using triggers and stored procedures? We're using Informix 9.1.4. thanks in advance, - chris -----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

On Wed, 03 Mar 1999 23:28:08 GMT, chrisw@ignitemedia.com wrote: >Hello - > >I have the need to perform inserts and updates to tables in two separate >databases, but the program code (using ODBC) currently only writes to one of >the databases. What options do I have to implement this, short of changing >all our program code to perform two updates where it currently performs only >one? The inserts and updates only need to go one-way. > >Ideally, we'd use some sort of near-real-time replication implemented by the >database itself, but that is apparently not an option with Informix 9.1.4. >Is there some sort of "ODBC multiplexer" available? Or do you suggest >implementing this using triggers and stored procedures? > >We're using Informix 9.1.4. I've done this. I used a SP on one machine running IDS to execute a SP on the other machine also running IDS, both inside a transaction. Both went through, or neither. You have to consider network latencies and network errors if you do this sort of thing. These days, I'd probably use a middleware layer to talk to the databases. Peter Wiley

Chris, Can you add triggers/stored procedures that perform the database updates on your behalf? That way your program code doesn't change. Hope this helps... Matt chrisw@ignitemedia.com wrote: > Hello - > > I have the need to perform inserts and updates to tables in two separate > databases, but the program code (using ODBC) currently only writes to one of > the databases. What options do I have to implement this, short of changing > all our program code to perform two updates where it currently performs only > one? The inserts and updates only need to go one-way. > > Ideally, we'd use some sort of near-real-time replication implemented by the > database itself, but that is apparently not an option with Informix 9.1.4. > Is there some sort of "ODBC multiplexer" available? Or do you suggest > implementing this using triggers and stored procedures? > > We're using Informix 9.1.4. > > thanks in advance, > > - chris > > -----------== Posted via Deja News, The Discussion Network ==---------- > http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

Hi Chris, Question: are both the databases on the same server. If that is the case then you can use fully qualified table names and that should work.Example insert into db2:username.tableName select * from db1:username.tableName If you have the databases on different machines, try creating a synonym and then use it as a regular table. Imran. =========================================== Imran Hussain MCP, MCSD imranh@imranweb.com FOR FREE Software http://www.imranweb.com/freesoft =========================================== chrisw@ignitemedia.com wrote: > Hello - > > I have the need to perform inserts and updates to tables in two separate > databases, but the program code (using ODBC) currently only writes to one of > the databases. What options do I have to implement this, short of changing > all our program code to perform two updates where it currently performs only > one? The inserts and updates only need to go one-way. > > Ideally, we'd use some sort of near-real-time replication implemented by the > database itself, but that is apparently not an option with Informix 9.1.4. > Is there some sort of "ODBC multiplexer" available? Or do you suggest > implementing this using triggers and stored procedures? > > We're using Informix 9.1.4. > > thanks in advance, > > - chris > > -----------== Posted via Deja News, The Discussion Network ==---------- > http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own --

You can create a view or synonym in the database you're accessing that point to the table in the other database. It would look like it was in the same database to your program. chrisw@ignitemedia.com wrote in message <7bkghr$11m$1@nnrp1.dejanews.com>... >Hello - > >I have the need to perform inserts and updates to tables in two separate >databases, but the program code (using ODBC) currently only writes to one of >the databases.