 | |
Last SQL Statement by Connection?
sybase.public.sqlanywhere.general sybase.public.sqlanywhere.general
 |
| | Thread Tools | Display Modes |
#11
 
| |||
| |||
|
Re: Last SQL Statement by Connection? -
04-26-2006
, 04:36 PM
|
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 in message news:444fa712$1 (AT) forums-1-dub (DOT) .. Robert Paresi wrote: 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. 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/ |
#12
| |||
| |||
|
|
The connection_property('LastStatement') mechanism records the last statement *prepared*, or parsed, for the connection. Usually this is equivalent to the last statement *executed*, and provides the information most people want. If you were to prepare a "select connection_property('LastStatement')" statement before executing any other statements and save the handle to that prepared statement, when an error occurs you could use the saved statement handle to obtain the statement that caused the error. This assumes that the API you are using allows access to statement handles. I withdraw my earlier comment that LastStatement won't work. 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 in message news:444fc2d7$1 (AT) forums-2-dub (DOT) .. 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 in message news:444fa712$1 (AT) forums-1-dub (DOT) .. Robert Paresi wrote: 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. 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/ |
#13
| |||
| |||
|
|
Bruce, Ok - I need some additional information - as I am trying to figure out what is going. When is the LASTSTATEMENT recorded in the SA_CONN_ACTIVITY??? Before execution or After Execution. This is VERY IMPORTANT to me to know and it is not in the documentation. Let's say you execute a statement, which is relying on a lock to be release. If you go to another workstation and type SA_CONN_ACTIVITY, are you seeing the statement on the frozen workstation that is CAUSING the freezing or are you seeing the last successful completed SQL Statement. I have an issue with frozen workstations and I don't know if SA_CONN_ACTIVITY is telling me which statement is BEING EXECUTED at that exact second. Where is it documented on when/how this is updated? Thank you very much. -Robert "Bruce Hay" <h_a_y~a_t~i_a_n_y_w_h_e_r_e~d_o_t~c_o_m> wrote in message news:444fe7ee$1 (AT) forums-1-dub (DOT) .. The connection_property('LastStatement') mechanism records the last statement *prepared*, or parsed, for the connection. Usually this is equivalent to the last statement *executed*, and provides the information most people want. If you were to prepare a "select connection_property('LastStatement')" statement before executing any other statements and save the handle to that prepared statement, when an error occurs you could use the saved statement handle to obtain the statement that caused the error. This assumes that the API you are using allows access to statement handles. I withdraw my earlier comment that LastStatement won't work. 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 in message news:444fc2d7$1 (AT) forums-2-dub (DOT) .. 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 in message news:444fa712$1 (AT) forums-1-dub (DOT) .. Robert Paresi wrote: 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. 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/ |
#14
| |||
| |||
|
|
"The connection_property('LastStatement') mechanism records the last statement *prepared*, or parsed, for the connection." How is this confusing? I'm jumping in here, mainly because I'm curious what you'll do with this information. You'll collect gigabytes of data, 99.9999% of which will be valid SQL statements that parsed and executed without a problem, just to trap the .0001% of those that threw an exception? I think, if a developer coded an SQL statement and spelled WHERE as WHRE, you've got bigger problems than trying to trap that in production. As in, how did a typo like that get released to production in the first place? -- Paul Horan[TeamSybase] Cynergy Systems www.cynergysystems.com "Robert Paresi" <FirstInitialLastName (AT) innquest (DOT) com> wrote in message news:445001c3$1 (AT) forums-1-dub (DOT) .. Bruce, Ok - I need some additional information - as I am trying to figure out what is going. When is the LASTSTATEMENT recorded in the SA_CONN_ACTIVITY??? Before execution or After Execution. This is VERY IMPORTANT to me to know and it is not in the documentation. Let's say you execute a statement, which is relying on a lock to be release. If you go to another workstation and type SA_CONN_ACTIVITY, are you seeing the statement on the frozen workstation that is CAUSING the freezing or are you seeing the last successful completed SQL Statement. I have an issue with frozen workstations and I don't know if SA_CONN_ACTIVITY is telling me which statement is BEING EXECUTED at that exact second. Where is it documented on when/how this is updated? Thank you very much. -Robert "Bruce Hay" <h_a_y~a_t~i_a_n_y_w_h_e_r_e~d_o_t~c_o_m> wrote in message news:444fe7ee$1 (AT) forums-1-dub (DOT) .. The connection_property('LastStatement') mechanism records the last statement *prepared*, or parsed, for the connection. Usually this is equivalent to the last statement *executed*, and provides the information most people want. If you were to prepare a "select connection_property('LastStatement')" statement before executing any other statements and save the handle to that prepared statement, when an error occurs you could use the saved statement handle to obtain the statement that caused the error. This assumes that the API you are using allows access to statement handles. I withdraw my earlier comment that LastStatement won't work. 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 in message news:444fc2d7$1 (AT) forums-2-dub (DOT) .. 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 in message news:444fa712$1 (AT) forums-1-dub (DOT) .. Robert Paresi wrote: 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. 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/ |
#15
| |||
| |||
|
|
Bruce, Ok - I need some additional information - as I am trying to figure out what is going. When is the LASTSTATEMENT recorded in the SA_CONN_ACTIVITY??? Before execution or After Execution. This is VERY IMPORTANT to me to know and it is not in the documentation. Let's say you execute a statement, which is relying on a lock to be release. If you go to another workstation and type SA_CONN_ACTIVITY, are you seeing the statement on the frozen workstation that is CAUSING the freezing or are you seeing the last successful completed SQL Statement. I have an issue with frozen workstations and I don't know if SA_CONN_ACTIVITY is telling me which statement is BEING EXECUTED at that exact second. Where is it documented on when/how this is updated? Thank you very much. -Robert "Bruce Hay" <h_a_y~a_t~i_a_n_y_w_h_e_r_e~d_o_t~c_o_m> wrote in message news:444fe7ee$1 (AT) forums-1-dub (DOT) .. The connection_property('LastStatement') mechanism records the last statement *prepared*, or parsed, for the connection. Usually this is equivalent to the last statement *executed*, and provides the information most people want. If you were to prepare a "select connection_property('LastStatement')" statement before executing any other statements and save the handle to that prepared statement, when an error occurs you could use the saved statement handle to obtain the statement that caused the error. This assumes that the API you are using allows access to statement handles. I withdraw my earlier comment that LastStatement won't work. 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 in message news:444fc2d7$1 (AT) forums-2-dub (DOT) .. 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 in message news:444fa712$1 (AT) forums-1-dub (DOT) .. Robert Paresi wrote: 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. 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/ |
#16
| |||
| |||
|
|
connection_property('LastStatement'), which is used by sa_conn_activity(), is set when a statement is prepared and is cleared when a statement is dropped. Only one statement string is remembered for each connection. For your original request of being able to record statements that cause errors, you would need to obtain the property value before the statement causing the error is dropped, and would need to use a statement prepared earlier to obtain it (as I described before). If sa_conn_activity() is reporting a non-empty value for a connection, it is most likely the statement that the connection is currently executing. If the statement had completed, it would likely have been dropped and the property value would have been cleared. If an application prepares multiple statements and retains their statement handles, the last statement value will not reflect what a connection is currently doing. Most applications have not been optimized this way. I will try to have the documentation on this feature improved to include these details. 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 in message news:445001c3$1 (AT) forums-1-dub (DOT) .. Bruce, Ok - I need some additional information - as I am trying to figure out what is going. When is the LASTSTATEMENT recorded in the SA_CONN_ACTIVITY??? Before execution or After Execution. This is VERY IMPORTANT to me to know and it is not in the documentation. Let's say you execute a statement, which is relying on a lock to be release. If you go to another workstation and type SA_CONN_ACTIVITY, are you seeing the statement on the frozen workstation that is CAUSING the freezing or are you seeing the last successful completed SQL Statement. I have an issue with frozen workstations and I don't know if SA_CONN_ACTIVITY is telling me which statement is BEING EXECUTED at that exact second. Where is it documented on when/how this is updated? Thank you very much. -Robert "Bruce Hay" <h_a_y~a_t~i_a_n_y_w_h_e_r_e~d_o_t~c_o_m> wrote in message news:444fe7ee$1 (AT) forums-1-dub (DOT) .. The connection_property('LastStatement') mechanism records the last statement *prepared*, or parsed, for the connection. Usually this is equivalent to the last statement *executed*, and provides the information most people want. If you were to prepare a "select connection_property('LastStatement')" statement before executing any other statements and save the handle to that prepared statement, when an error occurs you could use the saved statement handle to obtain the statement that caused the error. This assumes that the API you are using allows access to statement handles. I withdraw my earlier comment that LastStatement won't work. 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 in message news:444fc2d7$1 (AT) forums-2-dub (DOT) .. 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 in message news:444fa712$1 (AT) forums-1-dub (DOT) .. Robert Paresi wrote: 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. 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/ |
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.