--- In firebird-support@yahoogroups.com, "jrodenhi" <jack@...> wrote:

>
> Hi,
> I'm just sort of starting to reach a little farther with Firebird. Up
> to now, I have pretty much limited my activities to simple
> Insert, Update and Select activities in my database. Now I'm starting
> to check out the selectable stored procedures. This is a pretty slick
> feature. I just finished a little procedure that I use with
> Fast-Reports to print checks. If you select from the procedure while
> giving it a zero parameter, it returns the next check number from the
> Bank record. If you give it a non-zero parameter, it returns that
> same number to you as the next check number and updates the Bank to
> start the numbering there. It also updates the payable with the check
> number. That's nice.
>
> My question is this. I really like this power but I spent a fair
> amount of time debugging the procedure. I learned one little trick
> while I was developing it but I'll bet there are some things that some
> of the more experienced developers are doing that won't be apparent to
> me for a long time, if ever. Have you come up with any tricks for
> debugging stored procedures that help you a lot? Do you have any UDFs
> that you use to help in debugging?

We have a pretty simple UDF function that can save hours on complex
procedures.

Its definition is:

DECLARE EXTERNAL FUNCTION FB_ODS
CSTRING(64) CHARACTER SET NONE
RETURNS INTEGER BY VALUE
ENTRY_POINT 'ODS' MODULE_NAME 'FB_Foo';

All this function does is call OutputDebugString (google about it) and
return 0 back to firebird. I can't provide the source of the module,
but it is a trivial amount of code.

I drop lines into the stored procedure / trigger such as:

ODS = FB_ODS('Bar = ' || COALESCE(:Bar, '<null>'));

Then launch Debugview (SysInternals freeware), call the stored
procedure or perform an action on the underlying table to fire the
trigger and watch the running commentary in DebugView.

Within a few minutes you can see if a given variable does not contain
the value you thought it contained.

In the version of the dll that we release to customers, ODS simply
does nothing just in case we leave something in there by mistake.

> Do you do anything
> different with triggers? Or, maybe a little simpler, do you have a
> simple rule for when you use a colon with a variable and when you
> don't?

Rule of thumb I use:

Always use a colon except if it is on the left side of an equals.

It may be more complex but I have never encountered any issues with
this approach.

Adam