SAP HanaDB SQL中声明的数组/表变量值错误 [英] Errors with declared array/table variable values in SAP HanaDB SQL

问题描述

我正在尝试添加一个声明的变量来替换"where in"子句中的值的硬编码列表.研究Hana如何处理数组变量，似乎可以做到这一点，方法是声明一个数组，然后直接在其上使用选择，或者先将其取消嵌套到表中，但我不断遇到无法解决的错误.

当我这样尝试时:

  DO开始DECLARE CODES_ARRAY NVARCHAR(100)ARRAY = ARRAY('01'，'02'，'03'，'04');选择T0."ItemCode"从OITM T0T0上的内部联接OITW T1."ItemCode" = T1."ItemCode"在"WhsCode"输入位置(从:CODES_ARRAY中选择代码")；-发生错误的第9行结尾; 

我收到此错误消息 sql语法错误:)"附近的语法不正确:第9行col 54(在pos 239处)

我不知道什么是语法错误解决方案.

因此，然后我尝试插入一个声明的表变量，如下所示:

  DO开始DECLARE CODES_ARRAY NVARCHAR(100)ARRAY = ARRAY('01'，'02'，'03'，'04');DECLARE CODES_TABLE TABLE = UNNEST(:CODES_ARRAY)AS(代码")；-发生错误的第5行选择T0."ItemCode"从OITM T0T0上的内部联接OITW T1."ItemCode" = T1."ItemCode"在"WhsCode"中输入(从CODES_TABLE中选择"code")；-我知道:这里不见了，但是添加时，会出现与上一个块相同的错误结尾; 

，我收到此错误消息:必须声明标识符:1:第5行col 38(在pos 123处)

据我所知，数组变量已声明为应有的状态，所以我不知道如何解决该错误.

我已经一遍又一遍地阅读SAP Hana SQL参考文档(有关数组/表变量，嵌套函数等)，似乎我已经正确设置了所有内容，但无法弄清这些错误.我希望能够在可能的情况下在不同时间使用这两种方法(将数组变量转换为表变量"和仅使用数组变量"方法)

我不知道这到底是怎么回事，但是我注意到我的帖子中引用了两个不同的错误消息(请参见与前两个代码块中的错误的区别)，即每个错误要么立即发生，要么在变量与:一起使用之前(对于UNNEST)或紧跟在变量与:一起使用(如果在中使用)SELECT * FROM 在查询中).

因此，我想知道问题是否在 Hana ADO.NET 应用程序查询准备和执行调用级别上是上游"，但是我进行了测试，并且当我仔细检查了查询字符串时在执行之前，它看起来没有变化，并且带有:的变量仍然看起来应有，因此至少与通过 Hana ADO.NET HanaCommand 看起来正确-但是一旦使用 HanaDataReader 或 HanaDataAdapter 执行查询，它就会返回上述错误消息.从 Hana ADO.NET 级别追逐该问题，但又不知道该怎么办，可能是一个红色的鲱鱼.

更新

要进行进一步的故障排除，我尝试使用 hdbsql.exe -n XXX.XXX.XXX.XXX:30015 -u XXX -p XXX -m -I"c:\ temp \ test执行以下代码块.sql"-c"#"即可！因此，我看到的错误仅在通过 Hana ADO.NET 接口执行相同的查询时才会显示.

  DO开始DECLARE CODES_ARRAY NVARCHAR(10)ARRAY = ARRAY('01'，'02'，'03'，'04');DECLARE CODES_TABLE TABLE("code" NVARCHAR(10))= UNNEST(:CODES_ARRAY)AS("code");选择T0."ItemCode"从OITM T0T0上的内部联接OITW T1."ItemCode" = T1."ItemCode"在"WhsCode"输入位置(从:CODES_TABLE中选择代码")；-第10行，在使用Hana ADO.NET时发生错误结尾;# 

在通过 Hana ADO.NET 并显示以下错误消息时，上述操作失败: sql语法错误:)"附近的语法不正确:第10列第54行(在pos 325)，但在通过 hdbsql 执行时有效.

更新

执行查询的C#代码相当简单，但是为了完整地排除故障，我将包含我们的 HanaHelper 类的有趣部分.该代码每天可以成功执行100条查询，而不会出现错误或问题.这是第一次尝试通过此代码在查询中声明或使用任何类型的变量，并且当错误开始出现时.据我所知，该问题与在查询中使用变量时使用:有关.

 公共类HanaHelper{公共HanaConnection objConn = null;公共HanaHelper(字符串ConnectionString){尝试{objConn = new HanaConnection(ConnectionString);}捕获(异常e){Console.WriteLine(@"HanaConnection引发的异常:{0} \ n {1}"，e.Message，e.InnerException)；}}公共数据集GetData(字符串strSQL){使用(HanaCommand objCmd = new HanaCommand(strSQL，objConn)){使用(HanaDataAdapter objDA = new HanaDataAdapter(objCmd)){DataSet objDS = new DataSet();尝试{objDA.Fill(objDS);}捕获(异常){扔;}最后{//无论成功或失败，都要做一些有趣的事情}objConn.Close();返回objDS;}}}} 

这里有什么线索为什么相同的查询通过 hdbsql 工作但通过 Hana ADO.NET 执行时失败?

更新

我弄清楚了如何在C#代码中使用 HanaSQLTrace ，以便可以检查准备好的查询文本和中提琴，错误消息的来源显而易见，所有出现的:VARNAME"替换为"?"(?替换:，并在变量名称中的每个字符之间都添加一个空格).我想它正在尝试用?替换:的出现，就好像要替换的参数一样.

该行为如何被禁用，使用或解决，以便我可以有效地在 Hana ADO.NET 中的查询中使用变量?

解决方案

根据OP反馈进行了更新.

要在SQLScript中引用变量(以便访问其值)，它是必需在变量名称前加一个冒号:.

然而，主要问题却是 CODES_TABLE 表变量的声明.使用HANA 2 SPS 4时，错误消息为

SAP DBTech JDBC:[264]:无效的数据类型:未知类型SYSTEM.TABLE:第5行第23行

这指向TABLE类型的变量 CODES_TABLE 的声明，该变量缺少表中应包含哪些列的定义.

添加此内容即可解决此问题.

通过这些更改，您的代码应该可以工作:

  DO开始DECLARE CODES_ARRAY NVARCHAR(100)ARRAY = ARRAY('01'，'02'，'03'，'04');DECLARE CODES_TABLE TABLE("code" NVARCHAR(100))= UNNEST(:CODES_ARRAY)AS("code");-^^^^^^^^^^^^^^^^^^^^^^^-|--------------------------------------- +选择T0."ItemCode"从OITM T0内连接瓦T1ON T0."ItemCode" = T1."ItemCode"在哪里"WhsCode" IN(从:CODES_TABLE中选择"code")；-^-|--------------------------------------- +结尾; 

声明和分配表变量的另一种选择是不使用 DECLARE 命令.

  DO开始DECLARE CODES_ARRAY NVARCHAR(100)ARRAY = ARRAY('01'，'02'，'03'，'04');CODES_TABLE = UNNEST(:CODES_ARRAY)AS(代码")；选择T0."ItemCode"从OITM T0内连接瓦T1ON T0."ItemCode" = T1."ItemCode"在哪里"WhsCode" IN(从:CODES_TABLE中选择"code")；结尾; 

I am trying to add a declared variable to replace a hardcoded list of values in a "where in" clause. Researching how Hana handles array variables it seems like I can do this by declaring an array and then either using a select directly on it or by unnesting it first into a table but I keep getting errors I can't resolve.

When I try it this way:

DO
BEGIN

  DECLARE CODES_ARRAY NVARCHAR(100) ARRAY = ARRAY('01','02','03','04');

  SELECT T0."ItemCode"
  FROM OITM T0
    INNER JOIN OITW T1 ON T0."ItemCode" = T1."ItemCode"
  WHERE "WhsCode" IN (SELECT "code" FROM :CODES_ARRAY); -- line 9 where error occurs

END;

I get this error message sql syntax error: incorrect syntax near ")": line 9 col 54 (at pos 239)

I can't figure out what the syntax error resolution is.

So then I tried inserting a declared table variable like this:

DO
BEGIN

  DECLARE CODES_ARRAY NVARCHAR(100) ARRAY = ARRAY('01','02','03','04');
  DECLARE CODES_TABLE TABLE = UNNEST(:CODES_ARRAY) AS ("code"); -- line 5 where error occurs

  SELECT T0."ItemCode"
  FROM OITM T0
    INNER JOIN OITW T1 ON T0."ItemCode" = T1."ItemCode"
  WHERE "WhsCode" IN (SELECT "code" FROM CODES_TABLE); -- I know : is missing here but when adding, the same error from previous block shows up

END;

and I get this error message: identifier must be declared: 1: line 5 col 38 (at pos 123)

As far as I can tell the array variable is declared as it should be so I don't know how to resolve the error.

I've read the SAP Hana SQL Reference documentation (for array/table variables, unnest function, etc.) over and over and it seems like I've got everything setup correctly but can't figure out these errors. I would like to be able to use both of these approaches at different times if possible (the "array variable to table variable" and the "array variable only" approaches)

I don't know exactly what is going on here, but one thing I notice that the two different error messages referenced in my post (see difference from errors in the first two code blocks) is that each error is occurring either immediately before the use of the variable with the : (in the case of the UNNEST) or immediately following the variable with the : (in the case of using in the SELECT * FROM in the query).

Because of that, I wondered if the issue is "upstream" at the Hana ADO.NET application query preparation and execution call level, but I ran a test and when I double checked the query string just before it is executed, it appears unchanged and the variables with : still look as they should, so at least as far as just before execution through Hana ADO.NET HanaCommand it looks correct - but once executing the query using HanaDataReader or HanaDataAdapter it returns the error messages referred to above. It may be a red herring to chase the problem from the Hana ADO.NET level but don't know what else to do.

Update

To further troubleshoot, I tried executing this code block below using hdbsql.exe -n XXX.XXX.XXX.XXX:30015 -u XXX -p XXX -m -I "c:\temp\test.sql" -c "#" and it works! So, the errors I see only show up when executing the same query through the Hana ADO.NET interface.

DO
BEGIN

  DECLARE CODES_ARRAY NVARCHAR(10) ARRAY = ARRAY('01','02','03','04');
  DECLARE CODES_TABLE TABLE ("code" NVARCHAR(10)) = UNNEST(:CODES_ARRAY) AS ("code");

  SELECT T0."ItemCode"
  FROM OITM T0
    INNER JOIN OITW T1 ON T0."ItemCode" = T1."ItemCode"
  WHERE "WhsCode" IN (SELECT "code" FROM :CODES_TABLE); -- line 10 where error occurs when using Hana ADO.NET

END;
#

The above fails when through Hana ADO.NET with the error message: sql syntax error: incorrect syntax near ")": line 10 col 54 (at pos 325) but works when executed through hdbsql.

Update

The C# code that executes the query is fairly straight forward, but for completeness of troubleshooting effort I am including the interesting parts of our HanaHelper class. This code works successfully to execute 100s of queries a day without errors or problems. This is the first time where a variable of any type has been attempted to be declared or used in a query through this code and when the errors started showing up. As far as I can tell, the issue is tied to the use of the : when using the variable in the query.

public class HanaHelper
{
    public HanaConnection objConn = null;

    public HanaHelper(string ConnectionString)
    {
        try
        {
            objConn = new HanaConnection(ConnectionString);
        }
        catch (Exception e)
        {
            Console.WriteLine(@"Exception thrown by HanaConnection: {0}\n{1}", e.Message, e.InnerException);
        }
    }

    public DataSet GetData(string strSQL)
    {
        using (HanaCommand objCmd = new HanaCommand(strSQL, objConn))
        {
            using (HanaDataAdapter objDA = new HanaDataAdapter(objCmd))
            {
                DataSet objDS = new DataSet();
                try
                {
                    objDA.Fill(objDS);
                }
                catch (Exception)
                {
                    throw;
                }
                finally 
                {
                  // do something interesting regardless of success or failure
                }
                objConn.Close();
                return objDS;
            }
        }
    }
}

Any clue here why the same query works through hdbsql but fails when executing through Hana ADO.NET?

Update

I figured out how to use HanaSQLTrace in the C# code so that I can inspect the prepared query text and viola, the source of error messages becomes apparent, all occurrences of ":VARNAME" are replaced with "? " (a ? replaces the : and a space for each character in the variable name). I suppose it is trying to pre-substitute occurrences of : with a ? as if there were parameters to be substituted.

How can this behavior be disabled, or worked with, or worked around so that I can use variables in a query in Hana ADO.NET effectively?

解决方案

Updated based on the OP feedback.

To refer to a variable (in order to access its value(s)) in SQLScript it's necessary to put a colon : in front of the variable name.

The main issue, however, turns out to be the declaration of the CODES_TABLE table variable. With HANA 2 SPS 4 the error message is

`SAP DBTech JDBC: [264]: invalid datatype: unknown type SYSTEM.TABLE: line 5 col 23`

This points to the declaration of the TABLE typed variable CODES_TABLE which lacks the definition of what columns should be in the table.

Adding this fixes the issue.

With these changes, your code should work:

DO
BEGIN

  DECLARE CODES_ARRAY NVARCHAR(100) ARRAY = ARRAY('01','02','03','04');
  DECLARE CODES_TABLE TABLE ("code" NVARCHAR(100)) = UNNEST(:CODES_ARRAY) AS ("code"); 
  --                        ^^^^^^^^^^^^^^^^^^^^^^
  --                                     |
  ---------------------------------------+

  SELECT 
        T0."ItemCode"
  FROM 
               OITM T0
    INNER JOIN OITW T1 
      ON T0."ItemCode" = T1."ItemCode"
  WHERE 
        "WhsCode" IN (SELECT "code" FROM :CODES_TABLE);
  --                                     ^
  --                                     |
  ---------------------------------------+
END;

An alternative option to declare and assign the table variable is to not use the DECLARE command.

DO
BEGIN

  DECLARE CODES_ARRAY NVARCHAR(100) ARRAY = ARRAY('01','02','03','04');

  CODES_TABLE = UNNEST(:CODES_ARRAY) AS ("code"); 

  SELECT 
        T0."ItemCode"
  FROM 
               OITM T0
    INNER JOIN OITW T1 
      ON T0."ItemCode" = T1."ItemCode"
  WHERE 
        "WhsCode" IN (SELECT "code" FROM :CODES_TABLE);

END;

这篇关于SAP HanaDB SQL中声明的数组/表变量值错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！