Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 1 to 10 of 31 total |
Inserting Record Efficiently |
Wed, May 16 2007 11:13 AM | Permanent Link |
Norman L. Kleinberg | I have an app I'm developing with an eye towards remote operation. Roughly, I show records
in a grid and then Add/Edit/Delete through buttons. Following someone's tip on these forums (sorry I don't remember who but great idea) I tie the grid to a CANNED TDBISAMQuery so it's easier to scroll around. Then, for example, when someone wants to edit a specific record I Open a LIVE TDBISAMQuery with a WHERE clause that pulls ONLY that record. Edit using databound controls as normal, when done I Post the live query and copy the new information to the CANNED query so that everything is synchronized. Seems to work fine. My problem is when I need to ADD a record. What I really want is an EMPTY live query; I can then use databound controls to add the record, post it and add it to the canned query. Problem is when I try to create the empty live query by basically using a WHERE clause that can't be satisfied, add the record and then Post, the record "disappears" from the live query because the new record doesn't satisfy the WHERE either; thus I can't add the record to the canned query. Since the table has an AutoInc key I can't just copy BEFORE I post since I need the key value. Does that make sense? OK, so what I thought I'd do is take advantage of a Modified Date field in the record which is stamped as part of the BeforePost event. If I create a Live Query with a WHERE of dModified >= ate with pDate set to Now I should pull NO (or one or two) records but the NEW record should stay in the recordset. I will try it but I was wondering if anyone could think of a simpler way to be able to just add the ONE record to the table while maintaining this scenario of updating the canned query to reduce network traffic. Is there a way to CREATE an empty live query such that an added record will "stick"? I suppose I could use ExecSQL but the table has 105 fields and I'm loathe to have to create the command (but will do it if that is a more efficient way of accomplishing my goal). I just am trying to get DBISAM/Delphi to do the grunt work. Thanks for reading this and any pointers or experiences would be appreciated. Norman |
Wed, May 16 2007 11:43 AM | Permanent Link |
Chris Erdal | Norman L. Kleinberg <nlk11021@yahoo.com> wrote in
news:0E654CAC-D00E-4656-AE3B-638683D07F22@news.elevatesoft.com: > I have an app I'm developing with an eye towards remote operation. > Roughly, I show records in a grid and then Add/Edit/Delete through > buttons. > > Following someone's tip on these forums (sorry I don't remember who > but great idea) I tie the grid to a CANNED TDBISAMQuery so it's easier > to scroll around. Then, for example, when someone wants to edit a > specific record I Open a LIVE TDBISAMQuery with a WHERE clause that > pulls ONLY that record. Edit using databound controls as normal, when > done I Post the live query and copy the new information to the CANNED > query so that everything is synchronized. Seems to work fine. My > problem is when I need to ADD a record. Norman, As it was me that gave you the idea, I feel I should help you out here Since my live Query (updQuery) is in a master-detail relationship with the Canned Query (tblMaster), if I do an updQuery.Insert it just opens onto a new empty record, and I go ahead as for an update. here's the Canned Query's onBeforePost event: ------------------------------8<------------------------------- if tblMaster.ResultIsLive then exit // let normal updatable query function take over else begin // use UpdQuery which is a live query on the main table in master-detail link updQuery.Open; case tblMaster.State of dsEdit: begin aMsg := 'modified'; updQuery.Edit; end; dsInsert: begin aMsg := 'inserted'; updQuery.Insert; end; else updQuery.Close; raise Exception.Create(SUpdateQueryNotIn); exit; end; begin for i := 0 to pred(updQuery.FieldCount) do begin if tblMaster.FieldByName(updQuery.Fields[i].FieldName).Value <> tblMaster.FieldByName(updQuery.Fields[i].FieldName).OldValue then updQuery.Fields[i].assign( tblMaster.FieldByName(updQuery.Fields[i].FieldName)); end; updQuery.Post; end; // Showmessage(IntToStr(updQuery.RowsAffected)+' lines '+aMsg); updQuery.Close; exit; ------------------------------8<------------------------------- -- Chris (XP-Pro + Delphi 7 Architect + DBISAM 4.25 build 4 + EDB 1.02 build 1) |
Wed, May 16 2007 12:02 PM | Permanent Link |
Norman L. Kleinberg | Chris:
So YOU'RE the culprit. Well, thanks, but I got absolutely NO sleep last night trying to figure this thing out. I actually went through the scenario you suggest. If the live query is for the DETAIL then I have an AUTOMATIC WHERE clause (key-field = parent-key-field) which will be satisfied by few if any existing records. Inserting a record and then assigning the parent key to the child then automatically fulfills the WHERE and I'm set. What I'm trying to do is extend this to a very simple case: there is NO master-detail, just a single table in which to Insert a record. Odd that the more complicated setup (master-detail) makes things simpler. I am going, nevertheless, to study your code since I'm sure I'll be able to pick up some useful tips. Thanks much for the original suggestion and the follow-up. Norman Chris Erdal <chris@No-Spam-erdal.net> wrote: Norman L. Kleinberg <nlk11021@yahoo.com> wrote in news:0E654CAC-D00E-4656-AE3B-638683D07F22@news.elevatesoft.com: Norman, -snip- As it was me that gave you the idea, I feel I should help you out here Since my live Query (updQuery) is in a master-detail relationship with the Canned Query (tblMaster), if I do an updQuery.Insert it just opens onto a new empty record, and I go ahead as for an update. here's the Canned Query's onBeforePost event: ------------------------------8<------------------------------- if tblMaster.ResultIsLive then exit // let normal updatable query function take over else begin // use UpdQuery which is a live query on the main table in master-detail link updQuery.Open; case tblMaster.State of dsEdit: begin aMsg := 'modified'; updQuery.Edit; end; dsInsert: begin aMsg := 'inserted'; updQuery.Insert; end; else updQuery.Close; raise Exception.Create(SUpdateQueryNotIn); exit; end; begin for i := 0 to pred(updQuery.FieldCount) do begin if tblMaster.FieldByName(updQuery.Fields[i].FieldName).Value <> tblMaster.FieldByName(updQuery.Fields[i].FieldName).OldValue then updQuery.Fields[i].assign( tblMaster.FieldByName(updQuery.Fields[i].FieldName)); end; updQuery.Post; end; // Showmessage(IntToStr(updQuery.RowsAffected)+' lines '+aMsg); updQuery.Close; exit; ------------------------------8<------------------------------- -- Chris (XP-Pro + Delphi 7 Architect + DBISAM 4.25 build 4 + EDB 1.02 build 1) |
Wed, May 16 2007 1:01 PM | Permanent Link |
Chris Erdal | Norman L. Kleinberg <nlk11021@yahoo.com> wrote in
news:6D093F6F-12B4-4040-9662-45C14763D83D@news.elevatesoft.com: > Chris: > > So YOU'RE the culprit. Well, thanks, but I got absolutely NO sleep > last night trying to figure this thing out. > > I actually went through the scenario you suggest. If the live query is > for the DETAIL then I have an AUTOMATIC WHERE clause (key-field = > parent-key-field) which will be satisfied by few if any existing > records. Inserting a record and then assigning the parent key to the > child then automatically fulfills the WHERE and I'm set. What I'm > trying to do is extend this to a very simple case: there is NO > master-detail, just a single table in which to Insert a record. Odd > that the more complicated setup (master-detail) makes things simpler. Norman, I confused you on the master-detail bit. I'm only talking about one REAL table here. In my scenario the main grid is filled from a Query (tblMaster) which may or may not be live, depending on the SQL script. On the same form I also have a blank Query (upQuery) which is to be used (in the event that the main query is not live) for inserts, updates and deletes (because I wanted to take advantage of Context Database Extensions to enforce RI). When I'm about to open updQuery, I fill its SQL automatically: procedure TDBISAMAdvStrGrdForm.updQueryBeforeOpen(DataSet: TDataSet); begin inherited; updQuery.SQL.Text := 'SELECT * FROM ' + UpdTableName + WhereClause (true); updQuery.DataSource := dsMaster; end; The WHERE clause is also created automatically: function TDBISAMAdvStrGrdForm.WhereClause: string; var i: Integer; aKeyField: string; aFldName: string; begin Result := ' WHERE '; SetDelimiter(';'); i := 1; repeat aKeyField := GetToken(PrimaryKeyFields, i); if aKeyField > '' then begin if i > 1 then Result := Result + 'AND '; Result := Result + aKeyField + ' = :' + aKeyField; end; until not NextToken(PrimaryKeyFields, i); end; (GetToken etc. come from HyperString, by "EFD Systems") This way I end up with updQuery as the "child" table with tblMaster as its "Master" table in a pseudo-Master-Detail setup, but they're both based on the same REAL database table and linked by its Primary key field. That's how I keep updQuery in sync with tblMaster's active row - whenever updQuery opens, it is locked on to the current row in tblMaster by this link. This means that, in view of the onBeforePost event for tblMaster, when I am about to post a new row in tblMaster (which will be lost as it's a CANNED query) I open updQuery onto an empty row in the REAL table (as the key doesn't yet exist in the REAL table on the disk), and immediately put it into the Insert state, transfer all the required field values to it, and Post it. I can then update the new tblMaster's current record key field(s) with the value(s) I find (if it's Autoinc, for example) and the Grid is already showing the inserted row without a refresh. Voilą! -- Chris (XP-Pro + Delphi 7 Architect + DBISAM 4.25 build 4 + EDB 1.02 build 1) |
Wed, May 16 2007 5:15 PM | Permanent Link |
Norman L. Kleinberg | Chris:
>Chris Erdal <chris@No-Spam-erdal.net> wrote: >-snip- >Norman, >-snip- >This way I end up with updQuery as the "child" table with tblMaster as >its "Master" table in a pseudo-Master-Detail setup, but they're both >based on the same REAL database table and linked by its Primary key >field. >That's how I keep updQuery in sync with tblMaster's active row - whenever >updQuery opens, it is locked on to the current row in tblMaster by this >link. Understood. I have no problem with editing an existing record. I know the key and my "auxiliary" query can just use it to select the proper record from the REAL table. >This means that, in view of the onBeforePost event for tblMaster, when I >am about to post a new row in tblMaster (which will be lost as it's a >CANNED query) I open updQuery onto an empty row in the REAL table (as the >key doesn't yet exist in the REAL table on the disk), and immediately put >it into the Insert state, transfer all the required field values to it, >and Post it. This is the crux of my misunderstanding. What is your WHERE clause for this? Is it aKeyField := "whatever the new key is"? If so, and the table has an AutoInc primary key, how do you know what this value will be? For example, say I have an AutoInc CustomerID in my table. User hits Insert: the canned query BeforePost event opens the Query on an empty row, which I presume is accomplished by a WHERE clause such as KeyField = "new Key Field". But how do you know what that "new Key Field" is if it's AutoInc? I guess I'm asking what your WHERE clause looks like when you are opening the Query on an empty row. If the table has an AutoInc key then you won't KNOW the new key value until you Post the record, I believe. >I can then update the new tblMaster's current record key field(s) with >the value(s) I find (if it's Autoinc, for example) and the Grid is >already showing the inserted row without a refresh. >Voilą! >-- >Chris >(XP-Pro + Delphi 7 Architect + DBISAM 4.25 build 4 + EDB 1.02 build 1) I of course have no doubt it works but I can't see how if the table has an AutoInc key. Again, my understanding is that I won't know the value of that key until after I post the Live query, at which point the record disappears (unless you have a WHERE clause I haven't envisioned). The MasterSource locking idea is great, but ONLY if you have an actual key to connect the parent and child query. Perhaps if you could write out your WHERE clause for an Insert I could figure it out. I realize it's asking a lot so I would understand if you couldn't, it's just this Insert thingie has me totally confused. Thanks. Norman |
Wed, May 16 2007 10:55 PM | Permanent Link |
"Robert" | "Norman L. Kleinberg" <nlk11021@yahoo.com> wrote in message news:0E654CAC-D00E-4656-AE3B-638683D07F22@news.elevatesoft.com... > synchronized. Seems to work fine. My problem is when I need to ADD a > record. What I really > want is an EMPTY live query; I can then use databound controls to add the > record, Why do you need an empty query? When you do an insert, your databound controls will be empty because the new record will be the current record. who cares if you have other records in the live query? Nobody will see them. Robert |
Thu, May 17 2007 3:02 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Norman
What about doing the select into a memory table, and in its afterpost event either do an update or an insert? Roy Lambert |
Thu, May 17 2007 10:25 AM | Permanent Link |
Norman L. Kleinberg | "Robert" <ngsemail2005withoutthis@yahoo.com.ar> wrote:
"Norman L. Kleinberg" <nlk11021@yahoo.com> wrote in message news:0E654CAC-D00E-4656-AE3B-638683D07F22@news.elevatesoft.com... > synchronized. Seems to work fine. My problem is when I need to ADD a > record. What I really > want is an EMPTY live query; I can then use databound controls to add the > record, Why do you need an empty query? When you do an insert, your databound controls will be empty because the new record will be the current record. who cares if you have other records in the live query? Nobody will see them. Robert --------------------------------------------------------------------------------------- Robert: I don't need it to be empty, you're correct. What I want, however, is to minimize network traffic so I need this Query, which will be Live, to have to fetch as few records as possible. I've experimented with my "trick" of using a WHERE clause of DateModified > Now and it DOES work, so all this is academic, anyway. Norman |
Thu, May 17 2007 10:38 AM | Permanent Link |
Norman L. Kleinberg | Roy Lambert <roy.lambert@skynet.co.uk> wrote:
Norman What about doing the select into a memory table, and in its afterpost event either do an update or an insert? Roy Lambert ------------------------------------ Roy: You are of course correct. What I ran into, however, was an interesting issue which is new to me but must be well known to more experienced coders. I have two issues: an AutoInc primary key which I NEED to know so I can copy to the canned query AND the fact that my table has 105 fields. There are a number of different ways to update a table, including yours; do an SQL Insert with an ExecSQL (I have to write the SQL Statement but can retrieve the AutoInc value), memory table followed by an automated record copy routine (can't get the AutoInc value but wouldn't have to write 105 field transfers), Table or Live Query with a Post (can get the AutoInc value but am trying to avoid this because my DataSet is large) or a "small" Live Query (don't need to write the 105 field names, minimize network traffic, but how to get the AutoInc value back). This actually raises another question I had which was, basically, "what's the difference from DBISAM's point of view"? I guess there are some differences in efficiency and locking, etc., but basically DBISAM has got to handle a "record copy" and an "SQL Insert", for example, the same in the background. I guess I've gotten off the main point. It's quite possible I'm overlooking something simple but, to me, I'm in a box with this. The only way I've found is to execute a Live query with "DateModified > Now" which returns a very small or empty result set (not much network traffic, I think), Insert and Post. Since the newly posted record satisfies the WHERE it stays in the Query and I can retrieve all the field values, INCLUDING the AutoInc. Thanks to all for taking the time to post. I will reread all of the messages and see if I can improve my knowledge of Delphi Database coding in general and DBISAM in particular. Norman |
Thu, May 17 2007 11:26 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Norman
OK lesson 1 SELECT * INTO "Memory\Fred" FROM BandA WHERE _BoxNo = -100000 Creates a new database in memory with the exact structure as the source. The problem is the AutoInc. With an existing record it will pick up the existing Autoinc and that will be preserved when you post. A new record will create its own, but you should be able to set it to null after the initial post. Lesson 2 (returning the data to the table) will follow if I ever figure it out Roy Lambert |
Page 1 of 4 | Next Page » | |
Jump to Page: 1 2 3 4 |
This web page was last updated on Saturday, April 27, 2024 at 08:52 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |