在存储**过程中检测到相同的错误**，但不是在存储**功能**

问题描述

此问题是关系到我的previous之一：RaiseError （PERL，DBI）等效的unixODBC C API？

This question is related to my previous one: RaiseError (PERL, DBI) equivalent for unixODBC C API ?

当我孤立的问题后，我会发布新的问题，那就是更具体的，孤立的，没有不必要的信息。

As I isolated the problem later, I'll post new question, that is more specific, isolated and without unnecessary information.

版本：的unixODBC 2.3.0 结果
lib目录下：的unixODBC - C API

假设我有一个 存储功能的

Suppose I have a stored FUNCTION:

CREATE FUNCTION "test".func() RETURNING LVARCHAR(1000);
set debug file to '/home/directory_does_not_exists/unknown.log';
trace off;
trace on;
trace off;
return 'result is set here';
END FUNCTION;

和的相同的的身体，而是在 存储过程的

And the same body, but in stored PROCEDURE:

CREATE PROCEDURE "test".proc(pDummy SMALLINT)
set debug file to '/home/directory_does_not_exists/unknown.log';
trace off;
trace on;
LET pDummy = 2;
trace off;
END PROCEDURE;

正如你所看到的，他们是绝对相同的。调试文件的路径是错误的，所以预计错误。当我执行调用FUNC（）从的Aqua Data Studio的检测到错误：

Cannot open DEBUG file for SPL routine trace

这对通话PROC（1）。

BUT 的当我通过了unixODBC执行这些2个呼叫（使用 SQLExecute ），

execute procedure proc(1);

收益 SQL_ERROR （预期和精细），而

execute function func();

收益 SQL_SUCCESS .. 的但的 '结果在这里设置为的不的返回空字符串（）返回，而不是..

returns SQL_SUCCESS.. BUT 'result is set here' is not returned, empty string ('') is returned, instead..

执行调用FUNC（）给出相同的结果，为执行函数func（）;

Executing call func() gives the same results, as execute function func();

调用 SQLMORERESULTS 收益 SQL_NO_DATA ，的SQLFetch 收益 SQL_ERROR 。

任何想法？

推荐答案

首先， - 感谢的很多以@乔纳森莱弗勒（用于提示与 SQLIDEBUG = 2： XYZ + sqliprint 和测试自己的机器上），并@bohica（与 strace的）的支持！这真的帮我找到了真正的问题并解决它！ +1从我两个。结果
遗憾的是，答案不是在自己的岗位上，这就是为什么我会回答我自己的。

First of all - thanks a lot to @Jonathan Leffler(for the hint with SQLIDEBUG=2:xyz + sqliprint and testing on his machine) and @bohica (for the hint with strace ) for the support! That really helped me to find the real problem and solve it! +1 from me for both.
Unfortunately, the answer was not in their posts, that's why I'll answer it my own.

SQL prepare 和 SQLExecute 失败有时在部分的错误，但不是全部。保存时的程序时，这些功能捕捉到更多的错误。不幸的是，这种情况是与存储的功能不同。

SQLPrepare and SQLExecute fail sometimes on some errors, but not all. When stored procedure is used, these functions catch more errors. Unfortunately, the situation is different with stored functions.

如何我现在赶上错误？如果 SQLExecute 是全成，我称之为 SQLNumResultCols - 这是正常的。在那之后，我称之为的SQLFetch 这也在意料之中。但是，正如的SQLFetch 可能会失败的原因有很多（比如，它总是失败的存储过程），它的错误被忽略。并有一个，而像

How I catch the errors now? If SQLExecute is successfull, I call SQLNumResultCols - that's normal. After that, I call SQLFetch which is also expected. BUT, as SQLFetch may fail for many reasons (for example, it always fails on stored procedures), it's error is ignored. And there's a while like

if ( SQLNumResultCols( stmt, &nAllCols ) != SQL_SUCCESS )
// ...

int nSucceededFetches = 0; // added now, see below why
while ( SQL_SUCCEEDED( SQLFetch( stmt ) ) )
{
    ++nSucceededFetches; // added now, see below why
    /* bla bla */ 
}

和这里的关键 - 添加额外的检查：

And here's the key - add additional check:

if( 0 == nSucceededFetches && nColumns > 0 )

它说 - 如果有是返回的列并获取失败在第一次通话，然后什么是错的。然后，我有

which says - if there are returned columns and fetch fails on the FIRST call, then something's wrong. Then I have

while ( SQL_SUCCESS == SQLError( 0, 0, stmt, szSqlState, &nNativeError, szError, 500, &nErrorMsg ) )
{ /* bla bla */ }

---

和一切都很好。我还是不明白，为什么 SQLExecute 收益 SQL_SUCCESS （甚至不是 SQL_SUCCESS_WITH_INFO ..），但它并不重要。

---

And everything's fine. I still don't understand why SQLExecute returns SQL_SUCCESS (NOT even SQL_SUCCESS_WITH_INFO ..), but it doesn't matter.

这篇关于在存储**过程中检测到相同的错误**，但不是在存储**功能**的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！