Thread: PG 8 INOUT parameters & ADO

PG 8 INOUT parameters & ADO

From

"Philippe Lang"

Date:

25 April 2006, 16:17:03

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

Attachment

smime.p7s

Re: PG 8 INOUT parameters & ADO

From

Ludek Finstrle

Date:

25 April 2006, 16:38:12

> 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

Re: PG 8 INOUT parameters & ADO

From

"Philippe Lang"

Date:

25 April 2006, 17:06:19

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

smime.p7s

Re: PG 8 INOUT parameters & ADO

From

"Philippe Lang"

Date:

25 April 2006, 17:17:21

Ludek,

There is small problem apparently: if I replace

---

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)

---

With

---

oCommand.Parameters.refresh

oCommand.Parameters("a").value = a
oCommand.Parameters("b").value = b

---


... MS Access crashes at the "oCommand.Parameters.refresh".


I have tried enabling MyLog, but nothing partularly interesting shows up in
there.



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

smime.p7s

Re: PG 8 INOUT parameters & ADO

From

Ludek Finstrle

Date:

25 April 2006, 18:17:05

> ... MS Access crashes at the "oCommand.Parameters.refresh".
>
> I have tried enabling MyLog, but nothing partularly interesting shows up in
> there.

IMHO there is some unimplemented ODBC functions in psqlodbc (like
SQLDescribeParams - I write it from my mind)
08.01 branch doesn't even support Append, CreateParameter way.

Regards,

Luf

Re: PG 8 INOUT parameters & ADO

From

Hiroshi Inoue

Date:

25 April 2006, 21:32:29

Philippe Lang wrote:

>Ludek,
>
>There is small problem apparently: if I replace
>
>

Please replace the dll by the one at
 http://www.geocities.jp/inocchichichi/psqlodbc/index.html .

regards,
Hiroshi Inoue

Re: PG 8 INOUT parameters & ADO

From

Hiroshi Inoue

Date:

26 April 2006, 03:39:56

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

Re: PG 8 INOUT parameters & ADO

From

"Philippe Lang"

Date:

26 April 2006, 07:57:01

Hi Hiroshi,

It works fine, thanks.

Now I can use:

oCommand.Parameters.Refresh
oCommand.Parameters(0).Value = a
oCommand.Parameters(1).Value = b

Just one question, which might be ADO-specific... Is the refresh capable of
recognizing the parameters names?
After the refresh, all parameters names are set to "". If they inhereited
the names of the parameters in Postgreql, that would allow us to do
something like:

oCommand.Parameters.Refresh
oCommand.Parameters("a").Value = a
oCommand.Parameters("b").Value = b

That would avoid confusion and bugs when giving initial values to the
parameters...

-----Message d'origine-----
De : Hiroshi Inoue [mailto:inoue@tpf.co.jp]
Envoyé : mardi, 25. avril 2006 23:32
À : Philippe Lang
Cc : Ludek Finstrle; pgsql-odbc@postgresql.org
Objet : Re: [ODBC] PG 8 INOUT parameters & ADO

Philippe Lang wrote:

>Ludek,
>
>There is small problem apparently: if I replace
>
>

Please replace the dll by the one at
 http://www.geocities.jp/inocchichichi/psqlodbc/index.html .

regards,
Hiroshi Inoue

Attachment

smime.p7s

Re: PG 8 INOUT parameters & ADO

From

"Philippe Lang"

Date:

28 April 2006, 07:51:21

Hi,

Sorry to ask this question again, but is there a chance ADO
"oCommand.Parameters.Refresh" could get the parameters names configured in
Postgresql? The idea is to be able to do something like:

oCommand.Parameters.Refresh
oCommand.Parameters("a").Value = a
oCommand.Parameters("b").Value =

Instead of

oCommand.Parameters.Refresh
oCommand.Parameters(0).Value = a
oCommand.Parameters(1).Value = b

Thanks, bye

Philippe

-----Message d'origine-----
De : pgsql-odbc-owner@postgresql.org
[mailto:pgsql-odbc-owner@postgresql.org] De la part de Philippe Lang
Envoyé : mercredi, 26. avril 2006 09:57
À : Hiroshi Inoue
Cc : pgsql-odbc@postgresql.org
Objet : Re: [ODBC] PG 8 INOUT parameters & ADO

Hi Hiroshi,

It works fine, thanks.

Now I can use:

oCommand.Parameters.Refresh
oCommand.Parameters(0).Value = a
oCommand.Parameters(1).Value = b

Just one question, which might be ADO-specific... Is the refresh capable of
recognizing the parameters names?
After the refresh, all parameters names are set to "". If they inhereited
the names of the parameters in Postgreql, that would allow us to do
something like:

oCommand.Parameters.Refresh
oCommand.Parameters("a").Value = a
oCommand.Parameters("b").Value = b

That would avoid confusion and bugs when giving initial values to the
parameters...

-----Message d'origine-----
De : Hiroshi Inoue [mailto:inoue@tpf.co.jp] Envoyé : mardi, 25. avril 2006
23:32 À : Philippe Lang Cc : Ludek Finstrle; pgsql-odbc@postgresql.org Objet
: Re: [ODBC] PG 8 INOUT parameters & ADO

Philippe Lang wrote:

>Ludek,
>
>There is small problem apparently: if I replace
>
>

Please replace the dll by the one at
 http://www.geocities.jp/inocchichichi/psqlodbc/index.html .

regards,
Hiroshi Inoue

Attachment

smime.p7s

Re: PG 8 INOUT parameters & ADO

From

Hiroshi Inoue

Date:

28 April 2006, 08:51:02

Philippe Lang wrote:
> Hi,
>
> Sorry to ask this question again, but is there a chance ADO
> "oCommand.Parameters.Refresh" could get the parameters names configured in
> Postgresql? The idea is to be able to do something like:
>
> oCommand.Parameters.Refresh
> oCommand.Parameters("a").Value = a
> oCommand.Parameters("b").Value =
>
> Instead of
>
> oCommand.Parameters.Refresh
> oCommand.Parameters(0).Value = a
> oCommand.Parameters(1).Value = b

Could you try the latest dll at
   http://www.geocities.jp/inocchichichi/psqlodbc/index.html ?

regards,
Hiroshi Inoue

Re: PG 8 INOUT parameters & ADO

From

"Philippe Lang"

Date:

28 April 2006, 09:07:07

Perfect, thanks a lot Hiroshi.

-----Message d'origine-----
De : Hiroshi Inoue [mailto:inoue@tpf.co.jp]
Envoyé : vendredi, 28. avril 2006 10:50
À : Philippe Lang
Cc : pgsql-odbc@postgresql.org
Objet : Re: [ODBC] PG 8 INOUT parameters & ADO

Philippe Lang wrote:
> Hi,
>
> Sorry to ask this question again, but is there a chance ADO
> "oCommand.Parameters.Refresh" could get the parameters names
> configured in Postgresql? The idea is to be able to do something like:
>
> oCommand.Parameters.Refresh
> oCommand.Parameters("a").Value = a
> oCommand.Parameters("b").Value =
>
> Instead of
>
> oCommand.Parameters.Refresh
> oCommand.Parameters(0).Value = a
> oCommand.Parameters(1).Value = b

Could you try the latest dll at
   http://www.geocities.jp/inocchichichi/psqlodbc/index.html ?

regards,
Hiroshi Inoue

Attachment

smime.p7s