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.

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.

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

Monday, July 29, 2013

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?

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.

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.

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

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...

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:

Monday, July 15, 2013

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

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,

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

Friday, July 12, 2013

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.

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.

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.

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.