SQL Server错误处理：异常和数据库 - 客户端合同 [英] SQL Server error handling: exceptions and the database-client contract

问题描述

我们是SQL Server数据库开发人员团队。我们的客户是一个混合的包C＃/ ASP.NET，C＃和Java Web服务，Java / Unix服务和一些Excel。

我们的客户端开发人员只使用存储过程我们提供，我们期望（当然，当然）他们将它们视为Web服务方法。

我们的一些客户端开发人员不喜欢SQL异常。他们用他们的语言理解他们，但他们不明白SQL在如何沟通问题上是有限的。

我不只是意味着SQL错误，例如尝试将bob插入到int列中。

我也意味着例外，例如告诉他们参考值是错误的，或者数据已经改变了，或者他们不能这样做，因为他的总和不是零。

他们真的没有任何具体的替代方法：他们提到我们应该输出参数，但是我们假设一个例外是处理停止/回滚。

这里的人如何处理数据库 - 客户合同？一般情况下或DB和客户端代码猴子之间存在分隔。

编辑：

• 我们使用SQL Server 2005 TRY / CATCH专用


• 我们将回滚后的所有错误记录到异常表已经有


• 我们是担心我们的一些客户不会检查输出参数，并假设一切正常。我们需要标记的错误来支持查看。


• 一切都是一个例外...客户端应该做一些消息解析来分离信息与错误。为了将我们的异常与DB引擎和调用错误分开，他们应该使用错误号（我们当然是50,000）

解决方案

我是一个客户端代码猴子，处理数据库很多。这是我如何处理它。

在SQL中发生的异常（raiseerrors）被传播回到调用者。这将包括ref约束，唯一的索引违例，更严重的问题等。基本上不会使数据操作发生的任何事情都应该传播回来。

调用者C＃应该有：

  catch（SQLException sqlEx）
  

然后根据需要处理异常。他们应该有一个特定的SQLException处理程序。这很重要。

我通常远离输出参数，因为我认为这些与传输的数据有关，而不是任何错误消息，另外我可以检查异常对于SQL Server错误代码，所以我们需要的所有数据应该在该异常中。

此外，在某些情况下，使用SQL Server，我们有存储过程可以提高业务类型的例外。在这些情况下，我们添加一个自定义错误号（50000以上），并在需要时引发存储过程中的错误。一般来说，我们试图将它们保持在最低限度，因为它增加了复杂性，但在某些情况下，我们发现它们是必要的。

现在，由于客户端正在捕获SQLException，因此可以查看SQL Server在异常中返回的错误代码，然后执行任何特殊操作（如果需要）异常被捕获，错误号是一定值。这允许基于错误代码的二级错误处理，如果自定义错误（> 50000）是必需的。

这也允许DBA引发自定义错误并且客户端代码具有一致的方式来处理它们。然后，DBA将不得不告诉客户端代码猴子自定义错误是如何为他们准备的。

我通常不使用返回代码进行错误处理目的，虽然我可以看到它们如何被使用，但这意味着代码猴层中更多的逻辑来查看和处理返回码。如果他们是一个问题，我想要一个例外，因为我可以一直处理他们。如果我还要查看返回代码，现在有多种错误处理方式。

We’re a team of SQL Servers database developers. Our clients are a mixed bag of C#/ASP.NET, C# and Java web services, Java/Unix services and some Excel.

Our client developers only use stored procedures that we provide and we expect that (where sensible, of course) they treat them like web service methods.

Some our client developers don’t like SQL exceptions. They understand them in their languages but they don’t appreciate that the SQL is limited in how we can communicate issues.

I don’t just mean SQL errors, such as trying to insert "bob" into a int column.

I also mean exceptions such as telling them that a reference value is wrong, or that data has already changed, or they can’t do this because his aggregate is not zero.

They’d don’t really have any concrete alternatives: they’ve mentioned that we should output parameters, but we assume an exception means "processing stopped/rolled back.

How do folks here handle the database-client contract? Either generally or where there is separation between the DB and client code monkeys.

Edits:

• we use SQL Server 2005 TRY/CATCH exclusively
• we log all errors after the rollback to an exception table already
• we're concerned that some of our clients won't check output paramaters and assume everything is OK. We need errors flagged up for support to look at.
• everything is an exception... the clients are expected to do some message parsing to separate information vs errors. To separate our exceptions from DB engine and calling errors, they should use the error number (ours are all 50,000 of course)

解决方案

Well I'm a client code monkey that deals with databases a lot. Here's how I handle it.

Exceptions (raiseerrors) that happen in SQL get propagated back to the caller. This would include ref constraints, unique index violations, more serious issues, etc. Basically anything that would not make the data operation occur normally should be propagated back.

The caller C# should have this:

catch (SQLException sqlEx)

And then handle the exception as needed. They should have a specific SQLException handler. This is important.

I generally stay away from output parameters because I consider those to be related to the data being transported and not any error messages, additionally I can inspect the exception for the SQL Server error code so all the data we need should be in that exception.

Additionally, in some cases with SQL Server, we have Stored Procedures that could raise "business type of exceptions". In those cases we add a custom error number (above 50000) and raise that error in the stored procedure when needed. In general we try to keep these at a minimum because it adds complexity, but in some cases, we have found them to be necessary.

Now, since the client is catching the SQLException, they can look at the error code returned by SQL Server in the exception and then take any special action (if needed) when the exception is caught and the error number is a certain value. This allows a secondary level of error handling based on the error code if that is required for the custom errors (>50000).

This also allow the DBAs to raise custom errors and have the client code have a consistent way to deal with them. The DBAs would then have to tell the client code monkey what the custom errors were so they could prepare for them.

I usually don't use the return codes for error handling purposes, although I can see how they could be used, but that means more logic in the code monkey layer to look at and deal with the return code. If they is a problem, I want an exception back, because then I can deal with them consistently. If I have to look at return codes as well, now there a multiple avenues of error handling.

这篇关于SQL Server错误处理：异常和数据库 - 客户端合同的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！