David ,

> 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:

The code in your first post has a completely wrong syntax. As Ralf
posted, you have to use something like

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

Did you try that?

Uli

Uli,

Of course, I tried every reply and all of them failed to INSERT INTO my table. Even worse, now I don't receive any error message, but nothing is inserted.
But, more than that, why do you say this syntax is wrong?

insert into GroupPermissions(GroupID, PermissionID)
  select GroupID, ''Permission1'' from Groups;

This syntax is right in most of the commercial databases.
If I use the same sentence by itself, in ElevateDB Manager (that is, not as part of a procedure) it works.

David

Uli Becker wrote:

David ,

> 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:

The code in your first post has a completely wrong syntax. As Ralf
posted, you have to use something like

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

Did you try that?

Uli

David


Have you tried it as a script in EDBManager?

Roy Lambert

David


Since I don't have your database I can't try exactly what you're doing but

SCRIPT
BEGIN
execute immediate 'insert into documents(_fkcontents,_fkUser) select _id,''xxx'' from contents';
END

works fine in EDBManager.

Can you post your database zipped up to the binaries and I'll see if I can get things working.

Roy Lambert [Team Elevate]

David,

<< If I use the same sentence by itself, in ElevateDB Manager (that is, not
as part of a procedure) it works. >>

That's a good indication that the problem is how the INSERT is being
executed in the procedure.  As long as you specify the INSERT properly for
execution in a procedure/script, then ElevateDB will use the exact same code
to execute the INSERT as when you execute the INSERT as a one-off DML
statement.

The proper way to execute such a statement is this:

EXECUTE IMMEDIATE '<INSERT Statement';

and you must make sure to escape any single quotes properly in the INSERT
statement.

For example, in the EDB Manager I created a copy of the customer table
(right-click on table, Create Copy of Table option) called customer2, and
created this script:

SCRIPT
BEGIN
  EXECUTE IMMEDIATE 'INSERT INTO customer2 (CustNo) SELECT CustNo FROM
customer';
END

ElevateDB uses dynamic SQL for all routines (procedures, scripts, etc.),
which means that all SQL is executed dynamically in a similar way to how you
execute client-side SQL with a Set SQL/Prepare/Execute type of cycle, or a
one-off Execute Now (EXECUTE IMMEDIATE).  This is done to avoid issues with
mixing DDL statements with DML statements, as is the case when you want to
create a temporary table and then perform inserts into it.  Without this
architecture, the engine has to jump through all kinds of hoops to try and
figure out what you're trying to do.  SQL Server and Oracle, for example,
have to trigger recompilations of the routine and other types of responses
to such situations.  With ElevateDB, we let *you* decide when and how you
want to manually prepare SQL statements for performance purposes.  Sometimes
you only want a one-off execution, and in those cases EXECUTE IMMEDIATE is
the solution.  In other cases, you will want to manually PREPARE a
parameterized statement, and then EXECUTE it multiple times.  BTW, I'm not
yelling, those capitalized words are SQL statements for routines.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


There's obviously something going wrong with the way David's doing it. From above

<<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.>>

Roy Lambert [Team Elevate]

Roy,

After looking at what was working in ElevateDB procedures, I replaced most of my code with ElevateDB-ish code and now it is working.

CREATE PROCEDURE FixPermissions()
BEGIN
 DECLARE ContextQty         INT;
 DECLARE GroupToRestrict    VARCHAR(40);
 DECLARE PermissionToSee    VARCHAR(50);
 DECLARE PermissionToChange VARCHAR(50);
 DECLARE GroupCursor CURSOR WITH RETURN FOR Stmt;
                            
 SET PermissionToSee    = 'Permission1';
 SET PermissionToChange = 'Permission2';
 EXECUTE IMMEDIATE 'SELECT COUNT(*) INTO ? FROM Contexts' USING ContextQty;
 
 IF ContextQty > 2 THEN
   PREPARE Stmt FROM 'SELECT * FROM Groups';
 ELSE
   PREPARE Stmt FROM 'SELECT * FROM Groups WHERE ContextID = ''StandardInternetDongleID''';
 END IF;
 
 OPEN GroupCursor;
 FETCH FIRST FROM GroupCursor('GroupID') INTO GroupToRestrict;
 WHILE NOT EOF(GroupCursor) DO
   EXECUTE IMMEDIATE 'INSERT INTO GroupPermissions(GroupID, PermissionID)
     VALUES(?, ?)' USING GroupToRestrict, PermissionToSee;

   EXECUTE IMMEDIATE 'INSERT INTO GroupPermissions(GroupID, PermissionID)
     VALUES(?, ?)' USING GroupToRestrict, PermissionToChange;

   FETCH NEXT FROM GroupCursor('GroupID') INTO GroupToRestrict;
 END WHILE;
 CLOSE GroupCursor;
END

This procedure, in addition to a previous ALTER TABLE and UPDATE, was supposed to be temporary. But after calling it, DROP PROCEDURE failed because it couldn't lock FixPermissions for exclusive access.

Is there any way in my script for keeping trying until exclusive access is granted?
In a previous script, I was trying to ALTER a recently created table and ElevateDB reported the same problem.

David

Tim,

I don't know why, but when I tried INSERT INTO ... SELECT, it worked perfectly as the only statement in EDB Manager, as part of a script SCRIPT BEGIN <> END, but not as part of a script and inside of my CREATE PROCEDURE.

In any case, I replaced my simple INSERT with a cursor and it is working... Cursors are heresy in relational databases, but they worked for me.

David

David,

> I don't know why, but when I tried INSERT INTO ... SELECT, it worked perfectly as the only statement in EDB Manager, as part of a script SCRIPT BEGIN <> END, but not as part of a script and inside of my CREATE PROCEDURE.
>
> In any case, I replaced my simple INSERT with a cursor and it is working... Cursors are heresy in relational databases, but they worked for me.

As it was mentioned several times: you have to use Execute Immediate
'..' in a script or procedure! That's why I said, your syntax is wrong.

Uli

David


When you run a query / script / procedure its prepared either manually or simply by running it. Essentially this opens the various tables etc and compiles the code (yes Tim I know that's not strictly true). When you try and drop it ElevateDB recognises this has been done and won't let you. What you have to do is manually Unprepare the query / script / procedure then DROP it.

Roy Lambert [Team Elevate]