#55 problem with ADO.CommandeExecute() options

[Jason R. Coombs]

I posted this originally on comp.python. I'm reposting it
here since it seems a more appropriate place.

I'm hoping this is a coding error on my part, but I've
encountered a problem with parameters that I cannot
understand. Perhaps someone out there might be willing
to assist me with some suggestions.

First, here is some VB code that correctly does what I
want to do:

--------------------------- begin cmdtest.vbs
Dim cn
Dim rs
Dim cmd

Set cn = CreateObject( "ADODB.Connection" )
cn.Open( "Provider=SQLOLEDB;Data Source=
(local);Initial Catalog=Environmental
Monitoring;Integrated Security=SSPI" )

Set rs = CreateObject( "ADODB.Stream" )
rs.Open()

Set cmd = CreateObject( "ADODB.Command" )

cmd.ActiveConnection = cn
cmd.Properties("Output Stream").Value = rs
cmd.CommandText = "SELECT * FROM [Sources] for
XML AUTO"

cmd.Execute , , 1024 'adExecuteStream

rs.Position = 0
WScript.Echo rs.ReadText()
--------------------------- end cmdtest.vbs

Here is partial output I get:
C:\>cscript cmdtest.vbs
Microsoft (R) Windows Script Host Version 5.6
Copyright (C) Microsoft Corporation 1996-2001. All
rights reserved.

<Sources ID="1" Name="South Asia"
Type="7"/><Sources ID="2" Name="Japan" Type="2"/>...

Now, if I attempt to do the same thing in python, it
returns no output.
------------------------- begin cmdtest.py
import ADO, sys

cn = ADO.Connection()
cn.Open( "Provider=SQLOLEDB;Data Source=
(local);Initial Catalog=Environmental
Monitoring;Integrated Security=SSPI" )

rs = ADO.Stream()
rs.Open()

cmd = ADO.Command()

cmd.ActiveConnection = cn
cmd.Properties("Output Stream").Value = rs
cmd.CommandText = "SELECT * FROM [Sources] for
XML AUTO"

cmd.Execute( Options =
ADO.constants.adExecuteStream )

rs.Position = 0
sys.stdout.write( rs.ReadText() ) # prints nothing
------------------------- end cmdtest.py

If I pass no parameters to cmd.Execute in the VBScript
verison, I get no output. Other tests have further led
me to conclude that the constant value 1024 is not
being properly passed to ADO.Command.Execute in the
Python version only.

Here are some other points of information:
- Using the literal 1024 in the Python code instead of
the constant reference make no difference.
- The VBScript code does not recognize
adExecuteStream by name.
- Using win32com.client.Dispatch( 'ADODB.*' ) to
create the objects (instead of ADO.py created from
make PY) yields the same results.
- Using a different PROVIDER in the connection (such
as SQLXMLOLEDB) will yield different results, but still
indicates that the 'adExecuteStream' is not being set
properly.
- I'm using "Microsoft ActiveX Data Objects 2.8 Library"
for the ADO. I've tried using v2.5 and 2.7, but get
identical results.
- The first parameter to ADO.Command.Execute
appears to be an [out] parameter, but the
documentation is confusing and I haven't seen the first
parameter used anywhere.

Any insight into this problem would be most appreciated.

Regards,
Jason R. Coombs
Sandia National Laboratories

[Roger Upole]

Logged In: YES
user_id=771074

Output parameters are returned as results of the method call,
so you would do rs=cmd.Execute(....).
hth
Roger

[Jason R. Coombs]

Logged In: YES
user_id=599869

Thanks for the comment, but it doesn't address my issue. I
apologize for being unclear.

The reason I am passing an ADO.Stream (Prog ID
ADODB.Stream) object as the "Output Stream" property of
the command is because this is the only way AFAIK to
retrieve the results of a "SELECT ... FOR XML" query in SQL
Server via ADO.

I can use the rs = cmd.Execute(...) syntax to receive the
results of a standard query returning a recordset (where rs is
a two-tuple containing the recordset and the result status).

Upon further investigation, I found that the RecordsAffected
(first parameter to cmd.Execute) is involved in the ADO not
behaving as expected.

By sheer luck, I was able to get the Python code to work by
passing something (and as it turns out anything) as the
RecordsAffected Parameter.

Leaving all other code the same, but replacing the execute
line with the following yields the same results as the VB code.

cmd.Execute( 1, Options = ADO.constants.adExecuteStream )

Furthermore, passing any value as RecordsAffected or even
passing it as a keyword argument seems to alleviate the
problem.

cmd.Execute( RecordsAffected = None, Options =
ADO.constants.adExecuteStream )
cmd.Execute( 'jelly beans', Options =
ADO.constants.adExecuteStream )

all work just as well as the control code (VB).

The VB code works fine with all such parameters except for a
string value (such as "jelly beans").

I hope this sheds some more light on the issue I'm
experiencing with passing parameters to the
ADO.Command.Execute method. Fortunately, I've found a
workaround now, but it would be nice to track down the core
of the problem.

Any ideas?

[Mark Hammond]

Logged In: YES
user_id=14198

Interesting... The problem will be the way we present these
optional params. It may even be that '[ 985980 ] pywin32
custom i/f COM servers do not support var args' has some
insights

[Vernon Cole]

Roger's comments show correct use of the call.

[Jason R. Coombs]

Pardon me, but Roger's comment did not explain why the Options parameter is not supplied to the Execute call unless RecordsAffected is supplied. My initial analysis did betray some ignorance of the [out] parameter, but based on my second response, that was not the crux of the problem. I agree this might be a hard problem to solve, and that it's been around a long time, but Invalid is not the resolution. I investigated this problem fairly thoroughly (for my expertise level at the time) and was getting some very strange results, which I believe did reflect some more fundamental issue with the pywin32 COM layer.

I haven't worked with SQL Server outside of SQLAlchemy for a while, but it shouldn't take me too long to get back up to speed with the ADO code. I'll plan to take a look at this at some point.