GO SQL.CONN always gets stuck in conn.Close()

Question

In go I ran into the issue that the connection pool of sql.DB pilled up so many connections it reached the limit of the odbc driver. The connections should be closed automatically but this never happens.

So I wanted to see what happens when I specifically ask for a connection, do my queries and close it in the end to give it back to the pool. The following code gets stuck at the line err = conn.Close() at the end. Can someone figure out why?

package main

import (
    "database/sql"
    "log"

    "github.com/gin-gonic/gin"
)

var db *sql.DB

func main() {
    db, err := sql.Open("odbc", "DSN=ADSconnStr")
    if err != nil {
        log.Fatal(err)
        return
    }
    
    err = db.Ping()
    if err != nil {
        log.Fatal(err)
        return
    }
    
    router := gin.Default()
    router.GET("test/url", testQuery)
    log.Fatal(router.Run(":8080"))

    err = db.Close()
    if err != nil {
        log.Fatal(err)
    }
}

func testQuery(c *gin.Context) {

    ctx := c.Copy()
    conn, err := db.Conn(ctx)
    if err != nil {
        log.Fatal(err)
        return
    }

    rows, err := conn.QueryContext(ctx, "SELECT * FROM testTable;")
    if err != nil {
        log.Fatal(err)
        return
    }

    // scan results ...

    err = rows.Close()
    if err != nil {
        log.Fatal(err)
        return
    }

    // here it gets stuck. conn.Close never returns
    err = conn.Close()
    if err != nil {
        log.Fatal(err)
        return
    }
}

Answer 1

I found the issue:

In sql.Open("odbc", "DSN=ADSconnStr") I establish a connection to an old sql database management system (Advantage Database Server). This DBMS has this "feature" where it fiercely lets you not disconnect as long as there are any (cached?) objects remaining. Namely >prepared Statements<. They must be closed.

The following code works. It is possible to shorten it (see below) but I explicitly wanted to close the connection in this example.

func testADS(c *gin.Context) {
    conn, err := db.Conn(context.Background())
    if err != nil {
        log.Panic("no connection")
    }

    tx, err := conn.BeginTx(context.Background(), nil)
    if err != nil {
        log.Panic("no transaction")
    }

    stmt, err := tx.Prepare("SELECT some_code FROM testTable")
    if err != nil {
        log.Panic("no prepared statement")
    }

    rows, err := stmt.Query()
    if err != nil {
        log.Panic("no rows")
    }

    if !rows.Next() {
        log.Panic("no next row")
    }

    var code string
    err = rows.Scan(&code)
    if err != nil {
        log.Panic("no scan")
    }

    log.Printf("Code: %s", code)

    err = tx.Commit()
    if err != nil {
        log.Panic("no commit")
    }

    err = rows.Close()
    if err != nil {
        log.Panic("no rows close")
    }

    err = stmt.Close()
    if err != nil {
        log.Panic("no stmt close")
    }

    // now that the statement is closed the connection can be closed finally 
    err = conn.Close()
    if err != nil {
        log.Panic("no conn close")
    }

    // log.Celebrate("Yay :)") 
    c.Next()
}

How to shorten the code?

Create the prepared statement with stmt, err := db.Prepare(...). Now you can remove everything related to the transaction tx and connection conn. Code still works.