Re: Problems with getting the next number

->Date: Tue, 24 Aug 93 22:19:25 CDT ->From: "cheryl@redriverad-emh1.army.mil" <cheryl@rrad05.army.mil> ->To: informix-list@RMY.EMORY.EDU ->Cc: cheryl@REDRIVERAD-EMH1.ARMY.MIL ->Subject: Problems with getting the next number -> ->TO Bob at EZ Travel.... -> ->Yes that has been a problem, but as I explained earlier, we have had problems ->with the type of work we are doing with serial numbers. -> ->We have tried several ways of doing this. In one database we used time and ->the system was so heavily used that we created a unique number, add time ->to it and then wrote the record making it an character field instead. ->In another instance we do not read the next nbr until we read, increment ->then write at the point of the escape with error routines to make sure that ->the write occurred. -> ->Guess when I write back to the list, I just show my ignorance, but some of ->the things we are doing, I believe, require that we have a next number table. ->These are controled by permissions, and groups that can only access certain ->numbers, etc. -> ->I do not see how a serial number could do the thing with ->"E12345" + 4_digit_julian + sequential_number as a character field. [**] -> ->This is only 1 of the ways we are using next number tables. -> ->Cheryl Hello, Cheryl, I have been trying to mail to you direct; no success so far, but I'll keep trying different paths. Anyhow, here is info via the Informix-list at Emory: One of our applications needed to generate unique DOS format file names. We accomplished something similar to your needs (marked [**]) this way: DEFINE file_id INTEGER, file_name CHAR(12) LET file_id = get_next_id () { see below } LET file_name = "r", file_id USING "&&&&&&", "a.ext" This produces file_names of the form "r000123a.ext" The 'get_next_id' function was pretty complex, to accomplish synchronization across multiple remote sites having replicated databases, but using the 'file_id' table in the master DB for number assignment. However, the part that applies here is: 1. Insert a value of 0 into the file_id table, which looks like: CREATE TABLE file_id ( number SERIAL );2. Use SQLCA.SQLERRD[2] (in 4GL; or sqlca.sqlerrd[1] for ESQL/C) to get the value assigned to the serial column. 3. Delete all rows less than the new value. (I.e., keep this special table containing only one row.) You would need a separate one-column/one-row table for each SERIAL that you want to manage in this way. A separate discussion in this forum a while ago dealt with resetting SERIAL values. I saved a copy of that fairly lengthy exchange; if you need that info, let me know. Regards, Alan ___________________________ ______________________| R. Alan Popiel |__________________________ \\ Internet: | Martin Marietta, Tech Ops | / \\ alan@den.mmc.com | P.O. Box 179, M/S 5422 | Std disclaimers apply. / )Voice: | Denver, CO 80201-0179 USA | ( / 303-977-9998 |___________________________| (But you knew that!) \\ /________________________) (____________________________\\