Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 4 of 4 total |
Example Stored Procedure |
Fri, Oct 9 2015 10:23 AM | Permanent Link |
Shedden | Hi All,
I'm struggling a bit trying to learn ElevateDb stored procedures. I took one from the forum at this link: http://www.elevatesoft.com/supportfaq?action=view&category=edb&question=effective_privileges Here is the code: CREATE PROCEDURE "EffectiveTablePrivileges" (IN "UserName" VARCHAR COLLATE ANSI, IN "TableName" VARCHAR COLLATE ANSI) BEGIN DECLARE TempCursor CURSOR WITH RETURN FOR TempStmt; PREPARE TempStmt FROM 'SELECT TablePrivileges.* FROM Information.TablePrivileges INNER JOIN (SELECT Name FROM Configuration.Users WHERE Name=? UNION ALL SELECT Name FROM Configuration.UserRoles WHERE GrantedTo=?) Authorizations ON TablePrivileges.GrantedTo=Authorizations.Name WHERE TablePrivileges.Name=?'; EXECUTE TempStmt USING UserName, UserName, TableName; END I do have the statement terminator set to a semicolon in the EDBManager editor preferences. When I try to execute this statement in version 22b1 I get this error: ElevateDB Error #700 An error was found in the statement at line 4 and column 1 (Missing That points to the first column on the blank line after DECLARE. Many of my own attempts result in a similar error. I was trying this: CREATE PROCEDURE "updateWorkWeek" () BEGIN execute immediate 'update "workWeek" u set "hoursWorked" = (select sum(hours) from jimtime j where j.date between u.weekEndingDate-interval ''6'' DAY and u.weekEndingDate), "dollarsWork" = (select sum(dollars) from jimtime j where j.date between u.weekEndingDate-interval ''6'' DAY and u.weekEndingDate'); END DESCRIPTION 'Puts worked hours and dollars into the workWeek from JimTime records.' VERSION 1.00 That errors out with: ElevateDB Error #700 An error was found in the statement at line 7 and column 1 (Missing which is just before the END statement. The SQL statement itself executes outside the procedure definition. In both cases the character immediately preceding the row/column error address is a semicolon, so I find the error message confusing. I've played around a lot with the syntax and can't get beyond the 700 error. I appreciate any help. If I can't get the forum example to work, I'm probably missing something very basic. If there are any good books on SQL2003 that might help me, please feel free to make a recommendation. Thanks in Advance! Jim Shedden |
Fri, Oct 9 2015 10:34 AM | Permanent Link |
Shedden | Hi,
A quick note on the above. The emoticons are replacing semicolon-close parenthesis sequences. Jim S. |
Fri, Oct 9 2015 11:03 AM | Permanent Link |
Raul Team Elevate | On 10/9/2015 10:23 AM, Shedden wrote:
> ElevateDB Error #700 An error was found in the statement at line 4 and column 1 (Missing That seems to be the source of the problem - change it to default "!" and your procedure should work fine (it does here with !) Raul |
Fri, Oct 9 2015 12:24 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Jim,
<< I do have the statement terminator set to a semicolon in the EDBManager editor preferences. When I try to execute this statement in version 22b1 I get this error: >> Yeah, you need to use a different statement terminator. The semicolon is used within procedures/functions, so it can't be used as a statement terminator for a series of statements in the EDB Manager. << ElevateDB Error #700 An error was found in the statement at line 4 and column 1 (Missing >> Can't you read ? It's telling you that you're missing a wink there.... Tim Young Elevate Software www.elevatesoft.com |
This web page was last updated on Monday, April 29, 2024 at 05:23 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |