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

Hi,

A quick note on the above. The emoticons are replacing semicolon-close parenthesis sequences.

Jim S.

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

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