zOs/REXX/ER910

/* rexx ****************************************************************
rexx exec sql gives sql error for set path
***********************************************************************/
    address tso "SUBCOM DSNREXX"
    if rc <> 0 then do
       sRc = RXSUBCOM('ADD','DSNREXX','DSNREXX') /* ADD HOST CMD ENV  */
       if sRc <> 0 then
           call err 'rc' sRc 'from RXSUBCOM(ADD, DSNREXX, DSNREXX)'
       end
    address dsnRexx "connect DBAF"
    say 'connect sqlCode' sqlMsg()
    call sql "set current sqlid = 's100447'"
    call sql 'drop   view a540769.er910Pa9'
    call sql "set :var = current timestamp"
    say 'set var' sqlCode 'var=' var
    address dsnRexx "execSql set path = 'SYSIBM'"
    say "set path = 'SYSIBM' sqlCode" sqlCode sqlMsg()
    address dsnRexx "execSql set path = SYSIBM"
    say "set path = SYSIBM sqlCode" sqlCode sqlMsg()
    address dsnRexx "execSql set path = ""SYSIBM"""
    say "set path = ""SYSIBM"" sqlCode" sqlCode sqlMsg()
    call sql "set path = 'SYSIBM','SYSPROC'"
    call sql "set path = 'SYSIBM , SYSPROC'"
    path = 'set path = SYSIBM, "A540769", "SYSPROC", OA1P, "ganz bloed"'
    call sql "execute immediate :path"
    call sql 'create view a540769.er910Pa9' ,
             'as select * from sysibm.sysDummy1'
    call sql 'commit'
    address dsnRexx "disconnect"
    say 'disconnect sqlCode' sqlCode
    exit

sql:
parse arg ggSqlStmt
    address dsnRexx "execSql" ggSqlStmt
    say sqlMsg()
    return
/*--- issue an sql error message -------------------------------------*/
sqlMsg: /* no procedure, to keep variables sql... */
    signal on syntax name sqlMsgOnSyntax
    ggRes = 'sqlCode' sqlCodeT(sqlCode, sqlErrMc, sqlwarn.0':' ,
       || sqlwarn.1||sqlwarn.2||sqlwarn.3||sqlwarn.4||sqlwarn.5',',
       || sqlwarn.6||sqlwarn.7||sqlwarn.8||sqlwarn.9||sqlwarn.10)
    if 0 then
      sqlMsgOnSyntax: do
        ggRes = 'sqlCode' sqlCode translate(sqlErrMc, ',', 'ff'x),
                '<<rexx sqlCodeT not found or syntax>>\nwarnings'
        do ggX=0 to 10
            if sqlWarn.ggx <> '' then
                ggRes = ggRes ggx'='sqlWarn.ggx
            end
        end
    signal off syntax
    ggRes = ggRes'\nstate' sqlState'\nstmt = ' ggSqlStmt
    ggPref = '\nwith'
    ggXX = pos(':', ggSqlStmt)+1
    do 12 while ggXX > 1
        ggYY = verify(ggSqlStmt, ' ,:+-*/&%?|()[]', 'm', ggXX)
        if ggYY < 1 then
            ggYY = length(ggSqlStmt) + 1
        ggVar = substr(ggSqlStmt, ggXX, ggYY - ggXX)
        if ggVar <> '' then do
            ggRes = ggRes || ggPref ggVar '=' value(ggVar)
            ggPref = '\n    '
            end
        ggXX = pos(':', ggSqlStmt, ggYY+1) + 1
        end
    return  ggRes
endSubroutine sqlMsg