Re: Dynamic Arrays

John Baker writes:- > In X-Informix-List-Id <newsgate.577>, Jacob Salomon of Informix Software, Inc. > gave some sample code for using a fixed array to store rowid's from a scroll > cursor. This would let users delete a row in the current list and not have > it redisplayed when they did a "Fetch Previous." > > The main problem with the code is that you are limited to selecting no > greater number of rows than the number declared for the size of the array > DEFINEd in the function. etc > > About five months ago, Jim Gordon of DHL Systems Inc., San Mateo, CA had a > message on here about a dynamic array handler that he wrote. Jim if you are > etc The example I gave was the version in the Tech Notes slightly extended to include facilities for page up/down functionality which is necessary to improve the visual speed of array shuffling on slower boxes. It didn't cover the ability to add, update or delete rows. But this can be achieved using programs that apply this technique. You need to consider a level of abstraction higher than the version in Tech notes - instead of selecting the details that you wish to display in the array select only the primary key. Then when you wish to display the next line you FETCH absolute/relative the row you next want and then do a singleton SELECT to pick up the details you require for the array using the primary key values returned by the fetch in the where clause. On delete you delete the actual row from the table. Remember that on going back down the array you will still get the primary key for this deleted row but when doing the select it will fail (This works when another user deletes the row as well). Update works the same way and you will always retreive the current values. Add is a special case. You can use add in this structure but the new primary key will not be in your list. This may be fine for your purposes. If not you have two options:- 1) First after add operations you must rerun the query to include the row in the list. (It may not appear if the added row does not match the query. This is something you may want to abort the add operation on anyway) 2) Instead of using the system generated temporary table build your own and add and delete from it as you work on the array. Each time you add or delete you should rerun the query on the temporary table and reposition yourself in it. The first option uses less space but may take longer to rerun the query as the original table must be searched. The second option takes a small amount of extra space but has quick refresh after add/delete as it is SELECT * FROM temp. You still have to deal with rows deleted by other users. The method described above has the advantage that you reduce the size of your temporary table to only the primary key values so allowing a bigger possible array size but at the cost of the extra database access to gain the detail info when required. This method also decreases user memory space requirements drastically over defining a fixed array space. Hope this helps, Cheers - Jim -------------------------------------------------------------------- Name: Jim Gordon Internet: jgordon@ssf-sys.DHL.COM Company: DHL Systems Inc Phone: (415) 358-5911 (Work) Address: 1700 S. Amphlett Blvd. (415) 882-9728 (Home) San Mateo, CA 94402 Fax: (415) 571-6429 --------------------------------------------------------------------