Re: Print SQL in Error

[ron w scott <ron.w.scott@MAIL.SPRINT.COM>]

This may be more work than it's worth and may not even be possible on
your system, but you could read the SQR or SQC source file for the SQL
text and write it to your log.

If you use flag the SQL with a specific identifier ($SQL_name) and keep
track of the name of the SQR or SQC that the SQL is in ($SQR_SQC_name),
you could create an SQC to be called in your on-error procedure that
would read $SQR_SQC_name as a flat file and look for $SQL_name.  Once
$SQL_name is found, display or write all the lines that have a character
in the leftmost column starting with the 'begin-' and stopping with the
'end-'.

Just an idea.  If I didn't explain it well enough, feel free to contact
me.

-----Original Message-----
From: Dave.Ingram [mailto:Dave_Ingram@NAS.ADP.COM]
Sent: Monday, January 10, 2000 9:47 AM
To: SQR-USERS
Subject: Re: Print SQL in Error


Thanks for your insight.

We do use on-error=asdf and we will store the name of the current file,
name of the current procedure, and number of the sql statement within
the
procedure into variables so we can print them.  However, from your
description, is sounds like the only way to print the actual SQL code is
to
store it in a variable before execution.

Dave






Ray Ontko <rayo@ONTKO.COM>@list.iex.net> on 01/10/2000 09:58:21 AM

Please respond to sqr-users@list.iex.net

Sent by:  "Discussion of SQR, Brio Technology's database reporting
      language" <SQR-USERS@list.iex.net>


To:   SQR-USERS@list.iex.net
cc:
Subject:  Re: Print SQL in Error


Dave,

If you DO NOT have an on-error procedure for your SQL statement,
SQR will attempt to give you as much information as possible.
For example:

   (SQR 5528) ORACLE OPARSE error -904 in cursor 1:
      ORA-00904: invalid column name
   SQL:  SELECT t  from t where asdf = fred
   Error at:  fred

   Error on line 7:
      (SQR 3722) Could not set up cursor.

If you DO have an on-error, the only things you have available are
#sql-status, which is the error code (-00904) associated with the
error; $sql-error, the error message text; and, in the case of
a begin-sql paragraph, #sql-count, the number of records affected by
the DML statement.

What I have sometimes done is pass to the on-error procedure the
file name and line number at which the select begins (note that
the message above tells me which line but not which file in the
event that the query is in an #include file).  This allows me to
be certain of _where_ the error is, but not what the exact statement
was at the time of execution.  If portions of the query are dynamic,
you may wish to print the dynamic portion in your on-error procedure.
Again, the dynamic portion of the query may be passed as a string to
the on-error procedure, or you may wish to use global variables.

Hope this helps.

Ray

> Hello SQR experts:
>
> In the event that a SQL query fails, we would like SQR to print, to
the
> log, the SQL code which was executing at the time of the error.  Is
there
> an SQR variable which contains the currently executing SQL query?  Is
there
> any way to do this?
>
> Dave
>

----------------------------------------------------------------------
Ray Ontko   rayo@ontko.com   Phone 1.765.935.4283   Fax 1.765.962.9788
Ray Ontko & Co.   Software Consulting Services   http://www.ontko.com/