Merry Christmas! Have an XML parser :-)
So, after the entire Santa experience and after my kids left to be with their mother, I decided to pay a visit to the ghost of Source Code past. I dug up a little XML parser I wrote to accompany the Book of Sax. I was amazed that the code built and ran the first time (with several warnings that show how the Java language has changed in the past 10 years or so.) So, if you need a very small and pretty fast XML parser in Java, go to github and clone picoSax. If you go in and clean up all of the warnings, I’ll even send you a copy of the book!
Core data model migration on the iPhone.
Doing something as simple as adding a new attribute to an entity in your Core Data model will break your application when it comes to opening older persistent data stores. Core Data has some support for automatic migration, documented in the “Introduction to Core Data Model Versioning and Data Migration Programming Guide” (whew!) in the Lightweight Migration section. Unfortunately, there are crucial steps that are not mentioned in this section. Kudos to Grouchal on Stack Overflow for giving us the rest of the story.
The mother of all dynamic SQL Crosstab View Builders.
Ok, so maybe it’s not too poetic, but it’s descriptive. As I found myself writing a dynamic crosstab-query builder in MS SQL for probably the fifth time of my career, I decided I’d post it here to save you all just a little time in your day. Make sure you use the saved time for something I’d approve of (smoking a good cigar, playing a little poker, something like that).
This proc is designed for when you have a dynamic set of row-oriented data you want to pivot and show column-wise. It takes four parameters:
@tableName – Name of the source table (or view) that will be queried by the new crosstab view.
@excludeCols – A comma separated list of columns to exclude from the crosstab. In most cases, will be the ID column of the table.
@valueColName – Name of the column in the @tableName table that contains the value in a given row.
@targetViewName – Name of the view that the stored procedure will create. It will *automatically* drop this view before re-creating it, so be warned.
This proc also assumes that the @tableName table (or view) has a column called [colName]. It uses this value to create the corresponding columns across the top of the output view.
Good luck!
ALTER PROCEDURE BuildCrosstabView(
@tableName VARCHAR(100),
@excludeCols VARCHAR(1000),
@valueColName VARCHAR(1000),
@targetViewName VARCHAR(100)
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @cols VARCHAR(4000)
SET @cols = ''
EXEC ('SELECT DISTINCT colName INTO ##colList FROM ' + @tableName)
DECLARE col_cursor CURSOR FOR
SELECT colName
FROM ##colList
OPEN col_cursor
DECLARE @colName VARCHAR(100)
FETCH NEXT FROM col_cursor INTO @colName
WHILE @@FETCH_STATUS = 0
BEGIN
IF @cols <> ''
BEGIN
SET @cols = @cols + ', '
END
SET @cols = @cols + 'MAX(CASE colName WHEN ''' + @colName + ''' THEN ' + @valueColName + ' ELSE NULL END) AS [' + @colName + ']
'
FETCH NEXT FROM col_cursor INTO @colName
END
CLOSE col_cursor
DEALLOCATE col_cursor
DROP TABLE ##colList
DECLARE @sql VARCHAR(3000)
SET @sql = 'SELECT DISTINCT ' + @excludeCols + ', ' + @cols + ' FROM ' + @tableName + ' GROUP BY ' + @excludeCols
PRINT @sql
EXECUTE (@sql)
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[' + @targetViewName + ']'))
EXECUTE ('DROP VIEW ' + @targetViewName)
EXECUTE ('CREATE VIEW ' + @targetViewName + ' AS ' + @sql)
END
GO
posts feed