Re: Updating a database w/pro *C

In article <CJIFts.KM6_at_uk.ac.brookes>, p0070621_at_oxford-brookes.ac.uk (Tommy Wareing) writes:
|> Andrew Ryan (genanr_at_amiserv.xnet.com) wrote:
|> > Is the a quick way to do a update/append rows in a table using host
|> > arrays in pro*c. I would like it to change data if the key is already in
|> > the table and add a new row if the key is not in the table. From look at
|> > the book it looks like I can do either an insert or and update but it looks
|> > like I cannot make it append if the data was not found other than doing it
|> > in two steps. Which I could do, but would rather to it in one step.
 

|> > Andy
|>
|> I think what you're currently doing is:
|> SELECT data FROM table;
|> if (NOT_FOUND) {
|> INSERT new_data INTO table;
|> } else {

 UPDATE table SET columns=new_data;
|> }
|>
|> The alternative is, assuming that you have a unique index on the table:
|> INSERT new_data INTO table;
|> if (DUPLICATE_VALUE_IN_INDEX) {
|> SELECT data FROM table;
|> UPDATE table SET columns=new_data;
|> }
|>
|> or something like that...
|>
|> --
|> _________________________ _______________________________
|> / Tommy Wareing \ / 'Happy, happy. Joy, joy!' \
|> | p0070621_at_brookes.ac.uk X said Ren & Stimpy |
|> \ 0865-483389 / \ (Supplied by Simone, not me!) /
|> ~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

My understanding of the original question is whether there is a way to get this insert if not there/update otherwise functionality with array processing. The answer is NO, sort of.

One thing you can do is to insert your array. Then look at the value of sqlerrd(3) (the third element of the array) for the number of rows inserted. If this equals the size of your array, you're done. if not, extract the offending row into an update array and try again. Iterate over the rows that insert until they are done. Then use the update array to handle the rows that failed to insert.

This may be faster than doing each row individually.

-- 
Derek Fields
(derek_at_gamekeeper.bellcore.com)