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

kamil_kliczborkamil_kliczbor Community Edition User
edited July 2021 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 - Select Field - Administrator
    edited July 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)
    
    
  • byszabysza Community Edition User

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

  • byszabysza Community Edition User
    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_kliczborkamil_kliczbor Community Edition User

    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.

  • byszabysza Community Edition User
    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 Vertica Employee 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 Vertica Employee Employee

    Hi @bysza,

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

  • kamil_kliczborkamil_kliczbor Community Edition User

    @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 Vertica Employee Employee

    Hi @kamil_kliczbor,

    Updated that post as well.

    Best regards,

  • HibikiHibiki Vertica Employee Employee

    Additional information:

    TimeSpan Constructor
    TimeSpan(Int32, Int32, Int32, Int32, Int32)
    Initializes a new instance of the TimeSpan structure to a specified number of days, hours, minutes, seconds, and milliseconds.

    So the last argument should be 3 digits, not 6 digits as it is milliseconds.

Leave a Comment

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