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.  

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.

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 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

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.
>>
>
>

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

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.  

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

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

I think I pretty much found what I need, no further attention needed on this thread.
Thanks
Owen