Re: Working with the ADO .NextRecordset method
From: Rene (presse_spammerssuck_at_hotmail.com)
Date: 03/10/04
- Next message: Rene: "More info ..."
- Previous message: csheng: "call Stored procedure with no params"
- In reply to: david epsom dot com dot au: "Re: Working with the ADO .NextRecordset method"
- Next in thread: Rene: "More info ..."
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 10 Mar 2004 09:26:40 -0500
Hello David,
Many thanks for your post - you put me on an interesting track!
I immediately gave your suggestion a try, and here is what I found.
I did have to set a reference to ADO 2.7 - I had been using ADO 2.5 till
now.
Setting the 'Ouput Stream' property of the command object to a stream works
as expected.
Note: don't use the Set syntax:
mCommand.Properties("Output Stream") = mStm
Executing the command with the adExecuteStream option also worked,
I did change the stored proc and added the 'FOR XML Auto' clause to each
Select for the stream operation to run.
mCommand.Execute , , adExecuteStream
Remember, the server side stored procedure is set up to return multiple
recordsets.
So when I attempted to fetch what I expected would be the 'first recordset'
from my stored procedure I got the following message:
Set mRst = New ADODB.Recordset
mRst.Open mStm, , adOpenForwardOnly, adLockReadOnly
Run-Time error '-1072896683 (c00ce555)'
Only one top level element is allowed in an XML document
This leads me to believe that in the .Execute operation streams ALL the
recordsets in one execution; moreover it would appear they're all stored as
'top level documents' - whatever that means! The stream object would
contain multiple recordsets.
I took a look at the contents of the Stream object using its .ReadText
method. From what I can gather, indeed the result of two selects were
there - as was expected from the stored procedure.
One peculiar thing (I'm not very knowledgable in XML specifics):
the first resultset seemed OK,
but the second one had the view name prefixing each row ???
Anyways, in response to your question/suggestion, I'm not so sure anymore
that Command execution streaming its results directly to a Stream object is
meant to retrieving 'multiple' result sets through which one can iterate.
Comments?
Thanx a million for the pointer!
René
Montreal
> I think that the next interesting idea is to see if you can
> eliminate the initial stream-to-recordset conversion: ie,
> if you open a stream object against the connection (instead
> of a recordset), can you iterate through the multiple result
> sets?
>
> Try this: set an output stream as a command property:
> set mCommand.Properties("Output Stream") = mstm (????)
>
> specify adExecuteStream as an option of the Execute command:
> mCommand.Execute(,,adExecuteStream) (????)
>
> what result do you get ?????
>
> (david)
>
- Next message: Rene: "More info ..."
- Previous message: csheng: "call Stored procedure with no params"
- In reply to: david epsom dot com dot au: "Re: Working with the ADO .NextRecordset method"
- Next in thread: Rene: "More info ..."
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
