Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 10 of 11 total |
Executing a stored procedure interactively |
Fri, Apr 11 2008 7:07 AM | Permanent Link |
Karl Ross | Bit confused at the moment. I've been through the ElevateDb manuals and found out how to create, alter, and drop a stored procedure.
I even managed to add a stored procedure to my test database (using a create procedure... statement) I can run the stored proc from the manager tool by right clicking on it. All good so far. it even produces a result set (So I know the stored logic is reasonable) Buggered if I can work out how to execute the procedure interactively in the manager tool. I thought the following statment would do it EXECUTE PROCEDURE <PROCEDURENAME>(<PARAMETERS>); Instead, I get this error message ElevateDB Error #700 An error was found in the statement at line 1 and column 1 (Expected INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, GRANT, REVOKE, BACKUP, RESTORE, REPAIR, OPTIMIZE, IMPORT, EXPORT, DISCONNECT, REMOVE but instead found execute) Any help would be greatly appreciated Thanks |
Fri, Apr 11 2008 9:17 AM | Permanent Link |
Uli Becker | Karl,
if you want to execute a procedure within the EDBManager just use the context-menu or the "Execute procedure" link. Then you can enter your parameters and execute it. What you try is to execute the procedure from an sql-statement. In your application you have to use a EDBStoredProc component; you can execute the procedure by using this code: with EDBStoredProc1 do begin StoredProcName := 'MyProcedure'; prepare; try ParamByName('Param1').asInteger := myID; ExecProc; finally unprepare; end; end; If you use the procedure more than once you should assign the StoredProcName to the component and prepare it once. Then you just need this code: with EDBStoredProc1 do begin ParamByName('Param1').asInteger := myID; ExecProc; end; Hope that helps. Regards Uli |
Fri, Apr 11 2008 10:29 AM | Permanent Link |
Rolf Frei eicom GmbH | I'm new to EDB, so sorry for that question, but does this mean, that we
can't call another procedure inside another procedure? Regards Rolf "Uli Becker" <test@test.com> schrieb im Newsbeitrag news:791650D4-27DD-4450-AC7C-5462338260DD@news.elevatesoft.com... > Karl, > > if you want to execute a procedure within the EDBManager just use the > context-menu or the "Execute procedure" link. Then you can enter your > parameters and execute it. > > What you try is to execute the procedure from an sql-statement. > > In your application you have to use a EDBStoredProc component; you can > execute the procedure by using this code: > > with EDBStoredProc1 do > begin > StoredProcName := 'MyProcedure'; > prepare; > try > ParamByName('Param1').asInteger := myID; > ExecProc; > finally > unprepare; > end; > end; > > If you use the procedure more than once you should assign the > StoredProcName to the component and prepare it once. Then you just need > this code: > > with EDBStoredProc1 do > begin > ParamByName('Param1').asInteger := myID; > ExecProc; > end; > > Hope that helps. > Regards Uli > > > |
Fri, Apr 11 2008 11:39 AM | Permanent Link |
Uli Becker | Rolf,
> I'm new to EDB, so sorry for that question, but does this mean, that we > can't call another procedure inside another procedure? No, from a procedure you can use "call" to execute another procedure. Look at the chapter on SQL/PSM (persistent stored module) statements in the manual: 7.14 CALL Calls a procedure. Syntax CALL <ProcedureName>([<Value>[,<Value>]]) Usage Use this statement to call a procedure, passing parameter values if the procedure being called has been defined with parameters. Examples -- This trigger calls the external -- SendMail procedure with which group to -- send the email to along with the new -- value of the Notes column for the customer -- being updated CREATE TRIGGER "NotesUpdate" AFTER UPDATE OF "Notes" ON "Customer" BEGIN CALL SendEmail('CustomerReps',NEWROW.Notes); END Regards Uli |
Fri, Apr 11 2008 3:42 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Karl,
<< I thought the following statment would do it >> The only way to execute a procedure in the EDB Manager via SQL is inside of another procedure, function, or script via the SQL/PSM CALL statement: http://www.elevatesoft.com/manual?action=mantopic&id=edb1sql&category=13&topic=233 -- Tim Young Elevate Software www.elevatesoft.com |
Mon, Apr 14 2008 2:51 AM | Permanent Link |
Greg | Hi Tim, I am connecting to EDB v1.07 from a website using ASP and ODBC and am yet to find a way to execute/call a stored procedure. I created a simple procedure that returns a recordset (i.e. returning and open cursor). All that is returned is "ElevateDB Error #700 An error was found in the statement at line 1 and column 1 (Expected INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, GRANT, REVOKE, BACKUP, RESTORE, REPAIR, OPTIMIZE, IMPORT, EXPORT, DISCONNECT, REMOVE but instead found CALL)". Are there ways to call a stored procedure remotly via ODBC or indeed by any method? Regards Greg |
Mon, Apr 14 2008 3:36 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Greg,
<< I am connecting to EDB v1.07 from a website using ASP and ODBC and am yet to find a way to execute/call a stored procedure. I created a simple procedure that returns a recordset (i.e. returning and open cursor). All that is returned is "ElevateDB Error #700 An error was found in the statement at line 1 and column 1 (Expected INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, GRANT, REVOKE, BACKUP, RESTORE, REPAIR, OPTIMIZE, IMPORT, EXPORT, DISCONNECT, REMOVE but instead found CALL)". Are there ways to call a stored procedure remotly via ODBC or indeed by any method? >> Unfortunately, calling a stored procedure via ODBC is not available at this time. I will see what I can do for getting this into the next 1.09 Build 2 release, which should be available very soon. -- Tim Young Elevate Software www.elevatesoft.com |
Sun, Apr 20 2008 6:27 AM | Permanent Link |
Karl Ross | Guys
Thanks for taking the time to answer the question. You explained very clearly what is available in ElevateDB, and I appreciate that. How do I handle the following in this database then? Lets say I have related data stored across a couple of tables, and have created a stored proc to get me that data back in one step. The body of it is somewhat equivalent to "select x,y,z from a left join b on (a.h = b.h)". It's done as a stored proc (MYPROC), and there is actually some fairly heavy logic in the body of the proc. Now I also have a case where I want to retreive the rows where x = 1. I would have expected to be able to write a query something like "select * from MYPROC where x = 1". |
Mon, Apr 21 2008 9:36 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Karl,
<< Now I also have a case where I want to retreive the rows where x = 1. I would have expected to be able to write a query something like "select * from MYPROC where x = 1". >> No, you can't use a stored procedure as a table expression in ElevateDB due to the fact that ElevateDB cannot project what the result set structure will look like due to the dynamic nature of the statement execution in a stored procedure. The way to do this would be to have the stored procedure create a temporary table with the result set that you further query using the temporary table name. See the front page of our web site for an example of a script that does this: http://www.elevatesoft.com/ -- Tim Young Elevate Software www.elevatesoft.com |
Mon, Apr 21 2008 10:23 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Tim
How come no announcement about the super-dooper new web site? Roy Lambert |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |