Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 9 of 9 total |
call procedure dynamically |
Mon, May 18 2015 8:41 AM | Permanent Link |
ccr Asar GmbH | hello,
i need your help i implemented a dispatcher-system for my customer in the database. basically it is simply a table that contains a procedure name for a current scenario. i thought that calling this procedure (which is only a string/varchar) is as straightforward as: execute immediate('CALL ' + myProcedureNameVariable + '()'); however this does not seem to work am i missing something? your help is greatly appreciated Hendrik |
Mon, May 18 2015 8:53 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | ccr
>hello, >i need your help >i implemented a dispatcher-system for my customer in the database. >basically it is simply a table that contains a procedure name for a current scenario. >i thought that calling this procedure (which is only a string/varchar) is as straightforward as: >execute immediate('CALL ' + myProcedureNameVariable + '()'); >however this does not seem to work > >am i missing something? Never having tried this I'm guessing. EXECUTE IMMEDIATE expects a valid SQL statement. CALL isn't - its SQL/PSM so you have no chance of it working. Since its very possible I'm wrong what error was reported by ElevateDB? Roy Lambert |
Mon, May 18 2015 9:04 AM | Permanent Link |
ccr Asar GmbH | Roy,
thank you for the quick response. it seems you guessed rightly (which doesn't help me error: P_NTF_DISPATCHNOTIFICATION:700:An error was found in the statement at line 12 and column 28 (Expected SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, GRANT, REVOKE, BACKUP, RESTORE, COMPARE, SET BACKUPS, MIGRATE, REPAIR, VERIFY, OPTIMIZE, IMPORT, EXPORT, DISCONNECT, REMOVE, PUBLISH, UNPUBLISH, SAVE, LOAD, SET UPDATES, COPY FILE, RENAME FILE, DELETE FILE but instead found CALL) this is a shame since it works exactly like this in oracle Roy Lambert wrote: ccr >hello, >i need your help >i implemented a dispatcher-system for my customer in the database. >basically it is simply a table that contains a procedure name for a current scenario. >i thought that calling this procedure (which is only a string/varchar) is as straightforward as: >execute immediate('CALL ' + myProcedureNameVariable + '()'); >however this does not seem to work > >am i missing something? Never having tried this I'm guessing. EXECUTE IMMEDIATE expects a valid SQL statement. CALL isn't - its SQL/PSM so you have no chance of it working. Since its very possible I'm wrong what error was reported by ElevateDB? Roy Lambert Hendrik |
Mon, May 18 2015 10:25 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | ccr
Depending on what your procedure does I have a sneaky idea - can you post the code you're trying to CALL please Roy Lambert |
Mon, May 18 2015 10:31 AM | Permanent Link |
ccr Asar GmbH | hello roy,
of course i can. the dispatcher functions have only one parameter of type integer. so it is the line EXECUTE IMMEDIATE 'CALL ' + cDispatcherProcedure + '(?);' USING P_NOTIFICATIONID; CREATE PROCEDURE "P_NTF_DISPATCHNOTIFICATION" (IN "P_NOTIFICATIONID" INTEGER) BEGIN DECLARE cLog VARCHAR; DECLARE cDispatcherProcedure VARCHAR DEFAULT F_SYS_CURSOR_VARCHAR( 'SELECT T1.NAME FROM T_NOTIFICATIONTYPEDISPATCHERS T1 INNER JOIN T_NOTIFICATIONS T2 ON T1.NOTIFICATIONTYPE=T2.TYPE WHERE T2.ID=' + CAST(P_NOTIFICATIONID AS VARCHAR)); IF NOT cDispatcherProcedure IS NULL THEN IF F_SYS_CURSOR_INTEGER('SELECT COUNT(*) FROM INFORMATION.PROCEDURES T1 INNER JOIN INFORMATION.PROCEDUREPARAMS T2 ON T1.NAME=T2.PROCEDURENAME AND T1.NAME=''' + cDispatcherProcedure + ''' AND T2.TYPE=''INTEGER'' AND T2.MODE=''IN''') = 1 THEN SET cLog = 'DISPATCH "' + cDispatcherProcedure + '"'; CALL P_SYS_LOG('START ' + cLog, 'T_NOTIFICATIONS', P_NOTIFICATIONID); EXECUTE IMMEDIATE 'CALL ' + cDispatcherProcedure + '(?);' USING P_NOTIFICATIONID; EXECUTE IMMEDIATE 'UPDATE T_NOTIFICATIONS SET PREVIOUSCALL=CURRENT_DATE(),PREVIOUSCALL_TIME=CURRENT_TIME(),DONE=CURRENT_DATE(),PROCESSED=TRUE WHERE ID=?' USING P_NOTIFICATIONID; CALL P_SYS_LOG('END ' + cLog, 'T_NOTIFICATIONS', P_NOTIFICATIONID); ELSE RAISE ERROR CODE 10112 MESSAGE '%s:INVALID NOTIFICATION DISPATCHER "' + cDispatcherProcedure + '"'; END IF; END IF; EXCEPTION RAISE ERROR CODE 11001 MESSAGE 'P_NTF_DISPATCHNOTIFICATION:' + CAST(ERRORCODE() AS VARCHAR) + ':' + ERRORMSG(); END Roy Lambert wrote: ccr Depending on what your procedure does I have a sneaky idea - can you post the code you're trying to CALL please Roy Lambert Hendrik |
Mon, May 18 2015 11:38 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | ccr
Its the code in whatever cDispatcherProcedure is that I was hoping for, but looking therough the ngs my main idea won't work. I was thinking of grabbing the procedure code and creating the dispatcher on the fly http://www.elevatesoft.com/forums?action=view&category=edb&id=edb_sql&page=1&msg=4559#4559 the second idea was, depending on the code to simply load the code in and execute it line by line the final idea is to build a mega procedure with branching so that you can call it with the dispatcher id and the necessary parameter Roy Lambert |
Mon, May 18 2015 11:41 AM | Permanent Link |
ccr Asar GmbH | hello roy,
the final idea i wanted to skip anyway - i talked to tim now - the solution is to change the procedure to a function and use 'execute immediate ''select ' + myFuncName + '(' + cast(notificationID as varchar) + ')''' quite obvious Roy Lambert wrote: ccr Its the code in whatever cDispatcherProcedure is that I was hoping for, but looking therough the ngs my main idea won't work. I was thinking of grabbing the procedure code and creating the dispatcher on the fly http://www.elevatesoft.com/forums?action=view&category=edb&id=edb_sql&page=1&msg=4559#4559 the second idea was, depending on the code to simply load the code in and execute it line by line the final idea is to build a mega procedure with branching so that you can call it with the dispatcher id and the necessary parameter Roy Lambert Hendrik |
Mon, May 18 2015 1:25 PM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | ccr
>the final idea i wanted to skip >anyway - i talked to tim now - the solution is to change the procedure to a function and use 'execute immediate ''select ' + myFuncName + '(' + cast(notificationID as varchar) + ')''' > >quite obvious I know it works but I don't like it which is why I didn't suggest it Unless this is something new I don't know about don't forget either to choose a table with very few rows or use a WHERE clause that severely limits the rows. It might even be a good idea to have an empty table just for this purpose. Roy |
Mon, May 18 2015 3:48 PM | Permanent Link |
ccr Asar GmbH | thank you roy,
very good help. there where-condition indeed needed another criteria to limit the rows ... Roy Lambert wrote: ccr >the final idea i wanted to skip >anyway - i talked to tim now - the solution is to change the procedure to a function and use 'execute immediate ''select ' + myFuncName + '(' + cast(notificationID as varchar) + ')''' > >quite obvious I know it works but I don't like it which is why I didn't suggest it Unless this is something new I don't know about don't forget either to choose a table with very few rows or use a WHERE clause that severely limits the rows. It might even be a good idea to have an empty table just for this purpose. Roy Hendrik |
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 |