Re: [SQL] Returning a recordset and filling datatable in a .NET
От | Achilleus Mantzios |
---|---|
Тема | Re: [SQL] Returning a recordset and filling datatable in a .NET |
Дата | |
Msg-id | Pine.LNX.4.44.0210311805000.3526-100000@matrix.gatewaynet.com обсуждение исходный текст |
Ответ на | Returning a recordset and filling datatable in a .NET applicatio n (Jonas Wouters <Jonas@vanroey.be>) |
Список | pgsql-cygwin |
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
В списке pgsql-cygwin по дате отправления: