Rexx: Call procedure

Hallo Gernot,

ich bekam – ob warum?? – keine email-Benachrichtigung über Deine Antwort, deswegen diese paar Tage Verspätung.

Wie ich aus dem help herauslese, DB2 LUW lässt keine  Benutzung von host variables in dem CALL, anders als das mit z/OS möglich ist.

Ich habe mehrere Versuche verrichtet, die ich hier unten hineinkopiere, samt output Ergebnissen.

Wie könnte man ein parametrisiertes procedure erfolgreich rufen, interessiert mich nach wie vor…

Danke für Deine Antwort,

Katalin

callsp_list Rexx Programm:

/* callsp_list */
/* Stored procedure call, getestet:  WIN XP + oorexx + DB2 LUW V9.5 , V9.1 */

/* register DB2 */
  if RxFuncQuery(‚SQLDBS‘) <> 0 then
    rcy = RxFuncAdd( ‚SQLDBS‘,  ‚db2ar‘, ‚SQLDBS’  )
  if RxFuncQuery(‚SQLEXEC‘) <> 0 then
    rcy = RxFuncAdd( ‚SQLEXEC‘, ‚db2ar‘, ‚SQLEXEC‘ )

/* pull in command line arguments */
parse arg userid passwd .

/* check to see if the proper number of arguments have been passed in */
   PARSE ARG dbname userid password .
   if ((dbname = "" ) | ,
       (userid <> "" & password = "") ,
      ) then do
      SAY "USAGE: callsp.cmd <dbname> [<userid> <password>]"
       exit -1
   end

/* connect to database */
   SAY
   SAY ‚Connect to‘ dbname
   IF password= "" THEN
      CALL SQLEXEC ‚CONNECT TO‘ dbname
   ELSE
      CALL SQLEXEC ‚CONNECT TO‘ dbname ‚USER‘ userid ‚USING‘ password

   CALL CHECKERR ‚Connect to ‚
   SAY "Connected"

/* Create the CALL_TEST procedure with an OUTPUT parameter */
   call createsp

/* Variatons for calling the procedure */
   do ttidx = 1 to 4
      say
      say ttidx". Try"
      call callsp ttidx
   end
exit

createsp:
crea.1="CREATE PROCEDURE with_par ( OUT ch CHAR(1) )",
"LANGUAGE SQL   NOT DETERMINISTIC",
"P1: BEGIN SET ch=’B‘; END P1"
crea.2="CREATE PROCEDURE without_par ( )",
"LANGUAGE SQL   NOT DETERMINISTIC",
"P1: BEGIN DECLARE ch CHAR(1);  SET ch=’B‘; END P1"
do i=1 to 2
CALL SQLEXEC ‚EXECUTE IMMEDIATE :crea.i‘
CALL CHECKERR ‚Create proc ‚
end
CALL SQLEXEC ‚COMMIT‘
return

callsp:
arg testpar
select
when testpar=1 then do
    callcomm="CALL without_par()"
  CALL SQLEXEC ‚EXECUTE IMMEDIATE :callcomm‘
  CALL CHECKERR ‚EXECUTE IMMEDIATE (Call without parameter)‘
end
when testpar=2 then do
    callcomm="CALL with_par(:ch)"
  CALL SQLEXEC ‚EXECUTE IMMEDIATE :callcomm‘
  CALL CHECKERR ‚EXECUTE IMMEDIATE (Call with host variable)‘
end
when testpar=3 then do
    callcomm="CALL with_par(?)"
  CALL SQLEXEC ‚PREPARE S61 FROM :callcomm‘
CALL CHECKERR ‚Dynamic prepare without descriptor‘
if result=0 then call disp_sqlroda ’sqlroda after prepare – if implicit used?‘

  CALL SQLEXEC ‚EXECUTE S61 INTO :ch‘
  CALL CHECKERR ‚EXECUTE (after dynamic prepare without descriptor)‘
end
when testpar=4 then do
    callcomm="CALL with_par(?)"
  CALL SQLEXEC ‚PREPARE S62 INTO :sqlroda FROM :callcomm‘
CALL CHECKERR ‚Prepare into descriptor‘
if result=0 then call disp_sqlroda ’sqlroda after prepare‘

  CALL SQLEXEC ‚DESCRIBE S62 INTO :sqlroda‘
CALL CHECKERR ‚Describe into descriptor‘
if result=0 then call disp_sqlroda ’sqlroda after describe‘

  CALL SQLEXEC ‚EXECUTE S62 INTO DESCRIPTOR :sqlroda‘
  CALL CHECKERR ‚EXECUTE into descr (after Prepare into descriptor)‘
if result=0 then call disp_sqlroda ’sqlroda after execute‘
say ‚ch=‘ ch

  CALL SQLEXEC ‚EXECUTE S62 INTO :ch‘
  CALL CHECKERR ‚EXECUTE into host var. (after prepare into descriptor)‘
if result=0 then call disp_sqlroda ’sqlroda after execute‘
say ‚ch=‘ ch
end
otherwise do; say ‚wrong testpar‘; exit 999; end
end

return

disp_sqlroda:
arg out_loc
say out_loc
say ’sqlroda.sqldaid  =‘ sqlroda.sqldaid 
say ’sqlroda.sqld     =‘ sqlroda.sqld    
say ’sqlroda.sqldabc  =‘ sqlroda.sqldabc 
say ’sqlroda.1.sqltype=‘ sqlroda.1.sqltype
say ’sqlroda.1.sqllen =‘ sqlroda.1.sqllen
say ’sqlroda.1.sqldata=‘ sqlroda.1.sqldata
say ’sqlroda.1.sqlname=‘ sqlroda.1.sqlname
return

CHECKERR:
  arg errloc

  if  ( SQLCA.SQLCODE = 0 ) then
    return 0
  else do
    say “
    say ‚— error report —‚
    say ‚ERROR occurred :‘ errloc
    say ‚SQLCODE :‘ SQLCA.SQLCODE

    /******************************
    * GET ERROR MESSAGE API called *
    ******************************/
    call SQLDBS ‚GET MESSAGE INTO :errmsg LINEWIDTH 80‘
    say errmsg
    say ‚— end error report —‚

    /*
    if (SQLCA.SQLCODE < 0 ) then
      exit
    else do
      say ‚WARNING – CONTINUING PROGRAM WITH ERRORS‘
      return 0
    end
    */
  end
return sqlca.sqlcode

Output von dem Programm:

C:EclipseWorkspaceerekonz>rexx callsp_list sample

Connect to sample
Connected

1. Try

2. Try

— error report —
ERROR occurred : EXECUTE IMMEDIATE (CALL WITH HOST VARIABLE)
SQLCODE : -312
SQL0312N  The host variable "ch" is used in a dynamic SQL statement, a view
definition, or a trigger definition.  SQLSTATE=42618
— end error report —

3. Try
SQLRODA AFTER PREPARE – IF IMPLICIT USED?
sqlroda.sqldaid  = SQLRODA.SQLDAID
sqlroda.sqld     = SQLRODA.SQLD
sqlroda.sqldabc  = SQLRODA.SQLDABC
sqlroda.1.sqltype= SQLRODA.1.SQLTYPE
sqlroda.1.sqllen = SQLRODA.1.SQLLEN
sqlroda.1.sqldata= SQLRODA.1.SQLDATA
sqlroda.1.sqlname= SQLRODA.1.SQLNAME

— error report —
ERROR occurred : EXECUTE (AFTER DYNAMIC PREPARE WITHOUT DESCRIPTOR)
SQLCODE : -104
SQL0104N  An unexpected token "INTO" was found following "<identifier>".
Expected tokens may include:  "END-OF-STATEMENT".
— end error report —

4. Try
SQLRODA AFTER PREPARE
sqlroda.sqldaid  = SQLRODA.SQLDAID
sqlroda.sqld     = 1
sqlroda.sqldabc  = SQLRODA.SQLDABC
sqlroda.1.sqltype= 453
sqlroda.1.sqllen = 1
sqlroda.1.sqldata= 1208
sqlroda.1.sqlname= CH
SQLRODA AFTER DESCRIBE
sqlroda.sqldaid  = SQLRODA.SQLDAID
sqlroda.sqld     = 1
sqlroda.sqldabc  = SQLRODA.SQLDABC
sqlroda.1.sqltype= 453
sqlroda.1.sqllen = 1
sqlroda.1.sqldata= 1208
sqlroda.1.sqlname= CH

— error report —
ERROR occurred : EXECUTE INTO DESCR (AFTER PREPARE INTO DESCRIPTOR)
SQLCODE : -104
SQL0104N  An unexpected token "INTO" was found following "<identifier>".
Expected tokens may include:  "END-OF-STATEMENT".
— end error report —
ch= CH

— error report —
ERROR occurred : EXECUTE INTO HOST VAR. (AFTER PREPARE INTO DESCRIPTOR)
SQLCODE : -104
SQL0104N  An unexpected token "INTO" was found following "<identifier>".
Expected tokens may include:  "END-OF-STATEMENT".
— end error report —
ch= CH

C:EclipseWorkspaceerekonz>