Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 1 to 10 of 10 total |
Design Strategy |
Thu, Oct 17 2013 2:26 PM | Permanent Link |
Owen | I am pretty new to DBISAM and Delphi. I am rewriting a delphi 2009 program that I don't have the source code. I just have the UI an database. I started my development utilizing DBISAM queries and creating clientdatasets. This was pretty easy as the clientdataset kept track of changes etc and would apply them back to the database. But I discovered that it does not work well for queries that are composed of unions. Much of my tables contain foreign keys and I need to translate these foreign keys for display.
I have been reading and searching and I don't have a good alternative. I am wondering how other people handle displaying grids an such with foreign keys in an update scenario. |
Thu, Oct 17 2013 2:57 PM | Permanent Link |
Jose Eduardo Helminsky HPro Informatica | Owen
<< I am pretty new to DBISAM and Delphi. I am rewriting a delphi 2009 program that I don't have the source code. I just have the UI an database. I started my development utilizing DBISAM queries and creating clientdatasets. This was pretty easy as the clientdataset kept track of changes etc and would apply them back to the database. But I discovered that it does not work well for queries that are composed of unions. Much of my tables contain foreign keys and I need to translate these foreign keys for display. I have been reading and searching and I don't have a good alternative. I am wondering how other people handle displaying grids an such with foreign keys in an update scenario. >> Instead of using queries, just use tables and use the OnCalcField event. In this event you must use FindKey (avoid SQL in this code) to find record pointer of foreign tables and assign the fields you need into the CalculatedField. It is really fast. The only problem with this is if you need to sort using the foreign fields, then the solution is use queries with JOIN. |
Thu, Oct 17 2013 5:01 PM | Permanent Link |
Jeff Cook Aspect Systems Ltd | Hi Owen
I agree with Jose's approach and it is the way I have done things for years. As Jose says, you run into problems with sorts of foreign keyed fields. BUT a major disadvantage of using Calculated fields is that the calculations are done on the client, so it can be slow in a Client Server application as the app has to go back to the server for each field calculated ... and it is amazing how often the OnCalcFields event gets fired! And when I say slow, I mean too slow to use in practice especially over internet connections. This is very evident on populating a grid The way I now prefer is to use a query with all the JOIN's and ORDER BY's which execute at the server end without having to shift stuff through the network. I update the query dataset in the UI with edits, inserts and deletes and use the OnBeforePost and OnBeforeDelete events of the query to update the actual tables. Cheers Jeff -- Jeff Cook Aspect Systems Ltd www.aspect.co.nz <Owen> wrote in message news:BB01A649-D67C-4675-82ED-51F5A1F8E3AF@news.elevatesoft.com... >I am pretty new to DBISAM and Delphi. I am rewriting a delphi 2009 program >that I don't have the source code. I just have the UI an database. I >started my development utilizing DBISAM queries and creating >clientdatasets. This was pretty easy as the clientdataset kept track of >changes etc and would apply them back to the database. But I discovered >that it does not work well for queries that are composed of unions. Much >of my tables contain foreign keys and I need to translate these foreign >keys for display. > > I have been reading and searching and I don't have a good alternative. I > am wondering how other people handle displaying grids an such with foreign > keys in an update scenario. > |
Thu, Oct 17 2013 6:04 PM | Permanent Link |
Owen | "Jeff Cook" wrote:
Hi Owen I agree with Jose's approach and it is the way I have done things for years. As Jose says, you run into problems with sorts of foreign keyed fields. BUT a major disadvantage of using Calculated fields is that the calculations are done on the client, so it can be slow in a Client Server application as the app has to go back to the server for each field calculated ... and it is amazing how often the OnCalcFields event gets fired! And when I say slow, I mean too slow to use in practice especially over internet connections. This is very evident on populating a grid The way I now prefer is to use a query with all the JOIN's and ORDER BY's which execute at the server end without having to shift stuff through the network. I update the query dataset in the UI with edits, inserts and deletes and use the OnBeforePost and OnBeforeDelete events of the query to update the actual tables. Cheers Jeff -- Jeff Cook Aspect Systems Ltd www.aspect.co.nz <Owen> wrote in message news:BB01A649-D67C-4675-82ED-51F5A1F8E3AF@news.elevatesoft.com... >I am pretty new to DBISAM and Delphi. I am rewriting a delphi 2009 program >that I don't have the source code. I just have the UI an database. I >started my development utilizing DBISAM queries and creating >clientdatasets. This was pretty easy as the clientdataset kept track of >changes etc and would apply them back to the database. But I discovered >that it does not work well for queries that are composed of unions. Much >of my tables contain foreign keys and I need to translate these foreign >keys for display. > > I have been reading and searching and I don't have a good alternative. I > am wondering how other people handle displaying grids an such with foreign > keys in an update scenario. > Jeff and Jose, Thanks much. Your comments are what I needed to get my brain wrapped around this. I think I can proceed. Regards, Owen |
Thu, Oct 17 2013 7:07 PM | Permanent Link |
Jeff Cook Aspect Systems Ltd | Hi
While not going back on my previous statement regarding calculated fields causing slow C/S access, I have just discovered that most of my problem was caused by OnDrawColumnCell - I am using code to run down the fields on each line of a grid to determine the colour of the background and text. e..g. if (TransGrid.DataSource.DataSet.FieldByName('TransDate').AsDateTime > d) or (RedDueDate and (TransGrid.DataSource.DataSet.FieldByName('DueDate').AsDateTime > d) and (TransGrid.DataSource.DataSet.FieldByName('TransType').AsString = 'Invoice')) then FutureDated := True; Which presumably does a trip to the server for each field on every cell. Bad news! Cheers Jeff -- Jeff Cook Aspect Systems Ltd www.aspect.co.nz "Jeff Cook" <jeffc@aspect.co.nz> wrote in message news:A33E7BA8-A503-4148-8FB5-D298BE3E29F8@news.elevatesoft.com... > Hi Owen > > I agree with Jose's approach and it is the way I have done things for > years. As Jose says, you run into problems with sorts of foreign keyed > fields. > > BUT a major disadvantage of using Calculated fields is that the > calculations are done on the client, so it can be slow in a Client Server > application as the app has to go back to the server for each field > calculated ... and it is amazing how often the OnCalcFields event gets > fired! And when I say slow, I mean too slow to use in practice especially > over internet connections. This is very evident on populating a grid > > The way I now prefer is to use a query with all the JOIN's and ORDER BY's > which execute at the server end without having to shift stuff through the > network. > > I update the query dataset in the UI with edits, inserts and deletes and > use the OnBeforePost and OnBeforeDelete events of the query to update the > actual tables. > > Cheers > > Jeff > > -- > Jeff Cook > Aspect Systems Ltd > www.aspect.co.nz > > <Owen> wrote in message > news:BB01A649-D67C-4675-82ED-51F5A1F8E3AF@news.elevatesoft.com... >>I am pretty new to DBISAM and Delphi. I am rewriting a delphi 2009 >>program that I don't have the source code. I just have the UI an >>database. I started my development utilizing DBISAM queries and creating >>clientdatasets. This was pretty easy as the clientdataset kept track of >>changes etc and would apply them back to the database. But I discovered >>that it does not work well for queries that are composed of unions. Much >>of my tables contain foreign keys and I need to translate these foreign >>keys for display. >> >> I have been reading and searching and I don't have a good alternative. I >> am wondering how other people handle displaying grids an such with >> foreign keys in an update scenario. >> > > |
Thu, Oct 24 2013 3:31 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Jeff,
<< Which presumably does a trip to the server for each field on every cell. >> Just to clarify - OnCalcFields will only do a trip to the server if you're actually making a call to the server, that is, doing a search/lookup, etc. If you're only referencing normal fields and not doing any searching, then it is very fast and stays on the client. Tim Young Elevate Software www.elevatesoft.com |
Mon, Nov 18 2013 5:21 PM | Permanent Link |
Owen | I have been off on another project, getting back to this...
When I asked about a strategy for updating a clientdataset that was generated from a query with a bunch of joins. Jeff wrote: > The way I now prefer is to use a query with all the JOIN's and ORDER BY's > which execute at the server end without having to shift stuff through the > network. > > I update the query dataset in the UI with edits, inserts and deletes and > use the OnBeforePost and OnBeforeDelete events of the query to update the > actual tables. I am still trying to figure this out. I am assuming that when the DBISAM manual talks about "Updating Tables and Query Result Sets", is it really talking about updates for clientdatasets? So, this is not the area that I should be looking at. And that a real sql update (and there would be inserts and deletes) would be something like: UPDATE SalesInfo SET TaxRate=0.0825 SQL Reference Page 260 WHERE (State='CA') The CDCollector project, the example that comes with the DBISAM product, as far as I can tells is pretty much dataset oriented for updates, so that might not be what I am looking for. Does anyone happen to have examples of code that does regular SQL updates. I don't have examples and I would prefer not to reinvent the wheel. |
Wed, Nov 20 2013 1:32 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Owen,
<< I am assuming that when the DBISAM manual talks about "Updating Tables and Query Result Sets", is it really talking about updates for clientdatasets? >> No, it is talking about updating live query result sets: http://www.elevatesoft.com/manual?action=viewtopic&id=dbisam4&product=delphi&version=7&topic=Live_Queries_and_Canned_Querie << Does anyone happen to have examples of code that does regular SQL updates. I don't have examples and I would prefer not to reinvent the wheel. >> You can see the syntax for an UPDATE statement on that page of the manual that you mentioned: http://www.elevatesoft.com/manual?action=viewtopic&id=dbisam4&product=delphi&version=7&topic=UPDATE_Statement If you have any other questions, please let me know. Tim Young Elevate Software www.elevatesoft.com |
Wed, Nov 20 2013 7:36 PM | Permanent Link |
Owen | Thanks Tim
I am still in a quandary and I apologize for my ignorance. From what I understand, the result sets are similar to clientdatasets in that you run a query and you get back a number of records. In a clientdataset they are stored in a memory, in a result set they are stored in a temp table. In any event you update the result set or clientdataset with fieldbyname verbiage and then in the end you post, which writes the data back to the table. I am working from clientdataset and my queries are full of joins because I did not want to do lookup tables. I would like to do my updates with SQL, insert delete and update statements rather than figure out and trust the clientdataset to post my updated data back to the table. But my problem is that I am basically ignorant in terms of database processing and all the mechanics. And, this is just not something the is discussed in any length on the internet or in books, at least that I can find. For example; If I find a record in my client dataset that I want to update, do I have to open a database and table, start a transaction, do an update, check for errors perform a commit or a rollback? I would like to get a hold of a project that does all this stuff so I don't have to reinvent the wheel. It would just save a lot of time and exploding brain cells. I do understand the individual statements, update, insert and delete. Maybe people don't write code that does this kind of thing I don't know. Maybe there is an easier way. If there is, I would like to hear it. Regards, Owen |
Thu, Nov 21 2013 2:49 PM | Permanent Link |
Owen | I think I pretty much found what I need, no further attention needed on this thread.
Thanks Owen |
This web page was last updated on Saturday, May 4, 2024 at 12:54 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |