media, music, sql comments edit

I’m working on setting up various Smart Playlists in iTunes and every time I’m working with them I totally run into all these shortcomings with the way they work. Like, what if I want all of the pop songs by Depeche Mode and Erasure that show up between 1980 and 1990? You have your choice of AND or OR, right? So it’s “Genre is Pop” and “Year is in the range 1980 to 1990” and… what? “Artist is Depeche Mode” and “Artist is Erasure” won’t work. I need an OR.

Why can’t I just do this: SELECT * FROM Library WHERE Year BETWEEN 1980 AND 1990 AND Genre = 'Pop' AND Artist IN ('Erasure', 'Depeche Mode')

No reason, that’s why. Come on, guys, get with the program.

sql comments edit

I’m going to combine days two and three into one big entry because I don’t think I’ll get a chance to do a full day-three review at the end of today.

Yesterday was day two and we looked at SQL Server 2005 service broker stuff, its native HTTP support, and Notification Services.

The service broker facilities they’ve added are pretty cool. The ability to set up messages and queues and such inside the server is neat, and it folds nicely into the native HTTP support to allow for exposure of these things via web service. Cool. SQLXML has rolled into SQL Server, which we all anticipated and love.

Notification Services, though… OK, I’m just going to be brutally honest:

SQL Server 2005 Notification Services sucks big fat donkey dong.

Seriously.

Notification Services is the biggest hunk of claptrap Rube Goldberg mechanics I’ve been introduced to in a long time. It’s not debuggable, it’s super-hard to set up, and for what it offers I’m not sure it’s worth the effort.

We had a lab to set up some rudimentary Notification Services. The lab involved us creating a lot of hand-cranked XML configuration, running some setup to get the services up and running, then testing it out.

Problem 1: The XML contains SQL that turns into stored procedures. Why is that bad? When they make the stored procedures, there’s nothing to tell you whether you have a typo in the SQL. I mistyped a database object name and it never had a problem with it. In fact, I found that error in the Event Viewer - not even in the SQL error log - and only when it actually tried to execute the procedure. NO!

Problem 2: There’s insufficient logging. After I corrected my error, I tried to use the service again and something else went wrong. What happened? I have no idea. It just swallows the errors and moves on like there’s no issue. No errors, no warnings, no log. How am I supposed to debug that?

The whole thing where the XML that you have to hand-generate (and there’s a LOT) has a lot of magic words in it that you just have to sort of “know” about (like you have to just “know” that when you type “Foo” in one element that it becomes a stored proc with a name like “ServiceFooSomeProcNameHere” - yeah, that’s intuitive. It really does work like Rube Goldberg machines - this bit of XML kicks this block of code out that rolls down the hill and knocks this table row down and flips over and tosses a message over to that service… Come on, guys. It’s like someone took a weekend, architected the thing, and clamped it on the side of SQL Server 2005 so they could have a selling point or something.

Today we’ll be learning about managed code in SQL Server 2005, which looks great; working with client apps (ADO.NET), which won’t be news for me; and SQL management objects, which also looks great.

Oh, and before I forget, tomorrow is Thanksgiving, so Happy Thanksgiving to all!

gists, csharp, dotnet comments edit

I’m looking at different ways to provide caching from within an application so I can read in some information from a file, keep it in memory, and have cache dependencies perform a callback to update the cache whenever the file changes.

This is simple to do in an ASP.NET app using the System.Web.Caching.Cache object that you find in HttpRequest. Just add something to the cache with a cache dependency and you’re set. But what about if you’re working in a console or Windows Forms app?

I did an experiment and it looks like you can access the cache from any application as long as you access it through HttpRuntime directly. I haven’t tested to see if this works on a machine that doesn’t have IIS installed, but I don’t see why it wouldn’t.

Below is my experiment code. It’s a console app that reads/writes the cache with a file dependency that constantly changes. Running it, you can see that the cache is doing what you expect, just like in a web app.

using System;
using System.Diagnostics;
using System.IO;
using System.Web;
using System.Web.Caching;

namespace ConsoleApplication1{
  class Class1{
    const string KEY = "mykey";
    const string FILENAME = "dependency.txt";

    /// <summary>
    /// The main entry point for the application.
    /// </summary>
    [STAThread]
    static void Main(string[] args){
      WriteFile();
      InsertItem();

      for(int i = 0; i < 1000; i++){
        ReadItem();
        WriteFile();
      }
      ReadItem();

      if(Debugger.IsAttached){
        Console.ReadLine();
      }
    }

    static void InsertItem(){
      DateTime t = DateTime.Now;
      Console.WriteLine("Inserting item into cache: {0:m.s.ffffff}", t);
      HttpRuntime.Cache.Insert(
          KEY,
          t,
          new CacheDependency(Path.GetFullPath(FILENAME)),
          DateTime.MaxValue,
          TimeSpan.Zero,
          CacheItemPriority.Default,
          new CacheItemRemovedCallback(CallBack));
    }

    static void ReadItem(){
      object item = HttpRuntime.Cache[KEY];
      if(item == null){
        Console.WriteLine("Item is null.");
      }
      else if(item is DateTime){
        Console.WriteLine("Item is {0:m.s.ffffff}", item);
      }
      else{
        Console.WriteLine("Item is wrong type: {0}", item.GetType());
      }
    }

    static void WriteFile(){
      if(File.Exists(FILENAME)){
        File.Delete(FILENAME);
      }
      Console.WriteLine("Removing file.");
      FileStream stm = File.Create(FILENAME);
      stm.Close();
      Console.WriteLine("Wrote file.  File exists: {0}", File.Exists(FILENAME));
    }

    static void CallBack(string key, object value, CacheItemRemovedReason reason){
      Console.WriteLine("Callback invoked: {0}", reason);
      InsertItem();
    }
  }
}

If anyone finds a problem with it, do leave me a comment with reproduction info. Seems to work, though, which is pretty cool.

sql comments edit

I’m taking Microsoft training course #2734B: Updating Your Database Development Skills to Microsoft SQL Server 2005. I’ve got my MCSD; I really just want to see what’s new in SQL Server 2005 and how to use it. Rock on, right?

Day 1, we covered a general overview of SQL Server 2005 changes (enough to give you a general idea of what the course will cover, but not enough to really tell you anything), some of the T-SQL enhancements they’ve added, and a bit on how they’ve updated the handling of XML in the database.

The first module, the overview, was pretty good for the beginners but really didn’t tell me much.

The second module, the T-SQL updates, was neat. They added some stuff that should have been there all along (ALTER INDEX, anyone?) and put some really cool things in for partitioning. When we got to pivot tables… well, I won’t lie. I generally understand the concept of pivot tables, but I’m not a data analyst and really don’t care to be, so when we got to the use of PIVOT and UNPIVOT, I took it in from an academic standpoint but I can’t say I totally get it. The new ranking operators are cool, though, and I can see how they would be very useful. And how can I forget the TRY/CATCH they’ve added? Love that.

The third module, on handling XML, got a little more tricky. I feel pretty comfortable in my XML skin and worked with SQL Server’s OPENXML and SELECT...FOR XML in the 2000 version, so that stuff wasn’t new. The added ability to format the output of the XML to the level now available is very welcome, as is the ability to store XML as a native data type (either validated or not).

So what have I seen that I don’t like?

When we got into the trickier parts of the XML stuff - updating values of attributes in stored XML fields, for example - the book got a bit sparse. The lab would instruct you to do something like “change the ‘foo’ attribute in the document stored in field ‘bar’ in the first row in the table to have the value ‘val’” and when you went to look up the syntax for that… well, good luck with that. I ended up opening the solution and seeing how they did it (at which point it finally made sense).

The biggest issue, though, is how arbitrary some of the syntax has become. It turns out that in some cases, T-SQL requires semicolons to end statements and in other cases it doesn’t. That makes it difficult, but I figured out that they don’t penalize you for ending every statement in a semicolon, so maybe that’s the new habit to get into. I also don’t like the inconsistency of the syntax - sometimes you specify options in parentheses, sometimes you don’t; sometimes the parameters for a method are in [square brackets] and sometimes ‘single quotes.’ The worst bit is that they seem to intermingle it all without any rhyme or reason, so you might call a function like FUNCTION_CALL MODIFIER([param1], 'param2') 'param3', [param4] even if all of the parameters refer to database objects. Consistency! Pick something and stick with it! It feels very much like all this was tacked onto the end of T-SQL and they did their best not to alienate previous T-SQL users but couldn’t quite make it happen.

Today, day 2, we’re looking at the native SQL Server 2005 service oriented architecture provisions. I’m liking it so far. Better than SQLXML, anyway. It occurs to me that you could potentially replace BizTalk with SQL Server 2005. I wonder if that’s what they were going for.