SergVSmola
Programmer
I read help in NetCOBOL docs
Sample from documentation:
An Example of Calling a Stored Procedure
The following figure is a COBOL program that calls a stored procedure.
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 INPUT-VARIABLE PIC S9(4) COMP-5.
01 OUTPUT-VARIABLE PIC S9(4) COMP-5.
EXEC SQL END DECLARE SECTION END-EXEC.
PROCEDURE DIVISION.
EXEC SQL CONNECT TO DEFAULT END-EXEC.
MOVE 100 TO INPUT-VARIABLE.
EXEC SQL
CALL PROC INPUT-VARIABLE, :OUTPUT-VARIABLE)
END-EXEC.
EXEC SQL ROLLBACK WORK END-EXEC.
EXEC SQL DISCONNECT DEFAULT END-EXEC.
STOP RUN.
Sample program that calls a stored procedure
My program where I use calling a stored procedure:
000010 @OPTIONS MAIN, APOST
000011 IDENTIFICATION DIVISION.
000020 PROGRAM-ID. STO_PROC.
000030 ENVIRONMENT DIVISION.
000040 DATA DIVISION.
000050 WORKING-STORAGE SECTION.
000060 EXEC SQL BEGIN DECLARE SECTION END-EXEC.
000142 01 SQLSTATE PIC X(5).
000143 01 SQLMSG PIC X(128).
000144 01 VAR1 PIC X(10).
000145 01 VAR2 PIC X(10).
000147 EXEC SQL END DECLARE SECTION END-EXEC.
000148
000149 PROCEDURE DIVISION.
000200* EMBEDDED EXCEPTION DECLARATION
000201 EXEC SQL
000202 WHENEVER NOT FOUND GO TO -END
000203 END-EXEC.
000204* CONNECT TO THE DATABASE ON THE DEFAULT SERVER.
000205 EXEC SQL CONNECT TO DEFAULT END-EXEC.
000206* storedprocedure SR.MB_PAY_PREDPR( "01.01.2003", "31.01.2003" )
000230 MOVE "01.01.2003" to VAR1
000231 MOVE "31.01.2003" to VAR2
000282 EXEC SQL
000283 CALL SR.MB_PAY_PREDPR ( :VAR1, :VAR2 )
000285 END-EXEC.
000290*******************
000300**** NOTES ****
000310*******************
000320* This program makes connect to Oracle RDBMS trough ODBC connection
000330* server - abs1
000340* user id - urv_1
000350* password - *****
000360* stored proc - SR.MB_PAY_PREDPR
000440*-----------------------------------------------------
000450 P-END.
000460* DETERMINE CONNECTION
000800 EXEC SQL ROLLBACK WORK END-EXEC.
000830* DISCONNECT FROM THE DATABASE.
000840 EXEC SQL DISCONNECT DEFAULT END-EXEC.
000880 END-PROC.
Compiler message is:
** DIAGNOSTIC MESSAGE ** (STO_PROC)
D:\fsc\PCOBOL32\samples\StoProc\StoProc.COB 26: JMN2633I-S SQL STATEMENT IS INVALID. ODBC-7600E MESSAGE ACCESS ERROR.
STATISTICS: HIGHEST SEVERITY CODE=S, PROGRAM UNIT=1
line 26 is: 000283 CALL SR.MB_PAY_PREDPR ( :VAR1, :VAR2 )
why SQL STATEMENT IS INVALID?
what are errors: JMN2633I-S and ODBC-7600E MESSAGE ACCESS ERROR?
1. I tried of calling the procedure with literals coded in the parameter list:
SR.MB_PAY_PREDPR( "01.01.2003", "31.01.2003" ). Obtained same rezult JMN2633I-S SQL STATEMENT IS INVALID. ODBC-7600E MESSAGE ACCESS ERROR.
2. I executed procedure from SqlDeveloper with 2 parameters "01.01.2003", "31.01.2003". I have got access to view sr.sr$mb$pay_predpr based on DB MB$PAY_PREDPR with data in dates diapason.
It's interesting. When I change string with calling a stored procedure to string with select
DECLARE CUR1 CURSOR FOR SELECT OKPO, NAME, ACCOUNT_NO, AMOUNT FROM SR.SR$MB$PAY_PREDP
or I insert string with stored procedure calling AFTER this one select comand compiler proccess is OK without any mistake. So I think may be problem is not only procedure calling. I use Fujitsy COBOL V3.0. May be not all DLL files in my version?
Sample from documentation:
An Example of Calling a Stored Procedure
The following figure is a COBOL program that calls a stored procedure.
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 INPUT-VARIABLE PIC S9(4) COMP-5.
01 OUTPUT-VARIABLE PIC S9(4) COMP-5.
EXEC SQL END DECLARE SECTION END-EXEC.
PROCEDURE DIVISION.
EXEC SQL CONNECT TO DEFAULT END-EXEC.
MOVE 100 TO INPUT-VARIABLE.
EXEC SQL
CALL PROC INPUT-VARIABLE, :OUTPUT-VARIABLE)
END-EXEC.
EXEC SQL ROLLBACK WORK END-EXEC.
EXEC SQL DISCONNECT DEFAULT END-EXEC.
STOP RUN.
Sample program that calls a stored procedure
My program where I use calling a stored procedure:
000010 @OPTIONS MAIN, APOST
000011 IDENTIFICATION DIVISION.
000020 PROGRAM-ID. STO_PROC.
000030 ENVIRONMENT DIVISION.
000040 DATA DIVISION.
000050 WORKING-STORAGE SECTION.
000060 EXEC SQL BEGIN DECLARE SECTION END-EXEC.
000142 01 SQLSTATE PIC X(5).
000143 01 SQLMSG PIC X(128).
000144 01 VAR1 PIC X(10).
000145 01 VAR2 PIC X(10).
000147 EXEC SQL END DECLARE SECTION END-EXEC.
000148
000149 PROCEDURE DIVISION.
000200* EMBEDDED EXCEPTION DECLARATION
000201 EXEC SQL
000202 WHENEVER NOT FOUND GO TO -END
000203 END-EXEC.
000204* CONNECT TO THE DATABASE ON THE DEFAULT SERVER.
000205 EXEC SQL CONNECT TO DEFAULT END-EXEC.
000206* storedprocedure SR.MB_PAY_PREDPR( "01.01.2003", "31.01.2003" )
000230 MOVE "01.01.2003" to VAR1
000231 MOVE "31.01.2003" to VAR2
000282 EXEC SQL
000283 CALL SR.MB_PAY_PREDPR ( :VAR1, :VAR2 )
000285 END-EXEC.
000290*******************
000300**** NOTES ****
000310*******************
000320* This program makes connect to Oracle RDBMS trough ODBC connection
000330* server - abs1
000340* user id - urv_1
000350* password - *****
000360* stored proc - SR.MB_PAY_PREDPR
000440*-----------------------------------------------------
000450 P-END.
000460* DETERMINE CONNECTION
000800 EXEC SQL ROLLBACK WORK END-EXEC.
000830* DISCONNECT FROM THE DATABASE.
000840 EXEC SQL DISCONNECT DEFAULT END-EXEC.
000880 END-PROC.
Compiler message is:
** DIAGNOSTIC MESSAGE ** (STO_PROC)
D:\fsc\PCOBOL32\samples\StoProc\StoProc.COB 26: JMN2633I-S SQL STATEMENT IS INVALID. ODBC-7600E MESSAGE ACCESS ERROR.
STATISTICS: HIGHEST SEVERITY CODE=S, PROGRAM UNIT=1
line 26 is: 000283 CALL SR.MB_PAY_PREDPR ( :VAR1, :VAR2 )
why SQL STATEMENT IS INVALID?
what are errors: JMN2633I-S and ODBC-7600E MESSAGE ACCESS ERROR?
1. I tried of calling the procedure with literals coded in the parameter list:
SR.MB_PAY_PREDPR( "01.01.2003", "31.01.2003" ). Obtained same rezult JMN2633I-S SQL STATEMENT IS INVALID. ODBC-7600E MESSAGE ACCESS ERROR.
2. I executed procedure from SqlDeveloper with 2 parameters "01.01.2003", "31.01.2003". I have got access to view sr.sr$mb$pay_predpr based on DB MB$PAY_PREDPR with data in dates diapason.
It's interesting. When I change string with calling a stored procedure to string with select
DECLARE CUR1 CURSOR FOR SELECT OKPO, NAME, ACCOUNT_NO, AMOUNT FROM SR.SR$MB$PAY_PREDP
or I insert string with stored procedure calling AFTER this one select comand compiler proccess is OK without any mistake. So I think may be problem is not only procedure calling. I use Fujitsy COBOL V3.0. May be not all DLL files in my version?