Is there a better way to debug SQL?

Question

I have been working with SQL for several years, primarily MySQL / PhpMyAdmin, but recently, Oracle / iSqlPlus and PL / SQL. I have programmed in PHP, Java, ActionScript and more. I understand that SQL is not a required programming language, like others, but why do error messages seem so much less specific to SQL? In other environments, I pointed directly to the root of the problem. Most often this is not the case, MySQL gives me errors like "error AROUND where u.id = ..." and prints the entire query. This is even more complicated with stored procedures, where debugging can be a complete nightmare.

Did I miss the magic tool / language / plugin / setup that gives better error reporting or do we stick to this? I need a debugger or language that gives me the same amount of control that Eclipse does when I set breakpoints and type code. Is it possible?

Answer 1

I think the answer is that SQL is a set-based language with several procedures attached. Because designers thought in terms of a set, they did not think that the usual type of debugging that exists in other languages ​​is important. However, I think some of them are changing. You can set breakpoints in SQL Server 2008. I didn’t actually use it, since you must have SQL Server 2008 databases before it works, and most of our data is still SQL Server 2000. But it is available and it allows you to go through things. You will still have problems when your select statement is 150 lines long and it knows that the syntax is incorrect, but it cannot indicate exactly where, since this is all one command.

Personally, when I write a long procedural SP package, I create a test mode that includes showing me the results of the things that I do, the values ​​of the key variables at certain points that interest me, and printing lines that let me know what steps were completed, and then collapse it all when done. This way, I can see what would happen if it worked for real, but did not damage any data in the database, if I am mistaken. I find this very useful. However, it can significantly increase the size of your program. I have a template that I use, which has most of the structure that I need to configure, so I don’t need to do too much time. Especially since I never add an insert. update or delete in proc without first checking the appropriate selection to make sure that I have the records that I want.

Answer 2

I think the explanation is that “regular” languages ​​have much smaller individual statements than SQL, so the granularity of one statement indicates a much smaller portion of the code in them than SQL. One SQL statement can be a page or longer; in other languages, this is usually one line.

I don’t think that this prevents debuggers / IDEs from identifying errors more accurately, but I suspect that this makes execution difficult.

Answer 3

I agree with your complaint.

Creating a good logging framework and using it in your sprocs is what works best for me.

Before and after each transaction or an important part of the logic, I write the name sproc, the timestamp of the step, and the line (if necessary) in the log table. I find that when I do this, I can usually narrow down the problem within a few minutes.

Add a debugging parameter to sproc (the default is "N") and pass it on to any other calls it calls so you can enable or disable logging.

Answer 4

As for breakpoints and code jumps, you can do it using MS SQL Server (in my opinion, this is easier in 2005 than in 2000).

For simple cases, early development debugging sometimes cryptic messages are usually good enough to resolve the error - a syntax error cannot make X with Y. If I'm in hard sproc, I will go back to "debug printf" in the sproc text because it is fast and just. After a while, with your database of choice, simple problems become an old hat, and you just take them aside.

However, once the code is released, the complexity of the problems is too high. I consider myself happy if I can reproduce them. Also, in those places where the developer in me wants a debugger, the database administrator in me says that "you do not put a debugger there."

Answer 5

I use the following tactics.

When writing a stored procedure, there is @procStep var every time a new logical step is executed set @procStep = "What ... happens here";

the rest is here