Problem with RecordCount and TDbGrid, TTable and Oracle 7.3
Ruediger Hoppe
Hi all,
after two days of debugging I'm sure to have found the source of some big
troubles we have with TTable and Oracle. I need to be a little bit more
specific, but as I believe this is a real, but very hidden bug, please
read on.
The problems show and are reproducable in a simple, codeless application
using TDbGrid, TDBNavigator, TDatasource and TTable. We have reproduced
the error with LookupCombos, too.
The visual effects are, that the grid simply shows the same rows of a
table several times.
The environment to reproduce this effects is a little bit special, but
after recognizing the error not that special.
We use Delphi C/S 3.02 (german) with BDE 4.51 (new dbclient.dll) under
NT4.0 SP3.
The problems show with our Oracle 7.3 NT workgroup server, not with
Access, which we use as low end database. This is understandable, if you
see below where the error is located.
Normally, we access Oracle with SQLLinks, but using the ODBC driver gives
the same problems.
The problem shows if the grid has more space to display rows than the
table actually has rows.
And only with TTable. TQuery works ok.
I think using TQuery with SQL is preferable over TTable, but TTable
should work and I like its simplicity.
After consulting DejaNews and several other bug solving resources without
luck, we decided to dig in ourselves.
This meant debugging the VCL, and I will reference to VCL source if
necessary.
To say it simple, the immediate cause to our problems was in
TDataLink.GetRecordCount.
This function gets the recordcount from its attached dataset reading
DataSource.DataSet.FRecordCount.
Knowing that a dataset has a property named RecordCount, I thought I had
a little "programmer too lazy" glitch (I know those) and happily changed
code into DataSource.DataSet.RecordCount.
This broke everything. After just hacking it to work, I could see that
using Recordcount instead of FRecordcount would solve the problem, but
raise lots of others.
To make it short, and get it off my chest. I think that Delphi is a
wonderful language and the VCL is quite a nice piece of work. But the
implementation of the TDataset.Recordcount completely differing from self
set standards and the internal use of FRecordcount made me nervous.
TBDEDataset.Recordcount gives the correct value. In our case 5. But due
to the error, the internal FRecordcount, which gets it value from many
places, had a value of 10. And this could be changed just by resizing the
grid.
Now, I traced the real cause of the bug in
TBDEDataSet.InternalGotoBookmark and there
DbiSetToBookmark, where I am stuck now.
Somewhere while filling internal buffers, the TDataset tries to read the
records prior to the ones it has in buffer with GetPriorRecords and
GetPriorRecord.
It uses its buffer[0] and the there stored bookmark to set the table to
this bookmark position with TDataSet.SetCurrentRecord(0).
Exactly this fails in our environment, leaving the dataset at EOF.
Reading prior records works, of course, up until BOF, resulting in the
visible results of duplicate rows.
The InternalGotoBookmark procedure is simple, and adding a BookmarkValid
call actually results in a bad bookmark. But handling this (with Check)
confuses the TDataset a lot.
MS Access bookmarks are valid, so the problem does not show. But who
knows.
We used another day to set up our Oracle again, with special regards to
sort orders and charactersets, but this only gave more Oracle experience,
but does not solve the problem :-)
Does anyone have a solution to this problem?
With best regards and thanks in advance.
---
Rüdiger Hoppe
r...@edit.de
Nick Pavlina
We have seen this effect several times with Oracle tables. Indeed it
seems to be a buffering problem either in the VCL or BDE. We haven't
traced it as deeply as you have, but have found a few workarounds:
1. Frequent Refresh, especially afterPost, but even afterScroll (D3) is
necessary sometimes.
2. IndexFieldNames property set to a unique key; seems to get confused
when ordering by non-unique key values, and this can cause update
problems.
3. If IndexFieldNames specifies a long concatenated key (~64 bytes), use
IndexName instead.
We don't see these problems with Interbase using the same code.
There are also problems with parameterized queries in Oracle.
Ruediger Hoppe wrote:
>
> Hi all,
>
> after two days of debugging I'm sure to have found the source of some big
> troubles we have with TTable and Oracle. I need to be a little bit more
> specific, but as I believe this is a real, but very hidden bug, please
> read on.
>
> The problems show and are reproducable in a simple, codeless application
> using TDbGrid, TDBNavigator, TDatasource and TTable. We have reproduced
> the error with LookupCombos, too.
>
> The visual effects are, that the grid simply shows the same rows of a
> table several times.
>
{SNIP}
Scott Frolich [Borland]
Ruediger Hoppe wrote:
>
> Hi all,
>
> after two days of debugging I'm sure to have found the source of some big
> troubles we have with TTable and Oracle. I need to be a little bit more
> specific, but as I believe this is a real, but very hidden bug, please
> read on.
>
> The problems show and are reproducable in a simple, codeless application
> using TDbGrid, TDBNavigator, TDatasource and TTable. We have reproduced
> the error with LookupCombos, too.
>
> The visual effects are, that the grid simply shows the same rows of a
> table several times.
>
What is the error??
If you are doing a record count on Oracle, if you do not want to use a
select COUNT(*) query then you will need to fetchall the records via
TTable.FetchAll before calling TTable.RecordCount. When using a SQL
database, getting a record count should be avoided because a SQL
database was not designed to handle this.
Scott
--
BDE Support:
http://www.borland.com/devsupport/bde
Delphi Support:
http://www.borland.com/devsupport/delphi
Common Delphi and BDE Questions and Answers:
http://www.borland.com/devsupport/delphi/qanda/
Ruediger Hoppe
In article <353FBD...@corp.borland.com>, sfro...@corp.borland.com
says...
> Hello,
> What is the error??
>
> If you are doing a record count on Oracle, if you do not want to use a
> select COUNT(*) query then you will need to fetchall the records via
> TTable.FetchAll before calling TTable.RecordCount. When using a SQL
> database, getting a record count should be avoided because a SQL
> database was not designed to handle this.
>
> Scott
> --
> BDE Support:
> http://www.borland.com/devsupport/bde
> Delphi Support:
> http://www.borland.com/devsupport/delphi
> Common Delphi and BDE Questions and Answers:
> http://www.borland.com/devsupport/delphi/qanda/
>
>
as I said in my ealier posting, I can reproduce the error with a CODELESS
application. Just connect a DbGrid to a datasource with TTable and you
will get the error.
Its not me that needs the FRecordcount, it's the grid.
On the other hand, TBDEDataset.RecordCount works perfectly ok, just the
TDataset does not use it in this case, it uses FRecordcount.
The problem showed up in a 80000+ lines application, and I'm very happy I
was able to reproduce it that easy.
Please read my posting again.
thanks
---
Rüdiger Hoppe
r...@edit.de
wwr...@sierra.net
As you may already know, the RecordCount property returned by data servers is
intended to indicate the number of records affected by a SQL Command that
does not return a record set (INSERT, UPDATE, DELETE). The ODBC call
SQLRowCount is not universally supported by all drivers to return a row count
for a SELECT call, and those that do are not necessarily accurate for more
than a few records. One workaround is to send the data cursor to the end of
the result set before querying the recordcount--if the driver supports row
counts for SELECT calls then the returned count should be accurate. The
safest way to get the row count is a SELECT COUNT call.
I know this in not helpful answer, but using row count on a SELECT statement
has been a problem in the past, and I think bug you've found is another
instance of this problem.
Bill Reynolds
In article <MPG.fa96add3...@forums.borland.com>,
-----== Posted via Deja News, The Leader in Internet Discussion ==-----
http://www.dejanews.com/ Now offering spam-free web-based newsreading
Scott Frolich [Borland]
in your earlier posting you said: "The visual effects are, that the
grid simply shows the same rows of a table several times." This sounds
like you do not have a UNIQUE INDEX or PRIMMARY KEY on the table. If
not, data aware controls will not behave correctly.