iDB2 Select command with parameters returning SQL0418(带有返回 SQL0418 参数的 iDB2 Select 命令)

问题描述

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

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

我需要执行一个包含 n 个参数的 SELECT 命令，这些参数在查询中定义为 @paramX，然后设置参数值，但是当我运行代码时，我得到一个 SQL048参数标记的使用无效..我到处搜索文档/示例，但我读过的所有内容都与我正在使用的代码一致.我错过了什么吗?如果这无效，最好的选择是什么?

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?

这是我用来测试的独立代码.

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(@param0 as char(10))),TIME(cast(@param1 as 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(@param0 as DATE),cast(@param1 as TIME)) FROM SYSIBM.SYSDUMMY1

这篇关于带有返回 SQL0418 参数的 iDB2 Select 命令的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！