I'm trying to create a stored procedure which will create additional permissions for existing groups. This is the procedure.

CREATE PROCEDURE FixPermissions()
BEGIN
 declare ContextQty int;

 set ContextQty = (select count(*) from Contexts);
 
 if ContextQty > 2 then
   insert into GroupPermissions(GroupID, PermissionID)
     select GroupID, 'Permission1'
     from Groups;

   insert into GroupPermissions(GroupID, PermissionID)
     select GroupID, 'Permission2'
     from Groups;
 else
   insert into GroupPermissions(GroupID, PermissionID)
     select GroupID, 'Permission1'
     from Groups
     where ContextID = 'Context1';

   insert into GroupPermissions(GroupID, PermissionID)
     select GroupID, 'Permission2'
     from Groups
     where ContextID = 'Context1';
 end if;
end

For some reason, I'm receiving the following error:

ElevateDB Error #700 An error was found in the statement at line 9 and column 7 (Expected VALUES but instead found select)

This is happening from EDB Manager, but also when using a script

SCRIPT
BEGIN
 EXECUTE IMMEDIATE '<same previous code using ''strings''>';
END

What can I do for avoiding this error?

David

David Novo wrote:

> I'm trying to create a stored procedure which will create additional
> permissions for existing groups. This is the procedure.
>
> CREATE PROCEDURE FixPermissions()
> BEGIN
>   declare ContextQty int;
>
>   set ContextQty = (select count(*) from Contexts);
>   
>   if ContextQty > 2 then
>     insert into GroupPermissions(GroupID, PermissionID)
>       select GroupID, 'Permission1'
>       from Groups;
>
>     insert into GroupPermissions(GroupID, PermissionID)
>       select GroupID, 'Permission2'
>       from Groups;
>   else
>     insert into GroupPermissions(GroupID, PermissionID)
>       select GroupID, 'Permission1'
>       from Groups
>       where ContextID = 'Context1';
>
>     insert into GroupPermissions(GroupID, PermissionID)
>       select GroupID, 'Permission2'
>       from Groups
>       where ContextID = 'Context1';
>   end if;
> end
>

David,

you can test this:

CREATE PROCEDURE FixPermissions()
BEGIN
 declare ContextQty int;
 DECLARE SelectCursor CURSOR FOR SelectStmt;
 
--  set ContextQty = (select count(*) from Contexts);
 PREPARE SelectStmt FROM 'select count(*) as C from Contexts';
 OPEN SelectCursor;

  FETCH FIRST FROM SelectCursor('C') INTO ContextQty;
  
  CLOSE SelectCursor;


 if ContextQty > 2 then
   EXECUTE IMMEDIATE 'insert into GroupPermissions(GroupID,
PermissionID)
     select GroupID, ''Permission1'' from Groups';

   EXECUTE IMMEDIATE 'insert into GroupPermissions(GroupID,
PermissionID)
     select GroupID, ''Permission2'' from Groups';
 else
   EXECUTE IMMEDIATE 'insert into GroupPermissions(GroupID,
PermissionID)
     select GroupID, ''Permission1''
     from Groups where ContextID = ''Context1'' ';

   EXECUTE IMMEDIATE 'insert into GroupPermissions(GroupID,
PermissionID)
     select GroupID, ''Permission2''
     from Groups
     where ContextID = ''Context1'' ';
 end if;
end



Ralf

Ralf,

did you know this variation?

instead of

  declare ContextQty int;
  DECLARE SelectCursor CURSOR FOR SelectStmt;
  PREPARE SelectStmt FROM 'select count(*) as C from Contexts';
  OPEN SelectCursor;
  FETCH FIRST FROM SelectCursor('C') INTO ContextQty;
  CLOSE SelectCursor;

just

  declare ContextQty int;
  Execute Immediate 'select count(*) into ? from Contexts' using
ContextQty;

Regards Uli

Uli,

no, but the beauty is, that everyone can learning in news groups.

Ralf

Ralf,

> no, but the beauty is, that everyone can learning in news groups.

That's true - and this is the best NG I know.

The syntax I mentioned was introduced in 2.03 and it works as well with
a query component:

SELECT Version INTO :Version
FROM Information.Tables
WHERE Name=:TableName

here is a new INTO clause for SQL SELECT statements that allow you to
specify one or more OUT parameters as the target of a SELECT statement.
Such a statement doesn't return a result set at all, which is useful for
situations where you only want one, or a few, values from a specific row
in a table. However, the use of the INTO clause requires that the SELECT
statement only return a single row.

Regards Uli

>> no, but the beauty is, that everyone can learning in news groups.
>
>That's true - and this is the best NG I know.

Definitely +1 (million or so)

>The syntax I mentioned was introduced in 2.03 and it works as well with
>a query component:

Its very good of Tim to take the time to write the manuals - I just wish he could also provide the time for me to read them

Must test that syntax because even if, as I suspect, its no faster it's cleaner and more understandable.

Roy Lambert

Roy,

> Its very good of Tim to take the time to write the manuals - I just wish he could also provide the time for me to read them

Good point.

> Must test that syntax because even if, as I suspect, its no faster it's cleaner and more understandable.

I tested the speed - it's just the same with both versions in my tests
(which makes sense).
But as you said: it's short and easy to read.

Regards Uli

Thanks to everyone for the comments.
The problem still stands. In fact, the original code for ContextQty was working, but the code for INSERT INTO using SELECT instead of VALUES is still failing. It compiles, because now that sentence is the parameter to EXECUTE IMMEDIATE, but if I run the code, it fails without even reporting an error.

The real problem here is not about many non-standard ways of doing something, but about accepting standard SQL code and giving correct results.

INSERT INTO <table>[(<column list>)]
SELECT <value list> FROM ....

is plain standard SQL code which is not being accepted by the parser. Passing it as a string avoid the compile error, but it is failing at runtime. Something must be broken in the current build.

David

David,

> is plain standard SQL code which is not being accepted by the parser. Passing it as a string avoid the compile error, but it is failing at runtime. Something must be broken in the current build.

Please post the complete procedure you are using and which fails.

Uli

Uli, my complete procedure is already posted in my first post.
If I try to run that code in ElevateDB Manager, it reports error #700.
My real code does ExecScript passing:

SCRIPT
BEGIN
<my previous code>
END

and I receive the same error, telling me that INSERT INTO <table>(col1, col2) cannot be followed by SELECT, only by VALUES...

David