Ryan’s Tech Blog

I have a set of python scripts to help me manage a few SQL Servers at work, and one of the things I do is take database backups using BACKUP DATABASE and BACKUP LOG.  I’ve been using pymssql to connect, but today tried switching to pyodbc.  pymssql seems to be having momentum problems, so I figured I’d try the alternative and see how well it works.

I ran into two issues:

• pymssql supports “%s” as a parameter placeholders, and pyodbc wants “?”
• BACKUP and RESTORE operations on SQL Server don’t run like normal queries

The first was trivial to fix, the second took some digging.  If you try to run a BACKUP via pyodbc, the cursor.execute() call starts and finishes with no error, but the backup doesn’t get made.  With help from CubicWeb‘s post MS SQL Server Backuping gotcha, I learned that BACKUP and RESTOREs over ODBC trigger some kind of asynchronous / multiple result set mode.  To get around this, you can poll for file size (as Cubicweb did), but that gets ugly when making a backup on a remote server.

In a backup, I think each “X percent processed.” message is considered a different result set to ODBC. Instead of polling the file size, you can call cursor.nextset in a loop to get all the “percent processed” sets:

After adding that while loop, backups of small and medium sized databases worked like a charm.

For the last few years I’ve been learning and using Lisp more, and here is a disorganized, poorly-worded dump of how Lisp changed my opinion about other languages.

Static Types (as implemented by C# and Java) are oppressive.

I spend most of my time in C#, and it always feels like I’m toiling in the Type mines under the iron fist of compiler. I frequently run into problems where I can’t abstract common code because the .NET framework doesn’t use C#’s abstraction mechanisms enough (more detail). In Lisp it feels like types are a tool I can employ when I need it, but it’s rare that I need COERCE something from one type to another. Too much of my C# is devoted to casting to / from types, a hefty tax I pay to the compiler.

Syntax doesn’t have to be so hard.

I was recently working on a ruby script, and had to stop and think: “what does ruby want for ifs again? curly braces? indentation?” In Lisp it’s easy: it wants matching parentheses. You can add syntax if you find it useful (CL-INTERPOL is one of my common additions), but there’s nothing I need to remember, no need for cheat sheets. The simplicity also highlight the strangeness of things like python’s “pass” statement. There’s no human meaning to a pass statement, it’s just to handhold the parser, and I don’t think that should be one of my responsibilities.

Development tools don’t have to be such a pain in the ass.

In C#, I frequently end up waiting for Visual Studio, and that’s on a modern dual-core workstation with 2GB of RAM. In Lisp, I end up waiting once when I start slime/emacs, and once when I initially load a big system, and then everything else is pretty instant. That’s on my Asus EEEpc, a 900MHz Celeron with 512MB RAM. I could use cores to eliminate most of that start-up time. At work we have one fast server we all use to run our lisps, and if we didn’t have to run programs like Visual Studio, we could use low-power, low-cost workstations and be perfectly happy. I know some people think Emacs is a heavyweight program, but devenv.exe sets a new standard.

Variables don’t have to be so hard.

LET statements and lexical scoping in lisp are pretty basic, and meet all my needs. The scoping rules in other languages seem really overcomplicated by comparison. In C#, sometimes curly braces open a new scope, but sometimes not. Sometimes you have to declare variables away from their usage in order for them to be available in all the right scopes you want. In python / ruby, I’m frequently confused whether I’m declaring a new variable or using one from a higher scope. Most of that is from lack of ruby/python practice, but even when first learning Lisp, the rule was so simple that I never had trouble with it.