Editing array of rows in i4gl

Hi, I'm building a i4gl application which willl be used to edit a series of rows with an INPUT ARRAY (and delete or add to the rows). My problem is that the rows are not necessarily unique (ie they can contain the same data in every field), so I am having trouble when the editing is finished and the array is copied back to the database. IE I can't use things like UPDATE xxx WHERE field1="zxc" AND field2="qqq", as this could point to multiple rows in the table. Can anyone point me to some way of easily provide an array editing function which does not rely on rows being the same and is relatively easy to program? One way I've thought of is if INPUT ARRAY could be set to directly edit the table rows, rather than going through the screen array, but I don't this is possible? Thanks for any suggestions! Wookie

If you are not using fragmented tables (i.e. huge datawarehouses) then you can use rowid's. A rowid uniquely identifies a row within a non-fragmented table. You can select the rowid like any other column of the table, e.g. select rowid,field1,field2 from xxx. You can use an integer to store rowid values in 4gl and use it to keep track of individual rows in your input array, a little bit complicated but doable. Another solution might be to simply delete all the rows in the table that correspond to the rows in your array and then simply insert all the array rows back. There are also other problems with input arrays: 1. The array will always be limited in size by the program array size which can eat up memory. 2. A long time can pass before the user updates the database table with the edited array contents. The user risks losing a large amount of work if anything were to happen (e.g. PC freeze, etc.) during editing. 3. Input arrays are, practically speaking, limited to the width of your screen which is often not enough. A different approach is using a list program. The user is presented with the a list of rows which he can then edit one by one in a normal input form. The Power-4gl toolkit provides just such a mechanism (see address below). ________________________________________________________________ John H. Frantz Power-4gl: Extending Informix-4gl john@rl.is http://www.rl.is/~john/pow4gl.html Wookie wrote: > > Hi, > > I'm building a i4gl application which willl be used to edit a series of rows > with an INPUT ARRAY (and delete or add to the rows). > > My problem is that the rows are not necessarily unique (ie they can contain the > same data in every field), so I am having trouble when the editing is finished > and the array is copied back to the database. IE I can't use things like > UPDATE xxx WHERE field1="zxc" AND field2="qqq", as this could point to > multiple rows in the table. > > Can anyone point me to some way of easily provide an array editing function > which does not rely on rows being the same and is relatively easy to program? > > One way I've thought of is if INPUT ARRAY could be set to directly edit > the table rows, rather than going through the screen array, but I don't > this is possible? > > Thanks for any suggestions! > > Wookie

John H. Frantz wrote: > > If you are not using fragmented tables (i.e. huge datawarehouses) then > you can use rowid's. A rowid uniquely identifies a row within a > non-fragmented table. You can select the rowid like any other column of > the table, e.g. select rowid,field1,field2 from xxx. You can use an > integer to store rowid values in 4gl and use it to keep track of > individual rows in your input array, a little bit complicated but > doable. > > Another solution might be to simply delete all the rows in the table > that correspond to the rows in your array and then simply insert all the > array rows back. Yes, this can work but may fall foul of more sophisticated constraints and privileges. > > There are also other problems with input arrays: > > 1. The array will always be limited in size by the program array size > which can eat up memory. If you are using a virtual memory system the unused parts of memory should not be allocated pages so there should be little overhead. > > 2. A long time can pass before the user updates the database table with > the edited array contents. The user risks losing a large amount of work > if anything were to happen (e.g. PC freeze, etc.) during editing. You can update after each row and commit when the user exits the screen or on user demand. > > 3. Input arrays are, practically speaking, limited to the width of your > screen which is often not enough. Yes, without lots of messing about or Nils Myklebusts's method of just using two screen lines per array row. > > A different approach is using a list program. The user is presented with > the a list of rows which he can then edit one by one in a normal input > form. The Power-4gl toolkit provides just such a mechanism (see address > below). > ________________________________________________________________ > John H. Frantz Power-4gl: Extending Informix-4gl > john@rl.is http://www.rl.is/~john/pow4gl.html > > Wookie wrote: > > > > Hi, > > > > I'm building a i4gl application which willl be used to edit a series of rows > > with an INPUT ARRAY (and delete or add to the rows). > > > > My problem is that the rows are not necessarily unique (ie they can contain the > > same data in every field), so I am having trouble when the editing is finished > > and the array is copied back to the database. IE I can't use things like > > UPDATE xxx WHERE field1="zxc" AND field2="qqq", as this could point to > > multiple rows in the table. > > > > Can anyone point me to some way of easily provide an array editing function > > which does not rely on rows being the same and is relatively easy to program? > > > > One way I've thought of is if INPUT ARRAY could be set to directly edit > > the table rows, rather than going through the screen array, but I don't > > this is possible? > > > > Thanks for any suggestions! > > > > Wookie -- Peter Lancashire Information Systems Specialist, Bayer plc Eastern Way, Bury St Edmunds, Suffolk, IP32 7AH, UK Tel: +44-1635-562258, Fax: +44-1635-562281 -- If all else fails, read the instructions and the release notes. Join Infuse, the UK Informix User Group at http://www.infuse.org.uk/

Wookie wrote: > I'm building a i4gl application which willl be used to edit a series of rows > with an INPUT ARRAY (and delete or add to the rows). > > My problem is that the rows are not necessarily unique (ie they can contain the > same data in every field), so I am having trouble when the editing is finished > and the array is copied back to the database. IE I can't use things like > UPDATE xxx WHERE field1="zxc" AND field2="qqq", as this could point to > multiple rows in the table. Well, I can't help thinking that having rows with no unique key is a bad design idea. You can use ROWID (if you're not using fragmentation), as someone suggested, but I'd go for adding some kind of unique identifier to the table definition. Never mind the INPUT ARRAY: if you can't even issue an UPDATE that uniquely identifies a certain row, then you are asking for trouble. June -- june_t@hotmail.com Grounded in Palo Alto, living on M&M's (plain)