Not all rows are inserted when reusing the same prepared statement

[Polyaninartyom]

When inserting multiple times using a prepared statement, the number of inserted rows is limited to the minimum number in the batch. For example, if at some point the number of rows was 5, then subsequent executions will only insert a maximum of 5 rows, regardless of the actual number.

Code example:

package main

import (
    "database/sql"
    "fmt"

    _ "github.com/sijms/go-ora/v2"
)

func main() {
    db, err := getDB()
    if err != nil {
        panic(fmt.Errorf("failed to connect to db: %w", err))
    }

    stmt, err := db.Prepare("INSERT INTO TEMP_TABLE (ID) VALUES (:1)")
    if err != nil {
        panic(fmt.Errorf("error preparing statement: %w", err))
    }

    // 10 rows
    fmt.Println("Batch 1")
    if err = insertRows(stmt, generateRows(1, 10)); err != nil {
        panic(fmt.Errorf("error inserting rows: %w", err))
    }

    // 10 rows
    fmt.Println("Batch 2")
    if err = insertRows(stmt, generateRows(11, 10)); err != nil {
        panic(fmt.Errorf("error inserting rows: %w", err))
    }

    // 5 rows
    fmt.Println("Batch 3")
    if err = insertRows(stmt, generateRows(21, 5)); err != nil {
        panic(fmt.Errorf("error inserting rows: %w", err))
    }

    // 10 rows
    fmt.Println("Batch 4")
    if err = insertRows(stmt, generateRows(26, 10)); err != nil {
        panic(fmt.Errorf("error inserting rows: %w", err))
    }

    // 10 rows
    fmt.Println("Batch 5")
    if err = insertRows(stmt, generateRows(36, 10)); err != nil {
        panic(fmt.Errorf("error inserting rows: %w", err))
    }
}

func generateRows(startIndex, count int) []string {
    rows := make([]string, count)

    for i := 0; i < count; i++ {
        rows[i] = fmt.Sprintf("ID%02d", i+startIndex)
    }

    return rows
}

func insertRows(stmt *sql.Stmt, rows []string) error {
    fmt.Printf("Actual number of rows: %v\n", len(rows))

    result, err := stmt.Exec(rows)
    if err != nil {
        return err
    }

    inserted, err := result.RowsAffected()
    if err != nil {
        return err
    }

    fmt.Printf("Inserted number of rows: %v\n", inserted)

    return nil
}

Output:

Batch 1
Actual number of rows: 10
Inserted number of rows: 10

Batch 2
Actual number of rows: 10
Inserted number of rows: 10

Batch 3
Actual number of rows: 5
Inserted number of rows: 5

Batch 4
Actual number of rows: 10
Inserted number of rows: 5 <-- must be 10

Batch 5
Actual number of rows: 10
Inserted number of rows: 5 <-- must be 10

Results in DB:

Batch 1: ID01 ID02 ID03 ID04 ID05 ID06 ID07 ID08 ID09 ID10
Batch 2: ID11 ID12 ID13 ID14 ID15 ID16 ID17 ID18 ID19 ID20
Batch 3: ID21 ID22 ID23 ID24 ID25
Batch 4: ID26 ID27 ID28 ID29 ID30 <-- 5 rows lost
Batch 5: ID36 ID37 ID38 ID39 ID40 <-- 5 rows lost

The problem is also present when working with transactions using Tx.Stmt().

[sijms]

fixed in next release

[sijms]

fixed in v2.7.23