Hello All,
I have a procedure that is getting -443 after upgrading to V9.5 FP1.
The procedure was written to output only the SQLCODE. Here is the
output that is returned:
Value of output parameters
--------------------------
Parameter Name : V_OUTPUT
Parameter Value : ERR:-443
Return Status = 0
Error description from doc:
SQL0443N Routine "<routine-name>" (specific name "<specific-name>")
has
returned an error SQLSTATE with diagnostic text "<text>".
Explanation:
An SQLSTATE was returned to DB2 by routine "<routine-name>" (specific
name "<specific-name>"), along with message text "<text>". The routine
could be a user-defined function or a user-defined method.
My question is how can I modify this procedure to get all of the error
'message text'?
This is probably a very simple question, but I am just totally inept
at writing SPs.
Thanks in advance. 4 7739
jdokos wrote:
Hello All,
I have a procedure that is getting -443 after upgrading to V9.5 FP1.
The procedure was written to output only the SQLCODE. Here is the
output that is returned:
Value of output parameters
--------------------------
Parameter Name : V_OUTPUT
Parameter Value : ERR:-443
Return Status = 0
Error description from doc:
SQL0443N Routine "<routine-name>" (specific name "<specific-name>")
has
returned an error SQLSTATE with diagnostic text "<text>".
Explanation:
An SQLSTATE was returned to DB2 by routine "<routine-name>" (specific
name "<specific-name>"), along with message text "<text>". The routine
could be a user-defined function or a user-defined method.
My question is how can I modify this procedure to get all of the error
'message text'?
It would probably be helpful if you posted your stored proc so we can
see how you're doing this.
My guess is that you've set up something like,
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
set V_OUTPUT = 'ERR:' || char(SQLCODE);
END;
On Jun 19, 9:19*pm, Ian <ianb...@mobile audio.comwrote:
jdokos wrote:
Hello All,
I have a procedure that is getting -443 after upgrading to V9.5 FP1.
The procedure was written to output only the SQLCODE. *Here is the
output that is returned:
Value of output parameters
* --------------------------
* Parameter Name *: V_OUTPUT
* Parameter Value : ERR:-443
* Return Status = 0
Error description from doc:
SQL0443N *Routine "<routine-name>" (specific name "<specific-name>")
has
* * * returned an error SQLSTATE with diagnostic text "<text>".
Explanation:
An SQLSTATE was returned to DB2 by routine "<routine-name>" (specific
name "<specific-name>"), along with message text "<text>". The routine
could be a user-defined function or a user-defined method.
My question is how can I modify this procedure to get all of the error
'message text'?
It would probably be helpful if you posted your stored proc so we can
see how you're doing this.
My guess is that you've set up something like,
* * DECLARE EXIT HANDLER FOR SQLEXCEPTION
* * * *BEGIN
* * * * * set V_OUTPUT = 'ERR:' || char(SQLCODE);
* * * *END;- Hide quoted text -
- Show quoted text -
Here is the handler portion of the SP:
DECLARE EXIT HANDLER FOR SQLEXCEPTION
SET v_output = CONCAT('ERR:', CHAR(SQLCODE));
DECLARE CONTINUE HANDLER FOR SQLWARNING
SET l_sqlcode = 0;
I am trying to find a way to help the application team rewrite this to
get the complete error text from the procedure.
Thanks,
jdokos wrote:
On Jun 19, 9:19 pm, Ian <ianb...@mobile audio.comwrote:
>jdokos wrote:
>>Hello All, I have a procedure that is getting -443 after upgrading to V9.5 FP1. The procedure was written to output only the SQLCODE. Here is the output that is returned: Value of output parameters -------------------------- Parameter Name : V_OUTPUT Parameter Value : ERR:-443 Return Status = 0 Error description from doc: SQL0443N Routine "<routine-name>" (specific name "<specific-name>") has returned an error SQLSTATE with diagnostic text "<text>". Explanation : An SQLSTATE was returned to DB2 by routine "<routine-name>" (specific name "<specific-name>"), along with message text "<text>". The routine could be a user-defined function or a user-defined method. My question is how can I modify this procedure to get all of the error 'message text'?
It would probably be helpful if you posted your stored proc so we can see how you're doing this.
My guess is that you've set up something like,
DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN set V_OUTPUT = 'ERR:' || char(SQLCODE); END;- Hide quoted text -
- Show quoted text -
Here is the handler portion of the SP:
DECLARE EXIT HANDLER FOR SQLEXCEPTION
SET v_output = CONCAT('ERR:', CHAR(SQLCODE));
DECLARE CONTINUE HANDLER FOR SQLWARNING
SET l_sqlcode = 0;
As expected.
I am trying to find a way to help the application team rewrite this to
get the complete error text from the procedure.
The solution may be to not use the exit handler for any SQLEXCEPTION.
Currently, your stored procedure runs successfully (as far as DB2 and
the calling application are concerned). The application has to parse
the V_OUTPUT variable to determine if the procedure was actually
successful.
If you don't trap the SQLEXCEPTION, your procedure will fail "properly"
-- meaning the application will see that the "CALL YOUR_SP()" statement
failed. You'd need to change your code to handle this, but you would
get the actual SQLCODE and DB2 error message text.
Sometimes it's useful to trap specific errors, only so you can provide
more descriptive error codes/messages. For example, SQLSTATE '23505'
(primary key violation) isn't always very useful. So you could trap
it and raise a more descriptive error:
declare pk_violation condition for sqlstate '23505';
declare exit handler for pk_violation
signal sqlstate '75025'
set message_text = 'A user with this employee ID already exists.';
Jeff,
You can also have ur exit handler capture the message_text instead and
pass that out as an output parameter..
DECLARE EXIT HANDLER FOR SQLEXCEPTION
get diagnostics exception 1 V_OUTPUT = message_text;
-SA
On Jun 20, 5:14*pm, Ian <ianb...@mobile audio.comwrote:
jdokos wrote:
On Jun 19, 9:19 pm, Ian <ianb...@mobile audio.comwrote:
jdokos wrote: Hello All, I have a procedure that is getting -443 after upgrading to V9.5 FP1. The procedure was written to output only the SQLCODE. *Here is the output that is returned: Value of output parameters * -------------------------- * Parameter Name *: V_OUTPUT * Parameter Value : ERR:-443 * Return Status = 0 Error description from doc: SQL0443N *Routine "<routine-name>" (specific name "<specific-name>") has * * * returned an error SQLSTATE with diagnostic text "<text>". Explanation: An SQLSTATE was returned to DB2 by routine "<routine-name>" (specific name "<specific-name>"), along with message text "<text>". The routine could be a user-defined function or a user-defined method. My question is how can I modify this procedure to get all of the error 'message text'?
It would probably be helpful if you posted your stored proc so we can
see how you're doing this.
My guess is that you've set up something like,
* * DECLARE EXIT HANDLER FOR SQLEXCEPTION
* * * *BEGIN
* * * * * set V_OUTPUT = 'ERR:' || char(SQLCODE);
* * * *END;- Hide quoted text -
- Show quoted text -
Here is the handler portion of the SP:
DECLARE EXIT HANDLER FOR SQLEXCEPTION
SET v_output = CONCAT('ERR:', CHAR(SQLCODE));
DECLARE CONTINUE HANDLER FOR SQLWARNING
SET l_sqlcode = 0;
As expected.
I am trying to find a way to help the application team rewrite this to
get the complete error text from the procedure.
The solution may be to not use the exit handler for any SQLEXCEPTION.
Currently, your stored procedure runs successfully (as far as DB2 and
the calling application are concerned). *The application has to parse
the V_OUTPUT variable to determine if the procedure was actually
successful.
If you don't trap the SQLEXCEPTION, your procedure will fail "properly"
-- meaning the application will see that the "CALL YOUR_SP()" statement
failed. *You'd need to change your code to handle this, but you would
get the actual SQLCODE and DB2 error message text.
Sometimes it's useful to trap specific errors, only so you can provide
more descriptive error codes/messages. *For example, SQLSTATE '23505'
(primary key violation) isn't always very useful. *So you could trap
it and raise a more descriptive error:
* * declare pk_violation condition for sqlstate '23505';
* * declare exit handler for pk_violation
* * * *signal sqlstate '75025'
* * * *set message_text = 'A user with this employee ID alreadyexists.' ;
You can also have ur exit handler capture the message_text instead and
pass that out as an output parameter..
DECLARE EXIT HANDLER FOR SQLEXCEPTION
get diagnostics exception 1 l_message_text = message_text; This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Gema Gema |
last post by:
I have a large collection of directories full of various files and am
looking to create custom text files for the contents of each
directory. Here is the situation:
The directories are named with a name and number, i.e. Smith1234567.
I am looking to create a text file named with the number portion of
the directory name (1234567.txt). The contents of the text file would
look similar to the following, where the "TIF" files at the end of...
|
by: Pradip Sagdeo |
last post by:
I need to replace a portion of a url in a column as a result of
changing servers. Is there a SELECT/REPLACE/UPDATE combination query
that can do this. The table has close to a thousand entries and would
be nice if a query can be set to do this. Tried the REPLACE example
in the BOOKS ONLINE but it creates syntax error, apparently because it
does not like the characters in the url and/or wildcards. I don't need
to replace the entire url,...
|
by: Michael |
last post by:
I have an application that will export two files to fixed width text
to combine as a single text file. The first export will be a query
containing header information for the file, the second query will be a
table containing transactions. I have a routine that uses a command
button to set up the export and a module that takes care of the merge.
I've scraped this together using posts on this group The command
button portion works but I get...
|
by: JASON.BUCK |
last post by:
Access 2000
I have a text field named RoadName that contains the total road name,
for
example, "Johnson Road". I have another text field named StreetName
that I would like to just contain the name portion of the RoadName, in
this case,
just "Johnson". Is there a way to either remove the Road portion from
text
strings in the RoadName field, so that I am just left with "Johnson"?
Or
|
by: Darin |
last post by:
I have the following code that opens a comma-delimited text file:
connS = "Provider=Microsoft.Jet.OLEDB.4.0;"
connS &= "Data Source=\tmp;"
connS &= "Extended Properties=""text;HDR=No;FMT=Delimited"""
Dim tmpConnection As New System.Data.OleDb.OleDbConnection(connS)
tmpConnection.Open()
strsql = "SELECT * FROM PAYROLL.TXT"
Dim da As New System.Data.OleDb.OleDbDataAdapter(strsql, tmpConnection)
| |
by: lorirobn |
last post by:
Hi,
I have a form with several combo boxes, continuous form format, with
record source a query off an Item Table. The fields are Category,
Subcategory, and Color. I am displaying descriptions, not ID's, so I
have to convert the ID's from various lookup tables.
The combo boxes work fine except for subcategory, which is dependent on
category. Depending on category, the drop-down box for subcategory
will display different items. (for...
|
by: Vish |
last post by:
Hi,
I need to make the text on a combobox that is disabled to be drawn with a
black color. I was not able to find any help on this online. The drawItem
seems to apply only for the dropdpwn items. Any ideas or sugesstions would be
appreciated.
Thank you,
|
by: Warren Post |
last post by:
At <http://snow.prohosting.com/srcopan/dry/test.es.html>, you will see
that the background image runs down the left margin. The right hand side
of it is faded, watermark style, but it is one single image.
my intention is for the header and text to appear to the right of the
unfaded portion of the image, and on top of the watermarked portion. At
most viewport widths it works as intended. However, if the viewport is
narrowed enough to...
|
by: =?Utf-8?B?QnJpYW4gQ29vaw==?= |
last post by:
This is an example of the data;
2007/07/27 11:00:03 ARES_INDICATION 010.050.016.002 404.2.01 (6511)
RX 74 bytes
2007/07/27 11:00:03 65 11 26 02 BC 6C AA 20 76 93 51 53 50 76 13
48
2007/07/27 11:00:03 52 00 52 02 02 C7 83 D7 07 07 1B 0B 00 00 00
00
2007/07/27 11:00:03 28 0A 06 06 06 06 06 06 06 06 06 06 06 0A 06
06
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |