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

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

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

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

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

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

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

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

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