ADO Command object/Parameters problem...

[Kris J. Zaragoza]

In article <3d09426d.313465031 at netnews.attbi.com>, candiazoo at attbi.com wrote:
> Has anyone else run into this (note: I have seen other entries for the same
> error, which is rather generic, but not for my specific reason).

I've never seen this specific error before when using ADO from Python
or any other language.  The exact error text below may be a red
herring or otherwise not accurately describe the problem.  Looking
quickly at the code you provided, however, a few things smell fishy.
Comments below.

> HERE is an example of what I am doing... (note, I added the long() typecasting
> to make sure the value was coerced into a long integer)...
> 
> sql = "select pty.orgname " + \
>       "from ccc_party pty, ccc_right_rightsholder rgh, ccc_right rgt " + \
>       "where pty.pty_inst = rgh.rgh_inst " + \
>       "and rgh.rgt_inst = rgt.rgt_inst " + \
>       "and rgt.rgt_inst = ?"

First of all, you are performing one more join than needed for what
you are selecting.  If you need to restrict your record set based on
the rgt_inst field, you only need to join ccc_party and
ccc_right_rightsholder as the latter already has the foreign key
reference which is exactly the field you are restricting on.  In other
words, you only need to use:

select pty.orgname
from ccc_party pty, ccc_right_rightsholder rgh
where pty.pty_inst = rgh.rgh_inst
and rgh.rgt_inst = ?

Or, using the more modern join syntax

select pty.orgname
from ccc_party pty
join ccc_right_rightsholder rgh
	on rgh.rgh_inst = pty.pty_inst and rgh.rgt_inst = ?

> cmdOracle.ActiveConnection = cnnOracle
> cmdOracle.CommandType = 1
> cmdOracle.CommandText = sql
> 
> trsInst = long(rstTempDB.Fields("trs_inst").Value)
> aasInst = long(rstTempDB.Fields("aas_inst").Value)
> anyInst = trsInst or aasInst
>         
> cmdOracle.Parameters.Append(cmdOracle.CreateParameter("orgname", 200, 2, 80))
> cmdOracle.Parameters.Append(cmdOracle.CreateParameter("rgt_inst", 3, 1, 4,
> long(anyInst)))
> rstOracle, status = cmdOracle.Execute()

Here's another point that confused me.  Why are you adding two
parameters when you only have one parameter placeholder?  The orgname
field is what will be returned in the record set rstOracle.  You don't
need to add it as a parameter.

I don't know how helpful this has been.  If you need further
clarification or help, you may want to provide a more accurate or
complete piece of code.

Kris


-- 
Kris J. Zaragoza       | On the face of it, Microsoft complaining about
kzaragoza at attbi.com    | the source license used by Linux is like the
                       | event horizon calling the kettle black.
                       | -- Adam Barr, article on kuro5hin.org