Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Vertica .NET driver does not respect VerticaParameter.Type

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

 

 

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.