By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,851 Members | 1,729 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,851 IT Pros & Developers. It's quick & easy.

How can I output my own messages? Can anybody describe the Errorhandling please?

P: n/a
If I start an INSERT ... the Result is an "x Row(s) affected" on the
console.

Is it possible to print out my own messages (e.g. to output the value
of a variable)?

How can I do my own Errorhandling?
If I check, that a Parameter form my Procedure is wrong I want to stop
the Procedure with an Error (for example).

Markus

Nov 12 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
If you are talking about stored procedure language, you can generate
your own error messages using SIGNAL (check in DB2 information center).

However DB2 SQL is a language just to talk to database, not to output
messages to file or screen. So you must program it in your client
application.

You can also write you own UDF to write messages to file, and you can
the UDF in SQL (at DeveloperWorkd you can find sample implementations).

-- Artur Wronski

Nov 12 '05 #2

P: n/a
Markus wrote:
If I start an INSERT ... the Result is an "x Row(s) affected" on the
console.

Is it possible to print out my own messages (e.g. to output the value
of a variable)?

How can I do my own Errorhandling?
If I check, that a Parameter form my Procedure is wrong I want to stop
the Procedure with an Error (for example).

Markus

Take a look at the SIGNAL statement.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #3

P: n/a
>Is it possible to print out my own messages (e.g. to output
the value of a variable)?
Yes, there are two ways.

1) In the CREATE PROCEDURE statement, add an OUT Text VARCHAR(100) or
the like. When CALLing the PROCEDURE, pass a "?" (without the quotes)
for that parameter. When the CLP finishes the CALL, it will display the
contents of all OUT parameters.

2) In the PROCEDURE itself after the variable is set, add a block with
a CURSOR:
BEGIN
DECLARE x CURSOR WITH RETURN TO CLIENT FOR VALUES Text;
OPEN x;
END.

After the PROCEDURE finsihes, the CLP will ouput the data the CURSOR
has access to.
How can I do my own Errorhandling?


This is explained in the "Application Development Guide: Programming
Server Applications" in "Chapter 3. SQL-Bodied Routines" section
"Condition Handlers in SQL Procedures".

B.

Nov 12 '05 #4

P: n/a
Both of your questions will help me a little bit.
But I have some problems with the out-parameter.
This is my procedure:
CREATE PROCEDURE test_output2(OUT txt VARCHAR(100))
LANGUAGE SQL
BEGIN
SET txt = 'Hello World';
END

This is my call:
CALL test_output2(?)

And this is the Errormessage:
DB2 SQL error: SQLCODE: -313, SQLSTATE: 07004, SQLERRMC: null

What I'm doing wrong?

Markus

Nov 12 '05 #5

P: n/a
Markus wrote:
Both of your questions will help me a little bit.
But I have some problems with the out-parameter.
This is my procedure:
CREATE PROCEDURE test_output2(OUT txt VARCHAR(100))
LANGUAGE SQL
BEGIN
SET txt = 'Hello World';
END

This is my call:
CALL test_output2(?)

And this is the Errormessage:
DB2 SQL error: SQLCODE: -313, SQLSTATE: 07004, SQLERRMC: null

What I'm doing wrong?


I don't know. The above steps work quite fine on my system. (DB2 V8.2.3 on
Linux)

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Nov 12 '05 #6

P: n/a
Your example worked here (too) on Solaris.

I would check if the ? needs to be escaped.

B.

Nov 12 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.