Re: not all variables bound

From: Quantarc <info_at_quantarc.co.uk>
Date: Wed, 14 Jul 2004 15:53:24 +0100
Message-ID: <40f548df$0$93993$ed2619ec@ptn-nntp-reader01.plus.net>

André Hartmann wrote:

> "Quantarc" <info_at_quantarc.co.uk> schrieb im Newsbeitrag
> news:40f4fe43$0$93993$ed2619ec_at_ptn-nntp-reader01.plus.net...
>

>>Quantarc wrote:
>>
>>
>>>André Hartmann wrote:
>>>
>>>
>>>>"Quantarc" <info_at_quantarc.co.uk> schrieb im Newsbeitrag
>>>>news:40f3e8e2$0$92613$ed2619ec_at_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!
>>>>
>>>>
>>>>
>>>>if you want to use the default value defined in the function, then
>>>>dont pass
>>>>in anything. try this: call TEST.FOO()
>>>>
>>>>
>>>>
>>>>>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.
>>>>
>>>>
>>>>
>>>>
>>>problem solved!  i used a command object and bound variables without
>>>giving values amd it seems to work for the moment, excellent!
>>>
>>>thanx a lot guys!
>>>:D
>>>
>>>rod.
>>
>>wait, actually it's not solved.  all that happens now is a blank (i'm
>>not sure what) value gets passed in to the function so the default isn't
>>used, which isn't what i need.
>>
>>hmm...  thanx anyways, i'll just hafta keep looking...
>>
>>rod.

>
>
> did you actually consider my suggestion and not bind anything but just call
> FOO() without an argument (not even a ?) ?
>
>

yeah i did try that but i just got the same error message, besides the point of me doing this was so that i could just include some arguments, so if the function is this...

foo( first number, second number default 2, third varchar )

then i could call it like

foo ( 1, ?, 'Hello' );

ya know? it doesn't matter now anyways, i've decided to take a different way around the problem (especially after i found out that oracle doesn't actually support the DEFAULT_VALUE field in user_arguments).

thanx for all the suggestions anyways!

rod. Received on Wed Jul 14 2004 - 09:53:24 CDT