Re: not all variables bound

From: Quantarc <info_at_quantarc.co.uk>
Date: Tue, 13 Jul 2004 14:51:31 +0100
Message-ID: <40f3e8e2$0$92613$ed2619ec@ptn-nntp-reader02.plus.net>

tojo wrote:

> In article <40f3bf02$0$92613$ed2619ec_at_ptn-nntp-reader02.plus.net>, 
> info_at_quantarc.co.uk says...
> 

>>i posted a message a few days ago with a problem with an out parameter
>>in a package procedure i was using to return a recordset via ado to my
>>application. i am now trying using a function instead, and this works
>>when i pass in all the variables again, but if i leave any out if STILL
>>tells me that not all the variables are bound (i kinda understood before
>>when the final out parameter hadn't been bound, but with this all the
>>variables really seem to me to be bound).
>>
>>here's the test package i'm trying this with...
>>
>>--
>>-- package TEST
>>--
>>
>>create or replace package TEST as
>> type MYCUR is ref cursor;
>> function FOO( mynum NUMBER DEFAULT -1 ) return MYCUR;
>>end TEST;
>>/
>>
>>create or replace package body TEST as
>>
>>function FOO ( mynum NUMBER DEFAULT -1 )
>>return MYCUR
>>is
>> myc MYCUR;
>>begin
>> open myc for
>> select * from my_table;
>> return myc;
>>end FOO;
>>
>>end TEST;
>>/
>>
>>--
>>-- end package TEST
>>--
>>
>>i am calling the function from my application via ado like so...
>>
>>{call TEST.FOO( ? )}
>>
>>but this doesn't work. i just get the error "not all variables bound".
>> this does however...
>>
>>{call TEST.FOO( 1 )}
>>
>>am i not using default variables properly? or can anyone see another
>>problem? i have been beating my head against a wall with this problem

> 
> Maybe you could post the VB code? Are you binding an input parameter for 
> the "?", something like this:
> 
> Set cmd = New ADODB.Command
> With cmd
>     .CommandText = "{call TEST.FOO( ? )}"
>     .CommandType = adCmdText
>     .Parameters.Append .CreateParameter("mynum", blah....)
>     Set .ActiveConnection = g_conn
>     Set rst = .Execute()
> End With
> 
> Did you see the VB examples for OLE-DB, the one that uses a stored 
> function to return a rowset? It should be just what you need.
> 
> -- Tom

hey tom, the vb will just be like this...

objRS.Open objConn, "{call TEST.FOO( ? )}", etc...

i'm not using a command object to bind any variables because i want to use the defaults that are set up in the package (-1 in this example). i did see the article, that's where i found out how to return recordsets this way, it's just default variables are never covered in it, ack!

would binding parameters with the command object help then... i thought   you would only want o do that if you were either passing variables in, or getting information back. hmmm...

thanx for ur time btw.

rod. Received on Tue Jul 13 2004 - 08:51:31 CDT