Page 1 of 2

Generic DB2 SQLCODE handle for cobol

PostPosted: Thu Apr 05, 2012 4:57 pm
by mandor
Hello!

Me and my colleague are wondering if it is at all possible to generically handle sqlcode.
What we want in fact is to call a routine that will get the parameters of where clause of an unsuccessful SQL.
I.E.:

EXEC SQL
   SELECT 1 FROM SOME_TABLE
   WHERE CENTER = :FIRM
      AND UNIT = :ORG-UNIT
      AND EMPLOYEE STATUS = :STATUS
END-EXEC

IF SQLCODE NOT = 0 THEN
   PERFORM SQL-HANDLE
   MOVE SQL-HANDLE-OUT TO MESSAGE
END-IF


So that MESSAGE contains something like 'PAR1: 7; PAR2: 16; PAR3 = 99;'

So, F1, guys! :-D

Anything is helpful!
Thanks in advance!

Kind regards,
Mario.

Re: Generic DB2 SQLCODE handle for cobol

PostPosted: Fri Apr 13, 2012 12:46 pm
by mandor
Bumping the thread.

Please guys, realy ANY idea is most helpful!
Any reading material or the simplest of examples of anything remotely automatic and/or generic.

Re: Generic DB2 SQLCODE handle for cobol

PostPosted: Fri Apr 13, 2012 1:04 pm
by enrico-sorichetti
Bumping the thread.
Please guys, realy ANY idea is most helpful!


remember that... replying is
on voluntary base
on our own time and equipment
-- from a legal point of view <generally>
-- using the employer time and equipment for tasks not related to the job
-- is cause for lawful termination
-- replying to forum questions is NOT job related <generally>

based on experience ==> no reason to research from scratch something just for Your needs
interest of the topic
clarity of the requirement

there is no commitment for a reply,
so if You do not get any You will have to live without .

and usually soliciting/pestering for a reply will just piss off people

Re: Generic DB2 SQLCODE handle for cobol

PostPosted: Fri Apr 13, 2012 6:20 pm
by mandor
Sorry to see you pissed off.
I just wanted people to know that anything would help. I wasnt planning on bumping every day.
I just bumped once after a week. A "no way" answer would be just as good and helpful.

Re: Generic DB2 SQLCODE handle for cobol

PostPosted: Sat Apr 14, 2012 12:07 am
by dick scherrer
Hello,

I know of no method of capturing the predicates of a query after it has executed (successfully or not).

What many of my clients have done (with whatever database system is used) is to have a callable "db error module" that is invoked whenever a database failure has been detected. This requires that any program that is to use the common module must be modified to CALL the module and present all of the information the called module requires.

It is best done when the system is being developed. It can be implemented over time and does not require a global change. . .

Re: Generic DB2 SQLCODE handle for cobol

PostPosted: Sat Apr 14, 2012 1:37 pm
by NicC
Have you tried looking in the SQL manuals - there is something called 'whenever' and, possibly - cannot remember, another statement that sits alongside

Re: Generic DB2 SQLCODE handle for cobol

PostPosted: Sat Apr 14, 2012 2:30 pm
by mandor
Thanks guys!

Now, by combining these I can set WHENEVER SQLERROR GOTO DB2-ERROR-HANDLE.
This doesn't differ significantly from manualy handling sql errors as there is still a need for handling +100, -803 or -811.
Mr Dick Scherrer, how would one pass the predicates to the db error module? I cannot pass the whole dclgen structure(s) involved with the query as I don't have a way of parsing them genericaly or to unpack the numeric fields.

My application is reasonably flexible and relatively easy to change. And it is a CICS - COBOL - DB2 app.

Re: Generic DB2 SQLCODE handle for cobol

PostPosted: Sun Apr 15, 2012 5:08 am
by dick scherrer
Hello,

To get "things" to the called error handling module, each program that is to use it would have to be changed to create working storage fields that contain all of th einto you want to be handled by the common error module.

Dependng on how much you want to present, the amount of data to be formatted and given to the called module could be quite small or very large.

I'm not sure how this would be used. Keep in mind that many selects run into many "lines of code" . . .

Re: Generic DB2 SQLCODE handle for cobol

PostPosted: Mon Apr 16, 2012 6:11 pm
by mandor
I'm not sure if you understand me, Mr Scherrer.

The simple question is: Is there any way to capture the values of the host variables of the LAST SQL query so they can be written to some log?

Re: Generic DB2 SQLCODE handle for cobol

PostPosted: Mon Apr 16, 2012 9:48 pm
by dick scherrer
Hello,

I'm not sure if you understand me, Mr Scherrer.
I believe i do understand you :)

As i posted previously:
I know of no method of capturing the predicates of a query after it has executed (successfully or not).


Not knowing how to get what you want, i mentioned "another" way to get something similar that has been used many, many places with quite a few different database management systems.