Re: Cannot access the return value of a PostgreSQL function using ADO

On 10/28/2016 08:08 AM, Mick GRIFFIN wrote:
> Hi,
>
> I have a Postgres function
>
>
>
> CREATE OR REPLACE FUNCTION updateconfigitem(inputkey character varying,
>
>                                             inputvalue character varying,
>
>                                             modifier character varying)
>
>   RETURNS integer AS
>
> $BODY$
>
> BEGIN
>
>     UPDATE  configitems
>
>     SET _value          = inputvalue,
>
>         _modifier       = modifier
>
>     WHERE _key = inputkey;
>
>     RETURN 1;
>
> EXCEPTION
>
>    WHEN others THEN
>
>       RETURN 33;
>
> END;
>
> $BODY$
>
>   LANGUAGE plpgsql VOLATILE
>
>   COST 100;
>
>
>
> I call it from a VB6 program using ADO
>
>
>
> DimobjCmd AsADODB.Command
>
> DimcolParams AsADODB.Parameters
>
> DimobjParam AsADODB.Parameter
>
> DimlngRETURN_VALUE AsLong
>
> DimstrSQL AsString
>
>
>
>     ' Set connection properties and open
>
>     Set objCon = New ADODB.Connection
>
>     objCon.ConnectionString =
> "DATABASE=MYdatabase;UID=MyUser;PWD=Mypassword;DSN=PostgreSQL30"
>
>     objCon.CursorLocation = adUseClient
>
>     objCon.Open
>
>
>
>     Set objCmd = New ADODB.Command
>
>     'Setcommand properties
>
>     WithobjCmd
>
>         Set.ActiveConnection= objCon
>
>         .CommandTimeout= m_lngCommandTimeout
>
>         .CommandText= "updateconfigitem"
>
>         .CommandType= adCmdStoredProc
>
>         SetcolParams = .Parameters
>
>     EndWith
>
>
>
>     ' Define stored procedure params and append to command.
>
>     colParams.Append objCmd.CreateParameter("RetVal", adInteger,
> adParamReturnValue, 0)
>
>     colParams.Append objCmd.CreateParameter("inputkey", adVarChar,
> adParamInput, 50)
>
>     colParams.Append objCmd.CreateParameter("inputvalue", adVarChar,
> adParamInput, 255)
>
>     colParams.Append objCmd.CreateParameter("modifier", adVarChar,
> adParamInput, 255)
>
>
>
>     'Specifyinput parameter values
>
>     colParams("inputkey") = "colour"
>
>     colParams("inputvalue") = "Green"
>
>     colParams("modifier") = "mg"
>
>
>
>     ' Execute the stored procedure
>
>      objCmd.Execute
>
>
>
> End Sub
>
>
>
> When I execute I get error
>
> “Run-time error '-2147467259(80004005)': ERROR: could not determine data
> type of parameter $4; Error while executing the query”
>
>
>
> If I remove the line creating the return parameter
>
>
>
> colParams.AppendobjCmd.CreateParameter("RetVal", adInteger,
> adParamReturnValue, 0)
>
>
>
> the program works OK. I use the same type of ADO definition to call MS
> SQLServer stored procedures and I can pick up the return value.
>
>
>
> What do I need to do to get the Postgres Function Return Value?

Maybe this?:

https://www.postgresql.org/message-id/20040531142048.1067.qmail%40web20806.mail.yahoo.com
>
>
>
> I am developing on a Windows 7 Professional (SP1) operating system.
> Using Microsoft Visual Basic 6.0(SP6), Microsoft ActiveX Data Objects
> 2.7 Library and PostgreSQL version: 9.5.4
>
>
>
>
>
> Regards,
>
>
>
> Mick Griffin
>
> Software Engineer
>
> BTS Holdings PLC
>
>
>
> DDI: +44 (0)20 8401 9003           Fax: +44 (0)20 8401 9101
>
> http://www.bts.co.uk
>
>
>
>
> BTS Holdings PLC - Registered office: BTS House, Manor Road, Wallington,
> SM6 0DD - Registered in England: 1517630
>
> [Please note that we only accept emails below 10MB in size]


--
Adrian Klaver
adrian.klaver@aklaver.com