Inserting data, how?

[rogeralsing]

I'm stuck on the most trivial aspect of just inserting data and I don't find anything in the docs that helps me with this

The calling code:

var span = new Span
{
    timestamp = DateTimeOffset.Now,
    trace_id = "1234567890",
    model = "test"
};
await InsertSpan(connection, span);

The entity:

[PublicAPI]
public class Span
{
    public DateTimeOffset timestamp { get; set; }
    public string trace_id { get; set; }
    public string model { get; set; }
}

The code to insert an entity:

async Task InsertSpan(IDbConnection conn, Span span)
{
    var sqlInsert = 
"""
INSERT
INTO spans (timestamp, trace_id, model) 
VALUES (@timestamp, @trace_id, @model)
""";
    var res = await conn.ExecuteAsync(sqlInsert, span);
    Console.WriteLine(res);
}

It throws with the following exception:

Unhandled exception. ClickHouse.Client.ClickHouseServerException (0x0000003E): Code: 62. DB::Exception: Cannot parse expression of type DateTime64(9) here: @timestamp, @trace_id, @model): While executing ValuesBlockInputFormat. (SYNTAX_ERROR) (version 23.2.1.2537 (official build))

Is it a me problem? is it a library problem? is it a Clickhouse issue?

[wahmedswl]

@rogeralsing This can be due to this documented behavior https://github.com/DarkWanderer/ClickHouse.Client/wiki/ORM-support#dapper.

[rogeralsing]

Thanks @wahmedswl , I have it working now 👍🏻
One thing I wish for here, is better error messages, Clickhouse itself seems to be pretty weak in this area, and the client lib isn't super clear either here.

If anon objects are not supported, it would be pretty neat with an exception pointing this out and even linking to the docs.
just random errors like "failed to parse datetime" can be a real timesink when you don't quite know what you are looking for.

[wahmedswl]

@rogeralsing Yes, you are right. Can you contribute this? Might be @DarkWanderer has some plans to support anonymous objects?

[DarkWanderer]

Hi

Sorry for late response. No, I do not plan to support anonymous objects directly in the library. However, I do plan to maintain support for Dapper, and Dapper.Contrib has support for insertion - so this is on the table

Meanwhile, data insertion can be done in the "plain SQL" way:

ClickHouse.Client/ClickHouse.Client.Tests/ParameterizedInsertTests.cs

Lines 12 to 25 in c9ad6c5

	[Test]
	public async Task ShouldInsertParameterizedFloat64Array()
	{
	await connection.ExecuteStatementAsync("TRUNCATE TABLE IF EXISTS test.float_array");
	await connection.ExecuteStatementAsync("CREATE TABLE IF NOT EXISTS test.float_array (arr Array(Float64)) ENGINE TinyLog");
	var command = connection.CreateCommand();
	command.AddParameter("values", new[] { 1.0, 2.0, 3.0 });
	command.CommandText = "INSERT INTO test.float_array VALUES ({values:Array(Float32)})";
	await command.ExecuteNonQueryAsync();
	var count = await connection.ExecuteScalarAsync("SELECT COUNT(*) FROM test.float_array");
	Assert.AreEqual(1, count);
	}