|
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 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] |
