Conference orarep::nomahs::dbstars

757.0. "cognos bug using ext functions w/lib$spawn" by BROKE::BITHER () Fri Apr 04 1997 17:56

problem/solution below
---------------------
Hi,

Rdb 6.0-0 (no patches and doesn't want to because will have to
  shutdown Rdb.  Waiting to go to v7 instead.)

Has an external function defined in Rdb that works in everyway
except from his Powerhouse application.

What works:
----------
1.  select function(col) from table.
2.  Update table set function(col)=value where col=value
    (His update includes a trigger and this is what he is trying to do
     from Powerhouse.  But it only works in interactive SQL.)

What doesn't work:
-----------------
An update from Powerhouse.  He gets the following error:

  Data access error, External function error
  Failed to compile or execute successfully
  rdbms-e-nomessage
  00DD904A
  00DD91AA

I looked up these message numbers and they translate to:
  SYSTEM-F-IVSECIDCTL, invalid section identification match control
  RDMS-E-RTNIMAGERR, error activating the image

Powerhouse does a "put file" where file is the name of a table in
Rdb, and put means to update the table.  He runs the Powerhouse 
application from the same process that he uses for interactive SQL where 
he can get the function to work.

I asked if it creates a subprocess and he said maybe.  He has given the
image all privs.  Asked if it could see location of the image.  He says yes
because directory specification in LOCATION field of function def
is a system logical.

Earlier he was getting an "entry point not found" error from interactive
SQL and this was fixed by linking with universal=routine.  But it still does
not work from Powerhouse app.  Asked if Powerhouse has logging, and he
doesn't know.

Is this something I should have him call Powerhouse about or is
"error activating image" an obvious message with an obvious solution?

Below is a short procedure to build the function and trigger, the
function and trigger definitions, and the fortran code.

Thanks, Diane
----------------------------------------------------------------
$sql
set ver
ATTACH 'FILENAME IDEA$RDB:IDEA';
drop function reply_idea61;
commit;

CREATE FUNCTION REPLY_IDEA61 ( IN CHAR(8) )
RETURNS INTEGER;
EXTERNAL NAME REPLY_IDEA61
LOCATION 'nocc_ph$com:'
LANGUAGE FORTRAN
GENERAL PARAMETER STYLE;

drop trigger tt_change;
commit;
CREATE TRIGGER TT_CHANGE
AFTER UPDATE ON TTDTT 
WHEN REPLY_IDEA61 ( TTDTT.TT_NUMBER) <> 0
(ERROR)
FOR EACH ROW;
COMMIT;


---------------------------------------------
SQL> sho function reply_idea61
Function name is: REPLY_IDEA61
 Function ID is: 6
 Function owner is: NONE
 Language is: FORTRAN
 GENERAL parameter passing style used
 Number of parameters is: 1

                                Data Type
                                ---------

                                INTEGER
        Function result datatype
        Return value is passed by VALUE

                                CHAR(8)
        Parameter position is 1
        Parameter is IN (read)
        Parameter is passed by DESCRIPTOR

-------------------------------------
SQL> show trigger tt_change
     TT_CHANGE
 Source:
 TT_CHANGE
                     AFTER UPDATE ON TTDTT
                     WHEN REPLY_IDEA61 ( TTDTT.TT_NUMBER) <> 0
                     (ERROR)
                     FOR EACH ROW
-----------------------------------------
    PROGRAM REPLY_IDEA61
    IMPLICIT NONE
C Variables
    CHARACTER*250   R_STRING
    CHARACTER*4 P_STRING
    EXTERNAL    LIB$GET_FOREIGN
    CHARACTER*40    CMD
C Input format
100 FORMAT (A)
C------------------------------------------------------------------------------
C Get passed parameter 
    P_STRING = 'NULL'
    CALL LIB$GET_FOREIGN(R_STRING)
C------------------------------------------------------------------------------
    CMD = '$ REPLY/USER=IDEA61 TT#'//R_STRING
    CALL LIB$SPAWN (CMD)
8888    CALL EXIT
    END







---- End of Message ----

Hi Charlie,  
  
This is a writeup of what I told you via voicemail.   I talked to a   
programmer today and we came up with several things for you to try.  
  
1.  They thought it looks like your application is not even finding the   
Fortran program (which is what we thought too) and to confirm this, please  
rename the fortran executable to something totally different and put it in  
a different directory and try your application again.    
  
If the error messages are the same, then we can conclude that it cannot  
find your fortran executable.  At this point, you need to call the  
people at Powerhouse and clarify how to make external routine calls from w/in  
the Powerhouse application.  Possibly some parameters are missing in  
the routine call that you currently have defined w/in the app.  
(I know the trigger makes the call to the fortran program and that  
is from w/in Rdb but the total window/process environment is w/in your  
app and that is what you need to set up so that it can see external  
routines.)  
  
If the error messages are different, then we can conclude that it  
is finding your fortran executable, but having trouble executing something  
inside it.  The likely culprit is lib$spawn.  Continue to #2 only if  
this is the situation.  
  
2.  Comment out the following line in your fortran program:  
  
   CALL LIB$SPAWN (CMD)  
  
Try your app again.  See if the error messages are different.  If they  
are different, then it indicates a problem with the lib$spawn call and  
continue to #3.  
  
3.  Pass NL: as the output device on the lib$spawn call.   
  
If this doesn't work,  
  
4.  Also pass NL: as the input device on the lib$spawn call.  Don't remove  
the output device parameter.  
  
Please do 3 and 4 as separate steps.  
  
The defaults we believe are sys$input and sys$output, either of which might  
be illegal for spawn.  
  
For more information on passing arguments to lib$spawn, please see the  
vms doc set pages, LIB-382 thru LIB-385.  
  
Thanks, Diane  
 
 
SOLUTION FROM COGNOS:
--------------------

Hi Diane.  Charlie called me back under onw of my TARs, and meant to         
 call you back on this TAR.  I told him I'd pass on whatever info he wanted,  
 so he dictated and I typed......                                             
                                                                              
      Cognos finally called back and said there was a conflict with           
 BroadCast, Powerhouse and VMS, with the LIB$SPAWN command.                   
 Using  a Powerhouse default of 'broadcast deferred',  messages are put       
 into the user mailbox -- the same mailbox that VMS uses, thereby generating  
 an error.  Cognos doesn't consider this to be a bug, but they gave him       
 a work-around.  When activating QUICK under Powerhouse, he can set           
 broadcast = default.  He's tried this and it does work for him.              
                                                                              
 He said you can call him back if you want to but it wasn't necessary, and
 that he appreciates your help and said you were a pleasure to work with.