Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-odbc by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group