Thursday, March 27, 2008

.NET User Defined Functions in Sql Server

A bit slow on the wagon, but I didn't need this until now. It is all about creating a .NET assembly for use by the SQL Server. These are the quick and dirty steps to it.

C# Steps
1. Create a Class Library project (some links suggest an Sql Server Project, but that is not available for Visual Studio versions below Professional)
2. Add a public class
3. Add a public static method and decorate it with [SqlFunction]
4. Do not use static fields in the said class
Compile the assembly.

SQL Steps
1. Define the assembly in SQL:
CREATE ASSEMBLY MyAssembly FROM 'C:\SqlCLR\MyAssembly.dll'

2. Create the SQL function to use the method in the assembly:
CREATE FUNCTION MyUserDefinedFunction(
@s1 NVARCHAR(4000),@s2 NVARCHAR(4000) ... other parameters )
RETURNS FLOAT AS
EXTERNAL NAME MyAssembly.[MyNamespace.MyClass].MyUserDefinedFunction

3. Enable CLR execution in SQL Server:
EXEC sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

4. use the function like
SELECT dbo.MyUserDefinedFunction('test','test2'...)


Things to remember:
1. Make sure the parameter types are the same in the .NET method and the SQL function
- the float keyword in SQL means double in .NET! I have no idea what kind of SQL type you must use in your function to match a .NET float.
- the string in .NET is matched to nvarchar in SQL
- the bit is matched to a bool as expected
2. Whenever you change the DLL you must DROP all the functions, then DROP the assembly, then create it again. If there are no signature changes, I guess just replacing the dll file could work.

There are a lot of things to say about returning tables instead of single values or about defining user defined aggregate functions, but not in this post.

Friday, March 21, 2008

Just - RadioHead

Long time since I posted a song on the blog. This is one old, but good song AND video. Rarely you find a truly good combination of sound and presentation nowadays. Quoting from the Wikipedia article for this song: Radiohead frontman Thom Yorke wrote the song about a narcissistic friend of his, which on closer inspection is showcased by the imagery in the lyrics - a parallel to earlier My Iron Lung EP track "Lewis [Mistreated]". He also says that it was somewhat of a competition between him and Jonny Greenwood to see who could fit the most chords into a song. "Just" is especially notable for Greenwood's guitar solo, which showcases some of his first uses of the Digitech Whammy Pedal. The guitar work in the song has been seen as an homage to post-punk band Magazine, one of Radiohead's key influences at the time.



The video reminds me of an old sci-fi story I've read about a word that, when heard by a person, would transform them into a purple jelly cone, including the one saying it. When someone finally understood and accepted the reality of it, it all turned into a race to stop a deaf person going to a radio station and speaking the word. He was immune, you see. Anyway, enjoy.

Wednesday, March 19, 2008

Arthur C. Clarke died at age 90

Science-fiction author Arthur C. Clarke is one of the people that has defined me as a person and who's books provided both comfort and excitement during my childhood and adolescence. He is mostly known for the movie adaptation of 2001: A Space Odyssey although I liked the Rama series more. He also invented the concept of a geo synchronous satellite.
He has lived a full life and I don't believe in artificially prolonging living above a certain threshold (he was suffering for 13 years now), so I am just happy to have known about him rather than sad for his death.

More details at this BBC News article and this Wikipedia entry.

Sunday, March 16, 2008

TV Series I have been watching - part 4

Ok, I will make a quick list and description. The rest is up to you.
  • Stargate Atlantis - the spinoff series of Stargate SG1 has ended its 4Th season, but a 5Th is under way. I am watching it mechanically now, as the quality and originality of the show has decreased in time to the point of negative values

  • Battlestar Galactica - another sci-fi show, it reinvented completely the story of the old 70's BattleStar Galactica. It started great, but the third season ended in some weird pseudo spiritual mambo jumbo. Hopefully, the fourth season due in April will not suffer from the 'Lost' disease

  • Doctor Who - British sci fi, a bit goofy, but fun nonetheless. It is about an alien that travels space and time with his human female companions (nothing kinky though ;) )

  • Eureka - silly sci-fi show about an American town that is populated only with brilliant scientists. Very far fetched, but nice as a light comedy. I haven't been able to finish season 2 that finished airing. I think there will be a season 3 as well.

  • Regenesis - a bit hard to catalogue, this Canadian sci-fi depicts a multinational scientific group with no political associations trying to fight the medical national emergencies in the world. All kinds of diseases and wacky characters, but a bit failing in the field of science. Season 4 is ongoing.

  • Jericho - now this is a series that was almost cancelled, then brought back at popular demand. Twelve American cities have been blown to smithereens by carefully planted nukes. The US quickly destroyed Iran and North Korea, but it seems the actual perpetrators are American. A small town in the US has to fight off both corporate conspiracies, the newly formed American government and the bands of bandits and neighbouring towns that want what Jericho has: a fan base :).

  • Numb3rs - oh, what a waste. It started so beautifully as a mathematician applying math to help his FBI agent brother. By season 3 it got completely unscientific, season 4 lost math completely and turned into yet another cop show.

  • Lost - I am not watching this anymore, only my wife does. I stopped watching this crap after the first season. Meanwhile they use the same psychological system to carry on a story that makes no sense and that everybody watches for the sole purpose of seeing it end. Like a modern day 1001 Arabian nights played on the audience. The only worth watching part of Lost is the pilot episode, then you should continue the story in your own way.

  • Grey's Anatomy - medical/soap opera. My wife watches it. It started a bit interesting, but ended up crappy. Almost no medicine anymore, instead you see the personal issues of various medical staff people.

  • Private Practice - as if Grey's Anatomy was not enough, they made a spinoff out of it. I don't know if it will ever air again, though, with the writer strike and all.

  • Ugly Betty - it proved a commercial success in Latin America, so the TV corporations cloned it for the US market. A gayified version of the South American show, it is basically a women magazine made TV series.

  • Prison Break - interesting beginning. I recommend the first season, then it all got 'Lost', with psychological effect to keep the audience interested, an escalation of tension, a complete disaster of a plot.

  • House MD - this one lasted a bit longer before turning to shit. It is a medical show that depicts a wacky, but brilliant diagnostician trying to figure out what the disease and cure is before the sick expires. It has become repetitive and self-satirical, moving away from medicine and back on the ugly and not interesting ploy of human relationships, but then it got back on track. Still worth watching, although the quality is dropping and the story grows old.

  • Criminal Minds - TV cop show with a team of FBI agents trying to find criminals by using behavioural analysis. Interesting enough, although, as you can imagine, not very technical.

  • Sarah Connor Chronicles - [chuckle] Terminator is not a teenage girl. She protects John Connor and his mother from other Terminators that roam freely in the past, yet keep a low profile for some reason or another. It's not terrible.

  • South Park - delicious animated gross comedy, making fun of everything and everybody. They lost the way around season 10, when the authors seemed buried in scandals and full of rage, unable to make fun of things, starting to instead vent frustration in the show. I am happy to see they have recovered in season 11 and season 12 has just started.

  • Dexter - a serial killer hunts serial killers. This makes him good, somehow, but he is still a killer. Funny enough his cop father taught him how to hide from the police, his sister doesn't know and she is a cop, too, and a colleague of Dexter, who is a criminal forensic in the police department.

  • Big Love - this is like a family in distress kind of show, but this time the family is made of one husband, 3 wives and numerous children I've lost count of. Yes, they are Mormons and they must navigate the perils of hiding their religion and marital arrangements from the rest of the world, while managing to obey or wriggle around the organisational structure of the Mormons in their community. As much a fascinating subject as this is, the show is pretty ordinary. Tom Hanks is an executive producer.


That's about it.

Escape pod - the sci fi podcast site

While in search for interesting and high quality podcasts I've stumbled upon escapepod.org, a site that offers free sci-fi stories in audio format. They last about 40 minutes, so just about the time it takes me to get from my home to work and they are great in both content and narration quality. It completely takes care of my needs when I get back home and I don't feel like listening to some tech podcast.

Even more, it seems that they post two or three books a week, so you won't finish the site right away. Great job, guys! A site truly after my own heart: exactly what you need, how you need it and with no annoying ads or marketing ploys.

Monday, March 10, 2008

On Automated Test Driven Development

As a software developer with no formal training, I sometimes feel humbled by the more standardised approaches to programming like, for example, Test Driven Development or TDD. But I think that today I finally figured it out.

You see, TDD is supposed to "cover your code" with tests. Once all your tests run without fail, you know that you can focus on other parts of the code, like refactoring, interface or performance improvements or on new features. But what it actually means is a simulation of your client and/or debug person. Once you automatically simulate a client, you can make sure it is satisfied before you move on to the real person. And this is also where the whole thing fails, because it is obviously impossible to simulate a human being without real effort that surpasses the building of the very software you are trying to test. It would be fun to actually do it, then watch the software complain on its own functionality. If any software is going to take over the world, one that emulates an annoying client probably will.

One of the hardest parts to simulate is the work with the interface. No test will ever be able to look disappointed while expressing a lack of enthusiasm on your choice of colors. It is even harder to test web interfaces, although software that can test some of the behaviour of web apps and sites exists, with limited functionality. Also, it is impossible to test for functionality that is not there.

A good use of tests is to address (and in this way document) the bug findings! When you see a bug, you create a test that fails because of it, then you fix the bug. Also, by trying to cover as much of your code with the tests, you get to formalize the access to your code and also are forced to decouple interface from code. No wonder that all the test frameworks are used for Unit Testing. Once you can create a unit or a library of code with no other inputs or outputs than data types, you can test the hell out of it.

But it still leaves the interface out. I have been thinking of ways of describing the test procedure not in code, but in English, something like a unit test for a person rather than a testing framework. This can be further automated, where possible, or just followed by a dedicated tester.

What I would really be interested in would be a general way of creating tests for recurring bugs. A sort of code policy enforcement, if you will, but one that would test for the same bug multiple applications. Can it be done without also formalising the structure of those applications?

Thursday, March 06, 2008

Using the AjaxControlToolkit AutoCompleteExtender - Part 2

A while ago I wrote a quick post to remind me of how to use the AutoCompleteExtender, but recently I realised that it was terribly incomplete (pun not intended). I've updated it, but I also felt that I need to restructure the whole post, so here it is, with more details and more code fun.

First of all, a short disclaimer: I am not familiar with the ASP.Net Ajax javascript paradigm. If some of the things that I am doing seem really stupid, it's because I did it by trial and error, not by understanding why the code is as it is. Here it goes.

There are two ways in which to use the AutoCompleteExtender: using PageMethods or using web service methods. The details are in the previous post, I will only list the gotchas in this one.
  • PageMethods requirements:
    1. ScriptManager must have EnablePageMethods="true"
    2. The page method must have the form public static string[] MethodName(string prefixText, int count) AND THE SAME PARAMETER NAMES. If you change prefixText with text it will not work!
    3. The page method has to be public and STATIC
    4. No, it is not possible to declare the method in a web user control, it must be in the page
  • Web service requirements:
    1. The method must have the form public string[] MethodName(string prefixText, int count) AND THE SAME PARAMETER NAMES. If you change prefixText with text it will not work!
    2. The method has to be public and NOT STATIC
    3. The method must be marked as ScriptMethod
.

Now, the method can return an array different from a string array, but the only useful types there would be numerical or maybe dates. Any object that you send will ultimately be transformed into "[object Object]". There is a way to send a pair of value,text encoded in the strings, and for that you use:
AutoCompleteExtender.CreateAutoCompleteItem(text, value);

It doesn't help much outside the fact that in the client javascript events of the AutoCompleteExtender the first parameter will be the AutoCompleteExtender javascript object and the second an object with a _text and a _value properties.

One of the questions I noticed frequently on the web is: How do I show the user that there are no auto complete matches?. The easy solution is always to return at least one string in the string array that your method is returning. If there are no matches, make sure there is a "No Match" string in the list. But then the complicated part comes along: how do you stop the user from selecting "No Match" from the list? And I do have a solution. It seems that the text in the textbox is set based on the existence of a javascript object called control that has a set_text function. If the object or the function do not exist, then a simple textbox.value=text is performed. So I used this code:

string script = @"var tb=document.getElementById('" + tbAutoComplete.ClientID + @"');if (tb) tb.control={set_text:setText,element:tb};";
ScriptManager.RegisterStartupScript(Page,Page.GetType(),UniqueID+"_init",script,true);
to set the object for my textbox. And also the javascript code that looks liks this:
function setText(input) {
if (input=='No Match') return;
this.element.value=input;
}


These being said, I think that one can use the AutoCompleteExtender and know what the hell is making it not work.

Update: Fixing TabContainer to work with dynamic TabPanels

Update: The 30 September 2009 release of the AjaxControlToolkit doesn't have the error that I fix here. My patch was applied in July and from September on the bug is gone in the official release as well. Good riddance! :)

==== Obsolete post follows

I've just downloaded the 29 feb 2008 release of the AjaxControlToolKit and I noticed that the TabContainer bug that I fixed in one of the previous posts did not work anymore. So the post is now updated with the latest fix.

Fixing TabContainer to work with dynamic TabPanels

Apparently, the guys that make the Ajax Control Toolkit are not considering this a bug, since I posted it a long time ago as well as a bunch of other folks and there are also some discussions about it on some forums.

Wednesday, March 05, 2008

Naruto - Harry Potter meets InuYasha

Naruto and SasukeWell, imagine a lonely boy, without a family, learning magic in a special school and who's archenemy is a powerful yet evil wizard. He is always accompanied by his friends, a boy and a girl. The evil wizard likes snakes and manages to kill the headmaster of the magic school. No, it's not Harry Potter, it's Naruto!

It's like the Japanese liked Harry Potter, but thought they can do one better. They're not exactly magicians, but ninjas; Naruto Uzumaki, the main character, is a mix of both Harry and Ron, while boy number two, Sasuke Uchiha, is more like a small Snape. With a bit of a stretch, one could take the Naruto story and rip it off in a prequel to Harry Potter, with the parents as the characters :)

Anyway, long story short: the anime is children oriented, with all kind of soapy feelings, camaraderie and friendships, no gore, little blood, a bit of death, but "censored" where violence or tension is concerned. If you ignore the ridiculous simplicity of the characters, the story is pretty captivating and the "ninja science" fun. It more than makes up in quantity what it misses in quality. The first anime, Naruto, is concerned with the childhood of the characters and spans 220 episodes, while the ongoing Naruto: Shipp┼źden with the adolescence and it is close to 50 episodes so far. There are 20 minutes episodes, if you count the 1.5 minutes presentation in, but don't worry, the fights last well into fourth episodes >:). Also, there are currently 4 Naruto movies: 3 for the first series and 1 for Shipp┼źden.

Basically, if you cross Inuyasha with Harry Potter you get Naruto. I guess that any media industry, once it reaches a level of maturity, makes compromises in order to satisfy the greater audience. What Hollywood did for the US, the anime companies are doing for Japan, but in the end, the result is the same: dumbed down versions of what it could be.

Fortunately, animes are often based on manga publications and you can read Naruto well over the story arch in the anime, freely online.

Links:
Naruto Wikipedia
Naruto Manga Online
Naruto meets Harry Potter video