[SQL] Returning a recordset and filling datatable in a .NET application

[Jonas Wouters]

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