|
For Version 11.0.1.2276 and all subsequent versions, unless a "behavior change" is documented, is it safe to assume that CONNECTION_PROPERTY ( 'Name' ) evaluated inside an event will always return the event name as defined by the CREATE EVENT statement? I want to write the following... if another invocation of this event is already running then quit this invocation of the event; and I plan to query sa_conn_info() to do that. |
|
The value of connection_property('Name',number) for an event will be the name of the event; however, there is nothing preventing a normal connection from using the same name. The value of connection_property('EventName',number) is also the name of the event, but will be NULL for non-event connections. If this is the only property you are going to be using, sa_conn_list() is a more efficient way to get a list of connections. sa_conn_list was added in version 10. To limit the number of instances of an event that are active, checking the value of event_parameter('NumActive') within the event is the simplest approach. Question: So when did NumActive get added to SQL Anywhere? Answer: It's been there since Day One, when CREATE EVENT was added in Version 7. Yikes! Maybe it's time for me to take a course on SQL Anywhere, maybe get Dmitri to teach it. I would sign up for that. @Breck: To make it even more "embarassing", there' s even a sample with NumActive in the docs, cf. http://dcx.sybase.com/index.html#1200en/dbadmin/defining-trigger-events.html. - Double-Yikes!! It gets worse... not only have I not taken a course, or read the Help since Version 6, but apparently I don't even read this website: http://sqlanywhere-forum.sap.com/questions/958/event-parameternumactive 1
@Volker: At this point I may not be the right person to suggest the Help get changed, but the statement IF EVENT_PARAMETER( 'NumActive' ) = 1 THEN creeps me out because it is relying on an implicit data conversion... EVENT_PARAMETER returns VARCHAR. Such implicit conversions are not as well documented in SQL Anywhere as they are in, say PL/I (the Father Of All Implicit Type Conversions)... and even with PL/I wizened pros learned to be explicit about all such conversions. Correction: When I said I have "not read the Help since Version 6" I mean cover-to-cover, end-to-end, in one swell foop, er, fell swoop. I read the Help every day, but just individual topics. @Breck: I would not expect anyone (well, except the doc team) to have read the help completely for current versions... I remember to have read the v5.5 docs (nearly) from page to page, but nowadays - that's way too much stuff, I guess. - Another question to come: "What is the most important SQL Anywhere feature you wish you had noticed when it was introduced and not n years later?" More comments hidden
|
good question! I've got a handful of things that could really benefit from this.
Not really an answer, but I use following on V10:
ALTER FUNCTION "DBA"."EventIsRunning"() RETURNS INTEGER DETERMINISTIC BEGIN DECLARE @active_events integer ; -- SELECT event_parameter('NumActive') INTO @active_events ; -- if @active_events > 1 then Return 1 -- another instance of this event is already running end if ; -- RETURN 0 -- this is the only running instance of this event END
@Dmitri: I guess that's one of the best of those "not really answers" around here:)
@Dmtri: Shouldn't that function be NOT DETERMINSTIC? - IMHO, separate runs may give different results though the (empty) parameter list remains unchanged.
Well, maybe... but TBH I don't expect it to be called multilple times within single query.