Problem executing table function with LINQ

[dcb]

I have a stored procedure that returns a recordset. Here is how the function looks like:

Code: Select all

CREATE OR REPLACE FUNCTION sales_stats(IN start_date timestamp with time zone, IN end_date timestamp with time zone, OUT sale_date timestamp with time zone, OUT amount numeric, OUT quantity integer, OUT orders integer)
  RETURNS SETOF record AS
$BODY$

SELECT date_trunc('day', placed_at), SUM(soi.value), CAST(SUM(soi.quantity) AS int), CAST(COUNT(DISTINCT so.shop_order_id) AS int)
FROM shop_order so JOIN shop_order_item soi ON so.shop_order_id = soi.shop_order_id WHERE so.order_status != 'X' AND so.placed_at >= $1 AND so.placed_at <= $2 GROUP BY date_trunc('day', placed_at)
ORDER BY date_trunc('day', placed_at);

$BODY$
  LANGUAGE 'sql'

I've added the function in the Entity Developer, manually removed the output parameters (they are only used to describe the structure of the returned record set) and here is the debug output when I try to run it:

Code: Select all

dotConnect for PostgreSQL:  - Prepare: SELECT * FROM sales_stats($1, $2)
dotConnect for PostgreSQL:  - Execute: SELECT * FROM sales_stats($1, $2)
dotConnect for PostgreSQL:  - Prepare: FETCH ALL FROM "03/10/2009 00:00:00.000000+03 AD"
dotConnect for PostgreSQL:  - cursor "03/10/2009 00:00:00.000000+03 AD" does not exist

"03/10/2009 00:00:00.000000+03 AD" Is the value of the first parameter.
The function works as expected when executed manually in pg console.

Any solutions for this?

[AndreyR]

The problem is associated with the fact that the attempt to read the cursor is made after the cursor is disposed.
We are investigating this issue.
As a temporary workaround I recommend you to enclose the stored procedure call in local transaction.

[dcb]

Can you give me a hint for that? I've tried to start a transaction on the connection but it did not have any effect.

[AndreyR]

Use the DataContext.Transaction property to begin the transaction and commit.

[AndreyR]

The problem is fixed in the upcoming build of dotConnect for PostgreSQL.