Re: Fastest way to get table scheme?

From: Arthur Hoornweg (arthur.hoornweg_at_wanadoo.nl.net)
Date: 11/16/04 

Date: Tue, 16 Nov 2004 09:55:03 +0100

Del M wrote:

> The way that you do it is generic since systables etc are unique to Sql
> server. But ... what is the problem with just selecting your data, then
> making some fields invisible and the rest persistent. Why do you need the
> table structure to do that ?

Part of the problem is that the number and most of the names
of the data fields are not known at design-time. So I can't
just do a "select column1, column2..." in my query.

The other part of the problem is that it's not just *one* query
that is performed, in fact a new query is performed every time
the user scrolls through my dbgrid.

I have a form with a dbgrid (TMS dbAdvStringGrid).

The underlying table is 50,000+ records large and is accessed
by the customer through a VPN tunnel with only 64 kilobits/second.
The connection is ADO, using a client-side recordset. Data are
fetched using a firehose cursor and all updates are performed as
batch updates.

The table contains numerical data from oilwells and the various
parameters are different from oilwell to oilwell and from
customer to customer.
The users need to be able to scroll through the whole dataset
transparently with the dbgrid. The look-and-feel of a grid is
absolutely necessary for this particular purpose.

I have designed the logic of the form in such a way, that only the
data that are currently visible on the screen are fetched. So the
grid is maybe only 30 records in size. Still it has a scrollbar
that allows you to scroll through the entire dataset and behaves
like any other live dbgrid.

Everytime the vertical scrollbar is moved (or if the user presses
pgup/pgdn) a new query is automatically generated that fetches the
desired records. So the whole thing looks like a dbgrid, feels like
a dbgrid but doesn't have the bandwidth/table size limitations of
a normal dbgrid. Scrolling to *any* position in the dataset takes
no more than a second or two even over a 64 kbit connection.

At design-time, the exact number of fields in this table is
unknown, it depends on the number of parameters the particular
customer wants recorded. So my trick is, when the dataset is first
opened, I determine the table structure just to determine which
fields are present, I then exclude some fields that the customer
shouldn't see, add some calculated fields and then transform the
lot into persistent fields.

I want to do this only once, not every time the user scrolls through
the dataset, because I've found the grid behaves erratically if I
change the field layout all the time.

So when the dataset is first opened my query is "select * from mytable
where 0=1" which gives me the table structure.

Any subsequent queries look like "select * from mytable where
project='ABCDE' and depth between 1000.8 and 1030.8 order by depth"
where the exact numbers depend on the position of the scrollbar.

It works magnificently but I am a bit worried if all database
engines behave the same if you give them a "where 0=1" query.
The table can have millions of records, eventually, and it
would be pretty sad if the database engine would iterate
through all records verifying a condition that always evaluates
to "false". 

-- 
Arthur Hoornweg
(please remove the ".net" from my e-mail address.
  I had to take this measure to counteract SPAM
  flooding my mail box)

Relevant Pages

  • Re: DBGrid und Scrollwheel
    ... Es gebt darum, einem DBGrid beizubringen, mit dem Scrollrad nicht am Ende des Viewports stehen zu bleiben. ... if Message.WheelDelta < 0 then ... wie wenn ich den Scrollbar bewege. ...
    (de.comp.lang.delphi.misc)
  • DBGrid und Scrollwheel
    ... Es gebt darum, einem DBGrid beizubringen, mit dem Scrollrad nicht am Ende des Viewports stehen zu bleiben. ... Dazu überschreibe ich in einer abgeleiteten Klasse einfach CMMouseWheel von TDBGrid: ... if Message.WheelDelta < 0 then ... wie wenn ich den Scrollbar bewege. ...
    (de.comp.lang.delphi.misc)
  • Re: Results of AdoQuery
    ... But when making a query of only a few fields the result is an error message. ... Do I have to recreate the DBGrid according to the fields handled in the query? ... and grid columns, or runtime / dynamically created ones. ... Whenever you design the column layout of a dbgrid, the attached dataset needs to contain all the fields declared at designtime. ...
    (alt.comp.lang.borland-delphi)
  • Re: Finding a record number using SQL
    ... I am using a query and have a DBGrid with a datasource pointing to the query ... which is using an order by to sort the data by a specific field. ... to do is after inserting or updating a record I want to execute another SQL ...
    (borland.public.delphi.database.ado)
  • Re: Results of AdoQuery
    ... What's your error code? ... The DBGrid should refresh automatically after running the query. ... > The whole dataset is seen in a DBGrid, which is connected to AdoQuery. ...
    (alt.comp.lang.borland-delphi)