Typed dataset designer in VS2005 - failure to create dataset based of plpgsql function (set returning)

Typed dataset designer in VS2005 - failure to create dataset based of plpgsql function (set returning)

Postby Wojtek » Tue 30 Oct 2007 17:38

Hello

I wonder if you can advise on my specific problem.
I have just downloaded trial version of PostgreSQLDirect .NET 3.25.19.
I am using Postgres database ver 8.1 (linux) and 8.2 (Win) - same results in both cases.

I try to drag set returning function from server explorer into dataset designer area. When I tried that in previous version (2.25.27) dataset was created automatically (table adapter and Fill() + Get..() methods).

I version 3.25.19 however it seems only to work with pgsql tables - not with set returning functions !

Any idea what is the problem here ?
Have you removed that functionality from 3.25 ?

I was thinking to upgrade to 3.25.19 but not I am a bit puzzled...
I can provide samples on request (if needed).

Best regards,
Wojtek

PS. Previous version of PostgreSQLDirect was duly unninstalled (checked with GAC) before trial ver. 3.25.19 was installed.
Wojtek
 
Posts: 14
Joined: Sun 20 Nov 2005 18:51
Location: Warsaw

Postby Alexey » Wed 31 Oct 2007 09:04

Please post the definition of your database objects needed to reproduce the problem.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby Wojtek » Wed 31 Oct 2007 14:45

I experienced this problem on following table/function:

CREATE TABLE a_test_orders
(
id serial NOT NULL,
provider character varying(30) DEFAULT ''::character varying,
info character varying(50) DEFAULT ''::character varying,
info2 character varying(30) DEFAULT ''::character varying,
CONSTRAINT "PK_ID" PRIMARY KEY (id)
)
WITH (OIDS=FALSE);


CREATE OR REPLACE FUNCTION a_set_ret_f_test()
RETURNS SETOF a_test_orders AS
$BODY$

DECLARE row a_test_orders%rowtype;

BEGIN
FOR row IN
SELECT id, provider, info, info2 FROM a_test_orders
LOOP
RETURN NEXT row;
END LOOP;
RETURN;

END
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

Best regards,
Wojtek
Wojtek
 
Posts: 14
Joined: Sun 20 Nov 2005 18:51
Location: Warsaw

Postby Alexey » Thu 01 Nov 2007 10:12

Do you get any error?
What is you create a table adapter with the following query:
Code: Select all
select * from a_set_ret_f_test()
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Some testing done ...

Postby Wojtek » Thu 01 Nov 2007 12:40

Alexey

I made some testing with following results.
(„SP” below means my set returning function a_set_ret_f_test() )

1.Drag'n drop SP to dataset designer area (result: failure)
I do not get any error during drag'n drop. Nor during compiling...
However in result an object „QueriesTableAdapter” is created (?)
Dataset does not contain any table adapter nor data table - is unusable !.


2.Dataset designer: Right click-> Add -> TableAdapterter (result: partial success)
Table adapter wizard starts. I select radio button: „Use existing procedure”.
I can select SP from the list.
Finally wizard ends with table adapter properly configured but with no datatable definition !
I can use table adapter - fill method works (!) and data is retrieved from database.
However datatable class „a_set_ret_f_testDataTable” does not contain any columns !


3.Dataset designer: Right click-> Add -> TableAdapterter (result: failure)
Table adapter wizard starts. I select radio button: „Use SQL statement”.
I put „select * from a_set_ret_f_test()” as sql statement.
Wizard completes with error:
„The wizard detected when configuring the Tableadapter: Fill: Details: generated SELECT statement. Error in FROM clause: near '(' . Unable to parse query text.”


4.„Hybrid approach” (result: failure)

I created working tableadapter as in p.2. In dataset designer I added manually column to data table (Right click-> Add -> Column).
After compilation data table object showed all columns as properties.

Success ? Well, almost ... :(
I tried to add insert query to table adapter (Right click-> Configure)...
Another problem shows up: column mapping cannot be done in the wizard (drop down list is empty...



Conclusion:
Data set still unusable as it misses Insert /Update commands.
Actually this was most desired part as behind Update storage procedure I intended to put quite complex DB processing !
Any idea how to overcome this mapping problem ?
Best regard,
Wojtek
Wojtek
 
Posts: 14
Joined: Sun 20 Nov 2005 18:51
Location: Warsaw

Postby Alexey » Fri 02 Nov 2007 07:58

We will investigate all the problems.
Look forward to hearing from me again.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby Wojtek » Wed 14 Nov 2007 14:16

Alexey

Any progress/news ?

Best regards,
Wojtek
Wojtek
 
Posts: 14
Joined: Sun 20 Nov 2005 18:51
Location: Warsaw

Postby Alexey » Thu 15 Nov 2007 12:03

We are resolving the problem right now. Support of tabular functions appears to be complicated, and may take some time.
Thanks for your patience.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Fixed

Postby SecureGen » Tue 20 Nov 2007 16:53

We have fixed this problem for you.
Look forward to the next build.
SecureGen
Devart Team
 
Posts: 133
Joined: Thu 08 Sep 2005 06:27


Return to dotConnect for PostgreSQL