(duckdb ODBC) Cannot Insert INTEGER

[sdmcallister]

I can insert and select strings / varchar.

If the integer is hardcoded like "INSERT INTO person (name, age) VALUES (?, 30)" I can insert successfully and I can scan into a Go int.

The database is in :memory:.

Any ideas why would it be trying to insert a null?

package main

import (
	"database/sql"
	"fmt"
	"log"

	_ "github.com/alexbrainman/odbc" // Import the ODBC driver
)

func main() {
	// Set up connection string
	connString := "DSN=ddb;"

	// Connect to the database
	db, err := sql.Open("odbc", connString)
	if err != nil {
		log.Fatal("Error connecting to database:", err)
	}
	defer db.Close()

	// Ping the database to verify connection
	err = db.Ping()
	if err != nil {
		log.Fatal("Error pinging database:", err)
	}

	fmt.Println("Connected to database!")

	_, err = db.Exec("CREATE TABLE person (name VARCHAR(255), age INT NOT NULL)")
	if err != nil {
		log.Fatal("Error creating table:", err)
	}
	fmt.Println("Table created successfully!")

	// Insert sample values
	_, err = db.Exec("INSERT INTO person (name, age) VALUES (?, 30)", "John")
	if err != nil {
		log.Fatal("Error inserting values 30:", err)
	}
	fmt.Println("This works; inserted successfully!")

	insertStmt, err := db.Prepare("INSERT INTO person (name, age) VALUES (?, ?)")
	if err != nil {
		log.Fatal("Error preparing insert statement:", err)
	}
	defer insertStmt.Close()
// this below fails
	_, err = insertStmt.Exec("Alice", 25)
	if err != nil {
		log.Fatal("Error inserting values:", err)
	}
	fmt.Println("Sample values inserted successfully!")

	// Select the first value
	var name string
	var age int
	err = db.QueryRow("SELECT name, age FROM person LIMIT 1").Scan(&name, &age)
	if err != nil {
		log.Fatal("Error retrieving data:", err)
	}

	fmt.Printf("First person: Name=%s, Age=%d\n", name, age)
}

I get

go run .
Connected to database!
Table created successfully!
2024/04/23 16:34:30 Error inserting values:SQLExecute: {HY000} ODBC_DuckDB->SingleExecuteStmt
Constraint Error: NOT NULL constraint failed: person.age
exit status 1

[sdmcallister]

Just saw #186

[alexbrainman]

Just saw #186

Yes. It could be the problem with your duckdb driver.

But you could try to debug github.com/alexbrainman/odbc code before you blame duckdb driver code.

Unfortunately I don't have time to debug this myself.

Alex

[sdmcallister]

Floats seem to work (salary is a DOUBLE).

	// Insert sample values
	_, err = db.Exec("INSERT INTO person (name, age, salary) VALUES (?, 30, ?)", "John", 20.11)
	if err != nil {
		log.Fatal("Error inserting values:", err)
	}
	fmt.Println("This works; inserted successfully!")