[pywin32-bugs] [ pywin32-Bugs-801291 ] problem with ADO.CommandeExecute() options

[SourceForge.net <no...@so...>]

Bugs item #801291, was opened at 2003-09-05 12:39
Message generated for change (Comment added) made by kf7xm
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=551954&aid=801291&group_id=78018

Please note that this message will contain a full copy of the comment thread,
including the initial issue submission, for this request,
not just the latest update.
Category: None
Group: None
>Status: Closed
>Resolution: Invalid
Priority: 5
Private: No
Submitted By: Jason R. Coombs (jaraco)
Assigned to: Nobody/Anonymous (nobody)
Summary: problem with ADO.CommandeExecute() options

Initial Comment:
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

----------------------------------------------------------------------

>Comment By: Vernon Cole (kf7xm)
Date: 2010-05-03 01:40

Message:
Roger's comments show correct use of the call.

----------------------------------------------------------------------

Comment By: Mark Hammond (mhammond)
Date: 2004-10-09 00:04

Message:
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

----------------------------------------------------------------------

Comment By: Jason R. Coombs (jaraco)
Date: 2003-09-08 15:47

Message:
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?

----------------------------------------------------------------------

Comment By: Roger Upole (rupole)
Date: 2003-09-05 16:40

Message:
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


----------------------------------------------------------------------

You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=551954&aid=801291&group_id=78018