Vertica .NET driver does not respect VerticaParameter.Type

Toni

Hi,

 

I've noticed that when ever passing parameters to VerticaCommand, the data type does not seem to be handled correctly unless explicitly set in the SQL command text.

 

For example, a query

 

select @param from dual

fails with "ERROR: Could not determine data type of parameter $1" if given a .NET decimal as parameter even though I set the data type for the parameter in the code:

 

VerticaParameter param = new VerticaParameter("@param", VerticaType.Numeric, 1.5m)
or
VerticaParameter param = command.CreateParameter() as VerticaParameter;
param.ParameterName = "@param";
param.DbType = DbType.Decimal;
param.Type = VerticaType.Numeric;
param.Value = 1.5m;

 

This query

 

select @param::numeric from dual

handles the data type correctly and return a decimal value of 1.5.

 

 

The problem is, I'm using the driver in a situation where I cannot easily change the SQL command. Is there any way to make the Vertica driver respect the Type parameter (e.g. VerticaType.Numeric)?

 

With the below code you can easily reproduce the problem in two different scenarios. I'm using the 7.1.1 version of the driver.

 

using System;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using Vertica.Data.VerticaClient;
using System.Data;

namespace VerticaTester
{
    [TestClass]
    public class UnitTest1
    {
        string GetConnectionString()
        {
            var builder = new VerticaConnectionStringBuilder(); 
            builder.Host = "10.68.0.4";
            builder.Database = "xx";
            builder.User = "xx";
            builder.Password = "xx";
            return builder.ToString();
        }

        [TestMethod]
        public void TestMethod1()
        {
            var result = QuerySimple("select @param from dual");
            // Fails with "ERROR: Could not determine data type of parameter $1"
            // Even though param.Type = VerticaType.Numeric;
            Assert.AreEqual(1.5m, result);
        }

        [TestMethod]
        public void TestMethod2()
        {
            var result = QuerySimple("select @param::numeric from dual"); // Specify datatype in SQL command text            
            Assert.AreEqual(1.5m, result);
        }

        public object QuerySimple(string sql)
        {
            using (var connection = new VerticaConnection(GetConnectionString()))
            {
                connection.Open();
                using (VerticaCommand command = new VerticaCommand(sql, connection))
                {
                    //VerticaParameter param = new VerticaParameter("@param", VerticaType.Numeric, 1.5m); // This has the same effect              
                    VerticaParameter param = command.CreateParameter() as VerticaParameter;
                    param.ParameterName = "@param";
                    param.DbType = DbType.Decimal;
                    param.Type = VerticaType.Numeric;
                    param.Value = 1.5m;
                    param.Direction = ParameterDirection.Input;
                    command.Parameters.Add(param);

                    return command.ExecuteScalar();
                }
            }
        }

        [TestMethod]
        public void TestMethod3()
        {
            var result = QueryWithCase("select case when 1 = 1 THEN @param1 else @param2 end from dual");
            // Assert.AreEqual failed. Expected:< 1,5(System.Decimal) > Actual:< 2(System.String) >.
            Assert.AreEqual(1.5m, result);
        }

        [TestMethod]
        public void TestMethod4()
        {
            var result = QueryWithCase("select case when 1 = 1 THEN @param1::numeric else @param2::numeric end from dual"); // Specify datatype in SQL command text            
            Assert.AreEqual(1.5m, result);
        }

        public object QueryWithCase(string sql)
        {
            using (var connection = new VerticaConnection(GetConnectionString()))
            {
                connection.Open();
                using (VerticaCommand command = new VerticaCommand(sql, connection))
                {
                    //VerticaParameter param1 = new VerticaParameter("@param1", VerticaType.Numeric, 1.5m); // This has the same effect
                    VerticaParameter param1 = command.CreateParameter() as VerticaParameter;
                    param1.ParameterName = "@param1";
                    param1.DbType = DbType.Decimal;
                    param1.Type = VerticaType.Numeric;
                    param1.Value = 1.5m;
                    param1.Direction = ParameterDirection.Input;
                    command.Parameters.Add(param1);

                    VerticaParameter param2 = command.CreateParameter() as VerticaParameter;
                    param2.ParameterName = "@param2";
                    param2.DbType = DbType.Decimal;
                    param2.Type = VerticaType.Numeric;
                    param2.Value = 1.5m;
                    param2.Direction = ParameterDirection.Input;
                    command.Parameters.Add(param2);

                    return command.ExecuteScalar();
                }
            }           
        }
    }
}

Best Regards,

Toni