DATEDIFF Mistakes Case Study 2: Foxhound

---

Previously on . . . Documenting DATEDIFF described how the peculiar workings of SQL Anywhere's DATEDIFF() function can lead to coding errors, and DATEDIFF Mistakes Case Study: This Blog was a bug hunt for those errors in some sample code.

Now the bug hunt continues, this time in production code for an application that depends heavily on DATEDIFF(): the Foxhound performance monitor.

---

An early design decision is reflected in the code throughout Foxhound: All time intervals are calculated in milliseconds, even long intervals measured in seconds, hours or even days. As a result, even though DATEDIFF ( MILLISECOND, x, y ) is often called with TIMESTAMP values for x and y, and even though that can result in an error of (almost) one whole millisecond in the value returned by DATEDIFF, it doesn't matter: Foxhound only displays intervals to the nearest 0.1 second, so an error of 0.001 second doesn't affect the result.

Here's an example; the internal Foxhound function rroad_f_msecs_as_d_h_m_s() takes a BIGINT value in milliseconds and formats it to return a string like '1h 16m 56s' or '9.9s'

rroad_f_msecs_as_d_h_m_s ( DATEDIFF ( MILLISECOND, active_alert.recorded_at, @current_timestamp ) ),

Only small values (less than one hour) are shown to the nearest tenth of a second, like '9.9s', and large values are only shown to the nearest second like '1h 16m 56s', so DATEDIFF errors of one millisecond or less don't matter.

Foxhound is awash in DATEDIFF MILLISECOND calls, and they all share the same characteristic: The errors don't matter because the code doesn't care about milliseconds. Foxhound isn't a financial application that cares about the pennies, it isn't even an execution profiler that cares about how long a single statement takes to execute; it is a performance monitor that gathers samples every 10 seconds.

---

Are there any DATEDIFF something-other-than-MILLISECOND calls in Foxhound?

Yes, there are a few. This one checks a user input datetime for validity:

WHEN DATEDIFF ( DAY, @FOXHOUND3UPGRADE_timestamp, CURRENT TIMESTAMP ) > 100000 THEN

A @FOXHOUND3UPGRADE_timestamp value more than 100,000 days in the future is ignored because it is "way too big". Since DATEDIFF DAY counts the number of day boundaries between the two timestamps, the return value could be wrong by (almost) one entire day, which means a value only 99,999 days in the future could be incorrectly rejected.

Yeah, that's a bug... one that's not going to be fixed, or even documented in the Foxhound FAQ, but a bug nonetheless... the Foxhound Development Team promises to do better!

---

Here's another example; DATEDIFF DAY is called calculate how many days are left before the current rental period expires:

IF @edition_name = 'Rental' AND @expiry_date < '9999-12-31' THEN SET @rental_period_will_end_in_days = DATEDIFF ( DAY, CURRENT DATE, DATEADD ( DAY, 1, @expiry_date ) ); ELSE SET @rental_period_will_end_in_days = 9223372036854775807; -- never ends; i.e., it's not a rental END IF;

In this case, the second and third arguments to DATEDIFF are only precise to the nearest DAY, so counting the number of day boundaries is the same as counting the number of days, and DATEDIFF DAY returns the right answer.

What's the score?

Here's how DATEDIFF usage is scored according to the previous Case Study:

• FAIL means the DATEDIFF usage is badly flawed; it shouldn't have been coded that way, and it should be fixed.
  

• LUCKY means the DATEDIFF usage may be flawed but it doesn't matter given the data values involved.
  

• OK means the DATEDIFF usage is OK given the data types involved.
  

Foxhound gets a LUCKY for all those DATEDIFF MILLISECOND calls where the errors don't matter (the first example above), a FAIL for the DATEDIFF DAY bug that rejects 99,999 as being "way too big", and an OK for the DATEDIFF DAY calculation involving the expiry date.

This bug hunt didn't turn up anything worth changing... like all bug hunts it did find some stuff in need of fixing, just not DATEDIFF :)

Latest SQL Anywhere Updates: Windows 12.0.1.3942

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.3942 Update *** 15 Aug 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.
  

The WaybackGlennMachine

Once upon a time, there were two regularly-published blogs devoted to SQL Anywhere: this one, and a better one written by Glenn Paulley. Before leaving SAP on July 31, 2012, Glenn had published over 150 posts of a technical nature as well as many announcements and other posts.

Sadly, as part of the transition to the SAP Community Network, Glenn's blog and four others (Chris Kleisath, Eric Farrar, Jason Hinsperger and Tom Slee/Philippe Bertrand) were suddenly wiped from existence, taking with them over 300 posts containing valuable technical information.

A handful of Glenn's posts have been republished on the SAP Community Network, but it's clearly not a high priority task.

The Internet Midden To The Rescue!

The good news is, you can still find most of Glenn's posts in The Wayback Machine. It helps if you know what you're looking for, and the Technical Documents page on this blog can help as follows:

1. Scroll through the Technical Documents page looking for "Glenn Paulley" entries marked "Blog: ..."
   

2. When you find one you like, click right mouse - Copy link address on the title:

   

3. Open up The Wayback Machine and paste the link address into the "Take Me Back" field:

   

4. Click on "Take Me Back" button to see a calendar of all the different times Glenn's post was archived.
   

5. Click on any of the calendar entries for that post; it doesn't matter which one because Glenn wasn't in the habit of changing old posts.
   

6. Here's what you'll see, an almost-fully-functional copy of the original post:

   

Sure, it's a kludge...

Some of Glenn's posts are missing, and some of the embedded objects too, and some of his links have rotted.

And no, Google search doesn't reach into The Wayback Machine.

But... it's better than the alternative:

Characteristic Errors, Revision 4

UPDATE: See the latest version of this article here.

---

Back in June this list had 35 entries, now it has 42...

[click here to see the new entries]

---

A characteristic error is an error that is so easy to make that it appears you are being actively encouraged to make it by the very nature of the computer program you are using.

For example, sending an email without the attachment is a characteristic error of all email programs.

...except Gmail. Gmail warns you about missing attachments... Gmail is magic!

Here are some errors that are characteristic of SQL in general, SQL Anywhere in particular, and some companion programs.

1. SQL: Seeing too little data, or no data at all, because a predicate in the WHERE clause effectively turned your OUTER JOIN into an INNER JOIN.
   

2. SQL: Seeing too much data because a missing predicate effectively turned your INNER JOIN into a CROSS JOIN.

3. SQL: Getting the wrong COUNT() or SUM() because you forgot to code WHERE ... IS NOT NULL, or you *did* code it when you shouldn't have.
   

4. SQL: Getting the wrong answer because you forgot that, in general, NULL values [cough] suck.
   

5. SQL Anywhere: Not seeing MESSAGE output because you forgot to run SET TEMPORARY OPTION DEBUG_MESSAGES = 'ON';
   

6. SQL Anywhere: Not seeing any data because you forgot ON COMMIT PRESERVE ROWS or NOT TRANSACTIONAL.
   

7. SQL Anywhere: Coding ENDIF where END IF was required, or vice versa (before Version 11).
   

8. SQL Anywhere: Connecting to the wrong server because you forgot DOBROAD=NONE (before Version 12).
   

9. SQL Anywhere: Forgetting the asterisk in SELECT TOP 10 FROM ...

10. SQL Anywhere: Coding IF NOT VAREXISTS ( 'x' ) THEN ... instead of IF VAREXISTS ( 'x' ) = 0 THEN ...
    

11. SQL Anywhere: Coding the wrong magic numbers 1, 2, 3, ... in the get_value() and set_value() calls in an EXTERNAL C DLL function.
    

12. SQL Anywhere: Getting proxy table ODBC errors because the engine's running as a service and you've set up a User DSN instead of System DSN.
    

13. SQL Anywhere: Getting file-related errors because the file specifications are relative to the server rather than the client.
    

14. SQL Anywhere: Getting file-related errors because the engine's running as a service without the necessary permissions.
    

15. SQL Anywhere: Coding CREATE TRIGGER IF NOT EXISTS instead of CREATE OR REPLACE TRIGGER, or vice versa for CREATE TABLE (in 11.0.1 or later).
    

16. SQL Anywhere: Getting integer arithmetic when you wanted fractional parts because you forgot to CAST.
    

17. Stored procedure debugger: Setting it to watch a specific user id other than the one you're using to test your code.
    

18. Sybase Central: Setting it to display objects for owner names other than the one you're interested in.
    

19. Copy and paste: Forgetting to edit after pasting; e.g., Copy and paste SET @continue = 'Y' into the body of a WHILE loop and then forgetting to change it to 'N'.
    

20. MobiLink: Forgetting to call ml_add_column for any of the columns you're trying to synchronize, thus guaranteeing yourself a "Sassen Frassen Fricken Fracken!" moment when you run the first test.
    

21. MobiLink: Forgetting to call ml_add_[various] with the NULL parameter to delete old ml_[whatever] rows, thus ending up with thousands of orphan system table rows in the consolidated database.
    

22. OLAP Windowing: Coding the wrong combination of ASC and DESC in an inner OVER ORDER BY clause and the outer SELECT ORDER BY: different when they should be the same, the same when they should be different, or some other variation of "wrong combination"...
    

    SELECT older_sample_set.sample_set_number
                  INTO @20_older_sample_set_number
                  FROM ( SELECT TOP 20
                                ROW_NUMBER() OVER ( ORDER BY rroad_sample_set.sample_set_number ASC ) AS scrolling_row_number,
                                rroad_sample_set.sample_set_number                                    AS sample_set_number
                           FROM rroad_sample_set
                          WHERE rroad_sample_set.sampling_id       = @sampling_id
                            AND rroad_sample_set.sample_set_number < @sample_set_number
                          ORDER BY rroad_sample_set.sample_set_number DESC ) AS older_sample_set
                 WHERE older_sample_set.scrolling_row_number = 20;

23. MobiLink: Forgetting to call ml_add_column() when trying to use named parameters instead of "?" in versions 10 and 11 MobiLink scripts, resulting in a "What the ... ? Sassen Frassen Fricken Fracken!" moment during the first test (thank you, Jeff Albion).
    

24. SQL: Omitting a PRIMARY KEY column from the WHERE clause, thus turning a singleton SELECT (or DELETE!) into something rather more enthusiastic than expected (thank you, Ron Hiner).
    

25. HTTP web services: Leaving an & in the code when a ? is required, and vice versa, when editing service URLs; e.g., 'HTTP://localhost:12345/web_service&service_parm2=!parm2'
    

26. SQL Anywhere: Forgetting that not all functions look like functions: SELECT CAST ( CURRENT TIMESTAMP, VARCHAR )
    

27. Batch file: Trailing spaces on SET commands; e.g., SELECT CAST ( xp_getenv ( 'DEBUG_MESSAGES' ) AS VARCHAR ) returns 'OFF ' instead of 'OFF' after SET DEBUG_MESSAGES=OFF
    
28. Forum: Clicking Reply on the main Question or Answer entry instead of the comment you wanted.
    

29. SQL Anywhere: Forgetting to run dblog to tell the database file where the log is now, after moving the database and log files to a different folder (thank you, Justin Willey).
    

30. SQL Anywhere: Having to look up WAIT in the Help ... every ... single ... time, to be reminded that's it's WAITFOR, not WAIT.
    

31. SQL: Forgetting to check the SELECT against the GROUP BY, resulting in "Function or column reference to ... must also appear in a GROUP BY" (thank you, Glenn Paulley).
    

32. SQL: Coding too much in the GROUP BY (like, say, the primary key) so every group contains but a single row (thank you, Glenn Paulley).
    

33. Design: Forgetting to accomodate or prevent loops in a tree structure, resulting in a tree traversal process that pegs the CPU at 100%... forever (thank you, Ove B).
    

34. MobiLink: Unwittingly using a variety of user ids when running sync*.sql, updating MobiLink scripts and running the MobiLink server, resulting in inexplicable inconsistencies.
    

35. MobiLink: Accidentally creating multiple script versions and then getting them crossed up when updating MobiLink scripts and running the MobiLink client.
    
    

    ---

    New entries...

36. SQL Anywhere: Forgetting to run the 32-bit version of SQL Anywhere when working with Excel proxy tables.
    

37. ODBC Administrator: Running the 64-bit version (huh?) of odbcad32.exe (say what?) when you need 32-bit version at C:\WINDOWS\SysWOW64\odbcad32.exe (oh, fer #*@&!!!)
    

38. ODBC Administrator: Forgetting to click OK ... twice ... to actually save your new ODBC DSN after celebrating your success with Test Connection.
    

39. ODBC Administrator: Setting up an ODBC DSN on the wrong computer: "It goes with the client!" ... but sometimes it's not obvious where the client is actually located.
    

40. Security: Forgetting which Windows user id you're using on which system, then spending too much time with Windows menus, firewall software and Google searches before the "Doh!" moment.
    

41. SQL: Getting an exception that is not only completely inexplicable, but absolutely impossible for the statement that raised it... until you think to look inside the triggers.
    

42. SQL Anywhere: Getting an exception because a FOR loop variable has a scope conflict with a column name, or worse, NOT getting an exception, just a wrong result.
    

---

DATEDIFF Mistakes Case Study: This Blog

After writing about problems with DATEDIFF it seems natural to look for examples, and there's no more exciting place to start throwing stones than inside one's own glass house!

And so we have . . .

Dogfooding DATEDIFF

. . . a critical look at the use and abuse of SQL Anywhere's DATEDIFF function in this blog.

In other words, a Bug Hunt, with each example of DATEDIFF ranked as follows:

• FAIL means the DATEDIFF usage is badly flawed; it shouldn't have been coded that way, and it should be fixed.
  

• LUCKY means the DATEDIFF usage may be flawed but it doesn't matter given the data values involved.
  

• OK means the DATEDIFF usage is OK given the data types involved.
  

That last point about data types is interesting; it isn't discussed in the Help, it wasn't mentioned in Documenting DATEDIFF, and it makes this Bug Hunt all the more worthwhile.

---

Example 1 is OK: Let's play "Gotcha!" - Round Two

All the examples share one characteristic in common: The second and third DATEDIFF arguments (the date/time values) are no more precise than the first argument (the unit name). In other words, DAY is used on dates with no time component, and SECOND is used on timestamps that don't have fractional seconds.

SELECT DATEDIFF ( DAY, '2011-09-28', '2011-09-29' ); SELECT DATEDIFF ( SECOND, '2011-09-28 23:59:58', '2011-09-28 23:59:59' ); SELECT DATEDIFF ( SECOND, '7910-12-31 23:59:58', '7910-12-31 23:59:59' ); SELECT DATEDIFF ( SECOND, '7910-12-31 23:59:59', '7911-01-01 00:00:00' );

As a result, they all give correct results.

Well, the first 3 do, and the last one would have worked if the third argument hadn't been out of range (the whole point behind the article :).

---

Example 2 is OK: Let's play "Gotcha!" - Round Three

DATEDIFF ( SECOND, '2011-09-28 23:59:58', '2011-09-28 23:59:59' ) DATEDIFF ( SECOND, '7910-12-31 23:59:58', '7910-12-31 23:59:59' ) DATEDIFF ( SECOND, '6910-12-31 23:59:58', '7910-12-31 23:59:59' )

No DATEDIFF problems here at all, for the same reason: All the timestamps are precise only to the second.

---

Example 3 is LUCKY: Intra-Procedure Parallelism

There are a couple of problems with these DATEDIFF calls:

... DECLARE @start TIMESTAMP; DECLARE @start_step_1 TIMESTAMP; DECLARE @start_step_2 TIMESTAMP; ... SET @start = CURRENT TIMESTAMP; SET @start_step_1 = CURRENT TIMESTAMP; ... MESSAGE STRING ( CAST ( DATEDIFF ( MILLISECOND, @start_step_1, CURRENT TIMESTAMP ) AS DECIMAL ( 11, 2 ) ) / 1000.0, ' seconds to perform step 1' ) TO CONSOLE; ... SET @start_step_2 = CURRENT TIMESTAMP; ... MESSAGE STRING ( CAST ( DATEDIFF ( MILLISECOND, @start_step_2, CURRENT TIMESTAMP ) AS DECIMAL ( 11, 2 ) ) / 1000.0, ' seconds to perform step 2' ) TO CONSOLE; MESSAGE STRING ( CAST ( DATEDIFF ( MILLISECOND, @start, CURRENT TIMESTAMP ) AS DECIMAL ( 11, 2 ) ) / 1000.0, ' seconds to perform both steps' ) TO CONSOLE; ... 10.1680000 seconds to perform step 1 19.9700000 seconds to perform step 2 30.1440000 seconds to perform both steps

First, the calls may return values that are incorrect by up to one millisecond because DATEDIFF MILLISECOND returns the number of millisecond boundaries between the two timestamps.

Second, since DATEDIFF MILLISECOND returns a BIGINT, the CAST is singularly pointless, possibly dangerous: You can't magically add two digits of precision to an integer, and DECIMAL ( 11, 2 ) isn't big enough for a BIGINT:

SELECT CAST ( 9223372036854775807 AS DECIMAL ( 11, 2 ) ); Value 9223372036854775807 out of range for destination SQLCODE=-158, ODBC 3 State="22003"

But wait! There's no way this code will run long enough overflow a DECIMAL ( 11, 2 ), and the CAST is there to force decimal rather than integer division. The division by 1000.0 indicates that the user is interested in seconds, rather than milliseconds.

In other words, DATEDIFF MILLISECOND is being used to increase accuracy, not decrease it... an error of one millisecond is OK whereas DATEDIFF SECOND might have an error of a whole second.

The code's not wrong, but it is a bit sloppy: The output shows 7 digits of precision to the right of the decimal point whereas the actual values are only precise to the second digit. An outer CAST AS DECIMAL ( 11, 2 ) call could be used to show this... or call to ROUND().

---

Example 4 is OK: Today's Tip: Counting Days of the Week

The code is OK because "number of day boundaries" is the same as "number of days" when you're talking about dates with no time component:

DATEDIFF ( DAY, '2007-12-14', '2008-01-29' )

Even the explanation in the article is OK, in this particular case: "The DATEDIFF ( ... ) call returns the number of days between the two dates."

---

Example 5 is a FAIL: I'm lonely! signed, Your Database

This code uses DATEDIFF SECOND on full-precision timestamps, so it can return a value that may be up to one second too large because it counts the number of second boundaries between the two timestamps, not the number of seconds difference.

In this example, that means a repeated email may be sent in 19 seconds rather than 20:

... DECLARE @email_sent_at TIMESTAMP; DECLARE @current_timestamp TIMESTAMP DEFAULT CURRENT TIMESTAMP; ... DECLARE @email_repeat_threshold_in_seconds BIGINT DEFAULT 20; ... SELECT email_sent_at INTO @email_sent_at FROM lonely; IF DATEDIFF ( SECOND, @email_sent_at, @current_timestamp ) >= @email_repeat_threshold_in_seconds THEN ...

Maybe a 5% error doesn't matter, or even a 10% error (if the DEFAULT was 10 instead of 20), but that's not the point... the author (me) didn't realize the implications of using DATEDIFF SECOND on precise timestamp values.

Not knowing is not good, not in this business.

---

Example 6 is OK: Everything Looks Like a Database

This one is ok, a DATEDIFF DAY on two dates that don't contain time components:

... DECLARE @from_date DATE; DECLARE @to_date DATE; SET @from_date = '2009-02-01'; SET @to_date = '2009-12-31'; ... DATEDIFF ( DAY, @from_date, @to_date ) ) ) ...

---

Example 7 is LUCKY: Capturing the Server Console Log

Here's an example of a DATEDIFF call that can return values that are too large by up to one millisecond:

... DECLARE LOCAL TEMPORARY TABLE checkpoint_record ( checkpoint_starting TIMESTAMP NOT NULL PRIMARY KEY, checkpoint_finished TIMESTAMP ) NOT TRANSACTIONAL; ... SELECT *, DATEDIFF ( MILLISECOND, checkpoint_record.checkpoint_starting, checkpoint_record.checkpoint_finished ) AS msec FROM checkpoint_record ORDER BY checkpoint_record.checkpoint_starting; ... checkpoint_starting checkpoint_finished msec 2011-01-30 05:11:32.000 2011-01-30 05:11:32.281 281 2011-01-30 05:31:33.453 2011-01-30 05:31:33.937 484 2011-01-30 05:51:35.046 2011-01-30 05:51:35.515 469 2011-01-30 06:11:36.640 2011-01-30 06:11:37.078 438 2011-01-30 06:31:38.234 2011-01-30 06:31:38.781 547 2011-01-30 06:51:39.937 2011-01-30 06:51:41.125 1188

With these particular values one second is an error of 0.3% to 0.08%... which is probably OK.

The point, however, is the same as before: One shouldn't use DATEDIFF MILLISECOND if one wants millisecond accuracy.

---

Example 8 is OK: Great Moments In History: Housing Bubble

The code in this post uses DATE values with no time components, so DATEDIFF DAY works just fine:

DATEDIFF ( DAY, first_date, last_date ) AS days,

---

It could be said the scoring is too lenient: Given the ignorance factor all the OK scores should really be marked as LUCKY. And maybe, all the LUCKYs should be FAIL.

TechEd 2013 Session Catalog - Updated

25 sessions have recently appeared in the MOB (Mobile) track in the TechEd 2013 Session Catalog, but none of them mention SQL Anywhere or MobiLink in either the title or description.

SELECT COUNT ( DISTINCT Session_ID ) FROM SessionDownload2 WHERE Session_ID LIKE 'MOB%' AND ( Title LIKE '%MobiLink%' OR Title LIKE '%SQL Anywhere%' OR Description LIKE '%MobiLink%' OR Description LIKE '%SQL Anywhere%' ); COUNT ----- 0

Here are all the new MOB titles...

Session_ID Title ---------- ---------------------------------------------------------------------------------------------------- MOB100 Understand How SAP Mobile Platform Solves Enterprises' Mobility Needs MOB101 Syclo Agentry for SAP Mobile Professionals MOB102 Mobile Application Development with SAP HANA Cloud Platform MOB103 SAP runs SAP – Mobile security with SMP & SAP Afaria MOB105 Customer Success with the SAP Machine-to-Machine Platform MOB106 Deploy the SAP Mobile Platform in Weeks with SAP Rapid Deployment Solutions MOB107 Using SAPUI5 in Mobile Application Development MOB108 Rapid Application and Service Development with SAP Mobile Platform 3.0 MOB109 Co-Innovate with SAP Experts to Develop Consumer-Grade Mobile Solutions MOB111 How to Build and Design Mobile Enterprise Applications MOB113 Mobile or Immobile? MOB114 How Newell Rubbermaid Uses Mobile Applications to Improve Quality and Sales MOB115 Using SAP Mobile Solutions, SAP Afaria, and a Rapid-Deployment Solution to Manage Product Location MOB116 Enabling Mobile Access to SAP – Design Decisions and Lessons Learned MOB117 Mobile Platform Design & Management Overview MOB118 Secure Mobile Content Management with SAP Mobile Documents MOB119 Tackling Mobile Security – Deep Dive into SAP Afaria MOB202 Creating Services for Mobile Applications Using SAP NetWeaver Gateway OData Channel MOB203 Rapid Mobile Deployment with SAP NetWeaver Gateway and Adobe PhoneGap MOB260 Mobilizing SAP HANA with SAP Mobile Platform MOB261 Build an enterprise mobile application on SAP Mobile Platform MOB262 Build a Process Driven Mobile Application on the SAP Mobile Platforrm MOB263 Building an E2E Solution with SAP Mobile Platform Against a Non-SAP Backend MOB264 Learn How to Build Mobile Solutions to Integrate with CTS+ and SAP Solution Manager MOB840 Secure Mobile Content Management with SAP Mobile Documents

Here's the list of sessions that DO touch on SQL Anywhere and MobiLink:

Session_ID Title ---------- ---------------------------------------------------------------------------- RDP109 Orbiting the Enterprise – SAP Sybase SQL Anywhere as a Satellite Server RDP118 Introduction to SAP Sybase SQL Anywhere RDP119 SAP Sybase SQL Anywhere Satellite Database Case Studies RDP121 Best Practices for Embedding Databases in Lines of Business Applications RDP122 Enhancing Business Intelligence Deployments with SAP Real-Time Data Platform RDP124 Getting Started with SAP Sybase SQL Anywhere, On-Demand Edition RDP141 Powerful Data Access at the Edge of the Enterprise RDP220 Mobilizing Data-Driven Applications RDP222 OData Support in SAP Real-Time Data Platform RDP278 Extending SAP HANA to SMEs Using SAP Sybase SQL Anywhere and MobiLink RDP868 SQL Anywhere Road Map for Mobile and Embedded Systems TEC101 How to Best Embed SAP Technology

RDP868 is new on this list, replacing EA269 (Everything You Need to Know to Use SAP Crystal Reports) which is still being offered but the description no longer mentions SQL Anywhere or MobiLink.

So, the count is still 12... is that enough to get you to Las Vegas?

Are you interested in HANA?

SELECT COUNT ( DISTINCT Session_ID ) FROM SessionDownload2 WHERE Title LIKE '%HANA%' OR Description LIKE '%HANA%'; COUNT ----- 229

---

TechEd 2013: Last Day for Early Bird Rate

Today (August 2) is the last day for the Early Bird rate at the SAP TechEd 2013 conference in Las Vegas on October 21 to 25.

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.

---