Re: Print SQL in Error

[Ray Ontko <rayo@ONTKO.COM>]

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/