CSV Strings in SQL

by BillyTheKid August 18, 2010 06:35

I’m working on a webservice that will receive a comma separated list of ID’s and return XML and was concerned about the performance of the function I had been using in SQL to split a string into a table to join and select records.

I have been using a pretty basic iterative function that uses CHARINDEX and SUBSTRING, acceptable for small strings, but it was not going to work for this project.

Thankfully I found a CLR function in c# which has amazing performance. Credits to Adam Machanic over at SQLBlog. Find the code in his post here

http://sqlblog.com/blogs/adam_machanic/archive/2009/04/28/sqlclr-string-splitting-part-2-even-faster-even-more-scalable.aspx

To use his code, the first thing you will need to do is copy the class into a .cs file and compile it:

csc /target:exe sql_split_clr.cs

If the c sharp compiler is not in your path, add it. For SQL 2005 you want to use the framework version 2.

set path=C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727;%PATH%

CLR functions and procedures are disabled by default in SQL 2005. If you have not yet enabled it, use the following

sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

You will then need to create the assembly and functions in SQL like this:

CREATE ASSEMBLY SQL_Split
FROM 'c:\clr\sql_split_clr.dll'
GO

CREATE FUNCTION string_to_list(@list nvarchar(MAX),@delim nchar(1) = N',')
RETURNS TABLE (item nvarchar(4000))
AS EXTERNAL NAME SQL_Split.UserDefinedFunctions.SplitString_Multi
GO

Now the new CLR function should be ready to use

SELECT * FROM dbo.string_to_list('a,b,c,d,e',DEFAULT)

As a final aside, what’s with having to specify the DEFAULT keyword to use the default parameter value on functions. You don’t have to with stored procedures.

Tags: sql, c#, .net

Coding | English

Encoding H264 video for iPhone streaming with ffmpeg

by BillyTheKid May 21, 2010 05:43

The first thing you need to know is that to stream a video to the iPhone using progressive downloading you will need to use 2 pass encoding. There is something called the MOOV atom which needs to be at the beginning of the file, but can't be created until after the encoder has been through the entire source file at least once. Pres umably bitrates, metadata etc, need to be pre-calculated, but that's just a guess. If you come across something called qt-quickstart which will move the MOOV atom, avoid it, just take the time/cpu cycles to do a 2 pass encoding, the videos look better anyway.

Make sure you have a recent build of ffmpeg which has the ffpresets directory. I'm currently using Rev 18639 from Tripp's unofficial win32 builds. One thing to note, on Windows, you have to specify the full path to the preset file, in the -vpre parameter, and put it in double quotes, like in my example below.

On the first pass ffmpeg will create a log file in the current directory that it uses during the second pass. You will see examples on the internet for 2 pass encoding that have an output file for the first pass, but it is unnecessary, we only need the log file so you should specify NUL to save the hard drive I/O. On Linux you’d use /dev/null.

For this little test, I have downloaded a 60 second public domain newsreel clip from 1967 discussing the opening of the world expo held in Montreal that year. Head on over to the National Film Board (NFB) website to get any number of videos you can try converting. The NFB video was 720x480, but the iPhone supports only 640x480 video up to 1500k bitrate. In my example I’m scaling the video down to 640x428 and padding the top and bottom with 26 pixels.

Here is the command for pass 1:

ffmpeg -y -i Expo67.mpg -f ipod -pass 1 -vcodec libx264 -s 640x428 -padtop 26 -padbottom 26 -r 30000/1001 -vpre "D:\utils\ffmpeg\ffpresets\libx264-fastfirstpass.ffpreset" -vpre "D:\utils\ffmpeg\ffpresets\libx264-ipod640.ffpreset" -b 250k -bt 50k -bf 0 -an -threads 0 NUL

The b and bt parameters are bitrate and bitrate tolerance, which you might want to increase for your video. The –bf 0 parameter turns b-frames off, the iPhone does not support H.264 b-frames. Adding it here prevents an error when encoding the 2nd pass. The –an parameter turns audio off, which we won’t encode until the second pass.

Here is the command for pass 2:

ffmpeg -y -i Expo67.mpg -f ipod -pass 2 -vcodec libx264 -s 640x428 -padtop 26 -padbottom 26 -r 30000/1001 -vpre "D:\utils\ffmpeg\ffpresets\libx264-hq.ffpreset" -vpre "D:\utils\ffmpeg\ffpresets\libx264-ipod640.ffpreset" -b 250k -bt 50k -acodec libfaac -ab 56k -ac 2 -ar 22050 -async 1 -threads 0 Expo67.mp4

The trick here is to use two ffmpeg presets at the same time, fastfirstpass/ipod640 then hq/ipod640 for the second pass, always specifying the ipod640 preset second.

Here is a link to the Expo67.mp4 video, which should stream directly to your iPhone using progressive download.

Tags: ffmpeg, iphone, h264

Coding | English

Steve Wozniak at Concordia University

by BillyTheKid September 10, 2009 23:09

Today Sylvain and I went to Concordia University to see Steve Wozniak who was doing a question and answer in the big room on the main floor of the hall building. I received an email a few weeks back inviting students and alumni to see Woz, and being a student at Concordia (more on that later) I decided to register. Sylvain is an alumni so I asked him if he remembered his student number, he said yes, rhymed it off, so I forwarded him the email and he registered as well.

The Woz was there from 2-3 and we arrived a bit early and got a pretty good seat. Check out the pic, sorry about the poor quality, the lighting was dim and the iPhone wouldn't take a nice photo. Steve arrived on stage and proceeded to talk a little before taking questions. He described the environment in Silicon Valley in the late 70's and the course of events leading up to the creation of Apple. Most of us know the story but it is interesting to hear it told by Woz himself. Like many other super geeks, he discovered an aptitude and interest in technology at an early age. Oh, and he's a bit of an idealist. He talked about having designed the Apple I and having given the designs away at the computer club he attended. This inherent desire that programmers have to share is at the heart of why there is an open source movement today.

The audience was then allowed to ask questions and you can imagine with a group of university students, they were quite varied. Several of the questions were about Apple and the early years. Someone asked him about his accident in his Beechcraft airplane. The student who went and read Steve's book just for the occasion, easily spotted, they always start their question with "in your book ..." Other than that, patents, intellectual property, hacking/phreaking, and oh of course, someone had to ask him about dancing with the stars, which garnered a few laughs.

Anyway, it was kinda cool. I guess being a student has some benefits. Oh yes, the student thing. I have been taking a few courses part-time for fun and exercise for the brain. I was actually back at Concordia this evening for my ECON 201 class.

Tags: apple

English

iMac Surgery

by BillyTheKid September 08, 2009 09:02

So my brother came over the other day and said that his iMac (late 2006 core2duo) was not working. I asked him what was wrong with it when he says that a few weeks earlier it had started making some “clicky” sounds and being all “slow and weird”. Now when he turns it on, it just shows a picture of a folder with a question mark on it. I fairly quickly diagnosed it as a hard drive failure, at which time I asked him if he had a backup, which of course he did not. I guess I shouldn’t complain, if the hard drive dies in my personal machine, I probably wouldn’t have a current backup either. There is a suitable expression here somewhere, oh yes, “people who live in glass houses shouldn’t throw stones”.

Initially I think my brother was a bit worried about letting me open up his iMac, but after the local Apple store on St. Catherines street told him it would be $400 for the replacement hard drive and another $170 for installation he seemed more willing to let me do it. And what’s with that Apple store anyway, they wanted me to make an appointment to give me a quote for a hard drive, which I could only get after talking to a blue shirt person, who makes you put your name on a list with an orange shirt person first. Sure the stores are shiny, but what a horrible retail experience.

Anyway, I went to a local Microbytes store and picked up a 640GB Apple branded Western Digital hard drive for $79 and proceeded with the installation. They sure don’t make them easy to take apart. There are a few torx screws on the bottom which allows the top to come off, from the bottom pulling it off on a slight angle, being very careful of the connections to the built-in iSight camera at the top.

The next part was the most difficult which was getting the screen taken out. The screen is taped all around with a metallic tape that you need to slowly peel the lip back and pull off the sides of the screen to get it out. I unhooked 4 connectors from behind the screen on the left side and rested it against the foot. Now I was able to access the hard drive, which was relatively easy to change.

It took me over an hour to get the whole thing apart, change the drive and put it back together. It was definitely a better deal for my little brother than the Apple store. I didn’t charge him our (quite reasonable) hourly rate, and I even paid for the replacement hard drive.

If you happen to be in the same situation with hard drive issues, take a look around the net, you’ll find a few sites, and at least one youtube video showing you how.

Tags: apple

Hardware | English

News from the front

by BillyTheKid August 27, 2009 05:33

Well, I've finished upgrading our blog to use the newest version 1.5 of BlogEngine.net. So far so good, I managed to copy over all the content, the little custom widget I made, our template, plus the hacks Sly put in to the global.asax to use our language classes to switch the interface between english and french.

I might not have upgraded for a while but we started getting blog comment spam when we had been getting none before. It seems there has been an auto-poster tool developed to work against BlogEngine.net. There is a quick fix 4 lines of code added to CommentView.ascx.cs. For more information check out this blog post over here

While on the subject of BE.net, if you're a c# coder, even if you don't ever intend on running a blog, you should download the source for the BE.net project and check it out, it is very well written.

More news;

I'm writing this blog post from a Mac! Not that it's my first Apple computer, just not for a while now. Not since somebody broke into our old office and stole my iMac (and Sly's iBook). They didn't touch any of the Dell PC's, funny. Anyway, we are doing some iPhone development, and I got voted in to learn Objective-C. More on that later.

I was using Windows Live Writer to post to our blog; on the Mac it seems to be a battle between Mars Edit or Ecto for blog software. For me, Ecto seems the clear winner, simply because they have a plugin that supports BE.net features directly.

Funny, for years and years, the last computer I ever wanted to have was a Mac, but all that changed when Apple released OSX which was built on top of a Unix framework, which made it officially geek cool. I actually had the privilege of playing with a NeXT computer way back in the day when I was at the University of Toronto, so I had an appreciation for what was underlying the OS. Even the iPhone development kit still uses objects like NSString, inherited from NeXT, the NS meaning NeXT Step. Anyway, as you can see the Terminal app holds a place of honor in my dock right beside the finder icon.

Tags: .net, apple, blog, c#

English | Software | Technoponics

.Net remoting, interesting and frustrating

by BillyTheKid July 08, 2009 09:33

Well I've been testing out .Net remoting and so far it is pretty frustrating. There is very little good examples anywhere on the net, the error messages it generates are cryptic, and I think there are a few known framework bugs that are pretty easy to run into. Basically what I'm trying to build is a service on a machine that executes long running processes in a queue, specifically ffmpeg, mencoder, possibly flvtool2 or others.

My project has 3 parts, server, client, and shared interfaces library. There are two interfaces defined, one for the task service queue, the other a custom task object, and for the purposes of this example, I've kept only the taskId field on the object.

Initially I had exposed the custom task object using RegisterWellKnownServiceType and although I could add multiple tasks to the queue, they all seemed to be shallow copies of the same object, changing a property on one changed them all. I then decided to go with the factory pattern and added a CreateTask method to my task service which returned a full value object copy of an ITask.

One thing to note, when passing a complex object back and forth using remoting, you have to set the TypeFilterLevel to Full or you will get serialization errors. The complex object needs to be marked as [Serializable] and should NOT inherit from MarshalByRefObject, whereas the main task service absolutely must.

More...

Tags: c#, remoting, .net

Coding | English

Using FFMpeg with c#

by BillyTheKid April 08, 2009 07:14

Video is becoming more pervasive on the web and any of you that have been asked to work with video, have probably looked at FFMpeg. For those who don’t know, it’s a command line video (and audio) conversion tool, and it’s open source.

For this example, I’m going to write a small class in c# which will use the System.Diagnostics.Process class to run ffmpeg and parse the video size, duration and frame rate from the output. I’ll run ffmpeg like this “ffmpeg –i myvideo.avi” without specifying an output file and redirect the error stream to get the info I want. Here is what the output looks like on the command line.

To use the class, first we pass the file name of the video in the constructor:

Technoponics.Video.FFMpeg v = new Technoponics.Video.FFMpeg("myvideo.avi");

Then you call the CheckVideo method, which will then fill in the Width, Height, Fps and Duration public properties.

v.CheckVideo();
Console.WriteLine("Video Size: "+v.Width+"x"+v.Height);

Here is the code for the FFMpeg class (ffmpeg.cs file):

More...

Tags: c#, ffmpeg

Coding | English

Asterisk PBX

by BillyTheKid March 30, 2009 09:28

Well I have finally finished installing the new phone system at the office. We purchased a 4 port analog phone line card by Dialogic, and are using Asterisk for our phone system.

I went with an analog card so I could use standard old POTS phone lines. We had only 1 line on our main number before, and it was just easiest to get them to add a second line and cascade it from the main number. Probably many of you use BRI or T1 interfaces for your lines, Dialogic (or the others) have cards for your setup. Probably the main reason why I went with a Dialogic card over Sangoma or Digium was because I had an opportunity to purchase one for half price. Like everyone else, I first tried one of those twenty dollar wildcard fxo single port cards, which gave me nothing but major echo problems and headaches. If you are going to put together an Asterisk system, save yourself some trouble and buy a good card for your phone lines.

The Dialogic cards use a CAPI interface (the ISDN standard), even for their POTS analog cards. You need to have libcapi20 installed and the kernel sources to build the dialogic drivers as a kernel module. I installed a somewhat never version of libcapi than they mentioned in their instructions, libcapi20-3.0.5-cm.tar.gz, works fine, grab the latest from melware.org

I first tried a plain install of Asterisk on Ubuntu 8.0.4, created my own extensions.conf and started on my dialplan. Once I realized how much work that was going to be I tried FreePBX. After the 3rd time I was reinstalling the box, getting Apache, MySQL and everything running for FreePBX and Asterisk, I decided to give Trixbox a try. It’s a CentOS distribution with Asterisk and FreePBX already setup and configured. Although I’d never used CentOS before, it is based on Red Hat and is pretty easy to find your way around. Actually I haven’t used Red Hat since around version 7.2. I’m using Trixbox 2.6.2, which has Asterisk 1.4 integrated.

I used “yum” to install a few extra thing needed to get the Dialogic drivers to compile and install. Use “yum install [packagename]”. I needed to install kernel-devel, gcc, gcc-c++, ncurses-devel and asterisk-devel.

Once installed FreePBX is pretty easy to add extensions and configure your phone system. We have a couple of Linksys SPA-942 telephones, but I hate them and would not recommend them. I am using a cheap $9 GE telephone and grandstream ATA (analog telephone adapter) on my desk and get better sound quality.

To get my Dialogic card to work in FreePBX all I had to do was add a custom trunk with the text DIALOGICDIVA/contr1/$OUTNUM$/b. contr1 is the first controller in my system, $OUTNUM$ is a variable FreePBX will replace in the dial plans, and the lower case B is for early B3 connects. See dialogic instructions for options. To receive calls from the Dialogic adapter, the most important thing to set is the call context in the dialogicdiva.conf file for your installation. I used the context “from-trunk” which will get FreePBX to route it correctly when it comes in.

For now, rather than when you dial instead of having an IVR (… if you know your party’s extension …), we created a ring/pickup group and set everybody’s phone to ring when a call comes in from the outside. The first person to answer their phone picks up the call and says hi, and then can transfer the call to another desk if needed. And with caller id, we usually know who the call is for anyway.

Dialogic Diva Analog 4P Card

Trixbox CE (Community Edition) Download Page

Tags: asterisk, linux

Coding | English

BlogEngine.Net custom control

by BillyTheKid March 11, 2009 18:45

Well, Sly installed BlogEngine.Net and he was pretty impressed with it so I though I would take a stab at creating a custom theme and copying the new skin from our site to see if I could get it to work. Long story short it was pretty easy and only took me a few hours to do.

Most of the BlogEngine themes had a top nav with home,archive,subscribe,login/logout. I wanted to put those links into a box in the right column and have our sites nav there. Not satisfied with just putting bit of HTML in the master file for the theme, I decided to make my own custom control for it.

More...

Tags: blog, c#

Coding | English

CSV Strings in SQL

More on Videos and HTML 5

Encoding H264 video for iPhone streaming with ffmpeg

Steve Wozniak at Concordia University

iMac Surgery

News from the front

.Net remoting, interesting and frustrating

Using FFMpeg with c#

Asterisk PBX

BlogEngine.Net custom control

Blog Menu

Technoponics

Blog Menu

Technoponics

Categories

Tags