"Doug Chamberlin" <DChamberlin@...> wrote in message
news:5.1.0.14.2.20011222082655.01ac78f0@......

>
> Everyone I know who has worked with triggers and stored procs in Interbase
> has had trouble identifying exactly where the errors occurred when
> Interbase reports an error. They usually come to me for help determining
> where the counting starts from. It certainly does not start counting

lines

> at the beginning of the script file being processed (which is everyone
> seems to expect and what I'd like).

I think this was discussed in IB-Architect a time ago, WRT what should be
done by the client and what should be done by the engine. The engine only
knows how to execute one statement at a time. Examples of statements, as you
know well, are:
SELECT, no matter how complex
INSERT
CREATE PROCEDURE

A script is no more than a sequence of statements. The client sents one at a
time, so the engine cannot know what came before, unless it loads the whole
script before. Maybe isql could try to add to the current line reported by
the engine the number of lines that precede the statement that's being
executed. This could be done only in non-interactive mode, with the -i
option.

> Also the character positioning treats tab characters as one char which
> confuses some people.

That's a moot point. Traditionally, tab has been 8 chars if I'm not
mistaken, but it's configurable in several editors. The underlying Windows
controls allow to define tabs positioning.

> Finally, the character positioning does not point to the beginning of the
> offending token, or alternatively to the end of the offending token, but

to

> the end of the preceding token. Either of the first two choices would be
> more helpful.

I agree, but you should base your judgement on the FB state that's the one
we can modify, not on IB unless you want to pose the problem to Borland. A
few days ago, I replied to a person that got an error in a strange position
that didn't exist in the command. I argued that my message was different and
asked why he was getting that message. Few minutes after and without needing
a reply, I realized he was using IB, so indeed he got a different message.

> Here is an example from a IB 5.6 WISQL script output:
> CONNECT
> 'C:\Program Files\InterBase
> Corp\InterBase\examples\database\EMPLOYEE.GDB' user 'SYSDBA' password
> 'masterkey';
> COMMIT;
> SET TERM ^ ;

1> ALTER PROCEDURE ADD_EMP_PROJ (EMP_NO SMALLINT,

> PROJ_ID CHAR(5))

2> AS
3>
4> BEGIN
5> BEGIN
6> INSERT INTO employee_project (emp_no, proj_id) XXX (:emp_no,
:proj_id);
7> WHEN SQLCODE -530 DO
8> EXCEPTION unknown_emp_id;
9> END
10> SUSPEND;
11> END

> ^
>
> Statement failed, SQLCODE = -104
> Dynamic SQL Error
> -SQL error code = -104
> -Token unknown - line 7, char 49
> -XXX
> COMMIT^
>
> The "Line 7" makes sense only if you understand it is line 7 of the stored
> proc when you count the ALTER PROCEDURE line as 1.

I disagree. See how I counted the lines. IB got the wrong line. It happened
to FB, too until I made it a little better.

> It would be more useful
> to identify it as line 12 of the script. That way I can have my text

editor

> go directly to line 12.

Sure, but remember set term is not a command seen by the engine; it's a hack
invented by isql since it doesn't do parsing, otherwise it could handle the
CREATE statement without changing terminator.

> The "char 49" points to the right paren following proj_id. This is NOT the
> problem token. It should report "char 51" as the problem, or maybe char

53.

It almost always misses the column. Also, it counts from zero. I made it
count from one. C programmers count from zero but users count from one.

This is your example handled by Firebird:

SQL> connect f:/bd/borland/interbase/examples/database/employee.gdb;
Database: f:/bd/borland/interbase/examples/database/employee.gdb
SQL> set term ^;
SQL> alter procedure add_emp_proj(emp_no smallint, proj_id char(5))
CON> as
CON>
CON> begin
CON> begin
CON> insert into employee_project (emp_no, proj_id) XXX (:emp_no, :proj_id)
CON> when sqlcode -530 do
CON> exception unknown_emp_id;
CON> end
CON> suspend;
CON> end
CON> ^
Statement failed, SQLCODE = -104

Dynamic SQL Error
-SQL error code = -104
-Token unknown - line 6, char 48
-XXX
SQL>

Here, line 6 matches if you count one from the ALTER line. Column 48 matches
exactly the first "X" if you make the "i" the character number one. I didn't
use indentation. We do not ack TAB, at least I'm not aware of it. I don't
know if everybody will agree to count it as 8 characters.

> This is not the best of examples since XXX stands out as an error and is
> easily seen. However, if the offending token was an identifier which is
> used throughout the procedure it becomes a bit more difficult to find.

This

> is especially true when working with a parser which doesn't match your
> expectations of identifying correct syntax.

Maybe we can display the offending line, but if people insist in writing
statements as Paul does, this is not going to help. I think this is a task
for the client tool. The client tool can get the position and (as configured
by the user) show the current line or show the whole script with a caret.

C. (Member of the royal academy for ancient and legacy bugs studies.)
--
Claudio Valderrama C. - http://www.cvalde.com - http://www.firebirdSql.org
Independent developer
Owner of the Interbase® WebRing