Re: Updating a Remote Database?

} From: spitz@GANS2X.ana.med.uni-muenchen.de (Richard Spitz) } Subject: 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 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 | | } +----------------------------+-------------------------------------------+ Depending on the nature of your records you may not have to use such an elaborate strategy. We perform the same functions (add, update, delete) by specifying that the update record contains all the data that the original had. Then we simply attempt to delete every record that is in the update file. If it does not exist, the SQL returns a success code. If it does (did) exist, the SQL returns a success code. Then we insert the record and in this way perform adds and updates. There's a character at the beginning of each record which indicates if it is to be deleted. In this event, we do not perform the insert. We use 4GL to do this usually. The "temp table" method works best. We read each line of the fixed-length records we get (from an AS/400--yuk!) into a character array and break them out into program variables, which are then inserted as records into the database. Free associations from the database of my mind... (all goods worth price charged) __________________________________________________________________ | Clem Akins Standard Disclaimers Apply | |Reynolds Metals Co, Alloys Plant "Climb High, Cave Deep!" | | Muscle Shoals, Alabama USA cwakins@leia.alloys.rmc.com | |________________________________________________________________|