Problem executing table function with LINQ

Problem executing table function with LINQ

Postby dcb » Mon 02 Nov 2009 15:28

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?
dcb
 
Posts: 5
Joined: Tue 13 Oct 2009 23:19

Postby AndreyR » Tue 03 Nov 2009 11:44

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.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby dcb » Tue 03 Nov 2009 12:51

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.
dcb
 
Posts: 5
Joined: Tue 13 Oct 2009 23:19

Postby AndreyR » Tue 03 Nov 2009 14:01

Use the DataContext.Transaction property to begin the transaction and commit.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby AndreyR » Mon 16 Nov 2009 12:43

The problem is fixed in the upcoming build of dotConnect for PostgreSQL.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16


Return to dotConnect for PostgreSQL