Re: get a unique phone # (10 digit) from a table. SLOW
If you want to make sure that your updates are atomic, you have to
ensure that the record(s) are locked...
DECLARE c_update CURSOR FOR
SELECT phone_num FROM call_list WHERE called = 1 FOR UPDATE
FOREACH c_update INTO num_800
UPDATE call_list
SET (user_name, date_called, called) = (username, TODAY, 0)
WHERE CURRENT OF c_update
-- Or WHERE phone_num = num_800, but it will be slower (just)
EXIT FOREACH
END FOREACH
-- Worry about the case where there are no customers to call?
If you have transactions on your database, the FOREACH loop needs to be
inside the body of a transaction. And to stop other users from being
locked out, the transaction should enclose just this loop.
Yours,
Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>
PS: In this case, it doesn't matter which version of Informix you are
using, but in general it does. Please quote that as well as the platform.
}Date: Thu, 7 Mar 1996 16:36:04 -0500 (EST)
}From: Basheer Ahmed <bahmed@cris.com>
}X-Informix-List-Id: <list.8943>
}
}Platform: Unixware 2.0,Digital 2 CPUS (Pentium 90) SE, I4GL
} 80+ Users.
}
}One of the menu option is GET A # To Call
}
} Code is not exact. Just the concepts are given
}
} called = 1 means this num can be used
} called = 0 means this num has been already used
}
} what this code does is:
}
} --> get a num from the table
} select phone_num from call_list
} into num_800
} where called = 1
}
} --> update the table so that other users dont get the same num
} update call_list
} set (user_name, date_called,called) = (username,TODAY,0)
} where phone_num = num_800
}
} Usually we have to load 12,000 to 20,000 nums into this table everyday
} we do daily: clean up this table, update statistics, bcheck all tables.
}
}We have about 80+ users:
} Problem 1: Sometimes takes more time to get a #
} problem 2: Sometimes two or more people get the same #