Обсуждение: Returning a recordset and filling datatable in a .NET applicatio n

Hi,

I have a problem with using .NET and PostgreSQL. In a previous thread called
"" I noticed that it is possible to use and create functions that return
tuples or RecordSets. I now want to use them in .NET.

I followed the instructions which are available at :
http://developer.postgresql.org/docs/postgres/plpgsql-cursors.html these
work in the standard psql client :

I created the function using the following :

CREATE FUNCTION public.p0012_fetch_supplier(varchar, varchar, refcursor)
RETURNS refcursor AS '
BEGIN
    OPEN $3 FOR SELECT * FROM "SUPP_T" WHERE "CODE" = $1 AND "DSRCODE" =
$2;
                RETURN $3;
END;
'  LANGUAGE 'plpgsql' VOLATILE;


Then I did these in psql :

SBA=# BEGIN;
BEGIN
SBA=# SELECT p0012_fetch_supplier('1','1','funccursor');
 p0012_fetch_supplier
----------------------
 funccursor
(1 row)

SBA=# FETCH ALL IN funccursor;
DSRCODE | CODE
 1       | 1
(1 row)

SBA=# COMMIT;
COMMIT

The results tell me that the function works. (whoohoo)

But when I do the same thing in a .Net application, I get an empty DataTable
(row count = 0)

This is what I do in .Net 5and I do know that most of you people dislike
.NET and actually .. that is not the issue for me :), I just want this to
work because we are going to need this for our application)


        Dim CN As New Microsoft.Data.Odbc.OdbcConnection("DSN=PostgreSQL30")
        Dim CM As New Microsoft.Data.Odbc.OdbcCommand("BEGIN; SELECT
        p0012_fetch_supplier('1','1','funccursor'); FETCH ALL IN
        funccursor; COMMIT;", CN)
        Dim DA As New Microsoft.Data.Odbc.OdbcDataAdapter(CM)
        Dim DT As New DataTable()
        Try

            CM.CommandType = CommandType.Text
            DA.SelectCommand.Connection.Open()
            DA.Fill(DT)
            DA.SelectCommand.Connection.Close()
        Catch ex As Microsoft.Data.Odbc.OdbcException
            Debug.WriteLine(ex.Message)
            Debug.WriteLine(ex.Source)
            Debug.WriteLine(ex.HelpLink)
        Finally
            CN.Close()

It does not raise an exception so there are no real 'errors'; it just does
not give 'data' to the ADO.NET container.

If I am asking this in the wrong mailinglist, then please point me in the
right direction. I don't think that I will get a answer at Microsoft.com so
that is why I ask it here ..


Thx in advance
Jonas

On Thu, 31 Oct 2002, Jonas Wouters wrote:

Hi altho i doubt anybody here has any .net experience,
i'll give my bet.

try to place your sql commands in such a way the the
"FETCH ALL" command is the last in the stream,
(that is get rid of begin,commit statements)

>
>
> Hi,
>
> I have a problem with using .NET and PostgreSQL. In a previous thread called
> "" I noticed that it is possible to use and create functions that return
> tuples or RecordSets. I now want to use them in .NET.
>
> I followed the instructions which are available at :
> http://developer.postgresql.org/docs/postgres/plpgsql-cursors.html these
> work in the standard psql client :
>
> I created the function using the following :
>
> CREATE FUNCTION public.p0012_fetch_supplier(varchar, varchar, refcursor)
> RETURNS refcursor AS '
> BEGIN
>     OPEN $3 FOR SELECT * FROM "SUPP_T" WHERE "CODE" = $1 AND "DSRCODE" =
> $2;
>                 RETURN $3;
> END;
> '  LANGUAGE 'plpgsql' VOLATILE;
>
>
> Then I did these in psql :
>
> SBA=# BEGIN;
> BEGIN
> SBA=# SELECT p0012_fetch_supplier('1','1','funccursor');
>  p0012_fetch_supplier
> ----------------------
>  funccursor
> (1 row)
>
> SBA=# FETCH ALL IN funccursor;
> DSRCODE | CODE
>  1       | 1
> (1 row)
>
> SBA=# COMMIT;
> COMMIT
>
> The results tell me that the function works. (whoohoo)
>
> But when I do the same thing in a .Net application, I get an empty DataTable
> (row count = 0)
>
> This is what I do in .Net 5and I do know that most of you people dislike
> .NET and actually .. that is not the issue for me :), I just want this to
> work because we are going to need this for our application)
>
>
>         Dim CN As New Microsoft.Data.Odbc.OdbcConnection("DSN=PostgreSQL30")
>         Dim CM As New Microsoft.Data.Odbc.OdbcCommand("BEGIN; SELECT
>         p0012_fetch_supplier('1','1','funccursor'); FETCH ALL IN
>         funccursor; COMMIT;", CN)
>         Dim DA As New Microsoft.Data.Odbc.OdbcDataAdapter(CM)
>         Dim DT As New DataTable()
>         Try
>
>             CM.CommandType = CommandType.Text
>             DA.SelectCommand.Connection.Open()
>             DA.Fill(DT)
>             DA.SelectCommand.Connection.Close()
>         Catch ex As Microsoft.Data.Odbc.OdbcException
>             Debug.WriteLine(ex.Message)
>             Debug.WriteLine(ex.Source)
>             Debug.WriteLine(ex.HelpLink)
>         Finally
>             CN.Close()
>
> It does not raise an exception so there are no real 'errors'; it just does
> not give 'data' to the ADO.NET container.
>
> If I am asking this in the wrong mailinglist, then please point me in the
> right direction. I don't think that I will get a answer at Microsoft.com so
> that is why I ask it here ..
>
>
> Thx in advance
> Jonas
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

==================================================================
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel:    +30-10-8981112
fax:    +30-10-8981877
email:  achill@matrix.gatewaynet.com
        mantzios@softlab.ece.ntua.gr