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.
Typed dataset designer in VS2005 - failure to create dataset based of plpgsql function (set returning)
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
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
Do you get any error?
What is you create a table adapter with the following query:
What is you create a table adapter with the following query:
Code: Select all
select * from a_set_ret_f_test()Some testing done ...
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
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