[GENERAL] Cannot access the return value of a PostgreSQL function using ADO

[Mick GRIFFIN]

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

Dim objCmd As ADODB.Command
Dim colParams As ADODB.Parameters
Dim objParam As ADODB.Parameter
Dim lngRETURN_VALUE As Long
Dim strSQL As String

    ' 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
    ' Set command properties
    With objCmd
        Set .ActiveConnection = objCon
        .CommandTimeout = m_lngCommandTimeout
        .CommandText = "updateconfigitem"
        .CommandType = adCmdStoredProc
        Set colParams = .Parameters
    End With

    ' 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)

    ' Specify input 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.Append objCmd.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?

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]