Skip site navigation (1) Skip section navigation (2)

PG 8 INOUT parameters & ADO

From: "Philippe Lang" <philippe(dot)lang(at)attiksystem(dot)ch>
To: <pgsql-odbc(at)postgresql(dot)org>
Subject: PG 8 INOUT parameters & ADO
Date: 2006-04-25 16:16:47
Message-ID: 6C0CF58A187DA5479245E0830AF84F421D0C18@poweredge.attiksystem.ch (view raw or flat)
Thread:
Lists: pgsql-odbc
Hi,

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

------------

I'm trying to call this procedure with ADO (latest version under XP),
through the ODBC driver version 8.01.02.00, like this:

------------

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

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)

oCommand.Execute

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

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

End Function

------------

It fails, with error -2147217887 each time.

Is it possible to query stored procedures like this with the PG ODBC driver?


Thanks!

----------------------------------
Philippe Lang, Ing. Dipl. EPFL
Attik System
rte de la Fonderie 2
1700 Fribourg
Switzerland
http://www.attiksystem.ch

Tel:  +41 (26) 422 13 75
Fax:  +41 (26) 422 13 76 

Responses

pgsql-odbc by date

Next:From: Ludek FinstrleDate: 2006-04-25 16:37:52
Subject: Re: PG 8 INOUT parameters & ADO
Previous:From: Andrew NgDate: 2006-04-25 14:43:59
Subject: Re: Support for PostgreSQL 8.1.3 stable

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group