Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 6 of 6 total |
Parameterized procedure query returns empty result |
Sat, Mar 24 2007 6:53 AM | Permanent Link |
"Ole Willy Tuv" | 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 |
Sat, Mar 24 2007 7:59 AM | Permanent Link |
"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 |
Mon, Mar 26 2007 9:12 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Tue, Mar 27 2007 2:43 AM | Permanent Link |
Charalabos Michael | 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 |
Tue, Mar 27 2007 7:56 AM | Permanent Link |
"Ole Willy Tuv" | 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 |
Wed, Mar 28 2007 6:40 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
This web page was last updated on Saturday, May 4, 2024 at 09:18 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |