iDB2选择命令参数返回SQL0418 [英] iDB2 Select command with parameters returning SQL0418

问题描述

我正在开发连接到DB2 iSeries的7.1数据库，使用IBM.Data.DB2.iSeries.dll .NET应用程序。

I'm developing a .NET application that connects to a DB2 iSeries 7.1 database, using the IBM.Data.DB2.iSeries.dll.

我需要做的，有哪些是在查询定义为 @paramX -n参数，后来设置的参数值的SELECT命令，但是当我运行code，我收到了 SQL048使用参数标记无效。。我到处去寻找文档/例子，但我读过的一切是在参数与code我使用。我失去了一些东西？如果这不是有效的，什么是最好的选择吗？

I need to do a SELECT command that has n parameters which are defined in the query as @paramX, setting the parameter values afterwards, but when I run the code I get a SQL048 Use of parameter marker not valid.. I've searched everywhere for documentation / examples but everything I've read is in par with the code I'm using. Am I missing something? If this is not valid, what is the best alternative?

这是孤立code我使用来测试。

This is the isolated code I'm using to test.

    static void Main(string[] args)
    {
        String myConnectionString = "DataSource=*******;Database=*******;UserId=*******;Password=*******;";
        iDB2Connection myConnection = new iDB2Connection();
        try{
            myConnection.ConnectionString = myConnectionString;
            myConnection.Open();

            var cmd = new iDB2Command("SELECT TIMESTAMP(DATE(@param0),TIME(@param1)) FROM SYSIBM.SYSDUMMY1", myConnection);

            cmd.Parameters.Add(new iDB2Parameter("@param0", iDB2DbType.iDB2Char));
            cmd.Parameters["@param0"].Value = "1900-01-01";

            cmd.Parameters.Add(new iDB2Parameter("@param1", iDB2DbType.iDB2Char));
            cmd.Parameters["@param1"].Value = "00.00.00";

            using (var reader = cmd.ExecuteReader())
            {
                if (reader.HasRows)
                {
                    reader.Read();
                    StringBuilder sb = new StringBuilder();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        sb.AppendLine(reader[i].ToString().Trim());
                    }

                    Console.Out.WriteLine(sb.ToString());
                }
            }
        }catch(Exception e)
        {
                Console.Out.WriteLine(e.ToString());
        }finally{
            if (myConnection != null)
            {
                myConnection.Close();
            }
        }
        Console.Read();
    }

修改

在一个不相关的答案我发现这个问题可能是DB2不知道的基本类型的参数（这是奇怪的，因为我是强类型的话），因此，一个可行的办法是做投在查询到的预期参数类型，例如：

EDIT

In an unrelated answer I've found that the problem might be that DB2 doesn't know the underlying type of the parameter (which is strange since I'm strong typing it), thus, a possible solution is to do a cast in the query to the expected param type, as such:

SELECT TIMESTAMP（DATE（CAST（@参数0为char（10））），时间（CAST（@参数1为char（10））））FROM SYSIBM.SYSDUMMY1

这实际工作，但是，是不是有什么更好的办法来处理呢？

This actually worked but, isn't there any better way to handle this?

推荐答案

AFAIK，这是一个平台的限制。可以由该平台添加到应用程序异常*的说明得到证实。话虽这么说，因为我不能改变我收到的参数，并且没有访问信息，他们将要在查询中举行的，以我的具体问题，最好的解决办法是做一个 CAST 到了 TIMESTAMP 标量函数使用的类型，例如：

AFAIK, this is a platform limitation. that can be confirmed by an explanation that the platform adds to the application exception*. That being said, as I can't change the parameters I receive and don't have access to the info they are going to held in the query, the best solution to my specific problem is to do a CAST to the types that the TIMESTAMP scalar function uses, e.g.:

SELECT TIMESTAMP（CAST（@参数0为DATE），投（@参数1为TIME））FROM SYSIBM.SYSDUMMY1

这篇关于iDB2选择命令参数返回SQL0418的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！