Re: Print SQL in Error

[Ray Ontko <rayo@ONTKO.COM>]

Dave,

You _may_ be able to get some of what you want by running
the SQR program using "-s" (statistics), but that won't tell you
the content of any dynamic portions of the query.

I've added "$sql-text" to my list of requested enhancements to SQR.
See number 17 in:

   http://www.ontko.com/sqr/enhancements.html

Ray

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

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