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

How to pass ADO.NET VerticaType.Time and avoid IndexOutOfRangeException ?

edited July 16 in General Discussion

My question relates to the post https://forum.vertica.com/discussion/242318/net-5-ado-net-client-wrong-result-for-coalesce/p1?new=1

How can I pass correctly Time value to the query?

I tried:

[Test]
public async Task TestDirectlyFromConnectionTimeAsTimeSpan()
{
await using var connection = VerticaHelper.OpenLocalVerticaDatabaseConnection();
await using var command = connection.CreateCommand();
command.CommandText = $"SELECT COALESCE(@a, @b) AS \"exp\"";
command.Parameters.Add(new VerticaParameter("a", VerticaType.Time, new TimeSpan(0, 13,53,54, 814255)));
command.Parameters.Add(new VerticaParameter("b", VerticaType.Time, new TimeSpan(0, 13, 51, 54, 814255)));

        DbDataReader reader = await command.ExecuteReaderAsync();
        while (await reader.ReadAsync())
        {
            var value = reader.GetValue(0);
            value.Should().Be(new TimeSpan(0, 13, 53, 54, 814255));
        }
    }

For this case I receive the following exception:
System.IndexOutOfRangeException : Index was outside the bounds of the array.
at Vertica.Data.Internal.ADO.Net.TimeSpanConverter.FromTimeSpan(TimeSpan sourceData, TypeMetadata targetMetadata)
at Vertica.Data.Internal.ADO.Net.SCommand.GetExecutionContextInputs(IList1 targetMetadataList, SParameterCollection paramCollection, IDictionary2 metadataToParam)
at Vertica.Data.Internal.ADO.Net.SCommand.ExecuteReader(CommandBehavior behavior)
at Vertica.Data.Internal.ADO.Net.SCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)

    [Test]
    public async Task TestDirectlyFromConnectionTimeAsString()
    {
        await using var connection = VerticaHelper.OpenLocalVerticaDatabaseConnection();
        await using var command = connection.CreateCommand();
        command.CommandText = $"SELECT COALESCE(@a::time(6), @b::time(6)) AS \"exp\"";
        command.Parameters.Add(new VerticaParameter("a", VerticaType.Time, "13:53:54.814255"));
        command.Parameters.Add(new VerticaParameter("b", VerticaType.Time, "13:51:54.814255"));

        DbDataReader reader = await command.ExecuteReaderAsync();
        // GENERATED QUERY: SELECT COALESCE('13:53:54.814255'::time(6), '13:51:54.814255'::time(6)) AS "exp";
        while (await reader.ReadAsync())
        {
            //Returns datetime {01.01.0001 13:53:54}
            var value = reader.GetValue(0);
            value.Should().Be(new TimeSpan(0, 13, 53, 54, 814255));
        }
    }

For this one - the returned value is DateTime, and additionally is trimmed.

kind regards,
Kamil

Comments

  • Jim_KnicelyJim_Knicely Administrator
    edited July 16

    What's wrong with the second example? That seems to work.

    dbadmin=> CREATE TABLE ado AS SELECT COALESCE('13:53:54.814255'::time(6), '13:51:54.814255'::time(6)) AS "exp";
    CREATE TABLE
    
    dbadmin=> \x
    Expanded display is off.
    
    dbadmin=> \d ado
                                    List of Fields by Tables
     Schema | Table | Column | Type | Size | Default | Not Null | Primary Key | Foreign Key
    --------+-------+--------+------+------+---------+----------+-------------+-------------
     public | ado   | exp    | time |    8 |         | f        | f           |
    (1 row)
    
    
  • Jim, it looks that you're directly connected to Vertica using dbadmin, while Kamil uses Vertica ado.net provider as an intermediary.
    Vertica works ok, the problem is with the ado.net provider and its handling of the Time-typed parameter with Coalesce().

  • Jim_KnicelyJim_Knicely Administrator

    Right, but you said that in option number 2, the SQL that ran against the DB was:

    SELECT COALESCE('13:53:54.814255'::time(6), '13:51:54.814255'::time(6)) AS "exp";

  • Yes, the SQL is correct and result from Vertica is correct. The problem is with Ado.net driver incorrectly interpreting the result and returning the data as trimmed Datetime.
  • Dear @Jim_Knicely,
    I think it is a general problem of the ADO.NET provider to pass the correct parameter type to the command when we specify VerticaType and equivalent type value. I also found that problem was already identified a few years ago: see https://forum.vertica.com/discussion/234326/vertica-net-driver-does-not-respect-verticaparameter-type.

  • I would also add an off-topic that lack of BulkInsert support in Ado.net driver while jdbc driver supports it, looks like a <joke>negative discrimination against the dotnet community/> ;)
  • HibikiHibiki Employee

    Hi @kamil_kliczbor,

    Can you accept the following sample code?

    [Test]
    public async Task TestDirectlyFromConnectionTimeAsTimeSpan()
    {
        await using var connection = VerticaHelper.OpenLocalVerticaDatabaseConnection();
        await using var command = connection.CreateCommand();
        command.CommandText = $"SELECT COALESCE(@a, @b) AS \"exp\"";
        command.Parameters.Add(new VerticaParameter("a", VerticaType.VarChar, "0 13:53:54.814255"));
        command.Parameters.Add(new VerticaParameter("b", VerticaType.VarChar, "0 13:51:54.814255"));
    
        DbDataReader reader = await command.ExecuteReaderAsync();
        while (await reader.ReadAsync())
        {
            var value = reader.GetString(0);
            value.Should().Be("0 13:53:54.814255");
        }
    }
    

    Of course, you can change the part of the query to the following:

    command.CommandText = "SELECT COALESCE(@a::INTERVAL DAY TO SECOND, @b::INTERVAL DAY TO SECOND) AS \"exp\"";
    
  • HibikiHibiki Employee

    Hi @bysza,

    Does "BulkInsert" mean the batch insert described on the following page?
    Using Batch Inserts and Prepared Statements

  • @Hibiki said:
    Hi @kamil_kliczbor,

    Can you accept the following sample code?

    [Test]
    public async Task TestDirectlyFromConnectionTimeAsTimeSpan()
    {
        await using var connection = VerticaHelper.OpenLocalVerticaDatabaseConnection();
        await using var command = connection.CreateCommand();
        command.CommandText = $"SELECT COALESCE(@a, @b) AS \"exp\"";
        command.Parameters.Add(new VerticaParameter("a", VerticaType.VarChar, "0 13:53:54.814255"));
        command.Parameters.Add(new VerticaParameter("b", VerticaType.VarChar, "0 13:51:54.814255"));
    
        DbDataReader reader = await command.ExecuteReaderAsync();
        while (await reader.ReadAsync())
        {
            var value = reader.GetString(0);
            value.Should().Be("0 13:53:54.814255");
        }
    }
    

    Of course, you can change the part of the query to the following:

    command.CommandText = "SELECT COALESCE(@a::INTERVAL DAY TO SECOND, @b::INTERVAL DAY TO SECOND) AS \"exp\"";
    

    Hi, @Hibiki
    I think it is acceptable.
    Can you also can have a look at https://forum.vertica.com/discussion/242318/net-5-ado-net-client-wrong-result-for-coalesce/p1?new=1 ?

    thanks,
    Kamil

  • HibikiHibiki Employee

    Hi @kamil_kliczbor,

    Updated that post as well.

    Best regards,

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.