TSQLQuery - 查询未返回游标
现在，我正在尝试摆脱delphi程序中的一些旧搜索代码，并且我想将搜索推送到程序正在使用的sql服务器。为此，我创建了这个查询，它与我在不同的 C# 程序中使用的查询非常相似。
使用 SQL Management Studio 或 C# 程序，该查询工作得很好，但使用 Delphi 时，我收到“光标未从查询返回”错误。
这是查询
DECLARE @SearchString NVARCHAR(MAX);
SET @SearchString = ':Param1';
IF @SearchString = '' SET @SearchString = '%';
--Table for splitted values
DECLARE @SearchItms TABLE
(
Item NVARCHAR(MAX)
)
--Split Operator
DECLARE @SplitOn NVARCHAR(MAX)
SET @SplitOn = ' '; --Split Keywords on space
--Splitting
While (Charindex(@SplitOn,@SearchString)>0)
Begin
Insert Into @SearchItms (Item)
Select ('%' + Substring(@SearchString,1,Charindex(@SplitOn,@SearchString)-1) + '%')
Set @SearchString = Substring(@SearchString,Charindex(@SplitOn,@SearchString)+1,len(@SearchString))
End
--Add last Item
Insert Into @SearchItms (Item)
Select ('%' + @SearchString + '%')
--Select fitting items
SELECT MyTable.*
FROM MyTable INNER JOIN SecondTable ON (MyTable.Key = SecondTable.Key)
WHERE
NOT EXISTS(
SELECT * FROM @SearchItms WHERE NOT(
(OneField IS NOT NULL AND OneField LIKE Item)
OR (OneDateField IS NOT NULL AND (convert(varchar, OneDateField, 104) LIKE Item) OR (convert(varchar, OneDateField, 114) LIKE Item ) )
OR (AnotherField IS NOT NULL AND AnotherField LIKE Item)
OR (LastField IS NOT NULL AND LastField LIKE Item)
)
)
一件奇怪的事情是，当我删除时，
--Add last Item
Insert Into @SearchItms (Item)
Select ('%' + @SearchString + '%')
我没有收到“光标未从查询返回”错误。但是使用搜索字符串，我在 SQL Management Studio 中只得到 10 个结果，而在 Delphi 中我得到了数据库中的所有项目，就像搜索没有执行任何操作一样。
这种情况也会发生在一个简单的测试程序中，其中我只有一个 Form、一个 TSQLConnection、TSQLQuery、TDataSetProvider 和一个 ClientDataSet。
有人可以告诉我这里有什么问题吗？由于查询在其他环境下工作正常，我认为应该没问题。
非常感谢您的帮助。
Right now, I am trying to get rid of some old searching code in a delphi programm, and I want to push the searching to the sql-server the program is using. For this I created this query, which is very similar to a query I use in a different C# program.
The query works just fine using the SQL Management Studio or from a C# program, but with Delphi I get a "Cursor not returned from query" error.
This is the query
DECLARE @SearchString NVARCHAR(MAX);
SET @SearchString = ':Param1';
IF @SearchString = '' SET @SearchString = '%';
--Table for splitted values
DECLARE @SearchItms TABLE
(
Item NVARCHAR(MAX)
)
--Split Operator
DECLARE @SplitOn NVARCHAR(MAX)
SET @SplitOn = ' '; --Split Keywords on space
--Splitting
While (Charindex(@SplitOn,@SearchString)>0)
Begin
Insert Into @SearchItms (Item)
Select ('%' + Substring(@SearchString,1,Charindex(@SplitOn,@SearchString)-1) + '%')
Set @SearchString = Substring(@SearchString,Charindex(@SplitOn,@SearchString)+1,len(@SearchString))
End
--Add last Item
Insert Into @SearchItms (Item)
Select ('%' + @SearchString + '%')
--Select fitting items
SELECT MyTable.*
FROM MyTable INNER JOIN SecondTable ON (MyTable.Key = SecondTable.Key)
WHERE
NOT EXISTS(
SELECT * FROM @SearchItms WHERE NOT(
(OneField IS NOT NULL AND OneField LIKE Item)
OR (OneDateField IS NOT NULL AND (convert(varchar, OneDateField, 104) LIKE Item) OR (convert(varchar, OneDateField, 114) LIKE Item ) )
OR (AnotherField IS NOT NULL AND AnotherField LIKE Item)
OR (LastField IS NOT NULL AND LastField LIKE Item)
)
)
One strange thing is, that when I remove
--Add last Item
Insert Into @SearchItms (Item)
Select ('%' + @SearchString + '%')
I don't get the "Cursor not returned from query" error. But with a search string, where I get only 10 Results in the SQL Management Studio, I get all items in the database in Delphi, like the search did not do anything.
This happens also in a simple test program where I just have a Form, a TSQLConnection, TSQLQuery, TDataSetProvider and a ClientDataSet.
Can someone tell me whats the problem here? Since the query works fine in other environments, I think it should be OK.
Many thanks for any help.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
正如 ldsandon 在他的文章中提到的，问题似乎是，它不是一个简单的查询，而是一个完整的脚本。但由于其他 dbExpress 单元似乎都无法处理这个问题，所以我找到了一种让它工作的方法。
只需在脚本开头添加
SET NOCOUNT ON;，然后使用SET NOCOUNT OFF;关闭脚本，我认为这会抑制来自插入，因此可以无错误地运行。
如果您知道运行脚本而不出错的更好方法，请告诉我。
谢谢。
As ldsandon mentioned in his post, the problem seems to be, that its not a simple Query, but a complete script. But since non of the other dbExpress units seems to be able to handle this I found a way to make it working.
Just add a
SET NOCOUNT ON;to the beginning of the script, and close the script withSET NOCOUNT OFF;I think this surpresses the 'xx rows effected' message from the inserts and therefore makes it possible to run without errors.
If you know a better way to run the script without errors, let me know.
Thanks.
这不是查询 - 这是脚本。通常，Delphi TQuery 组件可以处理单个 SQL 语句，而不是多个 SQL 语句（首先有 INSERT，然后有 SELECT）。可能是 INSERT 显然没有返回游标。您可以尝试使用 TSQLDataset 并查看它是否可以处理这种 SQL Server“匿名块”，或者将其转换为存储过程并从 Delphi 程序中调用它。
This is not a query - this is a script. Usually a Delphi TQuery component can handle a single SQL statement, not multiple ones (you have INSERTs first and then a SELECT). It may be the INSERT that obviously does not return a cursor. You can try to use a TSQLDataset and see if it can handle such a kind of SQL Server "anonymous block", or turn it into a stored procedure and call it from your Delphi program.