TIBDataset - RecordCount
Adalberto Baldini
I have 2 TIBDataset in master detail relation. When master record moves
the Detail.Dataset returns as many records as they fit in grid that use
it. If I do Dataset.Last it get right number of records.
As it has not any sense, I tried it on a single Dataset attached to
another grid. I debug and I get this :
1) I connect dataset in FormCreate event and I get RecordCount = 1
2) I stop in FormActivate events and I get RecordCount = 32, after I dit
Dataset.Last and I get RecordCount = 42 (the right value), displayed
grid fit only 13 rows.
As RecordCount is a base element for grid producer I need some solution,
or I need to perform a Last and First command when I open Dataset ?
Thanks.
Adalberto Baldini
Bill Todd
fetched from the database. The IBDataSet has no way to know the number
of rows returned by the query because InterBase does not provide this
information. Call the FetchAll method to cause the IBDataSet to fetch
all of the rows returned by the query then check RecordCount.
--
Bill Todd (TeamB)
Aleksey Timohin
> IBExpr 11,11
> As RecordCount is a base element for grid producer I need some solution,
> or I need to perform a Last and First command when I open Dataset ?
FetchAll method fetches all records from database.
Adalberto Baldini
for you ?
thanks
Adalberto
---------------------
I don't know much about IBDataset's record management, but with many
back-ends its possible to have a live view without fetching all the
records.
For instance with the Delphi TQuery, it has a RequestLive property that
allows it to not fetch all the records, but still smartly maintain
things such as record number and record count. Only non-live views
would require that all records be fetched. I think you should further
investigate the limitations of IB Express. If it indeed does require
that you fetch all records, you should change your design so that only a
smaller subset is returned.
Jeff Overcash (TeamB)
Your vendor is talking about DBs like paradox only. Most (all?) SQL client
server database engines can not return a record count without fetching all the
records into memory which they do not do for performance reasons. It is
impossible to get a record count from InterBase without counting them on the
client side or doing a separate count(*) sql statement.
--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
And so I patrol in the valley of the shadow of the tricolor
I must fear evil. For I am but mortal and mortals can only die.
Asking questions, pleading answers from the nameless
faceless watchers that stalk the carpeted corridors of Whitehall.
(Fish)
Bill Todd
> I get this note from My 3rd part supplier, does it mean anything
> usefull for you ?
Useful in what regard? It explains that the BDE works differently than
IBX (which I already knew). In addition, that explanation of how the
BDE works applies to Paradox and dBase tables. If you use that
technique with the BDE and SQL Server it will work but the performance
will be poor. Perhaps it would help if you explain what problem you are
trying to solve. Why do you need to know the record count?n
--
Bill Todd (TeamB)
adalberto baldini
Perhaps it would help if you explain what problem you are
> trying to solve. Why do you need to know the record count?n
>
IBDataset, grid showed records and slider button of vertical scroll bar
was sized of same height of data area, it occupied completely vertical
scroll, and it was impossible to move, and also impossible to
understand there were more records to scroll.
I checked problem on other grids,displaying more lines, and I saw that
button size was different, but slide was not ok; if I went to last
record, cursor sized ok and slider worked properly.
( I seems that TDBGrid has same problem.)
I asked w2w newsgroup and Roy Woll informed that button is calculated on
Recordcount and problem might be from Interbase.
I checked and really RecordCount was not usefull, so I tried
DB.Open, DB.last, DB.first and it was ok.
After discussed in this newsgrp I got suggestion to use FetchAll.
Question ?
1) there is another solution to it (consider ever commit and reopen DB)
2) goto last and then goto first waste less resources than Fetchall or
are they are equal
Thanks
Adalberto
Bill Todd
> Question ?
> 1) there is another solution to it (consider ever commit and reopen
No. As I said in my earlier post, InterBase does not provide the number
of rows returned by a select.
> DB) 2) goto last and then goto first waste less resources than
> Fetchall or are they are equal
They are equal. Both will cause all of the records to be read from the
server and cached in local memory.
--
Bill Todd (TeamB)
Wayne Niddery [TeamB]
>
> After discussed in this newsgrp I got suggestion to use FetchAll.
If there are a lot of records then this will take time up front (versus a
little at a time as the user scrolls). Of course of the user drags the
scrollbacr to the bottom, that will also cause a fetch-all anyway.
If you really want the record count up front, then execute a separate
query - identical to the one you are executing but only "select count(*)
from...". In a multi-user system this record count may become wrong at any
moment after you get it since other users might add or delete records from
that table.
--
Wayne Niddery - Winwright, Inc (www.winwright.ca)
"The two most abundant elements in the universe are hydrogen and
stupidity." - Harlan Ellison
Adalberto Baldini
> Your vendor is talking about DBs like paradox only. ..
Here my Vendor Answer. It becames a guru discussion and I only can hope
that your discussion could arise a solution for my application.
Thanks
Adalberto
-------- Original Message --------
Subject: Re: WDBGrid slider size vertical scroll
Date: Mon, 9 Jul 2007 10:49:49 -0700
From: Roy Woll <sup...@woll2woll.com>
Organization: Woll2Woll Software
Newsgroups: woll2woll.customer.uploads
References: <hQIP9M8tHHA.356@U15173543> <CYb9J0ZuHHA.3628@U15173543>
<jVuyzlcuHHA.3628@U15173543> <UeeDs8quHHA.360@U15173543>
<Vd6XWLzuHHA.972@U15173543> <W2vGq26uHHA.360@U15173543>
<IgIaxnUvHHA.360@U15173543> <Zy$6lvYvHHA.356@U15173543>
<KJOrWRiwHHA.972@U15173543>
Your newgroup answer seems to show a lack of knowledge about Delphi's
database implementation in its native components as this is not just a
Paradox only issue. I'm a little baffled at their comments. RequestLive is
a property of TQuery and works with SQL Server, Interbase, Oracle, and will
return a live view of a dataset without loading all the records.
RequestLive is only a request so the back-end may deny the request if it
cannot determine the record count and record position.
Here is the delphi documentation.
RequestLive is a request that a SELECT query returns an updatable (or live)
or read-only result set from the database back-end. A false value for
RequestLive (the default) causes the result set to always be read-only. No
request is made to the database back-end to return an updatable result set.
A true value for RequestLive is a request to database back-end to return an
updatable result set. An updatable result set can be made available to the
application's user for direct data editing through visual data controls.
A true value for RequestLive is a request for an updatable result set. It
does not guarantee that the database back-end will return an updatable
result set. See the documentation for the specific database system used for
the criteria needed for a live query result set. If the database cannot
return an updatable result set, a read-only result set is returned instead.
This is done automatically and usually without error. Some database systems
may raise an exception on requests for live result sets that cannot be
fulfilled. Check the success of the request by inspecting the dataset
component's CanModify property.
If the SQL statement used in a TQuery is a general, multi-row SELECT
statement, RequestLive can be used with either a true or a false value. For
all other SQL statements issued from the TQuery, RequestLive should only be
set to false. These other statements include noncursor single-row SELECT
statements (more commonly found in stored procedure programming), noncursor
Data Manipulation Language (DML) statements like UPDATE or DELETE, and Data
Definition Language (DDL) statements like CREATE TABLE and DROP INDEX.
Note: All multi-table queries return read-only result sets. Other
conditions
may cause a query to return a read-only result set. For local tables (such
as dBASE and Paradox), see the local SQL help file for rules governing live
result sets (updatable queries). For other table types (such as InterBase,
Oracle, and Sybase), consult the documentation for the specific database
system.
Note: Some SQL database systems require strict case-sensitivity for
names of
metadata objects in SQL statements. These SQL databases typically have
problems with the way metadata names are passed by the BDE in live queries
and an exception is raised. A workaround that helps in most cases is to
enclose the metadata object references (like table and column names) in
quotation marks.
-Roy
Wayne Niddery [TeamB]
>
> Here my Vendor Answer. It becames a guru discussion and I only can
> hope that your discussion could arise a solution for my application.
>
>
> Your newgroup answer seems to show a lack of knowledge about Delphi's
> database implementation in its native components as this is not just a
> Paradox only issue.
Adalberto, nowhere in Roy's response does he mention anything about
*returning* the record count. His description of RequestLive is correct, but
has nothing to do with providing the record count to the application, but
only whether it can return an updateable dataset.
As far as how the record count has to be returned to the application for any
SQL database server, Jeff's reply is absolutely accurate - to get a record
count, an application must either manually count the rows returned which *by
definition* would require all rows to be fetched, or else it must execute an
additional query (select count(*) ...) against the database.
When TQuery/TTable were first developed, it was a "no-brainer" that, when
working against Paradox/Dbase, it could return the record count directly.
When used against SQL databases, they decided to provide this transparently
so that developers would see the same behaviour between TQuery and TTable.
How is this done? Simple, when you access the RecordCount property in a BDE
dataset working against a SQL database, under the covers it executes a
"Select count(*) ..." for you.
But, depending on the database involved, getting a record count can be slow
(and no matter what it is always an additional query that must be executed).
This is definitely the case for InterBase and so asking for a count should
be avoided unless absolutely necessary.
For this reason, it was decided that, for IBX, it would not automatically
query for the record count just by accessing the RecordCount, but leave it
to the developer to ask for it via SQL explicitly. IOW this is *by design*,
not an oversight or "bug".
Put yourself in the place of a developer where a complex query is being done
that perhaps takes a few moments to execute and return the first set of
records - and which would take some moments longer to return all records,
but where it is desired to display them in a grid. In this case,
automatically asking for the count from InterBase just so the grid's
scrollbar could be set more accurately would make performance of that form
very poor - *getting that count would take as long to execute on the server
as it would if it were asked to fetch all records*.
Given the choice between an ideal scrollbar and efficient, performant
behaviour, many prefer the latter.
--
Wayne Niddery - Winwright, Inc (www.winwright.ca)
"The legitimate powers of government extend to such acts only as are
injurious to others. But it does me no injury for my neighbor to say
there are twenty gods, or no God. It neither picks my pocket nor breaks
my leg." - Thomas Jefferson