Marzhill Musings

... Next>>

Transactions as a debugging tool

Published On: 2006-06-07 14:13:13
Have you ever wanted to test a long sql DDL script for syntax errors but didn't want to actually create your db structure yet? I've found the easiest way to do this is through the use of transactions. simply begin a transaction at the start of the script and roll it back at the end of the script. For example: 1 2 -- PostgreSQL DDL script 3 BEGIN; 4 -- begins our transaction block 5 CREATE TABLE test_tbl ( pk numeric NOT NULL, data varchar(128), ); 6 ROLLBACK; -- roll back everything this script just did 7 COMMIT; -- use this instead of ROLLBACK to commit the changes This has the benefit of allowing us to test the script for errors and yet not actually run it on the DB. The EXPLAIN command can do this also on some DB's but you would need it for every statement you wrote in the script and some statements will error out if you use EXPLAIN on them. I've found the Transaction method to work best for what I want to do.

Tags:

SQL Stupidity part II

Published On: 2005-09-07 21:31:35
I am working on a legacy web application right now that is giving me fits. I'd say about 90% or so of the application is done in SQL. Yes you got that right. The business logic is almost completely written in a huge number of stored procedures, sql functions, and scheduled database tasks. This makes tracking down the parts of your app you are trying to work on very difficult. Every time I turn around there is another stored procedure, function, or scheduled database task that needs tweaking. I'm starting to go a little crazy. The problem is it obfuscates what your application is really doing. You think a perl obfuscation contest produces difficult to follow code? They got nothing on this. I realize stored procedures were the cat's meow at the time but this is beyond all human decency. I have got to start refactoring this thing before it gets out of control.

Tags:

SQL stupidity

Published On: 2005-08-25 04:03:39
Let me let you in on a secret. SQL is a great language for getting information out of a database. But if your writing a long string of stored procedures that call functions which use a view that ties several tables together just to find out what particular piece of data is linked to your piece of data then you need a good talking too. Not only does all this unnecessary complexity make debugging hard for you. But it makes folks like me want to beat you up with a baseball bat. So do us all a favor. See if you can reduce the number of steps and keep the squirming pathway a little straighter. Then you can scream and rant and pummel the brains out of your fellow coders (who didn't heed this warning) with the rest of us.

Tags:
... Next>>