SQLGetDiagField返回SQL_SUCCESS，但是DiagInfoPtr的结果为-1 [英] SQLGetDiagField returns SQL_SUCCESS but result in DiagInfoPtr is -1

问题描述

我使用ODBC与MS SQL。当使用SQLExecute（hSTMT）执行查询时，我尝试用

I'm using ODBC with MS SQL. When executing query with SQLExecute(hSTMT) I try to receive total row count with

   SQLGetDiagField(SQL_HANDLE_STMT, hSTMT, 0, SQL_DIAG_CURSOR_ROW_COUNT, &RowCount, SQL_IS_INTEGER, &StrLen);

在执行像

SELECT Username, Password 
FROM dbUser with(nolock) 
WHERE Username LIKE ? 
ORDER BY UserName ASC 

尽管SQLExecute后的结果是绝对有效的。

Though result after SQLExecute is absolutely valid.

当我执行ORDER BY FullName时，我会收到有效的行计数。

When I do ORDER BY FullName,I receive valid row count.

接收有效行计数，但在生产服务器上我收到-1。我看过它可能是一个性能问题，但我有相同的结果，即使查询只有一个运行。

Moreover when I run locally I also receive valid row count, but on production server I receive -1. I've read it could be an issue with performance, but I have the same result even if the query only one that running.

通过尝试不同的查询变体发现只有当我使用字段名称，如'User％'（UserName，UserId等）作为order by子句中的第一个参数时，才会出现此问题。使用ORDER BY FullName，UserName即可正常工作。

By trying different variants of the query I found out that the problem appears only when I use field names like 'User%'(UserName, UserId, etc.) as first parameter in order by clause. Works fine with ORDER BY FullName, UserName.

这种行为的原因是什么？为什么问题只出现在用户％喜欢名称？

What could be the reason for such behaviour? Why the problem appears only in User% like names?

可能是ODBC驱动程序的问题吗？

Could it be the issue with ODBC driver?

提前感谢。

推荐答案

似乎问题是由于索引。当我删除相应列的索引 - 一切开始工作正常。虽然没有索引，一切都很慢，所以这是一个糟糕的决定。

It's appeared that the problem was due to indexes. When I removed indexes for corresponding columns - everything started to work fine. Though everything is pretty slow without indexes so it is a poor decision.

防止SQLGetDiagField用于获取行计数的TempDB使用的索引。

P.S. Indexes preventing usage of TempDB that is used by SQLGetDiagField to obtain row count.

这篇关于SQLGetDiagField返回SQL_SUCCESS，但是DiagInfoPtr的结果为-1的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！