Friday, August 28, 2015

How to translate T-SQL Datetime2 to Float or Real

The new Datetime2 data type introduced in Microsoft SQL Server 2008 has several advantages over the old Datetime type. One of them is precision in 100 nanoseconds rather than coarse milliseconds, another is that is has a larger date range. It has disadvantages, too, like a difficulty in translating it into numerical values. It was a classic hack to CONVERT/CAST a Datetime into a Float in order to get a numerical value that you could manipulate (like convert it to an integer to get the date without time, which is now accomplished by converting it to Date, another type introduced in SQL Server 2008). There are many reasons why one needs to translate a datetime into a numerical value, I don't get into that here. So here is how to convert a Datetime2 value into a Float.

First solution:
DATEDIFF(SECOND,{d '1970-01-01'}, @Time)+DATEPART(nanosecond,@Time)/1.0E+9
- returns a value in seconds with nanosecond precision from the beginning of the year 1970. Advantage: simple to use and understand. Disadvantage: not similar to the conversion from Datetime to Float.

Second solution:
DATEDIFF(DAY,{d '1900-01-01'}, @Time)+DATEPART(HOUR,@Time)/24.0+DATEPART(MINUTE,@Time)/(24.0*60)+DATEPART(SECOND,@Time)/(24.0*60*60)+DATEPART(nanosecond,@Time)/(24.0*60*60*1.0E+9)
- returns a value that is similar to the float conversion of a datetime. Advantage: doesn't lose precision like converting to a Datetime and then to Float. Disadvantage: look at the length of that!

Final solution:
25567+(DATEDIFF(SECOND,{d '1970-01-01'}, @Time)+DATEPART(nanosecond,@Time)/1.0E+9)/86400.0
- combines the two solutions above. It easy to read and understand. It computes the number of seconds with nanoseconds from 1970, divides by 86400 to get the number of days and adds 25567, which is the number of days between 1900 and 1970.

Wednesday, August 26, 2015

The story of the LEFT JOIN in T-SQL (and why I learned to love the NOT EXISTS)

As a software developer - and by that I mean someone writing programs in C#, Javascript and so on, and occasionally using databases when something needs to be stored somewhere - I have an instinctual fear of the Arrow Anti-pattern. Therefore I really dislike stuff like NOT EXISTS(SELECT 1 FROM Something). However, recent tests have convinced me that this is the best solution for testing for existing records. I am not going to reinvent the wheel; here are some wonderful links regarding this, after which I will summarize:

Let's say you want to insert in a table all records from another source that do not already exist in the table. You have several options, but the most commonly used are:
FROM SourceTable
LEFT JOIN DestinationTable
ON SomeCondition
WHERE DestinationTable.Id IS NULL
FROM SourceTable
WHERE NOT EXIST(SELECT 1 FROM DestinationTable WHERE SomeCondition)

Personally I prefer the first version, for readability reasons as well as having listened to the mantra "Never do selects in selects" for all my life. However, it becomes apparent that the second version is a lot more efficient. The simple reason is that for the first example Microsoft SQL Server will first join the two tables in memory, then filter. If you have multiple combinations of records that satisfy the condition this will result in some huge memory and CPU usage, especially if you have no indexes defined and, sometimes, because you have some indexes defined. The second option uses one of the few methods guaranteed to exit, NOT EXISTS, which immediately invalidates a record at the first match.

Other options involve using the EXCEPT or INTERSECT operations in SQL, but they are not really helping. Intersecting ids, for example, then inner joining with SourceTable works, but it is somewhere in between the two solutions above and it looks like crap as well. Join hints don't help either.

Friday, August 21, 2015

How to OUTPUT columns that you did not INSERT in T-SQL

The OUTPUT clause is a very useful tool in Microsoft SQL, allowing for getting automatically inserted columns in the same command as the INSERT. Imagine you have a table with an identity column and you need the generated ids as you insert new records. It would look like this:
     Value NVARCHAR(100) 

CREATE TABLE AnotherTable 
     Value               NVARCHAR(100), 
     AnotherValue        NVARCHAR(100), 
     SomeConditionIsTrue BIT 


     Id INT ,
     AnotherValue        NVARCHAR(100)

INSERT INTO MyTable (Value) 
    OUTPUT inserted.Id INTO #ids (id) 
    FROM   AnotherTable 
WHERE  SomeConditionIsTrue = 1 

-- Do something with the inserted Ids 

However, what do you do if you want to also insert the column AnotherValue to the #ids table? Something like this does not work:
INSERT INTO MyTable (Value) 
    OUTPUT inserted.Id,AnotherTable.AnotherValue INTO #ids (id,AnotherValue) 
    FROM   AnotherTable 
WHERE  SomeConditionIsTrue = 1 

Enter the often ignored MERGE, which can help us translate the query above into:
    SELECT Value , AnotherValue
        FROM   AnotherTable
    WHERE  SomeConditionIsTrue = 1 
) t ON 1=0 --FALSE
  INSERT (Value) VALUES (t.Value)
  OUTPUT Inserted.Id, t.AnotherValue INTO #ids (Id, AnotherValue);

Note the 1=0 condition so that the merge never "matches" and how the select from the first query now contains all the columns needed to be output, even if only some of them are inserted in the insert table.

This post was prompted by a StackOverflow answer that, as great as it was, didn't make it clear what to do when you get your values from a more complicated select. The answer is simple: put it all in the 'using' table.

Thursday, August 20, 2015

No Longer Human (Ningen Shikkaku), by Osamu Dazai

book cover I read this short novel from start to end in under a day. Osamu Dazai writes from the point of view of a sociopathic young man who cannot seem to understand the human condition and fears all people around him, mostly because he expects to be found out at every moment. The title of the book can be translated in several ways, the English one relates to the protagonist's feelings of losing one's humanity, while the literal translation reads as "disqualified from being human", implying a societal judgement. Imagine a Japanese version of The Stranger, by Albert Camus, and you get a good picture of the plot and feel of the book. Both books were written in the same period, more or less, but while Camus probably imagined the character, many believe Dazai was talking about himself - he committed suicide soon after.

No Longer Human is the second best rated Japanese book and was adapted in movie and manga. It is difficult to imagine those being better than the dry accounting of the inner turmoil of the character, starting as a little boy who devises "clowning" as a method of passing the test of humanity, outwardly fun and good natured and inwardly terrified of being discovered as a fraud and punished by the society of strange human beings that he cannot understand or empathize with. I highly recommend it.

Wednesday, August 19, 2015

Daemon, by Daniel Suarez

book cover I have been watching horror movies since I was six and read books of all sort through the years, but rarely have I seen something so truly scary as Daemon. Daniel Suarez manages to convey terror not by upgrading the villain, but by making it mundane. The daemon is not an all knowing Artificial Intelligence that takes over the world, but a stupid game engine run by a logic tree. The ease with which something like this could be created makes the book truly terrifying, particularly for me, who has actually thought of the weakness of humans when faced with decisions and pondered a world where machines make the decisions not because they want to rule us, but because we don't want to choose.

But there is more to this book than its subject. It is actually very well written and that is remarkable considering it is Suarez' first book. I will read the sequel to Daemon, Freedom™ as soon as I can. I loved the attention to detail, not a descriptive boring series of useless trivia, but a close focus on what makes people tick and how technology falls into place to fill the gaps that our failings leave. On the cover of the new book that Daniel Suarez wrote there is a quote that I feel is totally true: he is a true heir to Michael Crichton.

Tuesday, August 18, 2015

Bastard!! - a funny six episode anime




Sunday, August 16, 2015

Creatures of the Abyss, by Murray Leinster





Thursday, August 13, 2015

navigator.geolocation API strange behaviour on Microsoft Edge - a foolproof location snippet in Javascript

Today we tested a web application in the new Microsoft Edge browser. To our surprize, the site failed where Internet Explorer, Chrome, Firefox and even Safari worked perfectly well. I narrowed the problem to the navigator.geolocation.getCurrentLocation which wasn't working. The site would see navigator.geolocation, ask for the current location, the user would be prompted to allow the site to access location and after that would silently fail. What I mean by that is that neither the success or the error callbacks were called, even if the options object specified one second for the timeout. I don't have access to a lot of Windows 10 machines and I assume that if a lot of people met with this problem they would invade the Internet with angry messages, but so far I've found no one having the same issue.

Bottom line: forced to take into consideration the possibility that the geolocation API would silently fail, I changed the code like this:
if (navigator.geolocation) {
    var timeoutInSeconds=1;
    var geotimeout=setTimeout(function() {
    },timeoutInSeconds*1000+500); //plus 500 ms to allow the API to timeout normally
    navigator.geolocation.getCurrentPosition(function (position) {
        var pos = doSomethingWith(position.coords.latitude, position.coords.longitude);
    }, function () {
        timeout: timeoutInSeconds*1000
} else {

In the handleNoGeolocation function I've accessed the great service FreeGeoIp, that returns vague coordinates based on your IP and fell back to a static latitude, longitude pair if even this call failed.

Note: for the first time the function is called for your site, a browser dialog will appear, requesting permission to share the location. During the display of the dialog the timeout will fire, then, based on the user choice (and browser) a success/error handler will be called or nothing (like in this case), so make sure your code can handle running handleNoGeolocation followed by doSomethingWith.

Tuesday, August 11, 2015

Let's all help the police!






Monday, August 10, 2015

Star Trek Prime Directive, by Judith and Garfield Reeves Stevens

Another great Star Trek novel placed in the Kirk era, Star Trek Prime Directive keeps the reader/listener on the edge of their seat. It starts with a disgraced Kirk, a scattered crew and a scrapped Enterprise. It shows the dark, bureaucratic side of the Federation, cruel and merciless when you are not the lucky wearer of the golden captain uniform or, even better, an admiral. How did it come to this? The answer is both captivating, original and with deep roots in the Star Trek basic tenant: the Prime Directive.

I actually listened to the audiobook, also on YouTube (see embedded video), which was very well narrated. If I had any problems with the story was that it was clearly very biased. Kirk is always thinking of the poor alien species that are like humans, but seems to have no qualms to experiment with phaser fire and even slightly torture other alien beings if they are bug like. Also Spock seems very little a Vulcan in this.

Bottom line is that the idea was intriguing and original and the style of the writing was very good. One of the best ST novels so far.

Thursday, August 06, 2015

LoggerEventSource error: Not enough storage is available to complete this operation

I've come upon this strange Not enough storage is available to complete this operation ArgumentException when creating an instance of EventSource derived classes. This class is responsible for creating entries in Windows logs. Strangely enough, there are very few articles on the Internet connecting the class with this particular exception, so I started to investigate. One important thing to notice is that the exception is intermittent. Basically you can cycle a few times with a try/catch block and get a valid instance. That seems to indicate some sort of race condition. So far, this is the easy solution I could find. However, I really wanted to know why does it happen.

If I remove the EventSource class from the searches I get more pages reporting the same exception and one of the reasons that people say it happens is related to the size of the registry. Retrospectively it makes sense, but it never occurred to me that the system registry has a maximum size. But is that the problem? Looking with the EventViewer summary I see something like this:

Of course, the most obvious thing there is the exact size of each log category: 20.00 MB. If one right-clicks on any of the log groups and goes to Properties, the size limit for each is clearly shown and configurable. So is that the problem?

The exception is thrown almost exclusively when the logging is heavy: multiple threads trying to log stuff at the same time. Since retrying usually solves the problem, my guess is that the exception is thrown somewhere between the request for a new log entry and the process that eliminates old entries to allow for new ones. Unfortunately I don't see any configuration option for how many entries to eliminate. I would have liked to clear, let's say, 20% of the log when it is full to make this problem less relevant. Perhaps hidden in the bowels of the system registry there is a way to set this, but at this time I don't know it. Nor is it clear if I have the option to remove more of the less important events rather than just the oldest. Clearly the EventLog class in .NET supports deleting individual log entries, so this is feasible if it ever becomes a real problem.

So far, my solution is to just try again when the error is thrown:
LoggerEventSource eventSource = null; //EventSource derived class (see documentation)
for (var i = 0; i < 5 && eventSource == null; i++)
        eventSource = new LoggerEventSource();
    catch (ArgumentException)

Star Trek: Strangers from the Sky

audiobook cover Star Trek: Strangers from the Sky is an audiobook read by George Takei and Leonard Nimoy. While it is a typical ST The Original Series plot, with god like aliens, travel back in time to significant moments of Earth's history and a focus on high moral values that, in the end, save the day, I felt that it was a little bit more subtle, deeper than a typical episode of any of the series. Was it because of the introspection of the characters, or the wonderful narration of Nimoy and Takei, I do not know. What I can say is that I enjoyed listening to the story quite a lot and I recommend it highly for any Star Trek fan.

I also don't know if it is in the public domain or not, all I can say is that I listened to it on YouTube and so can you:
Of course you cannot listen to it on YouTube anymore. Some lawyers saw to that.

Wednesday, August 05, 2015

Gnome Man's Land (Tim Desmond Book 1), by Esther M. Friesner











Monday, August 03, 2015

Timeout expired exception when rolling back SQL TransactionScope in .NET

I had this situation where I had to execute large SQL script files and the default sqlcmd tool was throwing exceptions rather than execute them. So I created my own tool to read the scripts and execute them transactionally. Everything went smoothly, except at the end. You see, I didn't use TransactionScope.Complete from the beginning in order to see how the program would cope with a massive rollback. Not well, apparently.

The exception was thrown at rollback: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. I had set the TransactionOptions.Timeout to TransactionManager.MaximumTimeout and the SqlCommand.CommandTimeout to 0 (meaning never end) and I still got the exception. Apparently, the problem was the SqlConnection.ConnectTimeout which is a readonly property with a default value of 15 seconds. The value can be changed via the connection string, by adding something like Connect Timeout=36000 (10 hours) and many articles on the Internet suggest doing that. However, that is just really ugly. A better solution is to set the value of the timeout programmatically and this is how to do it:
var timeout = TimeSpan.FromHours(10);
SqlConnectionStringBuilder csb = new SqlConnectionStringBuilder(connectionString);
csb.ConnectTimeout = (int)timeout.TotalSeconds;
connectionString = csb.ConnectionString;

As you can see, the nice SqlConnectionStringBuilder helps us validate, parse and change the values in the connection string. One can imagine other interesting uses, like adding MARS to the connection string automatically or restricting the use to a list of databases or disallowing weak passwords, etc.