ABAP：在本机 SQL 块中使用 SQL Server 表提示

发布于 2024-09-30 11:59:04 字数 833 浏览 4 评论 0原文

我有一个 ABAP 类，其中包含用于读取/写入远程 Microsoft SQL Server 2005 实例的各种方法。

一切都按预期进行。现在，出于安全原因，我被建议向 SELECT 查询添加 SQL Server 表提示 (READPAST)（这应该是防止死锁的措施 - 我远非 SQL 专家）。

可悲的是我无法让它发挥作用。这是我的本机 SQL 块，它确实有效：

EXEC SQL.
  OPEN ritc FOR
    SELECT FIELD1,
           FIELD2,
           FIELD3,
           FROM MY_TABLE
           WHERE FIELD1 <= :lv_variable1
             AND FIELD3 =  :c_constant
ENDEXEC.

如果我尝试在 FROM MY_TABLE 之后添加 WITH(READPAST)，我会收到此错误：您可以仅在 READ COMMITTED 或 REPEATABLE READ 隔离级别中指定 READPAST 锁。

公平地说：我尝试在 OPEN ritc 行之前添加此命令：

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

这会引发不同的错误：在此块之后的第一个 FETCH 命令处，我收到一条错误消息，指出光标ritc存在并且已经打开。

此时我什至不确定是否可以将表提示添加到本机 SQL 块中？

有什么建议吗？提前致谢。

原文

I have an ABAP class with various methods for reading from / writing to a remote Microsoft SQL Server 2005 instance.

Everything works as expected. Now I've been advised to add a SQL Server table hint (READPAST) to a SELECT query, for safety reasons (it should be a measure against deadlocks - I'm far from a SQL expert).

Sadly I can't make it work. This is my Native SQL block and as it is it works:

EXEC SQL.
  OPEN ritc FOR
    SELECT FIELD1,
           FIELD2,
           FIELD3,
           FROM MY_TABLE
           WHERE FIELD1 <= :lv_variable1
             AND FIELD3 =  :c_constant
ENDEXEC.

If I try adding WITH(READPAST) right after FROM MY_TABLE, I get this error: You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ isolation levels.

Fair enough: I tried adding this command right before the OPEN ritc line:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

This elicits a different error: at the first FETCH command after this block I get an error message saying the cursor ritc exists and it's already opened.

At this point I'm not even sure I can add table hints to a Native SQL block?

Any suggestions? Thanks in advance.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

睡美人的小仙女 2024-10-07 11:59:04

我认为SET需要在OPEN之后完成。

EXEC SQL.
  OPEN ritc FOR
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED
    SELECT FIELD1,
           FIELD2,
           FIELD3,
           FROM MY_TABLE WITH (READPAST)
           WHERE FIELD1 <= :lv_variable1
             AND FIELD3 =  :c_constant
ENDEXEC.

I think the SET needs to be done after the OPEN.

EXEC SQL.
  OPEN ritc FOR
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED
    SELECT FIELD1,
           FIELD2,
           FIELD3,
           FROM MY_TABLE WITH (READPAST)
           WHERE FIELD1 <= :lv_variable1
             AND FIELD3 =  :c_constant
ENDEXEC.

回复收藏 0 原文