QCMDEXC into SQLRPGLE

[Yanez25]

Good morning,

In my SQLRPGLE program I have this simple statement:


BegSr SrStrSql;

Eval CmdString = %Trim('RUNSQL SQL((MYSQL))');
Eval OuNmProg = 'QCMDEXC';
Eval CmdLength = 256;
Callp CallerCmd (CmdString:CmdLength);

EndSr;

with a file read loop I update the string MYSTRING
with this instruction:

UPDATE MYFILE SET NRVALUE = NRVALUE+12

when I call the pgm (SQLRPGLE) it fails me at the line containing the statement:

Callp CallerCmd(CmdString:CmdLength);

[andreas.prouza]

What is the problem (error message)?

[Yanez25]

Hi Andreas,

I am trying with the RPG.
I'll forward you the lines from the pgm, which currently compiles it for me but if it runs it doesn't work.

InField has this value:
UPDATE MYFILESF SET CDVALU = CDVALU+19000000

here are the lines from the pgm:

F*
D WkSqStmt s 1000a
D InField s 256a
*
C/Exec SQL
C+ DECLARE Cur_Tab1 CURSOR FOR STMT1
C/End-Exec
*
C ExSr SrUpdFile
*
C Eval *Inlr = *On
*
C SrUpdFile BegSr
C
*
C/Exec SQL
C+ PREPARE STMT1 FROM :InField
C/End-Exec
C
*
C/EXEC SQL
C+ CLOSE Cur_Tab1
C/END-EXEC
*
c/Exec sql
c+ OPEN Cur_Tab1
c/end-exec
C Dow SqlCod = 0
c/Exec sql
c+ FETCH Cur_Tab1
c/end-exec
c Enddo
C/EXEC SQL
C+ CLOSE Cur_Tab1
C/END-EXEC
C Endsr
*
C *inzsr BegSr
C *Entry Plist
C Parm InField
C EndSr

where is the error ?


Thanks

[andreas.prouza]

Again, what exactly do you mean by "it doesn't work"?
Is there an exception? --> if yes, which one? --> Joblog, ...

[Yanez25]

CALL PGM(MYLIB/SQLCALL).
Unable to call query file options.
CONNECTION to relational database S7478EE0 completed.
Current connection is to relational database S7478EE0.
Unable to retrieve query file options.
The query access plan has been recreated.
PREPARATION of STMT1 statement completed.
CUR_TAB1 cursor not open.
The prepared STMT1 statement is not a select statement.
CUR_TAB1 cursor not open.
Connection to relational database S7886EE0 terminated.
SQL cursors closed.

[andreas.prouza]

Do the PREPARE statement first and then the DECLARE statement

[Yanez25]

It does not work:

Unable to retrieve query file options.
CONNECTION to relational database S7478EE0 completed.
Current connection is to relational database S7478EE0.
Unable to retrieve query file options.
The query access plan has been recreated.
PREPARATION of STMT1 statement completed.
CUR_TAB1 cursor not open.
The prepared STMT1 statement is not a select statement.
CUR_TAB1 cursor not open.
Connection to relational database S7886EE0 terminated.
SQL cursors closed.

[andreas.prouza]

Ah, your parameter is an Update statement, but a Cursor can only be a SELECT statement.
Just use EXECUTE IMMEDIATE for that job.

[Yanez25]

Excuse me Andreas,

but where do I place the SQL EXECUTE IMMEDIATE statement in my pgm?

D WkSqStmt s 1000a
D InField s 256a
*
C* /Exec SQL
C* + DECLARE Cur_Tab1 CURSOR FOR STMT1
C* /End-Exec
C/Exec SQL
C+ PREPARE STMT1 FROM :InField
C/End-Exec
*
C ExSr SrUpdFile
*
C Eval *Inlr = *On
*
C SrUpdFile BegSr
C
*
C* /Exec SQL
C* + PREPARE STMT1 FROM :InField
C* /End-Exec
C/Exec SQL
C+ DECLARE Cur_Tab1 CURSOR FOR STMT1
C/End-Exec
C
*
C/EXEC SQL
C+ CLOSE Cur_Tab1
C/END-EXEC
*
c/Exec sql
c+ OPEN Cur_Tab1
c/end-exec
C Dow SqlCod = 0
c/Exec sql
c+ FETCH Cur_Tab1
c/end-exec
c Enddo
C/EXEC SQL
C+ CLOSE Cur_Tab1
C/END-EXEC
C Endsr
************************************************** ****
C *inzsr BegSr
C *Entry Plist
C Parm InField
C EndSr

[Yanez25]

Sorry Andreas

I solved it with these 2 instructions:

C/EXEC SQL
C+ SET OPTION COMMIT=*NONE
C/END-EXEC
C
C/EXEC SQL
C+ EXECUTE IMMEDIATE :SQLSTMT1
C/END-EXEC

Thanks