Stored Procedures (Do You Really Need Them?)

The other day I was debating the need/use for stored procedures in a modern MVC web application with a colleague of mine. His argument is that ORMs are “stupid” and the best way to separate data (model) from the application is to use a stored procedure. The stored procedure can be wrapped in a model type class which can be loaded by the controller. I have a few issues with this.

  1. You are coupling your application to a specific database. Even if you are writing standard ansi SQL. Which is completely compatible with any ansi compliant database you still have to export and import the procs if you want to change the database.
  2. You have increased your debugging complexity. If a problem occurs I now have to determine if it is in the stored proc or the program. I know that proper logging and error capturing can alert you to where the problem is occuring but you still have to address it in another layer besides the application
  3. Databases were designed and best suited for storing and managing data. Not performing logic on that data. That is why we have programs. IMHO

Now, having said this I do realize that in some cases stored procedures are completely warranted. Like when you have a rather complex series of queries based on frequently changing parameters and it would be best to compile it and store it in the database. My opinion is that “most” web applications shouldn’t need to use stored procedures to separate the data from the application.

Retrieving Out Params From a Stored Procedure With Python

I was hacking some python today which calls a stored procedure that sends back an out parameter to indicate whether or not it completed is task succesfully. Now, calling a stored procedure in python is pretty straight forward

1
cursor.callproc("StoredProcName", (param1, param2, etc..))

I needed to grab the out parameter in my code and execute some logic based on the response returned. I’m a python noob and suprisingly I didn’t find any thing that really gave a good example. I found a few posts on Stack Overflow like this and this. Which gave me some clues. Then I found a post that led me to the mysql-python documentation about cursor objects. When I read the following snippet it clicked for me

callproc(procname, args)

Calls stored procedure procname with the sequence of arguments in args. Returns the original arguments. Stored procedure support only works with MySQL-5.0 and newer.

Compatibility note: PEP-249 specifies that if there are OUT or INOUT parameters, the modified values are to be returned. This is not consistently possible with MySQL. Stored procedure arguments must be passed as server variables, and can only be returned with a SELECT statement. Since a stored procedure may return zero or more result sets, it is impossible for MySQLdb to determine if there are result sets to fetch before the modified parmeters are accessible.

The parameters are stored in the server as @procnamen, where n is the position of the parameter. I.e., if you cursor.callproc(‘foo’, (a, b, c)), the parameters will be accessible by a SELECT statement as @foo_0, @foo_1, and @_foo_2.

Compatibility note: It appears that the mere act of executing the CALL statement produces an empty result set, which appears after any result sets which might be generated by the stored procedure. Thus, you will always need to use nextset() to advance result sets

The key part for me was:

The parameters are stored in the server as @procnamen, where n is the position of the parameter. I.e., if you cursor.callproc(‘foo’, (a, b, c)), the parameters will be accessible by a SELECT statement as @foo_0, @foo_1, and @_foo_2.

So what I need to do is perform an cursor.execute on the server variable @_procname_n. The results are tuples so accessing their values should be as simple as result[0]. Here is what I came up with. This is acutal code from the project I’m working on so I know it works.

1
2
3
4
5
6
7
8
9
10
 cursor.callproc("DeleteUser", (user[0].rstrip(), out_error))

 # This is how we have to get the out params in python.  See PEP-249
 cursor.execute("select @_DeleteUser_1")
 result =  cursor.fetchall()

 if result[0]:  #in this case a non-null response denotes a problem "user profile inavlid"
    print "Not Found: ", user[0].rstrip(), "\t", user[1]
 else:
    print user[0].rstrip(), "\t", user[1]

If I need to get more out parameters I just do this:

1
2
3
4
cursor.execute("select @_DeleteUser_2")
cursor.execute("select @_DeleteUser_3")
#etc...
 result =  cursor.fetchall()

I’m sure there is a more elegant “pythonic” way to do this but its cool to figure it out yourself and its a great learning experience.

Drop-Dead Simple Rbenv Install on Ubuntu 12.10

For a while now I’ve wanted to give rbenv a whirl at managing my ruby installs.
I used rvm for a while and it was great, but I got tired of it messing around with my directories and paths. I’ve also been wanting to start a blog. I’ve heard lot of nice things about Octopress and thought I’d give it a try. Octopress uses ruby and sinatra so what better way to get started with rbenv than to use it install the latest ruby and install Octopress. The rest of the blog post focuses on installing rbenv. Installing Octopress is pretty simple as well head on over there and check out the docs.

Now installation of rbenv on a Mac is pretty simple. If you want to give it a try check out the installation docs on Sam Stephenson github account.
I develop on a mac and deploy to linux (ubuntu) servers. Since I’m lazy I first did a quick google and bam! I found this post by Parker J. Moore, also using Octopress BTW, that lays out the exact steps. I’m just parroting what he wrote:

1
2
3
4
5
6
7
8
9
10
11
12
13
sudo apt-get install zlib1g-dev openssl libopenssl-ruby1.9.1 libssl-dev libruby1.9.1 libreadline-dev git-core make make-doc
cd ~
git clone git://github.com/sstephenson/rbenv.git .rbenv
echo 'export PATH="$HOME/.rbenv/bin:$PATH"' >> ~/.bashrc
echo 'eval "$(rbenv init -)"' >> ~/.bashrc
exec $SHELL # Restart the shell
mkdir -p ~/.rbenv/plugins
cd ~/.rbenv/plugins
git clone git://github.com/sstephenson/ruby-build.git
git clone git://github.com/sstephenson/rbenv-gem-rehash.git
rbenv install 1.9.3-p362
rbenv rehash
rbenv global 1.9.3-p362

I entered the above commands on a Ubuntu 12.10 server exactly as you see them and had rbenv and ruby 1.9.3 installed in under 20 minutes. Sweet!`