inserting record into TDbf-table lazarus / 16-Databases ---------------------------------------------------------------------- erkka · 2010-09-21 12:08 So it seems that with TDbf.Insert the new record will be always appended to the end of existing table. If I want to insert a record in between existing records, does that mean that I have to: 1. not to have my index field as type "ftAutoInc", but just plain integer, and programmatically handle autoincrement when appending, and reorganization when inserting 2. and then let indexing take care of displaying records in desired order ??? As I ask this I hope that someone jumps in with more elegant solution ---------------------------------------------------------------------- eny · 2010-09-21 13:42 Quote from: erkka on September 21, 2010, 02:08:52 pmIf I want to insert a record in between existing records You cannot do such a thing and there is no need to (physically). If you want to display your data in a certain order you need a value ('key') that determines the sort order. ---------------------------------------------------------------------- erkka · 2010-09-21 13:49 So my question needs to be re-formatted as: --- I have a Tdbf-database. It is indexed with field ID, which is of type ft_AutoInc. Now if I have records with ID's 0...6 and would like to insert a new record between, say, record 3 and 4, MyDbf.Insert will make it so that ID of inserted field is 7 (instead of making it 4 and re-numbering the rest.) So I guess that I have to change autoinc to ordinary integer and write my own insert-procedure which will take care of re-numbering the ID- fields. --- ah, where's the question? it reads: is there a more elegant or ready- made way to do this? EDIT: or is it necessary to change AutoInc to Integer? ie. can I programmatically re-number fields which are of type ft_AutoInc ? ---------------------------------------------------------------------- SteveF · 2010-09-21 14:34 Eny has it right:  inserting "between" existing records is not possible using an auto-inc field.  It will always append to the end. There's also a bit of database-logic error in your thinking:  from a "pure-RDBMS" point of view, a table should be thought of as just a bucket which holds data, with no guarantee that it will be maintained in any particular order.  If you want the data to be displayed in a specific order, then there must be a data field which contains the information by which to sort, and a query (or View) is used to both sort the data and function as a datasource for display, instead of showing the table directly. If you need to use an auto-inc column in order to uniquely identify your data, that's fine.  If the table must be sorted in a different order, you need an additional column which contains the data by which to properly sort.  Build a SQL query which sorts on this extra column, and use that as your data source. Steve ---------------------------------------------------------------------- garydale · 2011-07-21 21:10 I once dealt with a program that actually did insert records in order. This was 27 years ago. It was written in BASIC and used a number as the only key. The idiot who wrote the program would delete a record by giving the record to be deleted the highest key, sort the file then shrink it by one record. He used a shell sort so he obviously had some programming knowledge - just not any sense. Adding a record with an intermediate value key would be done by appending it to the end of the file and sorting. In both cases the sort was tortuously slow. Simply moving records up and down in the file was dramatically faster. Indexes are even better because inserting and deleting entries usually only involves accessing a few records, not the entire database. Moreover non-index methods are susceptible to data corruption during the sorting or sliding. Indexes only read (doesn't write) the original data so it can't be corrupted if the process fails. Finally, you can have multiple indexes while there is only a single physical order. The physical order is rarely relevant to anything. It reflects the order records are added unless the file is physically sorted. In that case, it is the field(s) the file was sorted on that are important. Building an index on them is faster and safer than sorting. ---------------------------------------------------------------------- eny · 2011-07-21 21:28 Quote from: garydale on July 21, 2011, 11:10:40 pmThis was 27 years ago.Almost as old as this topic ---------------------------------------------------------------------- garydale · 2011-07-21 22:21 Yes, but then things rarely disappear on the Internet. I came across the post through Google and felt that the topic hadn't been fully dealt with. ---------------------------------------------------------------------- eny · 2011-07-23 17:47 A topic where that last post is almost a year old by definition has been fully dealt with. ---------------------------------------------------------------------- BigChimp · 2011-07-24 07:26 Hi eny, Personally, I don't think so. I often use the forums to try to avoid asking questions that have already been answered. If somebody answered a question/clarified something, even a lot later, that still has value for me if I'm looking for help... ---------------------------------------------------------------------- eny · 2011-07-24 09:52 Quote from: BigChimp on July 24, 2011, 09:26:24 amPersonally, I don't think so. I often use the forums to try to avoid asking questions that have already been answered.Very good; so do I  QuoteIf somebody answered a question/clarified something, even a lot later, that still has value for me if I'm looking for help...Answering questions a lot later will not happen a lot. Most forum users, like me and hopefully yourself, are registered users that only read new messages via the very useful menu option 'Show unread'. This means that new questions will pop up immediately. And also all answers given to those questions. Eventually a question will be answered (or not) or it loses it's relevance. And if there's nothing more to contribute, it will not show up anymore in the list of unread topics. Adding reactions to such an old post will most likely not benefit the TS anymore (especially when it's a personal rant). If you feel a question is not answered in such a way that it helps you, then it's better to post a new question where you explain your particular problem in more detail,  with a reference to the original post. ---------------------------------------------------------------------- BigChimp · 2011-07-24 10:31 Hi Eny, Let's agree to disagree  I do use the Show unread messages. But when I have a problem with something, I do search the forums first; even old answers may help me. As for the rest of your post: I agree