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





---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Re: Parameters.Refresh and RETURN setof TEXT

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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Re: 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 accept a 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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Re: Parameters.Refresh and RETURN setof TEXT

This is a multi-part message in MIME format.

------=_NextPart_000_001E_01C6C5D1.BEF51F90
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

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


------=_NextPart_000_001E_01C6C5D1.BEF51F90
Content-Type: application/x-pkcs7-signature;
name="smime.p7s"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
filename="smime.p7s"

MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEH AQAAoIII/zCCAocw
ggHwoAMCAQICEEdYYafa1wkm0CP4DOffAiswDQYJKoZIhvcNAQEEBQAwYjEL MAkGA1UEBhMCWkEx
JTAjBgNVBAoTHFRoYXd0ZSBDb25zdWx0aW5nIChQdHkpIEx0ZC4xLDAqBgNV BAMTI1RoYXd0ZSBQ
ZXJzb25hbCBGcmVlbWFpbCBJc3N1aW5nIENBMB4XDTA2MDYyMjE3MzE0NloX DTA3MDYyMjE3MzE0
NlowZzENMAsGA1UEBBMETGFuZzERMA8GA1UEKhMIUGhpbGlwcGUxFjAUBgNV BAMTDVBoaWxpcHBl
IExhbmcxKzApBgkqhkiG9w0BCQEWHHBoaWxpcHBlLmxhbmdAYXR0aWtzeXN0 ZW0uY2gwgZ8wDQYJ
KoZIhvcNAQEBBQADgY0AMIGJAoGBAOfvCPREDwfIbzsNRoyBDoSOWfI2NNWd RRMJXuj5ldF22gSQ
6MiXFUjt4HlRIwPb8x13hrv89GW/SmLjFThxf7NKZ8dPTZzp4ZlvrQBRAYhS uTQOJ8azyzruZu54
Ak7fa3pz26Q3B8kg4knZBz3O9Q4K1tK8AEdjNQpyDg/iDdzjAgMBAAGjOTA3 MCcGA1UdEQQgMB6B
HHBoaWxpcHBlLmxhbmdAYXR0aWtzeXN0ZW0uY2gwDAYDVR0TAQH/BAIwADAN BgkqhkiG9w0BAQQF
AAOBgQAAzz+I6MrhWjbfWSgngW/DthHvXiTR6x0cCeb4T5mBb5dgueqgLnrh /2blsbCQuLWh0URe
/1l/QQ2dY2elG3PEDPv9cCl/LcwsCsE1Nj3bGM4gdVx9crYvTw5dyyUuDq1K ShV3uziE+o5Lyfx4
qWRYd6/yIBDVarohdpL8J0DJczCCAy0wggKWoAMCAQICAQAwDQYJKoZIhvcN AQEEBQAwgdExCzAJ
BgNVBAYTAlpBMRUwEwYDVQQIEwxXZXN0ZXJuIENhcGUxEjAQBgNVBAcTCUNh cGUgVG93bjEaMBgG
A1UEChMRVGhhd3RlIENvbnN1bHRpbmcxKDAmBgNVBAsTH0NlcnRpZmljYXRp b24gU2VydmljZXMg
RGl2aXNpb24xJDAiBgNVBAMTG1RoYXd0ZSBQZXJzb25hbCBGcmVlbWFpbCBD QTErMCkGCSqGSIb3
DQEJARYccGVyc29uYWwtZnJlZW1haWxAdGhhd3RlLmNvbTAeFw05NjAxMDEw MDAwMDBaFw0yMDEy
MzEyMzU5NTlaMIHRMQswCQYDVQQGEwJaQTEVMBMGA1UECBMMV2VzdGVybiBD YXBlMRIwEAYDVQQH
EwlDYXBlIFRvd24xGjAYBgNVBAoTEVRoYXd0ZSBDb25zdWx0aW5nMSgwJgYD VQQLEx9DZXJ0aWZp
Y2F0aW9uIFNlcnZpY2VzIERpdmlzaW9uMSQwIgYDVQQDExtUaGF3dGUgUGVy c29uYWwgRnJlZW1h
aWwgQ0ExKzApBgkqhkiG9w0BCQEWHHBlcnNvbmFsLWZyZWVtYWlsQHRoYXd0 ZS5jb20wgZ8wDQYJ
KoZIhvcNAQEBBQADgY0AMIGJAoGBANRp19SwlGRbcelH2AxRtupykbCEXn0t DY97Et+FJXUodDpC
LGMnn5V7S+9+GYcdhuqj3bnOlmQawhRuRKx85o/oTQ9xH0A4pgCjh3j2+ZSG Xq3qwF5269kUo11u
enwMpUtVfwYZKX+emibVars4JAhqmMex2qOYkf152+VaxBy5AgMBAAGjEzAR MA8GA1UdEwEB/wQF
MAMBAf8wDQYJKoZIhvcNAQEEBQADgYEAx+ySfk749ZalZ2IqpPBNEWDQb41g WGGsJrtSNVwIzzD7
qEqWih9iQiOMFw/0umScF6xHKd+dmF7SbGBxXKKs3Hnj524ARx+1DSjoAp3k mv0T9KbZfLH43F8j
JgmRgHPQFBveQ6mDJfLmnC8Vyv6mq4oHdYsM3VGEa+T40c53ooEwggM/MIIC qKADAgECAgENMA0G
CSqGSIb3DQEBBQUAMIHRMQswCQYDVQQGEwJaQTEVMBMGA1UECBMMV2VzdGVy biBDYXBlMRIwEAYD
VQQHEwlDYXBlIFRvd24xGjAYBgNVBAoTEVRoYXd0ZSBDb25zdWx0aW5nMSgw JgYDVQQLEx9DZXJ0
aWZpY2F0aW9uIFNlcnZpY2VzIERpdmlzaW9uMSQwIgYDVQQDExtUaGF3dGUg UGVyc29uYWwgRnJl
ZW1haWwgQ0ExKzApBgkqhkiG9w0BCQEWHHBlcnNvbmFsLWZyZWVtYWlsQHRo YXd0ZS5jb20wHhcN
MDMwNzE3MDAwMDAwWhcNMTMwNzE2MjM1OTU5WjBiMQswCQYDVQQGEwJaQTEl MCMGA1UEChMcVGhh
d3RlIENvbnN1bHRpbmcgKFB0eSkgTHRkLjEsMCoGA1UEAxMjVGhhd3RlIFBl cnNvbmFsIEZyZWVt
YWlsIElzc3VpbmcgQ0EwgZ8wDQYJKoZIhvcNAQEBBQADgY0AMIGJAoGBAMSm PFVzVftOucqZWh5o
wHUEcJ3f6f+jHuy9zfVb8hp2vX8MOmHyv1HOAdTlUAow1wJjWiyJFXCO3cnw K4Vaqj9xVsuvPAsH
5/EfkTYkKhPPK9Xzgnc9A74r/rsYPge/QIACZNenprufZdHFKlSFD0gEf6e2 0TxhBEAeZBlyYLf7
AgMBAAGjgZQwgZEwEgYDVR0TAQH/BAgwBgEB/wIBADBDBgNVHR8EPDA6MDig NqA0hjJodHRwOi8v
Y3JsLnRoYXd0ZS5jb20vVGhhd3RlUGVyc29uYWxGcmVlbWFpbENBLmNybDAL BgNVHQ8EBAMCAQYw
KQYDVR0RBCIwIKQeMBwxGjAYBgNVBAMTEVByaXZhdGVMYWJlbDItMTM4MA0G CSqGSIb3DQEBBQUA
A4GBAEiM0VCD6gsuzA2jZqxnD3+vrL7CF6FDlpSdf0whuPg2H6otnzYvwPQc UCCTcDz9reFhYsPZ
Ohl+hLGZGwDFGguCdJ4lUJRix9sncVcljd2pnDmOjCBPZV+V2vf3h9bGCE6u 9uo05RAaWzVNd+NW
IXiC3CEZNd4ksdMdRv9dX2VPMYIC+DCCAvQCAQEwdjBiMQswCQYDVQQGEwJa QTElMCMGA1UEChMc
VGhhd3RlIENvbnN1bHRpbmcgKFB0eSkgTHRkLjEsMCoGA1UEAxMjVGhhd3Rl IFBlcnNvbmFsIEZy
ZWVtYWlsIElzc3VpbmcgQ0ECEEdYYafa1wkm0CP4DOffAiswCQYFKw4DAhoF AKCCAdgwGAYJKoZI
hvcNAQkDMQsGCSqGSIb3DQEHATAcBgkqhkiG9w0BCQUxDxcNMDYwODIyMDgw MDA2WjAjBgkqhkiG
9w0BCQQxFgQUNbk2+biV/WfVHjkapzhVgtWMFP4wZwYJKoZIhvcNAQkPMVow WDAKBggqhkiG9w0D
BzAOBggqhkiG9w0DAgICAIAwDQYIKoZIhvcNAwICAUAwBwYFKw4DAgcwDQYI KoZIhvcNAwICASgw
BwYFKw4DAhowCgYIKoZIhvcNAgUwgYUGCSsGAQQBgjcQBDF4MHYwYjELMAkG A1UEBhMCWkExJTAj
BgNVBAoTHFRoYXd0ZSBDb25zdWx0aW5nIChQdHkpIEx0ZC4xLDAqBgNVBAMT I1RoYXd0ZSBQZXJz
b25hbCBGcmVlbWFpbCBJc3N1aW5nIENBAhBHWGGn2tcJJtAj+Azn3wIrMIGH BgsqhkiG9w0BCRAC
CzF4oHYwYjELMAkGA1UEBhMCWkExJTAjBgNVBAoTHFRoYXd0ZSBDb25zdWx0 aW5nIChQdHkpIEx0
ZC4xLDAqBgNVBAMTI1RoYXd0ZSBQZXJzb25hbCBGcmVlbWFpbCBJc3N1aW5n IENBAhBHWGGn2tcJ
JtAj+Azn3wIrMA0GCSqGSIb3DQEBAQUABIGArHDlSU6q6AucrvIvRdxNaR2/ nHaRhvNLVF53YqC+
H5e8qwLo+o0r02/HteSnt36kx3iIZsKwBkToRqethd2bolldW27xPA0WOr7e uz+PvlkIs90sBYFr
DRmDvB6R0y22HhgXuoUvbpuvcMRhzzx83+y9ADH8XeoKmV9a7gLgfAcAAAAA AAA=

------=_NextPart_000_001E_01C6C5D1.BEF51F90--

Re: Parameters.Refresh and RETURN setof TEXT

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 accept a 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



---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: Parameters.Refresh and RETURN setof TEXT

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


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org