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

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(at)matrix(dot)gatewaynet(dot)com
mantzios(at)softlab(dot)ece(dot)ntua(dot)gr