Tetris in SSMS - Part 1

Part of my "hacker nature" is to find creative and unusual ways to do things.  This expands my creative thinking and teaches me deeper concepts about the tools and languages I use.  Today I had the idea of trying to implement the game of Tetris in SQL Server, specifically through the use of SSMS. Tetris is sort of my go-to game for a creative challenge, having made a Tetris clone in Excel once already.

It's not so hard as it may sound if you break it down.  The key components of a game are as follows:

• Output
• Input
• Logic
• Data Storage

Output, data storage, and logical processing are pretty straightforward in SQL Server.  The tricky part is input - as well as getting interactive output rather than just static results from a query.  I'll start by attacking the latter - a means of interactive and dynamic output.  Instead of using SQL-based output, I'm going to use text output, by using the "Results to Text" option in SSMS.  Then getting interactive output means using print statements to display the current state of the game.

How do you update the state of the game?  You can simply print out a bunch of blank lines to shift everything up, and then reprint the next state.  To get timer-based event loop, use the WAITFOR command to introduce a half-second or so delay between frames.  If you do this, you will find a problem - the output doesn't display immediately.  Even if you print a bunch of output, do a WAITFOR, print more output, WAITFOR again, and then finally some output and finish the query, you'll see that nothing displays until the entire query is finished processing.  There is a trick to flush the output buffer - by raising a level-0 error:

raiserror('',0,1) with nowait --to flush the buffer
waitfor delay '00:00:10'      --pause for 10 seconds

This basically solves the problem of output - create a continuous loop that prints out the board state and has a delay.  The downside is an ever-growing output buffer which may eventually crash SSMS once it reaches the limit of available memory.  You may have to restart the output cycle from time to time to prevent reaching this limit.

Getting input is quite a bit trickier.  I explored a number of different options, including running queries in a separate tab, and using macros.  The best solution I've come up with is to have an entirely separate SSMS window open for input (works best with multiple monitors), so that you can interact with the second session while leaving the output screen running constantly.  To get somewhat interactive input, I've opted to make use of the Query Shortcuts feature, found in Tools -> Options -> Environment -> Keyboard -> Query Shortcuts.  This allows you to define something like a macro, in the form of a SQL command that runs when you hit the predefined key CTRL+1 through 9.

The key inputs for Tetris are moving the piece left, right, and down, dropping the piece to the bottom, and rotating left and right.  This would require a total of 6 macros, which can be handled by 6 stored procedures to implement the logic.  If the game state is stored in a table which is shared by the two SSMS sessions, then the output will update live as the macros are executed.  All that remains is to implement the logic for displaying the game and processing the input.  I will leave that for Part 2.

Happy Programming!

What happens with Path System Variable is too long?

Nothing is more disconcerting than an error message when attempting to use basic Windows features.  I was greeted this morning by an error when trying to open SSMS 2012 after I just installed it: "Exception has been thrown by target of an invocation."  Googling the error didn't return much helpful information except to suggest that I needed to adjust my PATH system environment variable.  So I decided the next best thing would be to review my path variable.

In Windows 7, the standard way I get to environment variables is through the "Advanced System Settings" option in Computer -> Properties:

Imagine my surprise when I get this instead: "Windows cannot find '%windir%\system32\systempropertiesadvanced.exe'. Make sure you typed the name correctly, and then try again."  What!?  Did I corrupt my Windows 7 installation again and I have to start all over?  Thankfully, no.

Turns out, the root cause was my installation of SSMS 2012 put the length of the PATH variable just above the maximum limit.  When the PATH variable is too long, it stops resolving %PATH% properly, as well as any other environment variable.  I could test this by literally going to Command Prompt and typing "echo %PATH%."  The result coming back was "%PATH%" instead of expanding the environment variable as it normally would.

To get it working, I manually went directly to the path %windir%\system32\systempropertiesadvanced.exe, by navigating to c:\windows (%windir%) in Windows Explorer.  I could open the program from there, go to the PATH editor, and remove all the extra junk to make the variable a more manageable length.  Once I did that, my "Advanced System Settings" started working again, as did SSMS 2012.  Crisis averted!

Happy programming!