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

[Markus]

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

[Artur]

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

[Serge Rielau]

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

[Brian Tkatch]

>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.

[Markus]

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

[Knut Stolze]

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

[Brian Tkatch]

Your example worked here (too) on Solaris.

I would check if the ? needs to be escaped.

B.