Thread: Parameters.Refresh and RETURN setof TEXT
Hi all,
If I have a simple set returning function like 'aafunc1'
----------------------------------------------------------------
CREATE OR REPLACE FUNCTION "public"."aafunc1" (v_acc integer) RETURNS SETOF text AS
$body$
BEGIN
RETURN NEXT 'Arbitary_string of unlimitedLength.';
RETURN NEXT 'Arbitary_string of unlimitedLength222222.';
RETURN;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
----------------------------------------------------------------
Which I then call using vbsrcipt, ADO & the postgresql odbc driver like this:-
----------------------------------------------------------------
Set oCmd = CreateObject("ADODB.Command")
With oCmd
.ActiveConnection = oConnection
.CommandType = 4 'adCmdStoredProc
.CommandText = "public.aafunc1"
.Parameters.Refresh()
.Parameters("v_acc").Value = 1
.Execute
End With
wscript.echo oCmd.Parameters(0)
----------------------------------------------------------------
Why does it not work, it returns an error ...
errmsg='ERROR: set-valued function called in context that cannot accept a set'
When looking at the odbc log
[3352]Exec_with_parameters_resolved: copying statement params: trans_status=1, len=30, stmt='{ ? = call
public.aafunc1(?)}'
[3352]ResolveOneParam: from(fcType)=-16, to(fSqlType)=4
[3352] stmt_with_params = 'SELECT public.aafunc1(1) '
[3352] it's NOT a select statement: stmt=1c62230
[3352]send_query(): conn=1c639b0, query='SELECT public.aafunc1(1) '
[3352]send_query: done sending query
How come it is not picking up that this function returns a SETOF something, and then changing the
Sql statement to "'SELECT * from public.aafunc1(1) '"
I am using windows XP and postgres ODBC driver 8.02.00.02, and the postgres dbase 8.1.4 is running on linux.
I would appreciate some help/thoughts on this issue
Thanks...
Rodney Franks
Rodney Franks wrote:
> Hi all,
> If I have a simple set returning function like 'aafunc1'
> ----------------------------------------------------------------
>
> CREATE OR REPLACE FUNCTION "public"."aafunc1" (v_acc integer) RETURNS SETOF text AS
> $body$
> BEGIN
> RETURN NEXT 'Arbitary_string of unlimitedLength.';
> RETURN NEXT 'Arbitary_string of unlimitedLength222222.';
> RETURN;
> END;
> $body$
> LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
> ----------------------------------------------------------------
>
> Which I then call using vbsrcipt, ADO & the postgresql odbc driver like this:-
>
> ----------------------------------------------------------------
> Set oCmd = CreateObject("ADODB.Command")
> With oCmd
> .ActiveConnection = oConnection
> .CommandType = 4 'adCmdStoredProc
> .CommandText = "public.aafunc1"
> .Parameters.Refresh()
> .Parameters("v_acc").Value = 1
> .Execute
> End With
> wscript.echo oCmd.Parameters(0)
> ----------------------------------------------------------------
>
> Why does it not work, it returns an error ...
> errmsg='ERROR: set-valued function called in context that cannot accept a set'
>
Hi Rodney,
Could you try the dll at
http://www.geocities.jp/inocchichichi/psqlodbc/index.html . ?
regards,
Hiroshi Inoue
Rodney Franks wrote: > Hello Hiroshi, > > Thanks for the prompt reply, > I have downloaded the psqlodbc35W.dll from your website and have overwritten the 8.2 dll on my pc - C:\Program > Files\psqlODBC\0802\bin with your one. > I then set the logging options on. I still seem to get the error 'set-valued function called in context that cannot accepta set'. > Here are the 2 log files attached to this email... Thanks. I've changed the driver a little. Please retry the dll at my site. > I presume that I don't have to unregister and reregister the dll to get your one to work? You can simply replace the dll. regards, Hiroshi Inoue
pgsql-odbc-owner@postgresql.org wrote: > Thanks. > I've changed the driver a little. > Please retry the dll at my site. Hi Hiroshi, Is the driver we are supposed to use the "PostgreSQL Unicode" version? http://www.geocities.jp/inocchichichi/psqlodbc/index.html What about the other version? (postgresql ANSI) --------------- Philippe Lang Attik System
Attachment
Many Thanks Hiroshi, This new driver has resolved my problem and it now works like a charm, I get my 2 records back successfully. Cheers, Rodney -----Original Message----- From: Hiroshi Inoue [mailto:inoue@tpf.co.jp] Sent: 22 August 2006 03:06 AM To: rodney@careerjunction.co.za Cc: pgsql-odbc@postgresql.org Subject: Re: [ODBC] Parameters.Refresh and RETURN setof TEXT Rodney Franks wrote: > Hello Hiroshi, > > Thanks for the prompt reply, > I have downloaded the psqlodbc35W.dll from your website and have > overwritten the 8.2 dll on my pc - C:\Program Files\psqlODBC\0802\bin with your one. > I then set the logging options on. I still seem to get the error 'set-valued function called in context that cannot accepta set'. > Here are the 2 log files attached to this email... Thanks. I've changed the driver a little. Please retry the dll at my site. > I presume that I don't have to unregister and reregister the dll to get your one to work? You can simply replace the dll. regards, Hiroshi Inoue
Philippe Lang wrote: > pgsql-odbc-owner@postgresql.org wrote: > > >> Thanks. >> I've changed the driver a little. >> Please retry the dll at my site. >> > > Hi Hiroshi, > > Is the driver we are supposed to use the "PostgreSQL Unicode" version? > Yes. > http://www.geocities.jp/inocchichichi/psqlodbc/index.html > > What about the other version? (postgresql ANSI) > I provided the ANSI version for the people who are unhappy with the Unicode vesrion. Please note I've not tested the version by myself and haven't updated the version so often. regards, Hiroshi Inoue