expected ... arguments, got ... when using db.Prepare()

[AndreKR]

Observed

The program below creates a table and tries to insert two rows into it. The first row is inserted using db.Exec() the second one using stmt, err := db.Prepare() + stmt.Exec().

The insert using db.Exec() works, the insert using stmt.Exec() returns an error: expected 3 arguments, got 2

Expected behaviour

I'd expect both inserts to work identically.

Code example

package main

import (
	"database/sql"
	"log"

	_ "github.com/ClickHouse/clickhouse-go/v2"
)

func main() {
	dsn := "https://clickhouse:443/test?secure=true&username=user&password=pass&debug=true"

	db, err := sql.Open("clickhouse", dsn)
	if err != nil {
		log.Fatalf("failed to connect: %v", err)
	}

	_, err = db.Exec(`DROP TABLE example`)
	if err != nil {
		log.Fatalf("failed to drop table: %v", err)
	}

	_, err = db.Exec(`CREATE TABLE example (
		  a String,
		  b Nullable(String),
		  c Nullable(String)
		) ENGINE = MergeTree PRIMARY KEY a
	`)
	if err != nil {
		log.Fatalf("failed to create table: %v", err)
	}


	// This works
	_, err = db.Exec(`INSERT INTO example(a, c) VALUES('foo','bar')`)
	if err != nil {
		log.Fatalf("exec failed: %w", err)
	}

	// This does not
	stmt, err := db.Prepare(`INSERT INTO example(a, c) VALUES(?,?)`)
	if err != nil {
		log.Fatalf("prepare failed: %w", err)
	}
	_, err = stmt.Exec([]interface{} {"foo", "bar"}...)
	if err != nil {
		log.Fatalf("execution failed: %w", err)
	}
}

Error log

[clickhouse-std][conn=0][clickhouse:443] [batch][exec] append error: clickhouse [Append]:  clickhouse: expected 3 arguments, got 2
2025/01/31 03:54:10 execution failed: %!w(*proto.BlockError=&{Append 0xc000030250 })
exit status 1

Details

Environment

• clickhouse-go version: v2.30.1
• Interface: database/sql compatible driver
• Go version: go version go1.23.5 windows/amd64
• Operating system: Windows 10 Pro
• ClickHouse version: 24.12.3.47
• Is it a ClickHouse Cloud? No.

[SpencerTorres]

Hello! Thanks for submitting an issue.

For Prepare you don't need to provide the ?, just providing the column names is enough. The values are then provided per column in Exec. See the examples/std folder in this repo for more examples. You may also find that the native API is simpler to use than the stdlib sql interface (examples/clickhouse_api).

These lines have a good example (src):

batch, err := tx.PrepareContext(ctx, "INSERT INTO go_json_example (product)")
if err != nil {
	return err
}


insertProductString := "{\"id\":1234,\"name\":\"Book\",\"tags\":[\"library\",\"fiction\"]," +
	"\"pricing\":{\"price\":750,\"currency\":\"usd\"},\"metadata\":{\"page_count\":852,\"region\":\"us\"}," +
	"\"created_at\":\"2024-12-19T11:20:04.146Z\"}"


if _, err = batch.ExecContext(ctx, insertProductString); err != nil {
	return err
}

These are the Context variations of those functions, but it should behave the same. Notice how the INSERT only provides the columns, and then the ExecContext provides the value.

Let me know if you're still having trouble, otherwise feel free to close this issue. Thanks!

[AndreKR]

I'm not sure if I did it correctly because the example only has one column while I have multiple.

I adjusted my code like this:

	stmt, err := db.Prepare(`INSERT INTO example(a, c)`)
	if err != nil {
		log.Fatalf("prepare failed: %w", err)
	}
	_, err = stmt.Exec("foo", "bar")
	if err != nil {
		log.Fatalf("execution failed: %w", err)
	}

To make it even closer to the example I also tried using a context and a transaction:

	ctx := context.Background()
	
	tx, err := db.BeginTx(ctx, nil)
	if err != nil {
		log.Fatalf("begin failed: %w", err)
	}
	
	batch, err := tx.PrepareContext(ctx, `INSERT INTO example(a, c)`)
	if err != nil {
		log.Fatalf("prepare failed: %w", err)
	}
	
	_, err = batch.ExecContext(ctx, "foo", "bar")
	if err != nil {
		log.Fatalf("execution failed: %w", err)
	}
	
	err = tx.Commit()
	if err != nil {
		log.Fatalf("commit failed: %w", err)
	}

But in both cases I still get the same error:

append error: clickhouse [Append]:  clickhouse: expected 3 arguments, got 2