I have worked with SQL for several years now, primarily MySQL/PhpMyAdmin, but also Oracle/iSqlPlus and PL/SQL lately. I have programmed in PHP, Java, ActionScript and more. I realise SQL isn't an imperative programming language like the others - but why do the error messages seem so much less specific in SQL? In other environments I'm pointed straight to the root of the problem. More often that not, MySQL gives me errors like "error AROUND where u.id = ..." and prints the whole query. This is even more difficult with stored procedures, where debugging can be a complete nightmare.
我已经使用SQL几年了,主要是MySQL / PhpMyAdmin,最近还有Oracle / iSqlPlus和PL / SQL。我已用PHP,Java,ActionScript等编程。我意识到SQL不是像其他语言那样的命令式编程语言 - 但为什么错误消息在SQL中看起来不太具体?在其他环境中,我直接指出了问题的根源。更常见的情况是,MySQL给了我错误,如“错误AROUND where u.id = ...”并打印整个查询。对于存储过程来说,这更加困难,因为调试可能是一场彻头彻尾的噩梦。
Am I missing a magic tool/language/plugin/setting that gives better error reporting or are we stuck with this? I want a debugger or language which gives me the same amount of control that Eclipse gives me when setting breakpoints and stepping trough the code. Is this possible?
我错过了一个神奇的工具/语言/插件/设置,可以提供更好的错误报告,还是我们坚持这个?我想要一个调试器或语言,它在设置断点和踩代码时给了我与Eclipse相同的控制权。这可能吗?
5 个解决方案
#1
6
I think the answer lies in the fact that SQL is a set-based language with a few procedural things attached. Since the designers were thinking in set-based terms, they didn't think that the ordinary type of debugging that other languages have is important. However, I think some of this is changing. You can set breakpoints in SQL Server 2008. I haven't used it really as you must have SQL Server 2008 databases before it will work and most of ours are still SQL Server 2000. But it is available and it does allow you to step through things. You still are going to have problems when your select statement is 150 lines long and it knows that the syntax isn't right but it can't point out exactly where as it is all one command.
我认为答案在于SQL是一种基于集合的语言,附带了一些程序性的东西。由于设计师正在考虑基于集合的术语,他们并不认为其他语言的普通调试类型很重要。但是,我认为其中一些正在改变。您可以在SQL Server 2008中设置断点。我没有真正使用它,因为您必须拥有SQL Server 2008数据库才能工作,而我们的大多数仍然是SQL Server 2000.但它可用,它确实允许您单步执行的东西。当select语句长度为150行时,你仍然会遇到问题,并且它知道语法不正确,但是它无法准确指出所有命令的确切位置。
Personally when I am writing a long procedural SP, I build in a test mode that includes showing me the results of things I do, the values of key variables at specific points I'm interested in, and print staments that let me know what steps have been completed and then rolling the whole thing back when done. That way I can see what would have happened if it had run for real, but not have hurt any of the data in the database if I got it wrong. I find this very useful. It can vastly increase the size of your proc though. I have a template I use that has most of the structure I need set up in it, so it doesn't really take me too long to do. Especially since I never add an insert. update or delete to a proc without first testing the associated select to ensure I have the records I want.
就个人而言,当我编写一个长程序SP时,我构建了一个测试模式,其中包括向我展示我所做的事情的结果,我感兴趣的特定点的关键变量的值,以及让我知道哪些步骤的打印标记已经完成,然后完成后将整个事情卷回来。这样我可以看到如果它运行真实会发生什么,但如果我弄错了,就不会伤害数据库中的任何数据。我发现这非常有用。它可以大大增加你的触发器的大小。我有一个我使用的模板,其中包含我需要设置的大部分结构,所以它并不需要我太长时间。特别是因为我从不添加插入物。更新或删除到proc而不先测试相关的选择,以确保我有我想要的记录。
#2
4
I think the explanation is that "regular" languages have much smaller individual statements than SQL, so that single-statement granularity points to a much smaller part of the code in them than in SQL. A single SQL statement can be a page or more in length; in other languages it's usually a single line.
我认为解释是“常规”语言的单个语句比SQL小得多,因此单语句粒度指向其中的代码比SQL中的小得多。单个SQL语句的长度可以是一页或更多;在其他语言中,它通常是一行。
I don't think that makes it impossible for debuggers / IDEs to more precisely identify errors, but I suspect it makes it harder.
我不认为这使得调试器/ IDE无法更精确地识别错误,但我怀疑这会让它变得更难。
#3
1
I agree with your complaint.
我同意你的投诉。
Building a good logging framework and overusing it in your sprocs is what works best for me.
建立一个良好的日志框架并在你的sprocs中过度使用它对我来说最有效。
Before and after every transaction or important piece of logic, I write out the sproc name, step timestamp and a rowcount (if relevant) to my log table. I find that when I have done this, I can usually narrow down the problem spot within a few minutes.
在每个事务或重要逻辑之前和之后,我将sproc名称,步时间戳和rowcount(如果相关)写出到我的日志表中。我发现当我这样做时,我通常可以在几分钟内缩小问题点。
Add a debug parameter to the sproc (default to "N") and pass it through to any other sprocs that it calls so that you can easily turn logging on or off.
将调试参数添加到sproc(默认为“N”)并将其传递给它调用的任何其他sprocs,以便您可以轻松打开或关闭日志记录。
#4
1
As for breakpoints and stepping through code, you can do this with MS SQL Server (in my opinion, it's easier on 2005+ than with 2000).
至于断点和单步执行代码,您可以使用MS SQL Server执行此操作(在我看来,它在2005年以上比在2000年更容易)。
For the simple cases, early development debugging, the sometimes cryptic messages are usually good enough to get the error resolved -- syntax error, can't do X with Y. If I'm in a tough sproc, I'll revert to "printf debugging" on the sproc text because it's quick and easy. After a while with your database of choice, the simple issues become old hat and you just take them in stride.
对于简单的情况,早期的开发调试,有时神秘的消息通常足以让错误得到解决 - 语法错误,不能用Y做X.如果我在一个棘手的sproc,我将恢复到“关于sproc文本的printf debugging“因为它快速而简单。在选择了您的数据库一段时间之后,简单的问题就变成了旧问题,您只需要大步迈进。
However, once the code is released, the complexity of the issues is way too high. I consider myself lucky if I can reproduce them. Also, the places where the developer in me would want a debugger the DBA in me says "no way you're putting a debugger there."
但是,一旦代码发布,问题的复杂性就太高了。如果我可以重现它们,我认为自己很幸运。此外,在我的开发人员想要调试器的地方,我的DBA说“你决不在那里调试器。”
#5
0
I do use the following tactics.
我确实使用以下策略。
During writing of the stored procedure have a @procStep var each time a new logical step is executed set @procStep = "What the ... is happening here " ;
在执行存储过程的过程中,每次执行新的逻辑步骤时都会有@procStep var。设置@procStep =“这里发生了什么......”;
the rest is here
剩下的就在这里
#1
6
I think the answer lies in the fact that SQL is a set-based language with a few procedural things attached. Since the designers were thinking in set-based terms, they didn't think that the ordinary type of debugging that other languages have is important. However, I think some of this is changing. You can set breakpoints in SQL Server 2008. I haven't used it really as you must have SQL Server 2008 databases before it will work and most of ours are still SQL Server 2000. But it is available and it does allow you to step through things. You still are going to have problems when your select statement is 150 lines long and it knows that the syntax isn't right but it can't point out exactly where as it is all one command.
我认为答案在于SQL是一种基于集合的语言,附带了一些程序性的东西。由于设计师正在考虑基于集合的术语,他们并不认为其他语言的普通调试类型很重要。但是,我认为其中一些正在改变。您可以在SQL Server 2008中设置断点。我没有真正使用它,因为您必须拥有SQL Server 2008数据库才能工作,而我们的大多数仍然是SQL Server 2000.但它可用,它确实允许您单步执行的东西。当select语句长度为150行时,你仍然会遇到问题,并且它知道语法不正确,但是它无法准确指出所有命令的确切位置。
Personally when I am writing a long procedural SP, I build in a test mode that includes showing me the results of things I do, the values of key variables at specific points I'm interested in, and print staments that let me know what steps have been completed and then rolling the whole thing back when done. That way I can see what would have happened if it had run for real, but not have hurt any of the data in the database if I got it wrong. I find this very useful. It can vastly increase the size of your proc though. I have a template I use that has most of the structure I need set up in it, so it doesn't really take me too long to do. Especially since I never add an insert. update or delete to a proc without first testing the associated select to ensure I have the records I want.
就个人而言,当我编写一个长程序SP时,我构建了一个测试模式,其中包括向我展示我所做的事情的结果,我感兴趣的特定点的关键变量的值,以及让我知道哪些步骤的打印标记已经完成,然后完成后将整个事情卷回来。这样我可以看到如果它运行真实会发生什么,但如果我弄错了,就不会伤害数据库中的任何数据。我发现这非常有用。它可以大大增加你的触发器的大小。我有一个我使用的模板,其中包含我需要设置的大部分结构,所以它并不需要我太长时间。特别是因为我从不添加插入物。更新或删除到proc而不先测试相关的选择,以确保我有我想要的记录。
#2
4
I think the explanation is that "regular" languages have much smaller individual statements than SQL, so that single-statement granularity points to a much smaller part of the code in them than in SQL. A single SQL statement can be a page or more in length; in other languages it's usually a single line.
我认为解释是“常规”语言的单个语句比SQL小得多,因此单语句粒度指向其中的代码比SQL中的小得多。单个SQL语句的长度可以是一页或更多;在其他语言中,它通常是一行。
I don't think that makes it impossible for debuggers / IDEs to more precisely identify errors, but I suspect it makes it harder.
我不认为这使得调试器/ IDE无法更精确地识别错误,但我怀疑这会让它变得更难。
#3
1
I agree with your complaint.
我同意你的投诉。
Building a good logging framework and overusing it in your sprocs is what works best for me.
建立一个良好的日志框架并在你的sprocs中过度使用它对我来说最有效。
Before and after every transaction or important piece of logic, I write out the sproc name, step timestamp and a rowcount (if relevant) to my log table. I find that when I have done this, I can usually narrow down the problem spot within a few minutes.
在每个事务或重要逻辑之前和之后,我将sproc名称,步时间戳和rowcount(如果相关)写出到我的日志表中。我发现当我这样做时,我通常可以在几分钟内缩小问题点。
Add a debug parameter to the sproc (default to "N") and pass it through to any other sprocs that it calls so that you can easily turn logging on or off.
将调试参数添加到sproc(默认为“N”)并将其传递给它调用的任何其他sprocs,以便您可以轻松打开或关闭日志记录。
#4
1
As for breakpoints and stepping through code, you can do this with MS SQL Server (in my opinion, it's easier on 2005+ than with 2000).
至于断点和单步执行代码,您可以使用MS SQL Server执行此操作(在我看来,它在2005年以上比在2000年更容易)。
For the simple cases, early development debugging, the sometimes cryptic messages are usually good enough to get the error resolved -- syntax error, can't do X with Y. If I'm in a tough sproc, I'll revert to "printf debugging" on the sproc text because it's quick and easy. After a while with your database of choice, the simple issues become old hat and you just take them in stride.
对于简单的情况,早期的开发调试,有时神秘的消息通常足以让错误得到解决 - 语法错误,不能用Y做X.如果我在一个棘手的sproc,我将恢复到“关于sproc文本的printf debugging“因为它快速而简单。在选择了您的数据库一段时间之后,简单的问题就变成了旧问题,您只需要大步迈进。
However, once the code is released, the complexity of the issues is way too high. I consider myself lucky if I can reproduce them. Also, the places where the developer in me would want a debugger the DBA in me says "no way you're putting a debugger there."
但是,一旦代码发布,问题的复杂性就太高了。如果我可以重现它们,我认为自己很幸运。此外,在我的开发人员想要调试器的地方,我的DBA说“你决不在那里调试器。”
#5
0
I do use the following tactics.
我确实使用以下策略。
During writing of the stored procedure have a @procStep var each time a new logical step is executed set @procStep = "What the ... is happening here " ;
在执行存储过程的过程中,每次执行新的逻辑步骤时都会有@procStep var。设置@procStep =“这里发生了什么......”;
the rest is here
剩下的就在这里