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(dot)klaver(at)aklaver(dot)com