Documenting DATEDIFF

Does anyone know how DATEDIFF works, in SQL Anywhere?

To put it another way, do you know exactly what DATEDIFF does, for all the date units: year, month, day and so on? Or even what it does for some of the date units?

As it turns out, I didn't, not exactly. And because DATEDIFF has appeared so many times in this blog, it's time for a closer look... especially after publishing Should Examples Work?

DATEDIFF hasn't just been used in this blog, it's been a cornerstone feature of how-to posts like Today's Tip: Counting Days of the Week and Everything Looks Like a Database as well as the subject of in-depth discussions like: EXPRTYPE, DATEDIFF, MICROSECOND, BIGINT and Volker Barth announced that starting with Version 12 DATEDIFF returns BIGINT instead of INTEGER for hour, minute, second, millisecond and microsecond date parts. Let's play "Gotcha!" - Round Two warned "don't let your SQL code stray outside the true useful range for the TIMESTAMP data type which is 1600-02-28 23:59:59 to 7910-12-31 23:59:59". Let's play "Gotcha!" - Round Three warned "don't let the fact that DATEDIFF has been enhanced to return BIGINT values lead you to assume that DATEADD will accept a BIGINT... it won't; you're stuck using INTEGER values." Beware CURRENT TIMESTAMP talked about how Windows might reset CURRENT TIMESTAMP backwards in time if a drifting system clock can't be handled by adjusting the clock rate.

Here's what the Help says about DATEDIFF:

This function calculates the number of date parts between two specified dates. The result is a signed integer value equal to (date-expression-2 - date-expression-1), in date parts. The DATEDIFF function results are truncated, not rounded, when the result is not an even multiple of the date part. When you use day as the date part, the DATEDIFF function returns the number of midnights between the two times specified, including the second date but not the first. When you use month as the date part, the DATEDIFF function returns the number of first-of-the-months between two dates, including the second date but not the first. When you use week as the date part, the DATEDIFF function returns the number of Sundays between the two dates, including the second date but not the first.

Here's what the Help should say:

DATEDIFF ( year, date-expression-1, date-expression-2 ) returns the integer number of year boundaries between date-expression-1 and date-expression-2. DATEDIFF ( quarter, date-expression-1, date-expression-2 ) returns the integer number of quarter boundaries between date-expression-1 and date-expression-2. DATEDIFF ( month, date-expression-1, date-expression-2 ) returns the integer number of month boundaries between date-expression-1 and date-expression-2. DATEDIFF ( week, date-expression-1, date-expression-2 ) returns the integer number of week boundaries between date-expression-1 and date-expression-2. DATEDIFF ( day, date-expression-1, date-expression-2 ) returns the integer number of day boundaries between date-expression-1 and date-expression-2. DATEDIFF ( dayofyear, date-expression-1, date-expression-2 ) returns the integer dayofyear difference = DATEPART ( dayofyear, date-expression-2 ) - DATEPART ( dayofyear, date-expression-1 ). DATEDIFF ( hour, date-expression-1, date-expression-2 ) returns the bigint number of hours between date-expression-1 and date-expression-2. DATEDIFF ( minute, date-expression-1, date-expression-2 ) returns the bigint number of minute boundaries between date-expression-1 and date-expression-2. DATEDIFF ( second, date-expression-1, date-expression-2 ) returns the bigint number of seconds between date-expression-1 and date-expression-2. DATEDIFF ( millisecond, date-expression-1, date-expression-2 ) returns the bigint number of millisecond boundaries between date-expression-1 and date-expression-2. DATEDIFF ( microsecond, date-expression-1, date-expression-2 ) returns the bigint number of microseconds between date-expression-1 and date-expression-2.

The following example shows all the DATEDIFF calculations for two timestamps that are exactly one microsecond (0.000001 second) apart. It shows

• that almost all of them return the number of unit boundaries (1 for year, quarter, etc),
  

• two return the number of units (0 for hour and second),
  

• one (microsecond) effectively returns the number of units because it's at the limit of timestamp precision so the difference between "unit" and "unit boundary" is moot, and
  

• one (dayofyear) returns a simple difference that disregards the context altogether; the value -364 might look funky but it makes sense when you consider the definition above.
  

SELECT DATEDIFF ( year, '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS year, DATEDIFF ( quarter, '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS quarter, DATEDIFF ( month, '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS month, DATEDIFF ( dayofyear, '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS dayofyear, DATEDIFF ( day, '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS day, DATEDIFF ( week, '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS week, DATEDIFF ( hour, '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS hour, DATEDIFF ( minute, '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS minute, DATEDIFF ( second, '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS second, DATEDIFF ( millisecond, '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS millisecond, DATEDIFF ( microsecond, '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS microsecond; year quarter month dayofyear day week hour minute second millisecond microsecond 1 1 1 -364 1 1 0 1 0 1 1

Whether the difference between "units" and "number of unit boundaries" is important depends on what your code is trying to do.

For example, if you're trying to calculate age, then DATEDIFF really sucks; it only gets the right answer half the time.

Want proof? Here it is...

If a baby was born on 2012-07-27 and the CURRENT DATE is 2013-07-28, DATEDIFF is correct in telling us the baby's age is 1. A baby born two days later, however, has not reached its first birthday so DATEDIFF is wrong. SELECT DATEDIFF ( year, '2012-07-27', '2013-07-28' ) AS correct_age, DATEDIFF ( year, '2012-07-29', '2013-07-28' ) AS incorrect_age; correct_age incorrect_age 1 1 Conclusion: DATEDIFF might be of assistance to underage drinkers, but otherwise it isn't much use for calculating age.

Since many (most?) uses of DATEDIFF are a variation on the age calculation (number of days, seconds, whatever), the "unit boundary" calculation may indeed have serious implications for program (in)correctness. Sadly, none of the examples in the DATEDIFF Help topic demonstrate how the unit boundary calculation differs from one that counts actual units... but other examples in the Help may be (adversely?) affected by it (e.g., Should Examples Work?)

Need a laugh?

That big ugly example above comes from the Microsoft SQL Server version of DATEDIFF:

1> SELECT DATEDIFF ( year, '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS year, 2> DATEDIFF ( quarter, '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS quarter, 3> DATEDIFF ( month, '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS month, 4> DATEDIFF ( dayofyear, '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS dayofyear, 5> DATEDIFF ( day, '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS day, 6> DATEDIFF ( week, '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS week, 7> DATEDIFF ( hour, '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS hour, 8> DATEDIFF ( minute, '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS minute, 9> DATEDIFF ( second, '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS second, 10> DATEDIFF ( millisecond, '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS millisecond, 11> DATEDIFF ( microsecond, '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS microsecond 12> GO year quarter month dayofyear day week hour minute second millisecond microsecond ---- ------- ----- --------- --- ---- ---- ------ ------ ----------- ----------- 1 1 1 1 1 1 1 1 1 1 1

If you like [cough] consistency, you should love SQL Server: no more zeroes, no more -364 :)

---

Should Examples Work?

More specifically, is it important that code examples actually work or is it sufficient that they give a rough idea of what the code should look like? Like a stick figure gives a rough idea of what a person looks like, as opposed to a photograph?

The interweb is so ... chock ... full ... of examples that don't work it seems the answer must be "no, examples don't have to work." Testing is expensive, Microsoft Word doesn't complain when the examples don't work, and nobody else seems to care, so why bother?

Why take the time?

Why spend the money?

Let's take a look close to home, the Text index refresh types Help topic:

MANUAL REFRESH MANUAL REFRESH text indexes are refreshed only when you refresh them, and are recommended if data in the underlying table is rarely changed, or if a greater degree of data staleness is acceptable, or to refresh after an event or a condition is met. A query on a stale index returns matching rows that have not been changed since the last refresh. So, rows that have been inserted, deleted, or updated since the last refresh are not returned by a query. You can define your own strategy for refreshing MANUAL REFRESH text indexes. In the following example, all MANUAL REFRESH text indexes are refreshed using a refresh interval that is passed as an argument, and rules that are similar to those used for AUTO REFRESH text indexes. CREATE PROCEDURE refresh_manual_text_indexes( refresh_interval UNSIGNED INT ) BEGIN FOR lp1 AS c1 CURSOR FOR SELECT ts.* FROM SYS.SYSTEXTIDX ti JOIN sa_text_index_stats( ) ts ON ( ts.index_id = ti.index_id ) WHERE ti.refresh_type = 1 -- manual refresh indexes only DO BEGIN IF last_refresh_utc IS null OR cast(pending_length as float) / ( IF doc_length=0 THEN NULL ELSE doc_length ENDIF) > 0.2 OR DATEDIFF( MINUTE, CURRENT UTC TIMESTAMP, last_refresh_utc ) > refresh_interval THEN EXECUTE IMMEDIATE 'REFRESH TEXT INDEX ' || text-index-name || ' ON "' || table-owner || '"."' || table-name || '"'; END IF; END; END FOR; END; At any time, you can use the sa_text_index_stats system procedure to decide if a refresh is needed, and whether the refresh should be a complete rebuild or an incremental update. A MANUAL REFRESH text index contains no data at creation time, and is not available for use until you refresh it. To refresh a MANUAL REFRESH text index, use the REFRESH TEXT INDEX statement. MANUAL REFRESH text indexes are not refreshed during a reload unless the -g option is specified for dbunload.

Never mind the SELECT ts.*, which might be "wrong" because it increases the burden on the reader without adding any value... that's not the issue here.

The issue is, does the example serve any purpose? Does it help the reader code a stored procedure for refreshing text indexes, more than just reading the surrounding text helps?

You decide

Maybe your answer is "Yes, it's fine, I'm not going to copy and paste, it's pointing me in the right direction, it tells me what I need to know."

Maybe it's not important that

• the procedure doesn't compile because of "text-index-name",
  

• there's no column in ts.* named last_refresh_utc (it's just last_refresh),
  

• the DATEDIFF will never be > refresh_interval because
  
  
  • CURRENT UTC TIMESTAMP will always be greater than sa_text_index_stats.last_refresh
    

  • so the DATEDIFF will always return a negative number, and
    

  • refresh_interval will never be negative because it's an UNSIGNED INT and
    

• DATEDIFF counts minute boundaries rather than complete minutes, which may or may not be acceptable in actual use.
  

Except for the bit about "doesn't compile" those points were discovered by inspection rather than testing... are there other issues that testing might reveal?

Maybe it matters, maybe not... you tell me.

---

Have you ever wished DATE() worked like this?

CREATE FUNCTION DBA."DATE" ( @y SMALLINT, @m SMALLINT, @d SMALLINT ) RETURNS DATE BEGIN RETURN DATE ( STRING ( @y, '-', @m, '-', @d ) ); END; SELECT DBA."DATE" ( 1991, 2, 3 ); DBA."DATE"(1991,2,3) -------------------- 1991-02-03

...oh, wait, now it does :)

---

Latest SQL Anywhere Updates: Windows 12.0.1.3924

Current builds for the active platforms... HP-UX 16.0 GA Dev Edition download (date n/a) Dev Edition registration Itanium 12.0.1.3894 Update 16 May 2013 11.0.1.2958 Update 08 Apr 2013 End of Life 31 May 2014 IBM AIX 16.0 GA Dev Edition download (date n/a) Dev Edition registration 12.0.1.3894 Update 16 May 2013 11.0.1.2958 Update 08 Apr 2013 End of Life 31 May 2014 Linux 16.0.0.1535 Update 30 May 2013 12.0.1.3873 Update 05 Apr 2013 12.0.1 Chinese, 16 Apr 2013 Japanese Docs (Eclipse) 16 Apr 2013 11.0.1.2958 Update 08 Apr 2013 End of Life 31 May 2014 Mac OS 16.0.0.1535 Update 09 Jul 2013 12.0.1.3901 Update 23 May 2013 11.0.1.2449 Update 29 Jun 2010 End of Life 31 May 2014 Solaris 16.0 GA Dev Edition download (date n/a) Dev Edition registration SPARC 12.0.1.3894 Update 16 May 2013 11.0.1.2958 Update 08 Apr 2013 End of Life 31 May 2014 Solaris 16.0 GA Dev Edition download (date n/a) Dev Edition registration x64 12.0.1.3894 Update 16 May 2013 11.0.1.2958 Update 08 Apr 2013 End of Life 31 May 2014 Windows 16.0.0.1535 Update 30 May 2013 12.0.1.3924 Update *** 22 Jul 2013 *** 12.0.1 French, 25 Sep 2012 English, 25 Sep 2012 German, 25 Sep 2012 Chinese, 28 Mar 2013 Japanese Docs (HTML/PDF) 28 Mar 2013 11.0.1.2960 Update 16 Apr 2013 End of Life 31 May 2014

Other Stuff... Older Updates Free support! Q&A forum ...or, call Tech Support SQL Anywhere... ...Sybase home page ...SAP home page ...SAP Developer Center Buy SQL Anywhere Developer Edition... [16.0] [12.0.1] [11.0.1] Download the... Educational Edition Web Edition Supported Platforms... SQL Anywhere Linux OnDemand ODBC Drivers for MobiLink

The asterisks "***" show which items have appeared on the Sybase website since the previous version of this page.

• Only the latest fully-supported versions of SQL Anywhere (11.0.1, 12.0.1 and 16.0) are shown here.
  

• Just because an older version or different platform isn't "fully supported" any more doesn't mean you can't download files (or ask questions, or get help), it just means there won't be any more new Updates released.
  

Searching for SQL

Google it, or go to it?    That's up to you...

Google what?	When Google shows you this, add your search words in front of it...
Google the SQL Anywhere 16.0 Help	site:dcx.sybase.com/sa160/en
12.0.1 Help   11.0.1 Help   10.0.1 Help	site:dcx.sybase.com/1201/en     site:dcx.sybase.com/1101/en     site:dcx.sybase.com/1001/en
Google the SQL Anywhere forum	site:sqlanywhere-forum.sybase.com
Google the SCN	site:scn.sap.com
Google the HANA Help	site:help.sap.com/hana/html
Google all the old NNTP forums	"sybase.public.sqlanywhere" site:nntp-archive.sybase.com
Google the old Futures forum (1,244)	"sybase.public.sqlanywhere.product_futures_discussion" site:nntp-archive.sybase.com
old General forum (2,218)	"ianywhere.public.general" site:nntp-archive.sybase.com
old General forum (33,727)	"sybase.public.sqlanywhere.general" site:nntp-archive.sybase.com
old Linux forum (1,122)	"sybase.public.sqlanywhere.linux" site:nntp-archive.sybase.com
old Mobilink forum (5,481)	"sybase.public.sqlanywhere.mobilink" site:nntp-archive.sybase.com
old SQL Remote forum (4,700)	"sybase.public.sqlanywhere.replication" site:nntp-archive.sybase.com
old Ultralite forum (3,148)	"sybase.public.sqlanywhere.ultralite" site:nntp-archive.sybase.com
Google this blog	Google Custom Search

Go to where?	Here's where you'll be heading...
Go to the SQL Anywhere 16.0 Help	dcx.sybase.com/sa160/en
12.0.1 Help     11.0.1 Help     10.0.1 Help	dcx.sybase.com/1201/en     dcx.sybase.com/1101/en     dcx.sybase.com/1001/en
Go to the SQL Anywhere forum	sqlanywhere-forum.sybase.com
Go to the SCN	scn.sap.com/
SCN Community	scn.sap.com/community/sybase-sql-anywhere
SCN Discussion	scn.sap.com/community/sybase-sql-anywhere/content?filterID=content~objecttype~objecttype[thread]
Go to the HANA Help master list	help.sap.com/hana_appliance/
HANA SQL reference	help.sap.com/hana/html/sqlmain.html
HANA Developer Center	scn.sap.com/community/developer-center/hana
HANA Discussion	http://scn.sap.com/community/developer-center/hana/content?filterID=content~objecttype~objecttype[thread]
Go to the old NNTP forums	nntp-archive.sybase.com/nntp-archive/action/product/detail/4
Go to the old Futures forum (1,244)	nntp-archive.sybase.com/nntp-archive/action/newsgroup/sybase.public.sqlanywhere.product_futures_discussion
old General forum (2,218)	nntp-archive.sybase.com/nntp-archive/action/newsgroup/ianywhere.public.general
old General forum (33,727)	nntp-archive.sybase.com/nntp-archive/action/newsgroup/sybase.public.sqlanywhere.general
old Linux forum (1,122)	nntp-archive.sybase.com/nntp-archive/action/newsgroup/sybase.public.sqlanywhere.linux
old Mobilink forum (5,481)	nntp-archive.sybase.com/nntp-archive/action/newsgroup/sybase.public.sqlanywhere.mobilink
old SQL Remote forum (4,700)	nntp-archive.sybase.com/nntp-archive/action/newsgroup/sybase.public.sqlanywhere.replication
old Ultralite forum (3,148)	nntp-archive.sybase.com/nntp-archive/action/newsgroup/sybase.public.sqlanywhere.ultralite

---

Read Only Rant

Sometimes ya gotta nip a Feature Request in the bud, while it's still in the form of question "How do I [do some thing]?"

Here's an example: "How do I stop SQL Anywhere from marking database files as read only?"

Without quick action that might morph into a change request, hence this rant...

---

This is a possible answer to a different question, "Why does SQL Anywhere mark the database file as read only?"

The original reason(s) may be lost in the sands of time since SQL Anywhere has always done that, so let's change the question:

Why do *I* want SQL Anywhere to mark the database file as read only?

Answer: Because SQL Anywhere files are often used very differently from other products' files. Unlike (for example) SQL Server databases, it is extremely easy to create SQL Anywhere databases, and copy and move them around, even across hardware and software platforms. SQL Anywhere files are binary compatible across big endian and little endian computers, for example... Windows, Linux, Sparc, AIX, mobile, whatever.

SQL Server (and Oracle, and ASE, and IBM) databases tend to get created once and sit in the same place forever and ever. Some of them (historically, at least) aren't even operating system files, they are magical low-level "native files" that are profoundly difficult to move around.

Yes, that is a stereotypical view of databases... BUT historically speaking, it is true. For example, it is a very rare thing for a programmer to have one or two or ten separate SQL Server (or, gasp, Oracle) databases... but it is a very common thing for SQL Anywhere. Speaking personally (as a possible outlier :) I have literally thousands of SQL Anywhere .db files on my laptop, of all versions from 5.5 through 16... nothing in the product discourages me from doing that.

And in the real world, mobile replication and synchronization makes it possible for a single SQL Anywhere production system to encompass tens of thousands of separate SQL Anywhere database files.

File proliferation comes with it's own hazards, and accidental overwrite is one of them. The read only attribute is one mechanism to help guard against that.

The read only attribute is much easier to deal with than, say, SQL Server's approach to protecting their .mdf files...



In conclusion: The read only attribute is a minor annoyance that has saved me from making mistakes on many occasions. Folks who know me, know how much I loathe restrictions on personal productivity (firewalls, security settings, etc), so for me to actually *like* a limitation is a big deal indeed :)

Example: SELECT LIST() FROM sa_split_list()

You might not code SELECT LIST() together with FROM sa_split_list() in the same query, but you might use them both on the same data.

The SQL Anywhere LIST() aggregate function turns a result set into a single string, and the sa_split_list() system procedure turns a string into a result set. Here's a round-trip example; first, the data:

CREATE TABLE t ( id DECIMAL ( 2 ) NOT NULL PRIMARY KEY, name VARCHAR ( 2 ) NOT NULL ); INSERT t VALUES ( 1, 'NY' ), ( 2, 'NJ' ), ( 3, 'VA' ), ( 4, 'DC' ), ( 5, 'CA' ); COMMIT; SELECT * FROM t ORDER BY id; id name ---- ---- 1 NY 2 NJ 3 VA 4 DC 5 CA

No, the fancy INSERT statement isn't using either LIST() or sa_split_list(), it's showing off the relatively new multiple row syntax of the VALUES list, aka the row constructor syntax... no Help link for this because, well, the Help is no help on this particular topic...

... ok, ok, see for yourself :)

Here's how LIST() works to create simple id and name strings, with the name string ordered by the corresponding id rather than alphabetically:

BEGIN DECLARE @ids VARCHAR ( 10 ); DECLARE @names VARCHAR ( 20 ); SELECT LIST ( STRING ( id ) ORDER BY id ) INTO @ids FROM t; SELECT LIST ( STRING ( name ) ORDER BY id ) INTO @names FROM t; SELECT @ids, @names; END; @ids @names ---------- -------------------- 1,2,3,4,5 NY,NJ,VA,DC,CA

So far, so good; here's the other way, using sa_split_list() to turn the strings into single-column result sets:

BEGIN DECLARE @ids VARCHAR ( 10 ); DECLARE @names VARCHAR ( 20 ); SET @ids = '1,2,3,4,5'; SET @names = 'NY,NJ,VA,DC,CA'; SELECT row_value AS id FROM sa_split_list ( @ids, ',' ) ORDER BY line_num; SELECT row_value AS name FROM sa_split_list ( @names, ',' ) ORDER BY line_num; END; id -- 1 2 3 4 5 name ---- NY NJ VA DC CA

Now, how about combining both strings to recreate the original table in one SELECT? The following code depends on the fact the two strings are positional; i.e., they both have the same number of entries, and each entry in one string corresponds to the entry in the same position in the other string:

BEGIN DECLARE @ids VARCHAR ( 10 ); DECLARE @names VARCHAR ( 20 ); SET @ids = '1,2,3,4,5'; SET @names = 'NY,NJ,VA,DC,CA'; SELECT tid.row_value AS id, tname.row_value AS name FROM ( SELECT * FROM sa_split_list ( @ids, ',' ) ) AS tid INNER JOIN ( SELECT * FROM sa_split_list ( @names, ',' ) ) AS tname ON tid.line_num = tname.line_num ORDER BY tid.line_num; END; id name ---- ---- 1 NY 2 NJ 3 VA 4 DC 5 CA

---

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,

• in general here: Task automation using schedules and events
  

• and more specifically here: CREATE EVENT statement
  

You can read about xp_sendmail()

• in the Help: xp_sendmail system procedure
  

• in this how-to post: 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
  

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? :)

---

Database Deadpool: 10:1 Odds Against Janrain's MyOpenID

Alert: If you obtained a naked OpenID from MyOpenID and then gave it to one or more other websites, quick! Get another naked OpenID, from someone else, then update your profile on all those other websites.

What's a "naked OpenID"? It's a URL like this:

• yourname.myopenid.com if you got it from MyOpenID, or
  

• yourname.pip.verisignlabs.com if you got it from Verisign, and so on,
  

as opposed to a "hidden OpenID" like your Google or Yahoo user id which you can use on other websites without having to set up separate passwords.

Where do I get a naked OpenID? Well, you could pick from the "Other Well Known & Simple Providers" list on the OpenID Cult Foundation website, just don't pick MyOpenID.

Naked OpenIDs aren't that popular for website logins, so chances are you will only have one or two to deal with. For example, on the SQL Anywhere forum,

• go to your profile page,
  

• click on User tools - authentication settings...
  
  
  

• then click the Add new provider button.
  

• That takes you to the User login page...
  
  
  

• where you can "Enter your OpenID url"
  

• and click Login to save it.
  

• To check it, go back to User tools - authentication settings - Add new provider to see if it shows your new OpenID url.
  

Why should I bother?

Because the MyOpenID provider service is at death's door... it's no longer supported by Janrain, and it was recently off the air for days. That meant some folks (well, one folk) had trouble logging in to the SQL Anywhere Forum and other websites like StackExchange.

That's why the Database Deadpool is offering 10:1 odds against MyOpenID surviving much longer.

Don't take my word for it, check out all the noise on Twitter...

Twitter search results for "myopenid" on Monday, July 8, 2013 at 1:45 PM EST Robert Denton @robertdenton 4h http://myopenid.com is down. Daniel Morrison @danielmorrison 4h dammit, I can’t water my plants because http://myopenid.com is down. #geekproblems from Collective Idea, Holland David Eisner @deisner 4h Any idea when #myopenid will be up again, @Janrain? http://downscout.com/services/myopenid.com/intervals/157284 … Ariel Ben Horesh @ArielBH 6h In the last few days I'm unable to use myopenid. is it dead? Paul Zagoridis @paulzag 7h Most of you don't use @Janrain's http://MyOpenId.com It's no longer supported, so you should migrate to another #OpenID service Thomas F. Nicolaisen @tfnico 10h This is why properly sunsetting products is a good thing: @janrain lets http://myopenid.com go down w/o warning nor status info. David R @davr 13h So is @Janrain purposefully killing off myopenid or what? Failing of openID: if your ID provider dies, you're locked out of tons of accounts David K. Jones @tadmas 14h Frustrated that MyOpenID is down right now. Sounds like they've been down for a few days. Time to set up another #OpenID provider, I guess. Ben Dornis @buildstarted 15h hey, @openid. you should remove myopenid from your list of well known and simple providers as it's no longer actively maintained Ben Dornis @buildstarted 15h so @janrain are "pioneers" of social identity yet they don't care about their products like myopenid Jan @jan 7 Jul hey @janrain http://myopenid.com is down for more than 12h now. what's up? pic.twitter.com/GOvveu40E0 Colin Charles @bytebot 6 Jul What has happened to http://myopenid.com ? @janrain any reason its dead? will it come back? #openid Alan Gardner @mr_urf 6 Jul So MyOpenId appears to be gone :/ Dod @TheRealDod 6 Jul Urgent! Any decent (e.g. has SSL) #OpenID provider I could direct customers to now that http://myopenid.com/ is dead? cc @Liberationtech Dod @TheRealDod 6 Jul A few days after google critically wounds #RSS, @Janrain's MyOpenID goes down, messing up the #OpenID community. Bad week 4 hippie standards Marius Gedminas @mgedmin 6 Jul Can't log in to identi.ca using my OpenID because myOpenID says "An error has occured while attempting to fulfill your request." Anthony Steele @AnthonySteele 5 Jul As soon as myopenid is back up, I can start movig my #stackoverflow account away from relying on #myopenid Eric A. Meyer @meyerweb 5 Jul Could whoever is in charge of myOpenId·com give the reboot button a kick? (The reboot is for everyone. The kick is for me.) Anthony Steele @AnthonySteele 5 Jul I can't log into #stackoverflow because myopenid http://myopenid.com/ is down. #wtf Tom Novak @to_nov 5 Jul #myopenid not working again. does anyone know whats going on?

OpenID: Your Very Own Single Point Of Failure

Think twice about using OpenIDs at all, naked or hidden. Ask yourself this, what happens to all the data when your OpenID provider goes dark?

Even if that doesn't worry you, what happens if an Evil Doer obtains your OpenID provider user id and password? It could be your MyOpenID password, or it could be your Google password since Google user ids can be used just like OpenIDs... in fact, your Google user id is an OpenID.

If that happens, then the Evil Doer has access to ALL the sites where you used that OpenID... because those other sites did not force you to set up different passwords.

Which is a very bad idea.



OpenIDs aren't really intended to make your life easier, they are designed for companies like Janrain to "offer a database to collect, manage and leverage social profile data".

Ask yourself this: Does your bank let you login with your Google user id?

No, banks have other ways to take your money.

---

Latest SQL Anywhere Updates: HP/IBM/Solaris 16.0 GA, Mac 16.0.0.1535

Current builds for the active platforms... HP-UX 16.0 GA Dev Edition download *** (date n/a) *** Dev Edition registration Itanium 12.0.1.3894 Update 16 May 2013 11.0.1.2958 Update 08 Apr 2013 End of Life 31 May 2014 IBM AIX 16.0 GA Dev Edition download *** (date n/a) *** Dev Edition registration 12.0.1.3894 Update 16 May 2013 11.0.1.2958 Update 08 Apr 2013 End of Life 31 May 2014 Linux 16.0.0.1535 Update 30 May 2013 12.0.1.3873 Update 05 Apr 2013 12.0.1 Chinese, 16 Apr 2013 Japanese Docs (Eclipse) 16 Apr 2013 11.0.1.2958 Update 08 Apr 2013 End of Life 31 May 2014 Mac OS 16.0.0.1535 Update *** 09 Jul 2013 *** 12.0.1.3901 Update 23 May 2013 11.0.1.2449 Update 29 Jun 2010 End of Life 31 May 2014 Solaris 16.0 GA Dev Edition download *** (date n/a) *** Dev Edition registration SPARC 12.0.1.3894 Update 16 May 2013 11.0.1.2958 Update 08 Apr 2013 End of Life 31 May 2014 Solaris 16.0 GA Dev Edition download *** (date n/a) *** Dev Edition registration x64 12.0.1.3894 Update 16 May 2013 11.0.1.2958 Update 08 Apr 2013 End of Life 31 May 2014 Windows 16.0.0.1535 Update 30 May 2013 12.0.1.3910 Update 17 Jun 2013 12.0.1 French, 25 Sep 2012 English, 25 Sep 2012 German, 25 Sep 2012 Chinese, 28 Mar 2013 Japanese Docs (HTML/PDF) 28 Mar 2013 11.0.1.2960 Update 16 Apr 2013 End of Life 31 May 2014

Other Stuff... Older Updates Free support! Q&A forum ...or, call Tech Support SQL Anywhere... ...Sybase home page ...SAP home page ...SAP Developer Center Buy SQL Anywhere Developer Edition... [16.0] [12.0.1] [11.0.1] Download the... Educational Edition Web Edition Supported Platforms... SQL Anywhere Linux OnDemand ODBC Drivers for MobiLink

The asterisks "***" show which items have appeared on the Sybase website since the previous version of this page.

• Only the latest fully-supported versions of SQL Anywhere (11.0.1, 12.0.1 and 16.0) are shown here.
  

• Just because an older version or different platform isn't "fully supported" any more doesn't mean you can't download files (or ask questions, or get help), it just means there won't be any more new Updates released.
  

ER Diagrams in SQL Anywhere 16

Tip: The "ER Diagram" feature of Sybase Central is all growed up and well behaved in SQL Anywhere 16.

I'm guilty of not trying it out for a long time, but today it seems to be actually useful:

• The right-mouse Choose ER Diagram Tables... context menu item makes it easy to pick the tables you want to display.
  

• It's easy to click and drag the tables to move them around,
  

• and the lines too, but with the lines you first have to click on a line to display the teeny tiny endpoint boxes, then click and drag those endpoints to move the line:

  

• You can resize the table images to show more columns by first clicking on the title bar to display the resizing icons.
  

• The View - Refresh Folder menu item lets you clear highlighting after clicking on columns.
  

• Best of all... your careful work moving tables and lines around isn't wiped out when you refresh the display.
  

• Better yet (better than best?) Sybase Central remembers the layout when you disconnect, connect, and redisplay the ER Diagram tab.
  

• Better still: right-mouse Export Layout... (new with SQL Anywhere 16) lets you save the layout in a funky XML text file (just the layout, not the columns and and what-not),
  

• and then you can revert to that layout with right-mouse Import Layout... after, for example, you completely [cough] muck it up :)
  

• The right-mouse Go To Table and Go To Foreign Key menu items take you to the relevant tabs in Sybase Central, and from there the Back button brings you back to the diagram.
  

Here's a before-and-after example of an ER diagram

• as originally displayed for the SQL Anywhere 16 Demo database (the first image) and
  

• after some fiddling about (the second image):
  

Is it an alternative to PowerDesigner?

Yes, it is, for 99% of what people actually need: Pretty pictures for management (90%), and diagrams for documentation (9%).

The remaining 1% of what folks need, which accounts for 99.999% of PowerDesigner's feature set (and complexity, and cost) is beyond the scope of the Sybase Central ER Diagram feature.