(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!")