Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 10 of 24 total |
Non-standard behavior of INSERT INTO in stored procedure |
Fri, May 20 2011 5:33 PM | Permanent Link |
Daniel Zimmerman | 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 |
Sat, May 21 2011 2:42 AM | Permanent Link |
Ralf Bieber EDV Dienstleistungen Ralf Bieber | 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 |
Sun, May 22 2011 8:51 AM | Permanent Link |
Uli Becker | 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 |
Mon, May 23 2011 2:02 AM | Permanent Link |
Ralf Bieber EDV Dienstleistungen Ralf Bieber | Uli,
no, but the beauty is, that everyone can learning in news groups. Ralf |
Mon, May 23 2011 2:36 AM | Permanent Link |
Uli Becker | 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 |
Mon, May 23 2011 3:12 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | >> 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 |
Mon, May 23 2011 5:49 AM | Permanent Link |
Uli Becker | 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 |
Mon, May 23 2011 11:03 AM | Permanent Link |
Daniel Zimmerman | 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 |
Mon, May 23 2011 2:48 PM | Permanent Link |
Uli Becker | 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 |
Mon, May 23 2011 3:04 PM | Permanent Link |
Daniel Zimmerman | 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 |
Page 1 of 3 | Next Page » | |
Jump to Page: 1 2 3 |
This web page was last updated on Tuesday, May 14, 2024 at 07:14 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |