Monday, July 15, 2013

I'm lonely! signed, Your Database

Question: How can I be notified when user activity drops to zero?

Answer: It's one thing to measure user activity, quite another to determine when it has actually dropped to zero for any length of time.

One method is to code a SQL Anywhere 16 scheduled EVENT that checks the TimeWithoutClientConnection database property every once in a while, then calls xp_sendmail() when the value gets too large.

You won't find TimeWithoutClientConnection in the GA version of SQL Anywhere 16, but it is available for download as part of the 16.0.0.1512 Update or later:

SQL Anywhere - Server

  ================(Build #1473  - Engineering Case #734038)================

  The database property TimeWithoutClientConnection has been added.
 
  The description for this database property is:
 
  Returns the elapsed time in seconds since a CmdSeq or TDS client connection 
  to the database existed.  If there has not been a CmdSeq or TDS connection 
  since the database started then the time since the database started is returned.  
  If one or more CmdSeq or TDS connections are currently connected, 0 is returned.

You also won't find TimeWithoutClientConnection in the Help yet, but chances are the above description is all you're ever gonna get anyway.

Plus this, here...

Catch 22: Don't expect anything other than zero when you run SELECT DB_PROPERTY ( 'TimeWithoutClientConnection' ) in ISQL... that's because you are currently connected [snork] :)

You can read about events in the DCX Help,
You can read about xp_sendmail()
Here's the code:
CREATE EVENT lonely 
SCHEDULE 
   START TIME '00:00:00'
   EVERY 5 SECONDS 
HANDLER BEGIN

DECLARE @seconds_without_client_connection   BIGINT;
DECLARE @email_sent_at                       TIMESTAMP;
DECLARE @current_timestamp                   TIMESTAMP DEFAULT CURRENT TIMESTAMP;
DECLARE @loneliness_threshold_in_seconds     BIGINT DEFAULT 30;
DECLARE @email_repeat_threshold_in_seconds   BIGINT DEFAULT 20;
DECLARE @return_code                         INTEGER;

SET @seconds_without_client_connection 
   = CAST ( COALESCE ( DB_PROPERTY ( 'TimeWithoutClientConnection' ), '0' ) AS BIGINT );

IF @seconds_without_client_connection = 0 THEN

   UPDATE lonely SET email_sent_at = '1900-01-01 00:00:00';
   COMMIT;

ELSE

   IF @seconds_without_client_connection >= @loneliness_threshold_in_seconds THEN

      SELECT email_sent_at
        INTO @email_sent_at
        FROM lonely; 

      IF DATEDIFF ( SECOND, @email_sent_at, @current_timestamp ) 
            >= @email_repeat_threshold_in_seconds THEN  

         UPDATE lonely SET email_sent_at = @current_timestamp;
         COMMIT;

         @return_code = CALL xp_startsmtp ( 
            smtp_sender          = 'Your.Name@gmail.com',  
            smtp_server          = 'smtp.gmail.com',  
            smtp_port            = 587,  
            timeout              = 60,
            smtp_sender_name     = 'Your Database',
            smtp_auth_username   = 'Your.Name@gmail.com', 
            smtp_auth_password   = 'Your Password',
            trusted_certificates = 'cert_name=Equifax_Secure_Certificate_Authority' );

         IF @return_code = -1 THEN
            MESSAGE STRING ( CURRENT TIMESTAMP, ' ERROR - xp_startsmtp ',
               xp_get_mail_error_code(), ' - ', xp_get_mail_error_text() ) TO CONSOLE;
            RETURN;
         END IF;

         @return_code = CALL xp_sendmail ( 
            recipient = 'Breck.Carter@gmail.com',  
            subject   = 'I''m lonely!',  
            "message" = STRING ( 'Nobody''s connected with me in the past ', 
                                 @seconds_without_client_connection, 
                                 ' seconds.' ) );

         IF @return_code = -1 THEN
            MESSAGE STRING ( CURRENT TIMESTAMP, ' ERROR - xp_sendmail: ',
               xp_get_mail_error_code(), ' - ', xp_get_mail_error_text() ) TO CONSOLE;
         END IF;

         @return_code = CALL xp_stopsmtp();

         IF @return_code = -1 THEN
            MESSAGE STRING ( CURRENT TIMESTAMP, ' ERROR - xp_stopsmtp: ',
               xp_get_mail_error_code(), ' - ', xp_get_mail_error_text() ) TO CONSOLE;
         END IF;

      END IF;
   END IF;
END IF;

END; 
  • The CREATE EVENT statement on lines 1 through 5 schedules the event to start firing right away, and forever after, every 5 seconds.

  • The SET statement on line 14 and 15 captures TimeWithoutClientConnection, and the UPDATE on line 19 handles the case where current connections do exist (TimeWithoutClientConnection = 0) by recording in the database that no "I'm lonely!" email has been sent; i.e., '1900-01-01 00:00:00' effectively means 'never'.

  • The IF statement starting on line 24 detects loneliness, and the nested IF starting on line 30 determines if it's time to send an email.

  • If it's been a while since the previous email was sent, the fact that (another) one is now being sent is recorded by the UPDATE on line 33,

  • and the big chunk of code on lines 36 through 69 does the actual work, using the techniques described in Sending Email From SQL Anywhere 12 Via Google's SMTP Server and in section "3. SMTP Email Enhancements" of this article: Top 10 Cool New Features in SAP Sybase SQL Anywhere 16.
The code shown above depends on two other objects:
  • First, the trusted_certificates = 'cert_name=Equifax_Secure_Certificate_Authority' argument makes use of a new feature in SQL Anywhere 16, the CREATE CERTIFICATE statement:
    CREATE OR REPLACE CERTIFICATE Equifax_Secure_Certificate_Authority
       FROM FILE 'C:\\certificates\\Equifax_Secure_Certificate_Authority.cer';
    

  • Second, the lonely table, which follows the example shown in One Row Only:
    CREATE TABLE lonely (
       one_row_only    INTEGER NOT NULL DEFAULT 1 CHECK ( one_row_only = 1 ),
       email_sent_at   TIMESTAMP NOT NULL DEFAULT '1900-01-01 00:00:00',
       PRIMARY KEY ( one_row_only ) );
    
    CREATE TRIGGER one_row_only BEFORE DELETE ON lonely FOR EACH ROW
    BEGIN
       ROLLBACK TRIGGER WITH RAISERROR 99999 
          'Do not attempt to delete from lonely';
    END;
    
    INSERT lonely DEFAULT VALUES;
    COMMIT; 
    
Here's what the output looks like:


If you run it without first changing any of the placeholders like 'Your.Name@gmail.com', here's what you'll see in the dbsrv16.exe -o file:
I. 07/14 12:59:23. 2013-07-14 12:59:23.217 ERROR - xp_startsmtp 534 - 5.7.9 
   Please log in with your web browser and then try again. 
   Learn more at 5.7.9 https://support.google.com/mail/bin/answer.py?answer=78754 
   ri10sm12739973igc.1 - gsmtp 

When events cough up and crash there's no "client" to receive the error message, so it has to go to the server console... but that's ok, every single engine startup command includes the -o filespec.txt option, right? :)



No comments: