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