Re: PG 8 INOUT parameters & ADO

Thanks Ludek, it works like a charm with the "experimental" driver.

For those who want to play with that, here some code to test... I think this
an elegant interface between MS Access, Postgreql and perl...

------------------ PG

CREATE FUNCTION perl_test(a inout integer, b inout integer, r1 out integer,
r2 out integer) AS
'
my ($a, $b) = @_;

$r1 = $a + $b;
$r2 = $a * $b;

if ($a > $b)
{
return {a => $a + 1, b => $b, r1 => $r1, r2 => $r2};
}
else
{
return{a => $b, b => $a + 1, r1 => $r1, r2 => $r2};
}

return;

' LANGUAGE plperl;

------------------ VBA

Public Function perl_test(ByRef a As Integer, ByRef b As Integer, ByRef r1
As Integer, ByRef r2 As Integer)
On Error GoTo ErrorHandler

Dim oConnection As ADODB.Connection
Dim oCommand As ADODB.Command
Dim oRecordset As ADODB.Recordset

Set oConnection = New ADODB.Connection
oConnection.Open "DSN=test"

Set oCommand = New ADODB.Command

Set oCommand.ActiveConnection = oConnection
oCommand.CommandText = "perl_test"
oCommand.CommandType = adCmdStoredProc

oCommand.Parameters.Append _
oCommand.CreateParameter("a", adInteger, adParamInputOutput, , a)

oCommand.Parameters.Append _
oCommand.CreateParameter("b", adInteger, adParamInputOutput, , b)

oCommand.Parameters.Append _
oCommand.CreateParameter("r1", adInteger, adParamOutput)

oCommand.Parameters.Append _
oCommand.CreateParameter("r2", adInteger, adParamOutput)

Set oRecordset = oCommand.Execute

a = oRecordset("a")
b = oRecordset("b")
r1 = oRecordset("r1")
r2 = oRecordset("r2")

oConnection.Close
Set oConnection = Nothing
Set oCommand = Nothing
Exit Function

ErrorHandler:
MsgBox "Error Number = " & Err.Number & ", Description = " & _
Err.Description, vbCritical, "GetNameDescFromSampleTable Error"

End Function

------------------ PG

Public Sub test()

Dim a As Integer
Dim b As Integer
Dim r1 As Integer
Dim r2 As Integer

a = 2
b = 8

Debug.Print "a = " & a
Debug.Print "b = " & b
Debug.Print "r1 = " & r1
Debug.Print "r2 = " & r2

perl_test a, b, r1, r2

Debug.Print "a = " & a
Debug.Print "b = " & b
Debug.Print "r1 = " & r1
Debug.Print "r2 = " & r2

End Sub

------------------ EXECUTION

Which gives:

test
a = 2
b = 8
r1 = 0
r2 = 0
a = 8
b = 3
r1 = 10
r2 = 16

Philippe

-----Message d'origine-----
De : Ludek Finstrle [mailto:luf(at)pzkagis(dot)cz]
Envoyé : mardi, 25. avril 2006 18:38
À : Philippe Lang
Cc : pgsql-odbc(at)postgresql(dot)org
Objet : Re: [ODBC] PG 8 INOUT parameters & ADO

> I have a PG 8.1.3 server with the following PL/PERL procedure:
>
> ------------
>
> CREATE FUNCTION perl_test(a inout integer, b inout integer, r1 out
> integer,
> r2 out integer) AS

...

> ------------
>
> I'm trying to call this procedure with ADO (latest version under XP),
> through the ODBC driver version 8.01.02.00, like this:
>
> ------------
>
> It fails, with error -2147217887 each time.
>
> Is it possible to query stored procedures like this with the PG ODBC
driver?

The psqlodbc 08.01.0200 doesn't support out parameters. Please try enhanced
experimental branch (07.03.026X) from pgfoundry.org.
This development branch is now CVS tip and it is the base for next stable
release (IMHO it is more stable then 08.01.0200).

Regards,

Luf