CREATE PROCEDURE Employee_Sales_by_Country
(
 Beginning_Date DATE,
 Ending_Date DATE
)
BEGIN
 DECLARE cur CURSOR WITH RETURN for stmt;
 PREPARE stmt from
 '
   SELECT
     Employees.Country,
     Employees.LastName,
     Employees.FirstName,
     Orders.ShippedDate,
     Orders.OrderID,
     "Order Subtotals".Subtotal AS SaleAmount
   FROM Employees
   INNER JOIN Orders ON
     Employees.EmployeeID = Orders.EmployeeID
   INNER JOIN "Order Subtotals" ON
     Orders.OrderID = "Order Subtotals".OrderID
   WHERE Orders.ShippedDate BETWEEN ? AND ?
 ';
 OPEN cur using Beginning_Date, Ending_Date;
 UNPREPARE stmt;
END

Executing the procedure in EDB Manager using the parameter values

Beginning_Date :  DATE'1997-01-01'
Ending_Date     :  DATE'1997-12-31'

returns an empty result set.

When I redefine the procedure to use the date literals instead of the
parameters, the procedure returns the expected result - 398 rows.

Ole Willy Tuv

<< Executing the procedure in EDB Manager using the parameter values

Beginning_Date :  DATE'1997-01-01'
Ending_Date     :  DATE'1997-12-31'

returns an empty result set.

<< When I redefine the procedure to use the date literals instead of the
parameters, the procedure returns the expected result - 398 rows. >>

It's obviously an issue with the procedure parameters not being set.

The following version works fine:

CREATE PROCEDURE Employee_Sales_by_Country
(
)
BEGIN
 DECLARE cur CURSOR WITH RETURN for stmt;
 PREPARE stmt from
 '
   SELECT
     Employees.Country,
     Employees.LastName,
     Employees.FirstName,
     Orders.ShippedDate,
     Orders.OrderID,
     "Order Subtotals".Subtotal AS SaleAmount
   FROM Employees
   INNER JOIN Orders ON
     Employees.EmployeeID = Orders.EmployeeID
   INNER JOIN "Order Subtotals" ON
     Orders.OrderID = "Order Subtotals".OrderID
   WHERE Orders.ShippedDate BETWEEN ? AND ?
 ';
 OPEN cur using DATE'1997-01-01', DATE'1997-12-31';
 UNPREPARE stmt;
END

Ole Willy Tuv

Ole,

<< returns an empty result set. >>

It works okay using the Northwind procedure that you sent me, so it must be
something that has already been fixed.

BTW, I thought you were going to take a break until build 2 was ready ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Hello Tim,

> BTW, I thought you were going to take a break until build 2 was ready ?

I thought that too!

--
Charalabos Michael - [Creation Power] - http://www.creationpower.gr

Tim,

< << returns an empty result set. >>>

<< It works okay using the Northwind procedure that you sent me, so it must
be something that has already been fixed. >>

The problem was that I set the date parameter in EDB Manager using the date
literal expression, e.g.:

DATE'1997-01-01'

When I set the parameter as an unqouted string without the DATE specifier:

1997-01-01

it works.

<< BTW, I thought you were going to take a break until build 2 was ready ?
>>

Yeah, developing the Northwind database creation script/procedure and
testing it, was actually the last excercise in my evaluation of EDB for now.

I'll post the Northwind procedure in the binaries group when build 2 is out
and I've checked that it works as expected.

I actually hope you'll release build 2 pretty soon now, since I assume that
you've fixed a lot of the bugs in build 1.

Ole Willy Tuv

Ole,

<< The problem was that I set the date parameter in EDB Manager using the
date literal expression, e.g.:

DATE'1997-01-01'

When I set the parameter as an unqouted string without the DATE specifier:

1997-01-01

it works. >>

Yep, that's the difference.

<< I'll post the Northwind procedure in the binaries group when build 2 is
out and I've checked that it works as expected. >>

Thanks.

<< I actually hope you'll release build 2 pretty soon now, since I assume
that you've fixed a lot of the bugs in build 1. >>

Yes, there are about 33 bugs, of which about 10-12 are categorized as
serious.  However, I suspect that this build will go a long way towards
taking care of most of the major issues, specifically the interval issues,
sub-query issues, and navigation issues.

--
Tim Young
Elevate Software
www.elevatesoft.com