Re: PG 8 INOUT parameters & ADO - Mailing list pgsql-odbc
From | Philippe Lang |
---|---|
Subject | Re: PG 8 INOUT parameters & ADO |
Date | |
Msg-id | 6C0CF58A187DA5479245E0830AF84F421D0C1A@poweredge.attiksystem.ch Whole thread Raw |
In response to | PG 8 INOUT parameters & ADO ("Philippe Lang" <philippe.lang@attiksystem.ch>) |
Responses |
Re: PG 8 INOUT parameters & ADO
(Hiroshi Inoue <inoue@tpf.co.jp>)
|
List | pgsql-odbc |
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@pzkagis.cz] Envoyé : mardi, 25. avril 2006 18:38 À : Philippe Lang Cc : pgsql-odbc@postgresql.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
Attachment
pgsql-odbc by date: