Skip to main content

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

  • July 16, 2021
  • 7 replies
  • 7 views
kamil_kliczbor

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

    7 replies

    Jim_Knicely
    Forum|alt.badge.img+2
    • Jim_Knicely
    • Participating Frequently
    • July 16, 2021

    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)
    bysza
    • bysza
    • New Participant
    • July 16, 2021

    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_Knicely
    Forum|alt.badge.img+2
    • Jim_Knicely
    • Participating Frequently
    • July 17, 2021

    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";

    bysza
    • bysza
    • New Participant
    • July 17, 2021
    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.
    kamil_kliczbor
    • kamil_kliczbor
    • Author
    • Participating Frequently
    • July 19, 2021

    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.

    bysza
    • bysza
    • New Participant
    • July 19, 2021
    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/> ;)
    kamil_kliczbor
    • kamil_kliczbor
    • Author
    • Participating Frequently
    • July 20, 2021

    @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

    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement