From: Achilleus Mantzios <achill@matrix.gatewaynet.com>
To: Jonas Wouters <Jonas@vanroey.be>
Cc: 'pgsql-sql@postgresql.org' <pgsql-sql@postgresql.org>
Cc: 'pgsql-cygwin@postgresql.org' <pgsql-cygwin@postgresql.org>
Subject: Re: [SQL] Returning a recordset and filling datatable in a .NET
Date: Thu, 31 Oct 2002 18:07:35 +0200 (EET)
Message-ID: <Pine.LNX.4.44.0210311805000.3526-100000@matrix.gatewaynet.com> (raw)
In-Reply-To: <A6FF006530424A42AA2924E5CCBE35293B9B20@msx.vanroey.be>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: achill@matrix.gatewaynet.com, Jonas@vanroey.be, pgsql-cygwin@postgresql.org
Subject: Re: [SQL] Returning a recordset and filling datatable in a .NET
In-Reply-To: <Pine.LNX.4.44.0210311805000.3526-100000@matrix.gatewaynet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox