I spoke about HTML5, and left out some notes

Posted on 04/14/2011 by Eric Fickes

Tonight I spoke about the state of HTML5 and my thoughts about it’s future.  The overall message of the talk was to think of HTML5 as a tool rather than a Flash killer.  In time, HTML5 is expected to advance to a point where we can deliver “flash like” audio, video, and application experiences on any device with an HTML5 compliant browser.  Similar to XML when it gave us a common format to exchange data between different computer systems in 2000, HTML5 may be that tool to connect us across all devices.

In the now there is a great deal of energy by the major browser makers to implement HTML5.  However, it really feels like only the fun stuff is here right now ( audio, video, canvas ).  Chrome and Opera have plenty more impressive features, but it’s going to be be a while until HTML5 is that magical ubiquitous platform.  With all the major browsers getting monthly ( and sometimes semi-monthly ) updates, HTML5 is more than just all the rage.  It’s actually getting built!

Targeting Screens with HTML5, Flash & Native
View more presentations from Eric Fickes

And here are some of the notes that didn’t make it into the presentation, and information on the demos that did.

TOOLING

MOBILE FRAMEWORKS

Posted in adobe, conference, FLASH, flash platform, FLEX, HTML5 | Leave a comment

McDonalds’ Chicken Nuggets are NOT boneless

Posted on 03/11/2011 by Eric Fickes

UPDATE 3.24.11- The owner of the McDonalds called and sounded very sincere on the phone, while having no idea how his restaraunt was being run. We were offered more free food, but politely declined. McDonalds’ insurance has now served Tyson Foods Inc with a bone notice.

Tyson Foods Inc bone notice

Tyson Foods make McDonalds Chicken McNuggets, and some have bones

“The chicken mcnuggets already come packaged and prepared to our insured location and they are not altered in any way; therefore Tyson Foods Inc. may be liable for said incident.”

Wow, my image of McDonalds is going right down the tubes. They don’t actually make their food, they just heat it up and put it in a bag? I’m loving it!

—————————————————————————–

I’m not a litigious guy, but I feel like something needs to be said about McDonalds and the bones in their Chicken McNuggets. Tonight is Friday, so the wife and I took the kids to McDonalds so they could play with their friends on the playground. Tonight was mostly like every other Friday, except that Fletch received two surprises in his happy meal.

  1. A Superman action figure ( nicely packed in it’s own plastic bag )
  2. A chicken bone ( hidden inside of a nugget )

The chicken bone came as quite a shocker. Fletch was eating slow as usual and got really weird, then started spitting out his nugget onto the bench. At first I thought he had a cough and was trying not to choke. After I cleaned all the mush off the bench I found this CHICKEN BONE!!!

This bone is on a nugget only for size reference

Holding the chicken bone from Fletch's Chicken McNugget

The manager of this location showed zero sympathy that my three year old just bit into a chicken bone and said we have to fill out an incident report. Notice the form is folded in half. The manager gave my wife Jenn the form folded in half and said to fill out our name and phone number. He will fill out the remainder of the form. He also said this isn’t the first time this has happened, and that McDonalds will just recall the batch of chicken that our bone filled McNugget came from.

Jenn filling out the McDonalds incident report for finding a chicken bone in a Chicken McNugget

Something else I’d like to point out is McDonalds says on their website their chicken McNuggets are boneless.

McDonalds lies about their Chicken McNuggets being boneless. My son bit into a chicken bone in a Chicken McNugget 2 hours ago, they are not boneless

While most chicken McNuggets are probably boneless, I consider this a straight up lie. How can big corporations like McDonalds get away with bold face lies and nobody does anything about it? In the past I didn’t care because I knew their food was shit, so I didn’t eat there ( even though I did grow up on McDonalds ). Now that I’m a parent, my wife and I are no match for the marketing power of McDonalds, their toys, and their Playplaces.

So, that’s all I know to do right now, make some noise. McDonalds will continue selling their mostly boneless chicken McNuggets and silencing anybody who finds the golden nuggets with bones.


If you think I’m crazy, you can ask Google about bones in Chicken McNuggets. If you’re into Facebook, there is a group you can join called Mc Donalds chicken nuggets DO contain bones!!

Posted in denver, ramblings, Uncategorized | Tagged , , , , , , , , , | 11 Comments

Use INFORMATION_SCHEMA to find data about your database

Posted on 02/21/2011 by Eric Fickes

Have you ever been in this situation?  You’re the new developer for a database powered application and you have to figure out what tables contain a specific column?  Or maybe you’ve got a legacy database that needs it’s stored procedures and user defined functions cleaned up?  Or perhaps you’ve been assigned a unique database inventory task that requires you to report on the structure of your database, rather than the contents of your database.  Besides cracking open your favorite SQL editor and clicking through your database by hand, you might find the data you need from your database’s INFORMATION_SCHEMA System View.

All of the RDMS systems I’ve worked with have built in System Views ( or tables ) that contain data about the structure and contents of your database.  These views are there to help you with non data tasks.  For the sake of this post we’re only going mention the INFORMATION_SCHEMA, which is the system view I use most in SQL Server, and MySQL.  For your reference, here are links to INFORMATION_SCHEMA docs for four popular RDMS systems.

  1. SQL Server – http://msdn.microsoft.com/en-us/library/ms186778.aspx
  2. MySQL – http://dev.mysql.com/doc/refman/5.5/en/information-schema.html
  3. Oracle – http://download.oracle.com/docs/cd/E17952_01/refman-5.0-en/information-schema.html
  4. DB2 – http://www.tar.hu/sqlbible/sqlbible0100.html

If you work with SQL Server, you’ll be happy to know you can see all of the System Views in Management Studio by default.

SQL Server Management Studio shows all System Views

System Views in the AdventureWorks database

If you work with MySQL, you can see the INFORMATION_SCHEMA database in MySQL Workbench, but you’ll have to enable it in Preferences first.

Edit > Preferences > SQL Editor > Check 'Show Metadata Schemata'

Now for the MySQL users, turning on the Metadata Schemata is not required in order to query those system tables.  Assuming your user has appropriate permissions, you can always fire up Workbench and fire a query like this.

SELECT *
FROM INFORMATION_SCHEMA.Columns
WHERE Column_Name LIKE '%user%'

MySQL has INFORMATION_SCHEMA tables too!

Now that you know about INFORMATION_SCHEMA, you should be happy to know this is part of the SQL-92 standard. Which means whatever sql scripts you write in MySQL, will most likely work with SQL Server as well. So go ahead and start querying your database, you’ll probably find uses for the INFORMATION_SCHEMA in your daily life really quickly.

I’m going to assume you’ve got the general idea here, so here are a few sql scripts that I’ve used over the years.  The first three are informational queries, and the last three are stored procedures that generate .NET or Coldfusion code based off of a table name.

Find all tables with the column EmployeeID

SELECT	*
FROM	INFORMATION_SCHEMA.COLUMNS
WHERE	COLUMN_NAME LIKE 'EmployeeID'

Which tables have an EmployeeID column?

How many tables have the word employee in the name

SELECT	*
FROM	INFORMATION_SCHEMA.TABLES
WHERE	LOWER(TABLE_NAME) LIKE '%employee%'

How many %employee% tables are in the DB?

How many SPROCs and UDFs does our database contain?

SELECT	s.SPROCs, f.UDFs
FROM
(
	SELECT	COUNT(ROUTINE_NAME) AS 'SPROCs', NULL AS 'UDFs'
	FROM	INFORMATION_SCHEMA.ROUTINES
	WHERE	ROUTINE_TYPE = 'PROCEDURE'
) AS s,
(
	SELECT	NULL AS 'SPROCs', COUNT(ROUTINE_NAME) AS 'UDFs'
	FROM	INFORMATION_SCHEMA.ROUTINES
	WHERE	ROUTINE_TYPE = 'FUNCTION'
) AS f

How many stored procedures and user defined functions are in the DB?

The stored procedures listed below all accept an incoming table name, then reads data from INFORMATION_SCHEMA to generate code for the specified database table.  Instead of listing the raw SQL, I’m just showing a sample result and allowing you to download the raw SQL sprocs.

Generate C# ADO.NET to VO Stored Procedure

Build custom C# ADO.NET to custom VO fillerup code

download stored procedure


Generate .NET VO class Stored Procedure

Generate C# VO class for DB table name

download stored procedure

Generate Coldfusion CFFunction with CFQUERY Stored Procedure

Generate Coldfusion CFFunction with CFQUERY for specified table name

download stored procedure

I use these System Views almost daily, and there are a lot of scenarios where my job would take a lot longer to do if I didn’t know about this information.  The INFORMATION_SCHEMA also feeds my passion to build code that makes code, just love it.  Hopefully this nickel tour was enough information to help somebody out.

Posted in database, development, internets, SQL | Tagged , , , , , , , , , , , , , | 1 Comment

An aggregate may not appear in the set list of an UPDATE statement

Posted on 02/20/2011 by Eric Fickes

Ever seen the error “An aggregate may not appear in the set list of an UPDATE statement” when working with SQL Server?  I ran into this one recently after trying to put a COUNT in an UPDATE statement.  I was rewriting some legacy code to use a stored procedure, and it turned out to be the perfect case for a Temporary Table.

Instead of boring you with a work scenario, let’s take a simpler one that uses the AdventureWorks database.  This example will create a list of sales people, total order count for each person, and store this list a single table variable to be used as the final data table.

Should be three simple steps right?

1. Create @Table variable

DECLARE @SalesPeople TABLE
(
  EmployeeID int NOT NULL,
  SalesPersonID int NOT NULL,
  FullName varchar(200) NOT NULL,
  Title varchar(200) NOT NULL,
  sales_count int NULL default 0
)

2. INSERT sales people into @Table

-- HACKISH : Match SalesPersonID to EmployeeID, and fill @SalesPeople
INSERT INTO @SalesPeople
( EmployeeID, SalesPersonID, FullName, Title )
SELECT	e.EmployeeID, sp.SalesPersonID,
		c.FirstName + ' ' + c.LastName as FullName,
		e.Title
FROM	Sales.SalesPerson sp,
		HumanResources.Employee e,
		Person.Contact c
WHERE	sp.SalesPersonID = e.EmployeeID
AND		e.ContactID = c.ContactID

3. UPDATE @Table with COUNT

UPDATE	@SalesPeople
SET
	sales_count = COUNT( soh.SalesOrderID )
FROM	@SalesPeople sp, Sales.SalesOrderHeader soh
WHERE EXISTS (
	SELECT DISTINCT SalesPersonID FROM @SalesPeople WHERE SalesPersonID = soh.SalesPersonID
)
AND	sp.SalesPersonID = soh.SalesPersonID

Not COUNT allowed in an UPDATE SET statement

The third step is where the original error comes in, so let’s update this to four steps and see how a Table Variable gets through this.

1 & 2 – Repeat from above

3. Create Table Variable of order counts

SELECT	soh.SalesPersonID, COUNT( soh.SalesOrderID ) AS sales_count
INTO	#SalesOrderCounts
FROM	Sales.SalesOrderHeader soh
WHERE EXISTS (
	SELECT DISTINCT SalesPersonID FROM @SalesPeople WHERE SalesPersonID = soh.SalesPersonID
)
GROUP BY soh.SalesPersonID

4. Update @Table with order counts

UPDATE	@SalesPeople
SET		sales_count = tmp.sales_count
FROM	@SalesPeople sp, #SalesOrderCounts tmp
WHERE	sp.SalesPersonID = tmp.SalesPersonID

And here’s the full script from start to finish with the table variable in use.

-- Master table of sales people
DECLARE @SalesPeople TABLE
(
  EmployeeID int NOT NULL,
  SalesPersonID int NOT NULL,
  FullName varchar(200) NOT NULL,
  Title varchar(200) NOT NULL,
  sales_count int NULL default 0
)

-- Match SalesPersonID to EmployeeID, and fill @SalesPeople
INSERT INTO @SalesPeople
( EmployeeID, SalesPersonID, FullName, Title )
SELECT	e.EmployeeID, sp.SalesPersonID,
		c.FirstName + ' ' + c.LastName as FullName,
		e.Title
FROM	Sales.SalesPerson sp, HumanResources.Employee e, Person.Contact c
WHERE	sp.SalesPersonID = e.EmployeeID
AND		e.ContactID = c.ContactID

-- put sales counts into the other kind of #tableVariable
SELECT	soh.SalesPersonID, COUNT( soh.SalesOrderID ) AS sales_count
INTO	#SalesOrderCounts
FROM	Sales.SalesOrderHeader soh
WHERE EXISTS (
	SELECT DISTINCT SalesPersonID FROM @SalesPeople WHERE SalesPersonID = soh.SalesPersonID
)
GROUP BY soh.SalesPersonID

-- Update our master @table with data from #tableVariable
UPDATE	@SalesPeople
SET		sales_count = tmp.sales_count
FROM	@SalesPeople sp, #SalesOrderCounts tmp
WHERE	sp.SalesPersonID = tmp.SalesPersonID

-- dump the results
SELECT	FullName, Title, sales_count
FROM	@SalesPeople

-- cleanup
drop table #SalesOrderCounts

Posted in database, development, internets, microsoft, SQL, tips and tricks, tsql | Leave a comment