Monthly Archives: March 2006

Writing for Engadget

I saw a posting on Engadget today, they’re looking for writers, or a writer, not sure. In any case I sent the link to Tom, who wasn’t interested, "Not my type of writing." I wasn’t sure I was interested either. i love reading engadget, even when it’s about something I couldn’t care less about, the writing is witty and creative, exactly my type of writing. so what the hell? Why not. So I just clicked send on my "application"



We’ll see what happens.

For XML Explicit gotchas

Free asked me something today which gave me the idea it put my answer in writing so I don’t forget it.



He was working on one of my procs and it was returning an error, that tag ID 2 was not open for use and had to be opened first.



I saw that enough to remember the culprit, or at least the common culprit for me. For XML Explicit needs the query output to be very specific in its ordering. Very very specific. If a single column is out of whack you will likely have troubles.



What I found was helpful (from a blog I didn’t take note of I’m sure) is to remove

FOR XML EXPLICIT



which will make your Stored Proc of query return a standard recordset. This way you can see the output and see your result rows. Each row needs to be output in numeric order basically. If row 1 has tag 1 and row 2 has tag 3, row 3 can’t have tag 2. Bad Juju ensues.



This little trick saved me so much greif.

SQL and XML FOR EXPLICIT. oh, my head!

The last few days I’ve been working on some crazy SQL. The project will be storing an xml representation of data (let’s just say it’s a resume), in one of it’s columns. The data spans a bunch of tables with lookups and such. Version 1 of the script ended up being 2251 lines long and just a wee bit of a pain in the arse to manage.



We paired it down into smaller stored proc chunks, which not only made managing the beast easier but made it way more flexible.



We decided to put the heavy lifting on SQL server 2k5. THe data is already there so why bring it down to CF just to put it back up into the DB?



Using SQL servers XML functions is somewhere between easy and hard. I suppose we started with the hardest approach, "For Explicit".



The jist is this. For Explicit puts all the control on the developer. Auto and Raw will generate XML based on how the data comes out of the query. For Explicit on the other hand, puts it all squarely in the hands of the developer. Writing an XML file is easy. Conceptualizing how it should look in SQL script in the form of a query, not so much.



Basically you use your first query to outline the layout of the xml file. all subsequent queries populate different nodes. Each query is unioned to the first. This is why it gets hairy. YOu have a first query with 30 or more fields, each following query has to have the same number of columns, some NULL, some not depending on where in the XML you are. In my example that meant the same basic query over 20 times, with about 25 or so columns. Here’s the first query.


set @xmldata = (

select *

from (

SELECT

    1                        as Tag,

    NULL                    as Parent,

    cr.ID                    as [ResumeCore!1!ID],

    cr.GUID                    as [ResumeCore!1!GUID],

    cr.CandidateResumeTypeID    as [ResumeCore!1!typeID],

    cr.ResumeName            as [ResumeCore!1!Name],

    NULL                    as [resume_content!2!!ELEMENT],

    NULL                    as [resume_contact_info!3!ID],

    NULL                    as [resume_contact_info!3!GUID],

    NULL                    as [resume_contact_info!3!resFirstName!cdata],

    NULL                    as [resume_contact_info!3!resMiddleName!cdata],

    NULL                    as [resume_contact_info!3!resLastName!cdata],

    NULL                    as [resume_contact_info!3!resProfessionalTitle!cdata],

    NULL                    as [resAddress!4!ID],

    NULL                    as [resAddress!4!GUID],

    NULL                    as [resAddress!4!res_address_addressLine1!cdata],

    NULL                    as [resAddress!4!res_address_addressLine2!cdata],

    NULL                    as [resAddress!4!res_address_addressCity!cdata],

    NULL                    as [resAddress!4!res_address_addressState!cdata],

    NULL                    as [resAddress!4!res_address_addressCountry!cdata],

    NULL                    as [resAddress!4!res_address_addressPostalCode!cdata],

    NULL                    as [resContacts!5!!ELEMENT],

    NULL                    as [resContact!6!ID],

    NULL                    as [resContact!6!GUID],

    NULL                    as [resContact!6!TypeID],

    NULL                    as [resContact!6!!cdata]

from candidateResume cr

    inner join candidateResumeType rt on rt.id = cr.candidateresumeTypeID

where cr.id = @resumeID AND cr.active = @active and cr.deleted = 0

union all





As you can see the column name is weird. THat’s what controls the output. Imagine this over and over. Each column having to line up.



One thing I learned later. SQL ignores column names on all succeeding queries. Only the first one is used for laying out the XML output.



After about three straight days of nothing but SQL I consider myself a little bit more "senior" in my SQL skillset.

What if…. Google was evil?


I was sitting at my desk, checking my Gmail (do we even call it Email any more?). I noticed that the program I use to store data in Gmail, Gdisk actually makes a message and stores it as a draft. It’s rumored Google is preparing to offer their own Gdisk product.



On top of that Google just bought Writely, meaning (it’s assumed) they plan to offer an online office suite of some kind.



Don’t forget the google Pack. they’re making it even easier to surrender your computer to Google. I know I run the Google desktop, you probably are too or know some one who is.



I’ve got Google Earth on my Dell and the iBook.



G talk keeps all of our conversations for "our convenience" and makes them searchable.



So now let’s assume all these great, free apps are part of a master stroke. What if, "don’t be evil" is a cover for, "be really evil, once everyone’s guard is down"?



What would their end game be? Steal all our data? Ransom? use data we thought was private for blackmail?



Could we stop them? Would we? What would we do if we did? They’re either buying up the competition or killing it off.