Parameters.Refresh and RETURN setof TEXT

From: "Rodney Franks" <rodney(at)careerjunction(dot)co(dot)za>
To: <pgsql-odbc(at)postgresql(dot)org>
Subject: Parameters.Refresh and RETURN setof TEXT
Date: 2006-08-18 18:22:20
Message-ID: 005001c6c2f3$3e9ff670$332c13ac@careerjunction.local
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-odbc

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

Responses

Browse pgsql-odbc by date

  From Date Subject
Next Message Blake McBride 2006-08-18 21:10:48 Re: SQLForeignKeys bugs
Previous Message Bart Samwel 2006-08-18 14:13:29 Buffer overrun in handle_notice_message()