Highlighted
Absent Member.
Absent Member.
1974 views

Calling MSSQL stored procedure

Jump to solution

[Migrated content. Thread originally posted on 02 September 2011]

I am doing a prof of concept for using stored procedures for my account. I create a cursor and call a stored procedure that get the top 25 rows from a table.
I then open the cursor and then do a fetch. I get a SQL error 000000001. Our error checking routine corrects the sql error. I can see that the first row is returned in my working storage fields.
I then try to insert into another table using embedded SQl in my cobol program and when I do the insert I get an error returned 000000100.
No knowing that this is a not found error I am not sure how I could be getting this error returned from an insert.

I will provide some code to show what I am doing.

My stored procedure
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO







ALTER PROCEDURE dbo.spDAL_TESTPID_SEL_OR_HEADER
@COMPANY CHAR(2),
@DC_ID CHAR(4)

AS
BEGIN
SELECT TOP 5 DC_ID, SO_ID, CUST_ORDER_ID, CUST_SHIP_TO_ID, CUST_SHIP_TO_SUFF
FROM CSI.OR_HEADER
WHERE (COMPANY = @COMPANY) AND (DC_ID = @DC_ID)

IF @@error <> 0
BEGIN
BEGIN TRANSACTION t1
INSERT INTO CSI.SY_ACTIVITY_LOG
VALUES( ' ',
'SEL_OR_HEADER',
substring(Convert(CHAR,getdate(),120), 1, 10),
substring(Convert(CHAR,getdate(),120), 12, 2) + '.' +
substring(Convert(CHAR,getdate(),120), 15, 2) + '.' +
substring(Convert(CHAR,getdate(),120), 18, 2),
'SPDAL',
'SYSTEM',
'A',
'Error:' + @@error,
0,
' ',
' ')
COMMIT TRANSACTION t1
RETURN('E') -- Ends running the stored procedure
END
ELSE
RETURN(0)
END







GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

My cobol code.

210-GET-OR-HEADER.

EXEC SQL
DECLARE C1 CURSOR FOR
CALL spDAL_TESTPID_SEL_OR_HEADER
(
:WTMS-COMPANY-ID
,:WTMS-DC-ID
)
END-EXEC .

EXEC SQL OPEN C1 END-EXEC.
PERFORM UNTIL EXIT
EXEC SQL
FETCH NEXT C1 INTO
:WSOH-COMPANY-ID,
:WSOH-SO-ID,
:WSOH-CUST-ORDER-ID,
:WSOH-CUST-SHIP-TO-ID,
:WSOH-CUST-SHIP-TO-SUFF
END-EXEC
MOVE 'FETCH ' TO WS-STD-SQL-COMMAND
PERFORM 11020-SYHDB-ADJ-SQLCODE THRU 11020-SYHDB-EXIT
IF SQLCODE NOT = WS-SQLCODE-NORESP
IF SQLCODE = WS-SQLCODE-NOTFND
EXEC SQL CLOSE C1 END-EXEC
MOVE 'CLOSE ' TO WS-STD-SQL-COMMAND
PERFORM 11020-SYHDB-ADJ-SQLCODE THRU 11020-SYHDB-EXIT
EXIT PERFORM
GO TO 210-EXIT
ELSE
IF SQLCODE NOT = WS-SQLCODE-NORESP
MOVE SPACES TO WS-MESSAGE-AREA
MOVE 'E' TO WHALG-RECORD-TYPE
MOVE '0203' TO WS-MESSAGE-ID
MOVE 'C_OR_LINE' TO WHALG-TABLE-NAME
MOVE SQLCODE TO WHALG-SQLCODE
MOVE '660' TO WHALG-DEBUG-REF-ID
PERFORM 999-ABEND
END-IF
END-IF
END-IF
PERFORM 216-WRITE-RESULTS THRU 216-EXIT
END-PERFORM.

216-WRITE-RESULTS.
ADD 1 to COUNTER-REC.
MOVE COUNTER-REC TO WHALG-DEBUG-REF-ID.

EXEC SQL
INSERT INTO CSI.SO_INDUCT_TEMP

(
ROW_NUM,
COMPANY_ID,
DC_ID,
CNTL_TYPE,
CNTL_ID,
CUST_SHIP_TO_ID,
CUST_SHIP_TO_SUFF,
WEIGHT_FACTOR,
CUBE_FACTOR,
QTY_PICK,
GROUP_CODE,
SEQ_LOCATION_ID,
PRIORITY,
PICK_PASS_GROUP,
PICK_PASS_PRIORITY,
SUB_ZONE_CODE,
SCANNER_NUMBER,
SCANNER_DIRECTION,
ROW_ID)
VALUES(
'1',
'OM',
:WTMS-DC-ID,
' ',
' ',
:WSOH-CUST-SHIP-TO-ID ,
:WSOH-CUST-SHIP-TO-SUFF,
0,
0,
0,
' ',
' ',
0,
' ',
0,
' ',
' ',
' ',
:WHALG-DEBUG-REF-ID
)
END-EXEC.
MOVE 'INSERT ' TO WS-STD-SQL-COMMAND
PERFORM 11020-SYHDB-ADJ-SQLCODE THRU 11020-SYHDB-EXIT
IF SQLCODE = WS-SQLCODE-NORESP
GO TO 216-EXIT
ELSE
MOVE SPACES TO WS-MESSAGE-AREA
MOVE '0204' TO WS-MESSAGE-ID
MOVE 'SO_INDUCT_TEMP' TO WHALG-TABLE-NAME
MOVE SQLCODE TO WHALG-SQLCODE
MOVE '221' TO WHALG-DEBUG-REF-ID
PERFORM 999-ABEND
END-IF.
216-EXIT.
EXIT.



thanks for any help provided.


0 Likes
1 Solution

Accepted Solutions
Highlighted
Micro Focus Expert
Micro Focus Expert

RE: Calling MSSQL stored procedure

Jump to solution
You should open up a support incident with Micro Focus Supportline if you haven't already done so.
We will need a cutdown program like the one you describe along with any ddl statements required to create the sample database etc.

You could also try to set the SQL TRACE directives and look at what is being executed under the covers for this.
See help on SQL directives:

TRACELEVEL and ODBCTRACE

for more information...

View solution in original post

0 Likes
2 Replies
Highlighted
Micro Focus Expert
Micro Focus Expert

RE: Calling MSSQL stored procedure

Jump to solution
You should open up a support incident with Micro Focus Supportline if you haven't already done so.
We will need a cutdown program like the one you describe along with any ddl statements required to create the sample database etc.

You could also try to set the SQL TRACE directives and look at what is being executed under the covers for this.
See help on SQL directives:

TRACELEVEL and ODBCTRACE

for more information...

View solution in original post

0 Likes
Highlighted
Absent Member.
Absent Member.

RE: Calling MSSQL stored procedure

Jump to solution
In addition you can include SQLCA copybook and while debugging the code try to see all the variable of SQLCA that may help.
Also i am not able to understand your question well.
0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.