ADORDD Question

[ask]

Hi,

My question is about ADO and not exclusively about ADORDD . If i want to use a browse (e.g XBROWSE) i have to open my recordset with adUseClient because the browse must use the absoluteposition property in order to work . My problem is that if I open a table with too much records (e.g 500000 records) then ADO is talking too much time to open. Is there any other solution ? Can i use adUseServer for cursorlocation and browse still work (meaning that I can not use absoluteposion property) ?

Thanks in advance
A.S.K

[Rick Lipkin]

ADOrdd opens the recordset locally .. which is good. I would sugest you trim your SELECT statement to only include those fields in the browse you wish to view .. SELECT * from table can get UGLY if you have a lot of records and lots of fields .. consider SELECT field1,field2,field3 from table order by field1... and I would highly consider you stay away from a full table scan and include a where clause for only those records that meet a certain cryteria.

Rick Lipkin

[nageswaragunupudi]

I am working extensively with large databases on MsSql and Oracle with ADO on high speed LAN and slow speed WAN environments. The question of making browses with large data table is not so easy. Client side cursors ( used by ADORDD) just dont work (Unacceptable long waits till all the data is read from server to the client with very high net work traffic which is highly undesirable). Yes I am talking about tables with around million rows.

On MSSql I use serverside cursors. They work reasonably fine. But we can not use Absolute position and RecordCount. I make the skipper similar to DBF skipper. Use a precalculated count(*) for total records. I make my data reader class behave on the basis whether the cursor is client side or serverside

Oracle is difficult, because Oracle does not provide serverside scrollable cursors. I employ a trick of reading small chunks of rows into different RecordSets and transparently switch between the recordsets giving an illusion of a continous browse. ( I am told SQLRDD of xHarbour.Com works gracefully. Yet I failed to make it work on my Oracle server. The SQLRDD is made with OCI rather than ADO)

Anyway for browsing large tables, none to beat Advantage Server. But most clients insist on oracle or mssql.

But it is only our fraternity who have roots in DBF and clipper still stick to providing browses. Other software do not provide this luxury to the users. Also the present and future is for webbased three tier architecture and detatched recorsets. They present only very small porttions of the total data, with small grids moving page by page rather than the beautiful scrolling in our browses. May be we shd chnage our habits of user interfaces.

NageswaraRao
India

[ask]

Thank you both for your suggestions. I found a solution that might be useful . By making some modifications inside the ADORDD.prg I open the recordset with this syntax :
e.g ORACLE DATABASE
select rownum absoluteposition,a.* from (select * from <table> order by <order_by>) a

With this I get a field named absoluteposition with all my recordsets. I replaced the oRS:absoluteposition with oRS:Fields("absoluteposition"):value() and oRS:RecordCount() with a recordset that i open first
e.g ORACLE DATABASE
select count(*) from (select * from <table> order by <order_by>)

So by replacing the absoluteposition and recordcount i can browse records with server side cursor location .

Any thoughts about my reply would be appreciated

Sorry for my English .
Thank you
A.S.K

[nageswaragunupudi]

Still this is a clientside recordset. ROWNUM of Oracle gives numbers from 1 onwards for the rows returned. Same values as ADO absolute position. When we use ADO we dont need to use ROWNUM. (Note: Rownum helps only to know the order in which we read oringally after we sort or filter rows in ADO on clientside. That is the only benefit.)

If you are planning to use the above syntax for a serversde cursor, the problem with oracle is it is a forward only cursot. You can not navigate backwards. For a browse the cursor should be scrollable. (Possible with MSSQL)

We are now considering how to browse a table of say million or more rows. We cant use a select statment to return all the rows.

I shall describe the problem more clearly and possible solutions for discussion / comments in my next post.