Serverside cursor with editable Dataset

h.hasenack · Post by **h.hasenack** » Thu 02 Apr 2009 07:45

I have managed to change my app to use TOraTable, but a now new problem arises: when opening a DB with 165K objects (which is quite small in our world) the app runs out of memory, and it takes forever to open the DB.

So I decided to try using ScrollableCursor=True.

Which is nice, because the dataset opens fairly fast, en memory footprint isn't insane anymore. But hey, now I cant edit the data anymore.

So what I basically need is a TTable alike component (or combination of components) that allows editing but does not cache all the data on the client. How can I accomplish this? (example?)

Regards - hans

Plash · Post by **Plash** » Fri 03 Apr 2009 07:30

The dataset with ScrollableCursor does not fetch all data to the client side. If you change a record, and scroll the dataset, next block of recods is fetched, and previous block that contains changed record is freed. When you scroll back, the record you have changed will be fetched again from server cursor, and there is no guarantee that the record will contain your changes.

So we have disabled modification of such dataset.

You can do the editing in a separate window with a separate TOraTable, than call Refresh for the main dataset.

h.hasenack · Post by **h.hasenack** » Fri 03 Apr 2009 09:28

the record you have changed will be fetched again from server cursor, and there is no guarantee that the record will contain your changes.

I understand. But when the record is posted and the query with the scrollable cursor is afterward 'refreshed' this should work fine. I think.

I desperately need some means to navigate through a oracle database table (not neccesarily a query/view) without caching the entire table on the client. Maybe even for pre-oracle 9 instances.

but how do I implement this? (It's hard to believe I'm the only one who needs this.)

1) I need access to DB tables, with VCL controls allowing editing (so TDataset not marked as readonly) using either Query or Oratable access
2) I dont mind having to write updatesql to implement the update of the table, but I defenitely dont want to cache the entire dataset on de client.

Plash · Post by **Plash** » Mon 06 Apr 2009 07:58

Millions of records are useless for a user. Maybe you should limit the number of records returned by your application. Use SQL like the following:

SELECT * FROM MyTable
WHERE rownum <= 500

You can also provide a feature of entering filter in your application.

h.hasenack · Post by **h.hasenack** » Mon 06 Apr 2009 14:46

Millions of records are useless for a user. Maybe you should limit the number of records returned by your application.

I agree. And I do net present the data to the user as 'milions of records'

The elegance of TTable (with paradox) and other table equivalents is that it actually only is a one record buffer with cursor support, making you look at no more than 1 record at a time (of not using grids, I know, I know).

This makes the use of SQL statements completely unnecessary, which is/was kind-of the idea behind delphi RAD apps.

So what I baslically need -as stated before- is a
* non-readonly,
* non-cached (at least not the entire table/result set),
* First/Last/Next/Prior/Locate navigatable interface to a table (query).

Thus all methods of TDataset implemented, and not caching ALL data on the client.

Rewriting my app to use SQL statements everywhere instead of ttable derivatives with findkey/setrange etcetara would take 10fold more time than allowed and make ODAC defninitely not the right option for us.

Regards -Hans

Plash · Post by **Plash** » Tue 07 Apr 2009 07:41

ODAC does not have this feature.

You can set the FilterSQL property of TOraTable instead of using Locate. When you set FilterSQL to a value like 'id=10', this value is added to the WHERE clause, and TOraTable is reopened. The table will contain one record, and you can edit this record.

h.hasenack · Post by **h.hasenack** » Tue 07 Apr 2009 07:53

ODAC does not have this feature.

Boohoo

Code: Select all

You can set the FilterSQL property of TOraTable instead of using Locate. When you set FilterSQL to a value like 'id=10', this value is added to the WHERE clause, and TOraTable is reopened. The table will contain one record, and you can edit this record.

Yes. But navigation with a standard TDBNavigator would be impossible. I'll have to rethink the way my app works if I want to use ODAC (or oracle for that matter)

Regards - Hans