Hello,

I know the connection number, and I'd like to query the database to get the
last SQL Statement submitted (whether it be correct or not). I'm trying to
just log any errors in my program.

Meaning, I have a general error handler in my program. If the SQL Statement
fails, I'd like to write it to a log for future client support when they
call.

Is there a way for me to get the last issues SQL Statement for the current
(My Application) connection so I can log it to a file for future review?

-Robert

BTW - I know I can use SA_CONN_ACIVITY - but that just returns
SA_CONN_ACTIVITY.

Cart before the Horse!!!

-Robert


"Robert Paresi" <FirstInitialLastName (AT) innquest (DOT) com> wrote

Look in the help index under "remember_last_statement".

Glenn

"Robert Paresi" <FirstInitialLastName (AT) innquest (DOT) com> wrote in
news:444d2d8b@forums-2-dub:



--
Glenn Paulley
Research and Development Manager, Query Processing
iAnywhere Solutions Engineering

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Glenn,

That does not work for me! The current active connection needs to be
responsible for recording this! What happens is the last statement ends up
being SA_CONNECT_ACTIVITY so you can't get this information for the
active/current connection in my program.

I am hoping Sybase would change the system so that a reference to itself
(SA_CONNECT_ACTIVITY) doesn't record SA_CONNECT_ACTIVITY but the previous
statement before that. Otherwise, this is useless to me. :-(

-Robert


"Glenn Paulley" <paulley (AT) ianywhere (DOT) com> wrote

For what you're trying to do, connection_property('LastStatement'), which is
what sa_conn_activity() uses, won't work.

If the statements you were tring to monitor were stored procedure calls (or
web service requests), you could add exception handlers to the procedures to
record the error before RESIGNALing the error to the caller. Otherwise,
enabling request logging (which records errors) could be done; however, this
will log everything, so would add a possible performance burden and would
require periodic filtering and purging of the logs. The last option, and the
one you're likely trying to avoid, is to modify the error handling within
your application so that the errors are logged.

Whitepapers, TechDocs, bug fixes are all available through the iAnywhere
Developer Community at http://www.ianywhere.com/developer

"Robert Paresi" <FirstInitialLastName (AT) innquest (DOT) com> wrote

I see your problem, but we update the last statement before we parse it, and
even if we did only conditionally update the last statement based on what
the statement was, that may cause other users problems.

I have two suggestions:
1) in your application, before each SQL statement, update a variable with
the SQL statement text so your error handler will know what statement
failed.
or 2) when you connect, determine the connection number (select
connection_property( 'number' )). Then if your error handler is called,
make a new connection, and on this new connection do select
connection_property( 'LastStatement', <conn_number> ). Since you are using
a different connection, the last statement text will not be replaced when
you attempt to retrieve it.

--

Ian McHardy (iAnywhere Solutions)

Please reply only to the newsgroup.

Whitepapers, TechDocs, bug fixes are all available through the iAnywhere
Developer Community at http://www.ianywhere.com/developer
"Robert Paresi" <FirstInitialLastName (AT) innquest (DOT) com> wrote

Trigger an event to record the last_statement property of a particular
connection (events run as an independent connection). Pass in the
connection_id (or user name) from the application as a parameter to the
event, so the event can retreive the LastStatement property for the right
connection by issuing the query

Select value
From sa_conn_properties()
where number = @conn_id and propname = 'LastStatement'

Glenn

"Robert Paresi" <FirstInitialLastName (AT) innquest (DOT) com> wrote in
news:444d498d$1@forums-1-dub:



--
Glenn Paulley
Research and Development Manager, Query Processing
iAnywhere Solutions Engineering

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Hi,

I have 17 DLL's and probably 10,000 SQL statements in the application. The
application has 100's and 100's of functions. #1 would take me months.

As for #2, I thought about that but there are two issues.

a) What if the second connection causes the user to go over # of connections
allowed?
b) I am not sure how to create a new connection level with the SQL Anywhere
driver that I am using in my application. I'd have to dig into that.

I think I'm going to have to modify my application to pass the &FILE name so
I can grab the statement from the driver, as it is stored on the TABLE
level, not the driver level ... OR see if the driver can return it based on
the file level, not the table level.

-Robert

"Ian McHardy" <mchardy_no (AT) spam_ianywhere (DOT) com> wrote

Robert Paresi wrote:
I still don't completely understand why it is you are trying to do what
you are. I personally don't know of any database app that does this at
the level you are talking about (capture the last statement just before
an abnormal disconnect?)

greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/

Greg,

My application processes SQL statements. If something goes wrong, an
errorcode is captured and I have a Generic Error Handler which stores the
information, and optionally gives the user the ability to transmit the error
information to our company database, so support can monitor problems.

I am working with the driver developer to see if I can just log the
statements on the driver level, before hit the ASA engine ... which they do
on the table level but not the driver level. I can do exactly what I want
on the TABLE level, but I have 150 tables and that is where my problem comes
in without coding a CASE statement of 150 lines.

Example:

MyView{prop:sql} = 'SELECT MYNAME,YOURNAME, THEIRNAME FROM MYTABLE WHRE
MYNAME = 'SpecialEd'
if errorcode()
ErrorHandler(111) ! ignore 111, it's not important
end


ErrorHandler function(short)

DriverError = FILEERROR()
LastSqlStatement = MyView{prop:sql}


Ok, so now I see that DriverError reports that WHERE is an invalid so and
so, and the last SQL Statement will show me the full statement. This works
just fine because I have MyView used in both places. However, My
"ErrorHandler" is a general error handles which traps up to 150 different
tables, so "MyView" is not valid as the lastSQL comes back from PROP:SQL on
the table level, not the driver level.

I tried changing ErrorHandler() to "sa_conn_activity" and it returns just
that "sa_conn_activity", so that won't work. I need it to show the SQL
Statement in error so I can record it to an error log.

I will see if I can get it returned/stored on the Driver level, since I see
Sybase ASA is unable to do what I want to do.

-Robert



"Greg Fenton" <greg.fenton_NOSPAM_ (AT) ianywhere (DOT) com> wrote