Voice of the DBA

Author: way0utwest

  • Parsing SQL Saturday Data – Looping Through And Loading All XML Files

    After my last post on parsing the XML, I decided to continue forward and get ready to put the data in a database. For that, I’m really looking for this data:

    • event ID
    • session title

    With this, I can easily insert data into a table. I’ll have separate tables for the events themselves and the speakers, but for now, I can easily showcase the titles of the sessions.

    With that in mind, I decided to start expanding my efforts and building a series of loops that get all the data from the XML documents.

    Looping through all files

    The first thing I needed to do was loop through all the files I’d downloaded and get the documents loaded. I decided to use a DO loop for this, since I should be doing this at least once each time. Eventually I’ll add logic to avoid downloading files I’ve downloaded already.

    Here’s the basic code:

    $loop = 1

    $loopend = 450
    $doc = New-Object System.Xml.XmlDocument

    do {
    #start large loop

      # get the filename
      $sourceURL = $baseURL + $i + ".xml"

      # do other stuff

      $i++

    #end outer loop
    } while ($i -lt $loopend)

    This is the basis for looping through all the file names, based on my downloads. A quick test shows this is building all the filenames I need.

    Loading files

    The next step is to actually load each XML file in and start querying it. I changed from the parsing code to use a loop since I’ll need to insert each item separately and I don’t think the code I had from the previous article will work. At least, I haven’t found a way.

    If you know of one, let me know.

    I used the Test-Path method to be sure that the XML exists, as there was at least one lost event in my initial download. I think that’s fixed now, but in any case, I added this code:

    #test the path first. If it exists, load the XML
    if (Test-Path $sourceURL) {
      $doc.Load($sourceURL

    #trap the event number. This will be the ID I use in the database table.
        $event = "SQL Saturday #" + $i

    That seems to work fine, and with with $event variable, I know which event the sessions are associated with.

    Next Steps

    That’s all I wanted to put here, giving me a nice, simple way of going through a series of files in a pattern. From here I’ll add more detail to the inner loop that gets the session titles out of the XML document and displays it.

  • The Vacation Conundrum

    Would I want unlimited time off? It’s an interesting question. I think I might like something more like a minimum time off, or perhaps an allowance, with the tracking and offer to work with employees that need more. There are problems with unlimited vacation, and it’s often because of poor management and social pressure of colleagues.

    Or, of course, workaholic syndrome, which is common in the technology industry.

    Really, I’ve never had an issue with technology, personally. I work mostly the stated hours, but because I’m productive, I never have had problems with managers if I come late or leave early. To be fair, that’s my experience, and if everyone acted as I did, perhaps that would be problematic.

    At Red Gate, I’ve been able to take days off for something, usually skiing, as I want. Actually, I usually only take a partial day off, and have to work part of the day to be sure the SQLServerCentral newsletter is prepped and going out. Or answering email, dealing with site issues, etc. I’ve even taken meetings on the road, or halfway to the mountains, pulling over to chat.

    The last few years I’ve not wanted to deal with that and typically book a day off in our HR system and plan to be away. If things break, I ignore them. Or someone else needs to be ready to handle them since I’ve taken vacation and I’m not going to be pushed into working when I have booked time off.

    The downside of this, which is more specific to my job, is that I have daily things that need handling, like the SSC site. As a result, if I take a day off, that usually means I’m putting in more time before the holiday to prep things. I’ve never done that as a DBA or developer. I had deadlines, but I didn’t work extra because of vacation, unless I had put myself behind.

    If someone else sends an unrealistic schedule, I’m not killing myself, or skipping vacation.

    It’s a tough question, and I need to think more about it. The pieces linked above are interesting and worth reading.

  • SQL Injection, Still?

    It seems as though SQL Injection issues are still around. Attacks from SQL Injection are on the uptick as we begin moving through 2015. As noted in that piece, the constraints put on software developers usually mean that testing and security are the first things to let go when time becomes short. That’s sad, and in some sense, I think this means that we aren’t teaching secure coding early enough to developers, and certainly not often enough.

    I ran across a piece from Kevin Kline that asks why this keeps happening. After all, as Kevin shows, much SQL Injection is easy to prevent. The coding patterns and tools we use are simple to write. There are lots of articles out there that show a variety of techniques you can cut and paste into your code. However there are two big problems that prevent us from eradicating SQL Injection: aging code and bad habits.

    There’s no shortage of code that comes from frameworks and application templates, not to mention naive or ignorant developers. Lots of this code is vulnerable to SQL Injection. Since so many of these existing applications work, there is no great pressure to go back and change them to be more secure. Since data theft may not even be noticed, there are plenty of companies (and technologists) that have no idea their systems are vulnerable.

    The other problem is bad habits. Far too many developers and DBAs have spent years writing insecure code. When they prototype, mock, or quickly knock out code, they often rely on their experience to get work done quickly. And they do this poorly if they aren’t writing in a pattern that prevents SQL Injection. They haven’t updated their templates, tools, or their knowledge in a way that ensures all their future code will be secure.

    Ultimately we as an industry need to take SQL Injection seriously and write better code. Whether you use an ORM, a framework, or anything other shortcut to build applications, if you don’t create secure software, you’re part of the problem.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.3MB) podcast or subscribe to the feed at iTunes and LibSyn. feed

  • Salaries and Experience

    This editorial was originally published on Aug 31, 2010. It is being re-run as Steve is on vacation.

    One of the main reasons that I got into IT was the salary. Honestly, there were relatively few places where I thought I had a skill AND could make good money. I graduated with an economics degree, with the idea that I’d go to Wall Street, follow Gordon Gecko, and work 140 hours a week and make a few million.

    That was until I wrecked my shoulder playing rugby my senior year, realized that I wasn’t invincible, and decided there were more important things than my salary. However I still wanted to have a good living, I had some skills in technology, and I moved in that direction. When I heard that our DBA was making $90k a year at a time when I was making $45k, I decided I should learn more about databases.

    Across the last two decades of my career, I think I’ve been fortunate in my career. It’s been a lot of work, , and a lot of fun. I’ve tried to grow my career, learn new things, and find better opportunities over time and in new positions. I’ve had success, and I’ve found the experience has resulted in higher salaries for me. I founda blog about salaries recently that says it’s not only experience, but also location that matters in terms of salary. That’s probably true, though I think industry, the particular company, and other factors are important as well.

    For most of us, we don’t necessarily want to change locations, but we can do something about our experience. I’ve seen it written before that for people that have 10 years of experience, it could be gained two ways. They could have one year of experience ten times, or ten different years of experience (or some combination). The point is that you should be continually expanding on your experiences, not just relying on skills you learned a long time ago.

    You may or may not be able to get a job that pays you more with more experience. I’d like to think, however, that any investment in improving your skills and learning more about your craft, is never wasted.

    Steve Jones