在存储的**过程**中检测到相同的错误，但不存储在**函数** [英] The same error is detected in stored **procedure**, but not in stored **function**
问题描述
此问题与我之前的问题有关： 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
假设我有一个 存储的FUNCTION ：
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 检测到错误：
As you can see, they are absolutely the same. The path to debug file is wrong, so error is expected. When I execute call func() from Aqua Data Studio the error is detected:
Cannot open DEBUG file for SPL routine trace
对于 call proc（1）是相同的。
em> 当我通过unixODBC执行这两个电话（使用 SQLExecute ），
BUT when I execute these 2 calls through unixODBC (using SQLExecute),
execute procedure proc(1);
返回 SQL_ERROR （这是预期和罚款），而
returns SQL_ERROR (which is expected and fine), while
execute function func();
返回 SQL_SUCCESS .. em> BUT 'result is set here' is 不 返回，空字符串（''）。
returns SQL_SUCCESS.. BUT 'result is set here' is not returned, empty string ('') is returned, instead..
执行调用func（）给出相同的结果，如 execute function func（）;
Executing call func() gives the same results, as execute function func();
调用 SQLMoreResults 返回 SQL_NO_DATA ， SQLFetch 返回 SQL_ERROR 。
任何想法？
推荐答案
首先 - 感谢很多到@Jonathan Leffler（用于的提示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.
SQLPrepare 和 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 )
其中 - 如果 返回第一个调用的列和fetch失败，那么有问题。然后我有
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.
这篇关于在存储的**过程**中检测到相同的错误，但不存储在**函数**的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
