help: exec sql declare cursor being skipped/ignored 2

[eirez]

Database: Oracle
Connection: ODBC
Compiler: NETEXPRESS 3.0

I am trying to open a cursor on a table in a database.
When the program runs, it skips the exec sql declare cursor and tries to execute the exec sql open cursor. Of course, this generates and error.

When I removed the declare cursor command and made it an exec sql select statement, the program ran and executed the exec sql.

Basically, the compiler does not like the DECLARE CURSOR.
This program ran before on MVS/DB2. Now it will run on NT/ORACLE, but it won't execute the declare.

The only directive I have is $set SQL. I open a connection using odbc, and all select statements are run.

Please help, as this is a major roadblock in going live with an implementation.


Code being skipped:
EXEC SQL DECLARE AG_GL_XREF_CUR CURSOR FOR
SELECT AG_ADP_FIELD_NAME
, AG_DEBIT_DIVISION
, AG_DEBIT_COST_CTR
, AG_DEBIT_GL_ACCT
, AG_DEBIT_ACCR_FLG
, AG_CREDIT_DIVISION
, AG_CREDIT_COST_CTR
, AG_CREDIT_GL_ACCT
, AG_CREDIT_ACCR_FLG
, AG_PAYROLL_DESC
, AG_ADP_CODE_VALUE
, AG_ADP_STATE_CD
, AG_ADP_SUI_CD
, AG_PERCENT
, PAYGROUP
FROM PS_AG_ADP_GL_XREF
WHERE PAYGROUP = S-CMPNY-CD
ORDER BY AG_ADP_FIELD_NAME
END-EXEC.
EXEC SQL OPEN AG_GL_XREF_CUR END-EXEC.

Thanks,
Erol

 

[MarcLodge]

Erol,
The DECLARE statement is what they call a 'declarative' statement. ie. it doesn't actually do anything. It's like an INCLUDE DCLGEN statement and so therefore if you were following the source whilst executing, it would skip this statement, just like it would skip and INCLUDE DCLGEN statement.
I would suggest having a look at the pre-compile options to check that all is well, and also to see if any warning/error messages are being output which pertain to the DECLARE statement.
hth
Marc

 

[eirez]

Thanks Marc!

You are right. I misunderstood the symptom. The declare and open cursor sql exec work, but the FETCH INTO sql exec does not work.

The following fetch is not moving to the next record:
EXEC SQL
FETCH AG_GL_XREF_CUR
INTO
:GL-FIELD-NAME,
:GL-DB-BU,
:GL-DB-DEPARTMENT,
:GL-DB-GL-ACCT,
:GL-DB-ACCR-FLG,
:GL-CR-BUS-UNIT,
:GL-CR-DEPARTMENT,
:GL-CR-GL-ACCT,
:GL-CR-ACCR-FLG,
:GL-PAYROLL-DESC,
:GL-CODE-VAL,
:GL-STATE,
:GL-SUI,
:GL-PERCENT,
:GL-CMPNY-CD
END-EXEC.

I call this fetch in a PERFOM - UNTIL SQLCODE NOT = 0 AND SQLCODE NOT = 1. Since the fetch does not move to the next record, yet it does successfully fetch a row from the cursor, my program goes in an endless do loop.

Is my syntax wrong (this is the first cobol program I ever wrote and I never took a class).

I thank everyone in advance...
Erol

 

[eirez]

THANK YOU ALL

The problem was not the declare nor the fetch. The problem was in the logic structure.

Thank you Marc for teaching me that a DECLARE CURSOR does not execute. It allowed me to continue the debugging process and find the REAL reason for the problem.

Erol

 

[MarcLodge]

Erol,
Glad to be of help. One of the many questions of coding is whether to place the DECLARE in the logic, or whether to place it in working storage somewhere. If placed in the logic, you can hit the problems you have come across, whereas if placed in Working Storage, it causes the support programmer (at 2.30 in the morning) a lot of grief having to refer back to sections in the program.

I'm delighted that you managed to solve the problem and that it was your first major Cobol program. Keep coming back here if you get problems or questions.

Marc
(if you think my post was good, then feel free to give it a star, but if you think it was poor, then let me know, and I'll try to do better next time!)

 

[eirez]

Thank you MArc. Big star for you!

I put the declare in working storage and the old MVS program started to work.

Ever onward....

 

[slade]

OK Mark,

Here's your star . I don't think Eirez knew what you meant.

Regards, Jack.

 

[MarcLodge]

Thanks Jack. I was far too embarrassed to labour the point. Thanks again. Marc

 

[eirez]

I am sorry Marc, but I clicked on the Star button (Mark this post as a helpful post) URL, but I guess I clicked on the wrong one.

I'll click on it a few more times to make you happy =)