Re: Updating a Remote Database?

Salvatore Saieva (saieva@dpg.rnb.com) wrote: : My need is to update a remote database each day. I would like to export : the data from my local database to ASCII flat files using the SQL unload : statement and then make it available for downloading (using Zmodem, : Kermit, or some other modem protocol) by the remote site. : At the opposite end a script will run which will use a SQL load statement : to load the data into the remote database. My problem is that some of the : records that are exported from my local database will update existing records : in the remote database. The SQL load command only allows an insert clause : for records being loaded from a flat file. Sal, do it the following way: 1) create a temporary table that has the same structure as the target table. 2) load the ASCII file into that temp table 3) With an ESQL/C program, open a cursor on that temp table that selects all records. 4) For each record, try an INSERT into the target table. If the INSERT works (sqlca.sqlcode == 0) then all is well. If it fails with sqlca.sqlcode == -239 (could not insert new row - duplicate value in a UNIQUE INDEX column) that means that the row is already there and you have to do an UPDATE of that row with the values from the temp table. Of course, you have to have an unique index on your primary key, otherwise this approach won't work. We have been using this approach successfully for several applications. We haven't been able to find a way to do this in pure SQL, since there are no error handling capabilities. Hope this helps, Richard -- +----------------------------+-------------------------------------------+ | Dr. Richard Spitz | INTERNET: spitz@ana.med.uni-muenchen.de | | EDV-Gruppe Anaesthesie | Tel : +49-89-7095-3421 | | Klinikum Grosshadern | FAX : +49-89-7095-8886 | | 81366 Munich, Germany | | +----------------------------+-------------------------------------------+