--- In firebird-support@yahoogroups.com, "Steve Miller"
<Steve_Miller@...> wrote:

>
> I would like to print the value of a variable to screen. In other SQL
> dialects, this is the PRINT command. What is it in Firebird?
>

There is no such thing as a variable in SQL, let alone the concept of
a screen for messages to be redirected. I am going to assume you are
talking about PSQL (Firebirds language used in stored procedures and
triggers) because that is the only place a variable makes sense.

But first, we need to clear up any notion that there is a 'screen' to
return something to. iSQL is a client application that connects to the
Firebird database engine, similar to a client-server application you
or I can write. In fact there are iSQL-like clones like FSQL
(http://www.volny.cz/iprenosil/interbase/fsql.htm). So Firebird has no
screen it can PRINT anything to.

In a stored procedure, it will return all of the output parameters to
the client application (eg iSQL) every time you call suspend, so for
simple problems that may be good enough.

There is also the option of creating an 'external table'. An external
table is a file that firebird can treat as a limited table (no nulls,
no indices, fixed length fields, create, insert, select or drop only,
no transaction isolation, no rollback). You could insert into that
table and select the value from a different iSQL client. That is
probably the easiest but also the most limited.

The way we handle this problem is using a User Defined Function (UDF).
We created a function called ODS that wraps around the Windows API
call OutputDebugString in our UDF library. It also logs the time and
process ID (we use classic server so can trace that to a connection).

Inside our procedures or triggers, we can do this.

CREATE OR ALTER PROCEDURE SP_FOO
(
BAR INTEGER
)
AS
DECLARE VARIABLE DUMMY INTEGER;
BEGIN
DUMMY = ODS('BEGIN SP_FOO(' || COALESCE(BAR, '<NULL>') || ')';

-- Do whatever

DUMMY = ODS('END SP_FOO(' || COALESCE(BAR, '<NULL>') || ')';
END
^

Microsoft has a tool called DebugView (formerly Sysinternals) for free
that catches these messages in real time. You can just set a compiler
directive in your dll to comment out this function for your production
environment. This means you can just swap over the UDF dll if you want
logging.

It also means that you can trace problems by simply using your
application in the way that caused the problem, and you get a running
commentary of the procedures being called and their parameters. In my
opinion, this is far superior to just sending text to stdout without
any regard to what is calling it.

One step better would be to have debug hooks in PSQL itself so you
could use a debugging tool to step through the code. Some admin tools
have a PSQL emulator and provide this ability, but it is an emulator
running through the logic, not Firebird so it may miss from time to time.

Adam