Meaning of Lif

SQL XML SPID Settings

I found some nifty uses of xQuery and xPath expressions in SQL recently, but I also ran into some errors promoting to a server with some SPID settings. Turns out there's a page on MSDN with the exact settings that need to be set in order for things like xml.query() and xml.nodes() to work.

ANSI_NULLS ON
ANSI_PADDING ON
ANSI_WARNINGS ON
ARITHABORT ON
CONCAT_NULL_YIELDS_NULL ON
NUMERIC_ROUNDABORT OFF
QUOTED_IDENTIFIER ON

http://msdn.microsoft.com/en-us/library/ms188285%28SQL.105%29.aspx

Replace tags in a string

I ran into a situation today where a client wants to be able to give us a string for a disclaimer, but they want to be able to add tags to the string which, after we work our magic, will be replaced with a value from a table, making the string somewhat dynamic in nature. Here's how I went about doing it.

First, I made a function to house the logic around the tags. I realize there are a few shortcomings here and currently, this means that you can't have a '<' in the text without it being the start of a full tag ending in '>', but there are a few adaptations that I feel would be easy enough to implement if this became problematic. The code is a modified version of a comma-delimited string parsing function I use a lot. Basically what this part does is gets the charindex of the opening and closing brackets and then length between the two. It cuts out the substring between them based on that length and pops it into a table. After it does that, it cuts off everything to the left of that closing bracket (or to be more accurate, it keeps the right side). This repeats until you have a table filled with the contents of all the bracketed terms in the string.



IF OBJECT_ID (N'dbo.TagParse') IS NOT NULL
DROP FUNCTION dbo.TagParse
GO

CREATE FUNCTION dbo.TagParse(@String varchar(max))
RETURNS @TagValues TABLE
(
ident int identity(1,1) primary key clustered,
TagValue varchar(100)
)
AS
begin

declare
@FragText varchar(255),
@OpenIndex int,
@CloseIndex int,
@FragLength int,
@StringLength int

--Get some relevant information about the frament length and position
select
@OpenIndex = CHARINDEX('<', @String) ,
@CloseIndex = CHARINDEX('>', @String),
@StringLength = datalength(@String)
select
@FragLength = @CloseIndex - @OpenIndex

while @CloseIndex > 0
begin

select @FragText = SUBSTRING(@String, @OpenIndex + 1, @FragLength -1)
select @String = RIGHT(@String, @StringLength - @CloseIndex)

insert into @TagValues(TagValue)
select @FragText

select
@OpenIndex = CHARINDEX('<', @String) ,
@CloseIndex = CHARINDEX('>', @String),
@StringLength = datalength(@String)
select
@FragLength = @CloseIndex - @OpenIndex

end

return
end


Once we've got the tags parsed out, we can get into getting those values from a presentation table. For the purposes of what I had to do, it will always come from the same table, but I suppose if one were so inclined, you could do some funky dynamic sql to be able to specify a table name too, but I won't go there (right now). I've built up some basic sample data so you can see this in action, but the idea behind it is to:

1) parse out the string using the above function
2) set a variable = the fact name parsed from the string
3) retrieve the value from the presentation table based on the fact name from step 2

Then, much to my dismay, using a loop, I go through the parsed table and replace instances of the tag with the values I just set. If there's a set based way to do this, I'd love to know it, but I couldn't think of a way that didn't involve dynamic sql and lots of nested replace statements. And given that these tagged values are probably not ever going to be more than a few tags here and there, the strain of RBAR is not too bad.


if OBJECT_ID('tempdb.dbo.#FactSet') > 0 drop table #FactSet
create table #FactSet
(
FactName varchar(100),
FactValue varchar(100)
)

insert into #FactSet (FactName, FactValue)
select 'FactName1', 'Test Value 1' union all
select 'FactName2', '3.14'

declare @Tags table
(
ident tinyint primary key clustered,
TagValue varchar(100)
)

declare
@String varchar(max),
@ident tinyint,
@MaxIdent tinyint,
@FactName varchar(255),
@FactValue varchar(255)

--This is the string that will constitute the disclaimer text they want to chop up
select @String = 'This is a test string. Field 1: , Field 2: '

--Parse the string into its constituent tags
insert into @Tags (ident, TagValue)
select ident, TagValue
from sysmon.dbo.TagParse(@String)

--Get the max ident through @@rowcount so I dont have to actually hit the @Tags table
select
@Ident = 1,
@MaxIdent = @@rowcount

while @ident <= @MaxIdent
begin
--Get the name of the fact name we parsed and assign it to @FactName
select @FactName = TagValue
from @Tags
where ident = @ident

--Get the value from the Fact Set table which corresponds to @FactName
select @FactValue = FactValue
from #FactSet
where FactName = @FactName

--We then replace any instances of the original tag with the value we just got.
--Enclosing the @FactName in <> to simulate the original tag syntax.
select @String = REPLACE(@String, '<' + @FactName + '>', @FactValue)

select @ident = @ident + 1

end

select @string


If anyone who reads this understands what the hell I just did, I'd be happy to hear any ways you think this could be done better.

Number 4

Line-work on my latest piece. P.S. the sternum hurts like a bitch.

MW3

So Modern Warfare 3 came out recently, but I didn't buy it. Something is kind of wrong with online gaming with fps games. It was one thing when these people had to slam their carpel-tunnel ridden little fists into the keyboard to insult you, but now we've got Teamspeak. I'm not so much a fan of getting called a faggot by a 13 year old. If I wanted that, I'd just give an anti-drug lecture at a middle school.

Software Developer Lifecycle

Found a good succinct article about career progression in programming that I thought I'd re-share:

http://www.aaronlowe.net/archive/2011/10/do-you-want-to-be-the-best/

Year 1 – Focus on Reliability/Standardization
Year 2 – Focus on Optimization
Year 3 – Focus on Automation
Year 4 – Focus on lolcats (ok so Kevin didn’t specifically say lolcats)

Is it legal?

After several years not DJing, I decided to get a little DJ mixing console and some software to make some new mixes and play around with making music again. I unpacked my Numark Mixtrack Pro, threw on some Pendulum and Shock One, and started recording. It sounded pretty good. So up it went to Soundcloud. Then I got this email:

It said my mix "...may contain content that is owned or licensed by Warner Music UK (Pendulum, Witchcraft).
As a result, we have paused the upload of your audio for the time being."

Hrm, ok. Maybe it's not going up. But what is legal anyway? At any given time you can find tons of free mixes online, rippable from youtube or from any of 1000 other source on the internet, but I can't post a 20 minute mix on Soundcloud? I found a few good articles, but to summarize it, any webcast or live performance you technically need all the rights from the labels to play their tracks (whether you're being paid or not). Oh well. I guess I just won't go pro with this.