ADO SQL VISIBILITY

[dagiayunus]

Dear Sir(s)

I have one table in sql database. if USER1 is browsing ARTICLE
table and make some changes, how to to update/visible USER2 browse with the
same change?

Something like dbf. when we change records it update to every where.



Regards
Yunus.

[Enrico Maria Giordano]

Yunus,

As far as I know, there is no way other than redoing the query.

EMG

[AHF]

You can use:

CursorLocation := adUseServer
CursorType := adOpenDynamic

Pros:
Selects without where clause much faster because you dont load immediately all records
You'll see new adds deletes changes by others. You will always have the last info version.

Cons:
Navigation slower and server overload higher
Many Dbs do not supported so you might get it through the ODBC OLedb driver.
This means all navigation is transformed into queries by the driver itself delaying everything.
In Mysql you'll need a column to be used as recno
Servers that supported might be better.

Never tried adUseServer without a server supporting it.

[Rick Lipkin]

Dagia

You can put a few well placed oRs:ReQuery() to re-fresh each workstation .. Generally you can place a :ReQuery() between screens which should keep each workstation in sync. .. again, SQL workstation visibility is an art-form and takes a fairly savvy programming imagination to keep them in sync.

Rick Lipkin

[dagiayunus]

Dear EMG, AHF & RICK LIPKIN

Thanks for the valuable information.

Thanks & Regards

[nageswaragunupudi]

SQL workstation visibility is an art-form and takes a fairly savvy programming imagination to keep them in sync.

Yes.

[nageswaragunupudi]

Severside queries (RecordSet with adUseServer) are *mostly* unidirectional and single pass, used mainly for fast single pass processing. Even this is not generally desirable and may result in wrong results because we lose the benefit of snap-shot query benefits.

What we mostly use is RecordSet with adUseClient with either adLockOptimistic or adLockBatchOptimistic. All this is static information as at the time when we executed the query. The recordset does not get updated on its own unless we explicitly execute oRs:Requery() or in suitable cases oRs:ReSync().

With a view to keep our browse uptodate, we can not keep executing oRs:Requery() or oRs:Resync() continuously. What Mr Rick refers to is when to do this.

XBrowse and TDataRow do this to some extent. That is when a user attempts to edit a row, either by inline edit or by calling oBrw:Edit() or EditSource(), the row is resynced first and then offered to edit. In other words, the user sees the latest data before he starts editing.

[AHF]

Mr Rao,

I think like in ADORDD the main problem is with additions because deletions or changes are always catched when we try to update.

In ADORDD this is only a problem working without true locks because otherwise for ex. the order will be locked and no additions are possible during a WS edition or transaction like in a normal DBF execution.

To control if we need to refresh the recordset because of additions we are studying this possibility:

1) RDD area array element keeping datetime value of the last refresh of the recordset

2) A SQL table NEEDREFRESH with fields:

TABLE = TABLENAME
DATETIME = DATE TIME ADD NEW RECORD

3) Every time a new addition is made its registered (add or replaced) in NEEDREFRESH with datetime of addition and table name. This can be done directly from ADORDD code or through a trigger or procedure in the server. We have to decide.

4) Every time any replace, delete, addnew takes place the ADORDD first looks in NEEDREFRESH for the table

5) If the datetime in NEEDREFRESH table is newer than the RDD area array element with the datetime last refresh of our recordset we refresh it again resetting the RDD area array element.

One can install a procedure at the server to run at certain hours when no one its connected to clean up this table.

Its a trade off between integrity and speed.

This is only primary proposal study phase and at the end we might go other way.
We will start working it middle August.

Nevertheless I would like very much to have you opinion.

[nageswaragunupudi]

Mr Antonio

You wanted a quick method to know if other users added new records after current user opened the recordset.
This discussion is confined to direct tables (not queries) opened using adUseClient.

If the table has a primary-key and value of primary-key inserted is always greater than the previously inserted key ( autoincrement or similar) then:
if the result of "select max(primarykeycolumn) from thetable" is greater than the maximum value of primary key in our recordset (we can keep this info somewhere in memory) it means other users added some rows.
Then we issue oRs:Requery() and reposition our record pointer again at the previous position.

"select max(primarykey)" is always highly optimized and fast.

If the table does not have a primary key, then leave it. The programmer anyway will face lots of problems and learn by mistakes. Anyway for small tables we may compare COUNT(*) with oRs:RecordCount() and decide, though it is slow and not foolproof.

When opening a table it is easy for us to identify the primary key and also if it is auto-increment.

SQLSERVER specific: In case the table has auto-increment primary key, we can use "SELECT IDENT_CURRENT(‘tablename’)" to know the last primary key value inserted. This is faster even than Max(PrimaryKey).

Some programmers may use rowVersion or timestamp fields. It is possible to identify such fields while opening the table. In such cases comparing the max() values indicates activity by other users.

Next question is how frequently we need to keep checking for other users' inserts. We can't keep checking continuously.
If the reocrdset is ordered on the primary key, it may be enough to check when (i) DBGOBOTTOM() and (ii) when DBSKIP( +n ) hits EOF.
In other cases we may need to do it with every SKIP() but this may slowdown the skips. You may please test it on large tables and decide.

[nageswaragunupudi]

This is just for academic interest.
From version 2005, MS SQLSERVER introduced a notification feature.
Instead of client querying the server frequently for any changes in the status, a notification queue can be created and server notifies the clients with subscription whenever the change happens. Client retrieves the notifications with "RECEIVE * FROM <quename>". This reduces the round-trip overhead.

I myself never had any occasion to use this feature.

[AHF]

Mr Rao,

"select max(primarykey)" is always highly optimized and fast.

Adordd its working like that but I found at least with Mysql that its taking too long.
If you try to use it for instances like a reccount() the browses begin to be very slow on a WAN even with tables of 50.000 recs.

I agree that its a must to have a primaryey as unique autoinc value.

COUNT() its not reliable and we should forget it. ADORRD recno its based on a primarykey and it can happen that count returns 20 rows but select max() returns 35. So adordd can only use select max otherwise we could have Lastrec() returning 20 and recno 35!

Next question is how frequently we need to keep checking for other users' inserts. We can't keep checking continuously.
If the reocrdset is ordered on the primary key, it may be enough to check when (i) DBGOBOTTOM() and (ii) when DBSKIP( +n ) hits EOF.

I was thinking in checking it before any update, addnew, delete. But dbgobott its good idea as a refresh trigger although it might not be ordered by the primarykey.

To test it on every skip is unfeasible with select max() it simply takes too long.

Another option Im study is to have a TRIGGER for every table primarykey field used as recno.
Something like this:

CREATE TRIGGER cname ON cTable BEFORE INSERT ...

Where it saves in a table like:

TLASTREC
TABLENAME C 30
MAXKEY N 10 0

The MAX value of the primarykey.
This table will be the size of n tables in the database so pretty small always.
Querying it even with skip should be lightning fast.

Concerning MSSQL QUEUES I didnt never tried it but I would like a solution that would be the same for every SQL engine.

What do you think about this?

Thanks

[nageswaragunupudi]

Mr Antonio

I agree that COUNT(* or any field) is the slowest on every RDMS.
Except for small tables or otherwise optimize queries yielding small subsets, no body uses count().

Because primary-key is indexed max(primary-key) should be faster but not instantaneous like the query I suggested for sql-server. I go by your tests about the acceptability or otherwise of this approach from speed point of view.

Can you please try this query for reliability and speed?
SELECT `AUTO_INCREMENT` FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'DatabaseName' AND TABLE_NAME = 'TableName';

Better we do not interfere with triggers etc. Better to leave it to their database programmers.

[AHF]

Mr Rao,

Better we do not interfere with triggers etc. Better to leave it to their database programmers.

True I know we shouldnt do it.

Code: Select all

SELECT `AUTO_INCREMENT` FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'DatabaseName' AND TABLE_NAME = 'TableName';

This might be it! Ill keep you posted.
Is it supported by all engines ?

[nageswaragunupudi]

No
This is for MySql and I did not use it.
For MsSql I advised another query.
Let us first provide optimal performance for at least two popular servers. Let's keep exploring.

[AHF]

Mr Rao,

It works ok I need further testing.

How can I check the size of the schema table being queried ?
Is it there only the max autoinc of each table ?