Accessing SQLCA within a SQL procedure

Roger Miller

Accessing SQLCA within a SQL procedure
I think you meant DSNHLI, rather than DSNHSQL. That's the usual trap
point, and there is a discussion about techniques for using it with call
attach in the Application Programming and SQL Reference. Some of the same
techniques can be used with other attaches.

Roger Miller

On Tue, 31 Dec 2002 19:35:36 +1000, James Campbell
<[login to unmask email]> wrote:

>I have a little experience with SQL Language Stored Procedures, but
>here goes:
>
>V7 - not officially
>Vnext - supposedly GET DIAGNOSTICS will do the trick.
>
>However, and here the hacker in me comes out of its shell, there is a
>technique that I believe (conceptually it should, but like a battle
>plan in war, I don't know if it will survive contact with reality)
>there is a way of getting the SQLCA - even in V6.
>
>The starting points to recognise are
>- your SQL Language SP is converted to a C language program
>- the C language code is available - a suitable amendment to DSNHSQL
>or DSNTPSMP will enable a user written program to take the code and
>make whatever amendments are desirable. WARNING this is not a
>documented programming interface, you will probably receive a "you
>keep the pieces" response if there are any problems (in fact I'll
>probably get one for just suggesting this).
>
>I would suggest making these changes to the code immediately before
>it is compiled. The way that WHENEVER/CONDITION code is implemented
>means that if you use the un-precompiled C code you may have problems
>finding a suitable point to insert your own code.
>
>So, if you make it a rule to have an output parameter called, say,
>LAST_SQLCA (as a bit string), read through the code looking for calls
>to DSNHLI and immediately before each add a memcpy to copy the SQLCA
>to LAST_SQLCA. If you wanted to get fancy you could call DSNTIAR to
>format the SQLCA and return the formatted version, enabling remote
>clients to understand it.
>
>Remember that there are a number of things that can change that will
>break this technique. So be prepared to have to rewrite your SQL
>code.
>
>James Campbell
>
>On 30 Dec 2002 at 6:11, Theo van Westrienen wrote:
>
>> Thanks for all the info and the references to the IBM site. But: I still
>> don't understand whether it is (in v7) or will be possible (in
>> vNext) to access SQLCA or at least the message text from within a SQL
>> Procedure.
>> Earlier this year (july) there was a thread called "Stored Procedure
Error
>> Checking", in which there was a reference to a few PTFs (a.o. PQ56323).
We
>> installed these, but I still cannot find a way to access the error
message
>> text.
>>
>> Can someone help me out on this?
>>
>> regards, Theo van Westrienen
>>
>>
>>
the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list
can
>
>
>
the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list
can



Jeremiah Eden

Re: Accessing SQLCA within a SQL procedure
(in response to Roger Miller)
I opened the ETR which drove PTF UQ66790 for apar PQ56323. It is suppose to
copy the entire SQLCA back to the SP for unhandled conditions. Originally
they were going to use Signal logic, but it was too difficult, so we agreed
on copying back the SQLCA. If this is not working correctly, then you should
open a problem with IBM.

ROGER, the ETR is archived.. If you want to see what IBM agreed to do, you
can email me directly for the number and branch office.

-----Original Message-----
From: Roger Miller [mailto:[login to unmask email]
Sent: Thursday, January 02, 2003 12:21 PM
To: [login to unmask email]
Subject: Re: Accessing SQLCA within a SQL procedure

>snip
>snip
>
>On 30 Dec 2002 at 6:11, Theo van Westrienen wrote:
>
>> Thanks for all the info and the references to the IBM site. But: I still
>> don't understand whether it is (in v7) or will be possible (in
>> vNext) to access SQLCA or at least the message text from within a SQL
>> Procedure. Earlier this year (july) there was a thread called "Stored
Procedure
>>Error Checking", in which there was a reference to a few PTFs (a.o.
PQ56323).
>>We installed these, but I still cannot find a way to access the error
message text.