Re: PG 8 INOUT parameters & ADO

[Hiroshi Inoue <inoue(at)tpf(dot)co(dot)jp>]

Philippe Lang wrote:
> 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

Thers may be another way.
oCommand.Execute(, , ADODB.ExecuteOptionEnum.adExecuteNoRecords)
a = oCommand.Parameters(0).Value
b = oCommand.Parameters(1).Value
r1 = oCommand.Parameters(2).Value
r2 = oCommand.Parameters(3).Value

regards,
Hiroshi Inoue