I have an application that connects to our Vertica cluster through ADO.NET and worked fine until myself and my users upgraded to the 7.2.1.0 dll. It appears C# and Vertica are having a difficult time with data types that previously worked fine.
It appears that the Vertica dll is trying to set the numeric results from my query to decimals in C# which may be causing issues.
Here's what the portion of my select looks like (previously worked fine with Vertica dll version 7.1.x
case when sum(customers) = 0 then 0 else sum(orders) / sum(customers) end as 'customers per order'
Here's what I have to do now as a workaround. I do not want to have to cast every single select field as I have several dozen.
cast(case when sum(customers) = 0 then 0 else sum(orders) / sum(customers) end as float) as 'customers per order'
Here's a snippet of code where I fill a VerticaDataAdapter.
VerticaDataAdapter adapter = new VerticaDataAdapter();
//adapter.ReturnProviderSpecificTypes = true;
VerticaCommand query = new VerticaCommand();
query.CommandText = sql; //sql query set previously
query.Connection = connection; //connection opened previously
adapter.SelectCommand = query;
try
{
adapter.Fill(vertica_datatable);
}
catch (Exception ex)
{
//Exception thrown here
}
Here are the contents of the exception:
base = {System.ArgumentOutOfRangeException: Specified argument was out of the range of valid values.
at Vertica.Data.Internal.DataEngine.VerticaNumeric.getDecimalFromBinaryBytes()
at Vertica.Data.Internal.DataEngine.VerticaNumeric.CreateNativeValue(Boolean...
at Vertica.Data.Internal.DataEngine.VerticaNumeric.getDecimalFromBinaryBytes()
at Vertica.Data.Internal.DataEngine.VerticaNumeric.CreateNativeValue(Boolean isBinaryTransfer)
at Vertica.Data.Internal.DataEngine.VType.get_NativeValue()
at Vertica.Data.Internal.DataEngine.VDataConverter.DoConvertToDefaultType(Object srcData, TypeMetadata srcMetadata)
at Vertica.Data.Internal.DotNetDSI.DataEngine.DataConverter.ConvertToDefaultType(Object srcData, TypeMetadata srcMetadata)
at Vertica.Data.Internal.ADO.Net.SDataReader.ConvertDataForOutput(Object data, TypeMetadata metadata)
at Vertica.Data.Internal.ADO.Net.SDataReader.GetValue(Int32 ordinal, Boolean convertTypesForOutput)
at Vertica.Data.Internal.ADO.Net.SDataReader.get_Item(Int32 ordinal)
at Vertica.Data.Internal.ADO.Net.SDataReader.GetValues(Object[] values)
at System.Data.ProviderBase.DataReaderContainer.CommonLanguageSubsetDataReader.GetValues(Object[] values)
at System.Data.ProviderBase.SchemaMapping.LoadDataRow()
at System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping)
at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
at Connection.Run_Query(String sql, Connection_Types connection_type) in c:\Users\Documents\Visual Studio 2013\Projects\VerticaService.cs:line 55