Re: What is the advantage of Event programming?
From: Ralph (msnews.20.nt_consulting32_at_spamgourmet.com)
Date: 11/25/04
- Next message: Rick Rothstein: "Re: ReadLine Method giving problems"
- Previous message: Jan Hyde: "Re: Cannot pass objects to DLL"
- In reply to: Robert: "Re: What is the advantage of Event programming?"
- Next in thread: Ken Halter: "Re: What is the advantage of Event programming?"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 25 Nov 2004 09:09:54 -0600
Replies inline... (The html format sort of threw me off.)
Also I think you will find my reply less than satisfactory. <g>
----------------------------------------------
>"Robert" <rmsuffyNO@SPAMhotmail.com> wrote in message
>news:eFwyzMl0EHA.2976@TK2MSFTNGP12.phx.gbl...
>Ralph stated:
>
>> I was thinking in terms of intervals of processing after the data was
>> received, eg, chew on 50 rows, then the next 50 rows. etc. Or updating a
>> progress/label after x number of records have arrived, ie, ADO's
>> FetchProgress event.
>
>I've taken a look at the FetchProgress and other events... I was amazed to
>find what these events do. Thanks for mentioning them! :)
>
>> To do what you are requesting depends on the 'provider' you are using,
the
>> database, cursors, etc. Usually you are better off using SQL to slice 'n
>> dice what lumps of data you need. Also take a look at ADO's GetRows and
>> PageSizes.
>
>Okay... correct me if I'm wrong but the GetRows function returns a
two-diminstional array, which wouldn't work if the recordset has more than
two fields, correct? And it looks like, to me at least, that the PageSize
is for reporting after the recordset has been retrieved - i.e., you can't
limit the number of records returned at a time... say 50 records at a time
out of 500 records.
>
>My knowledge on using SQL statements is somewhat limited. Could you
provide an example of how one would create an SQL statement to retrieve the
first 50 records... and then the next 50, and then the next 50 records?
>
>> Although, using one of these methods would entail as you suggested to
>> process items in a loop.
>>
>> Like I said though, there is no one way that will work all the time for
all
>> databases, cursors, providers. To provide an example - pick a database
and a
>> provider.
>
>Fair enough.... here is a function I wrote to retrieve a recordset based on
SQL statement that was passed to the class. The ADOConn is connected to a
MDB database.
>
>Public Function RetrieveSQL(ByVal SQL As String) As ADODB.Recordset
>
> 'Declare needed variables...
> Dim rsData As New ADODB.Recordset
>
> 'in case of an error...
> On Error GoTo HandleError
> If ADOConn.State = adStateClosed Then Err.Raise
ClassErr.CannotRetrieve, "", ""
>
> 'Retrieve data from database...
> Set rsData.ActiveConnection = ADOConn
> rsData.CursorLocation = adUseClient
> rsData.CursorType = adOpenStatic
> rsData.LockType = adLockBatchOptimistic
> rsData.Open SQL
> Set rsData.ActiveConnection = Nothing
> Set RetrieveSQL = rsData
> Set rsData = Nothing
> Exit Function
>
>HandleError:
> Set rsData = Nothing
> With Err
> Call HandleErrors(Access_Class_RetrieveSQL, .Number, .Source,
.Description)
> End With
>
>End Function
>
>Sincerely,
>
>Robert
===========================================================
Ralph Replies....
"Okay... correct me if I'm wrong but the GetRows function returns a
two-diminstional array, which wouldn't work if the recordset has more than
two fields, correct?"
GetRows is a 2-dim array and contains all the data requested from the
recordset by the function. The first dim is the columns (fields), the second
is the rows.
vRows(0,0) is a variant of the value at 1st Row, 1st field
vRows(0,1) is a variant of the value at 1st Row, 2nd field
Number of rows is "UBound(vRows, 2) + 1"
The order of the columns is the same as the SQL statement.
Pages are similar to 'bookmarks'. They are a way of 'marking' a collection
so we can address it in lumps. What those lumps are, or rather more germane
to your question, whether or not those 'lumps' occur in your address space
or are something delivered from the server's space on demand, depends on the
type of 'cursor' you are using and the paticular database engine and
provider.
[Cursors, data engines, locks, and providers, form an interesting commutable
psychosis. Which can demonstrate perverse behaviors under different
circumstances.]
Check out the following article:
http://delphi.about.com/od/database/l/aa061201b.htm
Which also answers one of your later questions - mdb databases don't support
dynamic cursors, thus don't support 'lumpy' queries.
Note, that even when you don't get all the data in one lump as with a
dynamic cursor - you still receive a lot of information on records you have
yet to retrieve.
As for SQL, again it depends on the engine. Oracle supports the "LIMIT"
keyword. (Oracle has rowIDs which it can track.) SQL Server doesn't support
Limit, but it has a "SET ROWCOUNT x" which has subtle differences between
versions and context.
Requesting a limited number of records at a time, was useful in the old days
when client memory was limited, bandwidth was expensive and slow, and the
data itself was stored in contiguous lumps. Some providers for ISAM data
store still provide this functionality.
With the relational model - data is requested in 'sets' with no intrinsic
knowlege of how many or in what order. The engines are highly optimized to
provide a 'bag' of what you want, but attempting to request just a few items
at a time from the bag is completely depended on other services - and
whether or not such services are even provided is the first consideration.
You can always logically partition your data (either horizonally or
vertically) in such a way to request data in manageable lumps if necessary.
And this is almost always the better solution.
How's that for a very murkly reply.
Happy Thanksgiving.
-ralph
- Next message: Rick Rothstein: "Re: ReadLine Method giving problems"
- Previous message: Jan Hyde: "Re: Cannot pass objects to DLL"
- In reply to: Robert: "Re: What is the advantage of Event programming?"
- Next in thread: Ken Halter: "Re: What is the advantage of Event programming?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
