executing a stored procedure on SQL Server from RPG pgm

[MelissaG]

I've been working with Scott Klement's "Accessing External Databases" presentation, and with some help from a co-worker, I've been able to both update and read an SQL table. Yay for me. I'm struggling with the last part of what I need to do. After I populate the table, I have to execute a stored procedure. I can't tell if it's MY code that isn't working to start the procedure or if the program done by the vendor (on the server) just isn't working. I'm using JDBC_ExecCall(stmt) where "stmt" seems to be a number created when I prep the call. I've tried "Google" but everything I find seems to be going the OTHER direction - trying to run a stored procedure on the ISeries using Java or something.

My program is connected to the server. It seems to be going through the JDBCR4 program(s).

I am probably making some OBVIOUS error, but I don't currently have a co-worker who can review my code with me. (My guru's daughter had a baby this week - he's being a grandpa!) Does anyone see anything in the following bit of code that causes questions or concerns?

        //  This is the connection procedure call
         conn = JDBC_Connect(DrvClass
                   : %trim(connURL)
                   : %trim(UserID)
                   : %trim(Passwrd) );

          If conn = *NULL;
             escape('Failed to connect to Server:'
             + %trim(Host) + ' Database:' + %Trim(Database)
               + ' CONTACT APP SUPPORT');
          ENDIF;

           //PROCESS STUFF
           //notes - V suggested that I submit a job here that watches
           //for a good result; if it doesn't end after x minutes, send
           //an  email to computer room / help desk/ APP support

            Cmd = 'SBMJOB CMD(CALL PGM(PD00085CL))'  +
               ' JOB(PD00085CL) ' +
               'JOBQ(QBATCH2) '     +
              'LOG(*JOBD *JOBD *MSG) LOGCLPGM(*YES)';

               $ExcCmd(Cmd:%Len(Cmd));  // Submit Job

           stmt = JDBC_PrepCall(conn: 'EXEC dbo.Run_Optimizer' );

           JDBC_ExecCall(stmt);


           //POPULATE OPTMZEFLG so loop pgm (PD00085CL) ends
           EXEC SQL
             INSERT INTO OPTMZEFLG
                 (Optdone)
                VALUES('DONE');

          jdbc_FreeCallStmt(stmt);
          jdbc_close(conn); 

As always I thank you for any advice. I hope to someday be able to help someone else who uses these forums, but so far, no one has asked how to spell RPG!

[jtaylor___]

I always do something like "call [dbo].[MyProcedure]", rather than EXEC like you're doing. I can't say if that's significant or not.

Anything in your joblog?

[Scott M]

My code looks very similar to yours and I use the EXEC, I use a variable for the EXEC and %trim it, otherwise looks fine. Does the user you are connecting with have Execute authority on the stored procedure you are trying to run? I would expect to find something in your job log.

[MelissaG]

Scott M., that was exactly it!

It turns out that the BA didn't tell the SQL Server guy to make sure we had execute authority. I appreciate knowing that either EXEC or CALL is appropriate. That was one of the things I was questioning. Thank you!