ADO record object - SINGLETON SELECTs
Stephen Howe
If you do a ADO Record object Open
I am doing effectively
(hr =pRec->raw_Open(_variant_t((IDispatch*)pCmd), vtMissing, adModeRead,adFailIfNotExists, adOpenExecuteCommand)
I will get back
S_OK if 1 record exists, great
DB_E_NOTFOUND if 0 records exists (or a _com_error exception if using the throwing variety)
But what if there are 2+ records?
It seems that I see the first record, and there is no way of detecting this has happened with ADO Record object
Any shedding of light on what ADO Record object does would be helpful.
thanks
Stephen Howe
ralph
No. For the simple fact HResults are used to return errors, warnings,
and other information from functions for various services, in this
case COM.
They are not, nor should they ever be used, for returning any
information about what that function was suppose to do or did do.
Your suggestion that S_OK 'means' at least one record was returned is
only an assumption, albeit one that makes some logical sense.
To determine if more than one record was returned you need to query
the resulting Recordset using the RecordCount method (if supported for
your cursor), directly request a Count in the query, or even enumerate
the Recordset and count the rows.
http://www.w3schools.com/ado/ado_ref_recordset.asp
-ralph
Bob Barrows
> On Wed, 28 Jul 2010 11:39:58 +0100, Stephen Howe
> <sjhoweATdialDOTpipexDOTcom> wrote:
>
>> Hi
>>
>> If you do a ADO Record object Open
>>
>> I am doing effectively
>>
>> (hr =pRec->raw_Open(_variant_t((IDispatch*)pCmd), vtMissing,
>> adModeRead,adFailIfNotExists, adOpenExecuteCommand)
>>
>> I will get back
>>
>> S_OK if 1 record exists, great
>> DB_E_NOTFOUND if 0 records exists (or a _com_error exception if
>> using the throwing variety)
>>
>> But what if there are 2+ records?
>> It seems that I see the first record, and there is no way of
>> detecting this has happened with ADO Record object
>>
>> Any shedding of light on what ADO Record object does would be
>> helpful.
>>
>
>
> the resulting Recordset using the RecordCount method (if supported for
Um, he's asking about a Record object, not a Recordset object.
Forgive me, Stephen, but that is an object I have never used so you have
more knowledge about it than I do. I've checked Sceppa's and Vaughn's books
and neither goes into any great detail. Sceppa's book simply seems to parrot
the information in the ADO documentation ... not very helpful. He does,
however go into the cases where the Record object is useful: basically it's
intended for situations where you have non-traditional data: hierarchical
and non-rectangular data. The Record object has properties and methods to
allow you to get at information that is not accessible via the Recordset
object. Is that what you are dealing with? If not, I would stop playing with
the Record object.
ralph
wrote:
>ralph wrote:
>> On Wed, 28 Jul 2010 11:39:58 +0100, Stephen Howe
>> <sjhoweATdialDOTpipexDOTcom> wrote:
>>
>>> Hi
>>>
>>> If you do a ADO Record object Open
>>>
>>> I am doing effectively
>>>
>>> (hr =pRec->raw_Open(_variant_t((IDispatch*)pCmd), vtMissing,
>>> adModeRead,adFailIfNotExists, adOpenExecuteCommand)
>>>
>>> I will get back
>>>
>>> S_OK if 1 record exists, great
>>> DB_E_NOTFOUND if 0 records exists (or a _com_error exception if
>>> using the throwing variety)
>>>
>>> But what if there are 2+ records?
>>> It seems that I see the first record, and there is no way of
>>> detecting this has happened with ADO Record object
>>>
>>> Any shedding of light on what ADO Record object does would be
>>> helpful.
>>>
>>
>>
>> To determine if more than one record was returned you need to query
>> the resulting Recordset using the RecordCount method (if supported for
>
>Um, he's asking about a Record object, not a Recordset object.
>
Yeah, I drifted off there.
My comments concerning the HR are still correct.
>Forgive me, Stephen, but that is an object I have never used so you have
>more knowledge about it than I do. I've checked Sceppa's and Vaughn's books
>and neither goes into any great detail. Sceppa's book simply seems to parrot
>the information in the ADO documentation ... not very helpful. He does,
>however go into the cases where the Record object is useful: basically it's
>intended for situations where you have non-traditional data: hierarchical
>and non-rectangular data. The Record object has properties and methods to
>allow you to get at information that is not accessible via the Recordset
>object. Is that what you are dealing with? If not, I would stop playing with
>the Record object.
>
Part of the mission statement for ADO was provide for "Universal Data
Access" and the ADO "Record" object was more or less provided for
completeness or as noted in the documentation for single data
packages, or for "un-structured" data (where not all 'rows' have the
same number of fields, or data types).
What you got back was dependent on the Provider, but if there were
several 'rows' it was often an ADO Record collection. You can
determine the type by reading the RecordTypeEnum. (Once again the
HResult only provided error/warning information.)
I remember seeing it used in a commercial 'Data' Tree-View control,
and about that time there was still some interest in "structured
documents" (with an associated provider and mostly with Automation).
There was also an XML and an INI 'reader' that converted to a
Collection of Records.
It seems a good idea as it provides a lot of functionality for
programmers writing their own data packages and presented more or less
standard or documented "ADO-way" methods for clients to access them.
However, like most 'higher-level' abstractions, the extra over-head is
often not worth it, as it is often easier and provides better
performance to just supply your own custom methods along with the
custom data.
The exception, of course, was with COM documents, however, most of
that technology never took off. Again simpler to just supply your own
custom object model than write a provider for a specific document.
hth
-ralph
Stephen Howe
>No. For the simple fact HResults are used to return errors, warnings,
>and other information from functions for various services, in this
>case COM.
>
>They are not, nor should they ever be used, for returning any
>information about what that function was suppose to do or did do.
I know that. But it could be argued that 2+ records breaks the Record object, therefore that could be a Com Error.
After all, 0 records is a Com Error.
But on experimentation, it seems that 2+ records is okay
it seems that I wil lhave to use a Recordset and count the records.
I want to be sure I have 1 record. Any other number is unacceptable in this situation.
>Your suggestion that S_OK 'means' at least one record was returned is
>only an assumption, albeit one that makes some logical sense.
>
>To determine if more than one record was returned you need to query
>the resulting Recordset using the RecordCount method (if supported for
>your cursor), directly request a Count in the query, or even enumerate
>the Recordset and count the rows.
Yup, I got that :-(
Thanks
S
Stephen Howe
>more knowledge about it than I do. I've checked Sceppa's and Vaughn's books
>and neither goes into any great detail. Sceppa's book simply seems to parrot
>the information in the ADO documentation ... not very helpful. He does,
>however go into the cases where the Record object is useful: basically it's
>intended for situations where you have non-traditional data: hierarchical
>and non-rectangular data. The Record object has properties and methods to
>allow you to get at information that is not accessible via the Recordset
>object. Is that what you are dealing with? If not, I would stop playing with
>the Record object.
I use the Record object a lot Bob.
It was added in ADO 2.6
And I have seen the documentation.
And it seems that 95% of the documentation is concerned with hierarchal use or Streams
But I noticed the 5% of the remaining documentation as using Singleton Selects.
I think the way it works is that effectively the 1st records fields are read as Output parameters, so somewhat like a Stored
Procedure. It is also about 15% faster than a Recordset as it does not have to construct Cursors or RowSets.
But as said MS's documentation is a bit thin here.
S
ralph
<sjhoweATdialDOTpipexDOTcom> wrote:
>
>>No. For the simple fact HResults are used to return errors, warnings,
>>and other information from functions for various services, in this
>>case COM.
>>
>>They are not, nor should they ever be used, for returning any
>>information about what that function was suppose to do or did do.
>
>I know that. ...
>
>Yup, I got that :-(
>
Sort of assumed you did know all that (have seen you post before), but
when it doubt one tends to be pedantic.
Please don't get too annoyed - not a reflection, just a case of
wanting to make sure. <g>
Out of curiousity, what provider are you using?
-ralph
sjh...@dial.pipex.com
Sorry - been on holiday for a week in North Wales. Back now.
>Sort of assumed you did know all that (have seen you post before), but
>when it doubt one tends to be pedantic.
>
>Please don't get too annoyed - not a reflection, just a case of
>wanting to make sure. <g>
I am not annoyed in the slightest :-)
It is good sound programmer behaviour, make sure all the bases are covered, make sure no points of ambiguity remain :-)
>Out of curiousity, what provider are you using?
SQLOLEDB. Database is SQL Server 2008, fully SP'ed
The reason I asked is it is has been some time since I have done any experiments with ADO.
I did so in 2001. And I did so when MDAC 2.6 came out, when I first used the Record object.
And really I need to do some more experiments with bound varchar versus char fields and INSERT statements.
It is just that there are always tons of things to learn and revisiting ADO has not been a priority.
I have some background niggling memory (probably false) that there was some way of telling when the Record object was having to
deal with 2+ record objects. I know for a fact that with 0, you will get a HR error (or _com_error thrown if using exceptions).
Perhaps the Status property or some combination of Open or Extended flags.
I am trying to recall if I saw anything about 5 years ago when I last experimented and checked.
Thanks
Stephen
--- news://freenews.netfront.net/ - complaints: ne...@netfront.net ---
ralph
>
>I have some background niggling memory (probably false) that there was some way of telling when the Record object was having to
>deal with 2+ record objects. I know for a fact that with 0, you will get a HR error (or _com_error thrown if using exceptions).
>Perhaps the Status property or some combination of Open or Extended flags.
>I am trying to recall if I saw anything about 5 years ago when I last experimented and checked.
>
Most likely the RecordTypeEnum.
It indicates if a 'single' record is returned, or if there is a
'collection'. The latter is assumed to mean more than one, but that is
only an assumption. And is the reason I asked about the provider. Many
one-off providers don't bother and either just return a collection or
a custom (of course the latter doesn't apply your in case).
IIRC the Jet and SQL Server providers are rather good at this, but it
heavily dependent on the source as well as the provider - whether
chewing on a Record, a Recordset object, or a query.
However, my memory may be faulty as well.
If you discover a definitive way, please post it. I'm curious, plus it
would be a boon to anyone else, having the same problem, who might
stumble upon this thread.
-ralph