| 1 | # ODBC Bridge Package |
| 2 | |
| 3 | ## Overview |
| 4 | |
| 5 | The `odbcbridge` package provides a reliable ODBC connection interface for Go applications, specifically designed to handle AS/400 and other enterprise database systems. It solves critical issues with existing Go ODBC drivers through a direct C bridge implementation. |
| 6 | |
| 7 | ## Why This Package Exists |
| 8 | |
| 9 | ### The Problem |
| 10 | |
| 11 | The popular `alexbrainman/odbc` Go driver has a critical bug where it doesn't properly clean up prepared statement handles when queries fail. This causes subsequent queries to fail with SQL0519 errors ("Prepared statement in use"), effectively breaking the connection until reconnection. |
| 12 | |
| 13 | ### The Solution |
| 14 | |
| 15 | This package implements a C-based ODBC bridge that: |
| 16 | |
| 17 | 1. **Always cleans up statement handles** - Even when queries fail |
| 18 | 2. **Prevents SQL0519 errors** - Proper statement lifecycle management |
| 19 | 3. **Optimizes performance** - Statement reuse with proper reset |
| 20 | 4. **Handles AS/400 quirks** - Negative row counts, special data types |
| 21 | |
| 22 | ## Features |
| 23 | |
| 24 | - **Reliable Statement Management**: Prevents SQL0519 errors through proper cleanup |
| 25 | - **Statement Reuse**: Pre-allocated statement handles for better performance |
| 26 | - **AS/400 Support**: Handles negative row counts and special behaviors |
| 27 | - **Type-Safe**: Proper handling of INT64, DOUBLE, STRING, and BINARY types |
| 28 | - **Context Support**: Full context.Context integration for cancellation |
| 29 | - **Thread-Safe**: Mutex protection for concurrent access |
| 30 | - **Error Recovery**: Automatic statement reset on errors |
| 31 | |
| 32 | ## Architecture |
| 33 | |
| 34 | ``` |
| 35 | Go Application |
| 36 | ↓ |
| 37 | connection.go (Go interface) |
| 38 | ↓ |
| 39 | CGO Bridge |
| 40 | ↓ |
| 41 | bridge.c (C implementation) |
| 42 | ↓ |
| 43 | ODBC Driver (unixODBC/iODBC) |
| 44 | ↓ |
| 45 | Database |
| 46 | ``` |
| 47 | |
| 48 | ## Usage |
| 49 | |
| 50 | ### Basic Connection |
| 51 | |
| 52 | ```go |
| 53 | import "github.com/netdata/netdata/go/plugins/plugin/ibm.d/pkg/odbcbridge" |
| 54 | |
| 55 | // Connect to database |
| 56 | conn, err := odbcbridge.ConnectOptimized(dsn) |
| 57 | if err != nil { |
| 58 | return err |
| 59 | } |
| 60 | defer conn.Close() |
| 61 | |
| 62 | // Execute query |
| 63 | rows, err := conn.QueryContext(ctx, "SELECT * FROM QSYS2.SYSTEM_STATUS_INFO") |
| 64 | if err != nil { |
| 65 | return err |
| 66 | } |
| 67 | defer rows.Close() |
| 68 | |
| 69 | // Process results |
| 70 | columns := rows.Columns() |
| 71 | values := make([]driver.Value, len(columns)) |
| 72 | |
| 73 | for rows.Next(values) == nil { |
| 74 | // Process row |
| 75 | for i, v := range values { |
| 76 | fmt.Printf("%s: %v\n", columns[i], v) |
| 77 | } |
| 78 | } |
| 79 | ``` |
| 80 | |
| 81 | ### Prepared Statements |
| 82 | |
| 83 | ```go |
| 84 | // Prepare statement |
| 85 | stmt, err := conn.PrepareContext(ctx, "SELECT * FROM TABLE WHERE ID = ?") |
| 86 | if err != nil { |
| 87 | return err |
| 88 | } |
| 89 | defer stmt.Close() |
| 90 | |
| 91 | // Execute multiple times |
| 92 | for _, id := range ids { |
| 93 | rows, err := stmt.Execute() |
| 94 | if err != nil { |
| 95 | continue // Safe - no SQL0519! |
| 96 | } |
| 97 | // Process rows... |
| 98 | rows.Close() |
| 99 | } |
| 100 | ``` |
| 101 | |
| 102 | ### Type-Safe Scanning |
| 103 | |
| 104 | ```go |
| 105 | var ( |
| 106 | name string |
| 107 | count int64 |
| 108 | ratio float64 |
| 109 | ) |
| 110 | |
| 111 | err := rows.ScanTyped(&name, &count, &ratio) |
| 112 | if err != nil { |
| 113 | return err |
| 114 | } |
| 115 | ``` |
| 116 | |
| 117 | ## Implementation Details |
| 118 | |
| 119 | ### Statement Lifecycle |
| 120 | |
| 121 | 1. **Connection**: Pre-allocates a statement handle for reuse |
| 122 | 2. **Query Execution**: Resets statement if needed, executes query |
| 123 | 3. **Error Handling**: Always resets statement on error to prevent SQL0519 |
| 124 | 4. **Cursor Management**: Properly closes cursors between queries |
| 125 | 5. **Cleanup**: Frees all resources on connection close |
| 126 | |
| 127 | ### Data Type Handling |
| 128 | |
| 129 | The bridge automatically detects SQL types and converts them appropriately: |
| 130 | |
| 131 | - `SQL_INTEGER`, `SQL_BIGINT` → `int64` |
| 132 | - `SQL_FLOAT`, `SQL_DOUBLE`, `SQL_DECIMAL` → `float64` |
| 133 | - `SQL_CHAR`, `SQL_VARCHAR` → `string` |
| 134 | - `SQL_BINARY`, `SQL_VARBINARY` → `[]byte` |
| 135 | |
| 136 | ### AS/400 Specific Handling |
| 137 | |
| 138 | - **Negative Row Counts**: Returns `int64` (not unsigned) to handle AS/400's negative row counts |
| 139 | - **String Conversions**: Handles EBCDIC conversions through the ODBC driver |
| 140 | - **Special SQL Types**: Proper handling of AS/400-specific data types |
| 141 | |
| 142 | ## Performance Considerations |
| 143 | |
| 144 | 1. **Statement Reuse**: Pre-allocated statement handles reduce allocation overhead |
| 145 | 2. **Buffer Management**: Reuses internal buffers for column data |
| 146 | 3. **Minimal CGO Calls**: Batches operations where possible |
| 147 | 4. **Connection Pooling**: Designed to work with connection pools |
| 148 | |
| 149 | ## Error Handling |
| 150 | |
| 151 | The bridge provides detailed error information: |
| 152 | |
| 153 | ```go |
| 154 | rows, err := conn.QueryContext(ctx, query) |
| 155 | if err != nil { |
| 156 | // Error includes SQLSTATE and native error codes |
| 157 | // Example: "SQLExecDirect: 42S02:1:-204:[IBM][System i Access ODBC Driver] |
| 158 | // [DB2 for i5/OS]SQL0204 - INVALID_TABLE in QSYS2 type *FILE not found." |
| 159 | log.Printf("Query failed: %v", err) |
| 160 | } |
| 161 | ``` |
| 162 | |
| 163 | ## Building |
| 164 | |
| 165 | Requires: |
| 166 | - C compiler (gcc/clang) |
| 167 | - ODBC development headers (`unixodbc-dev` on Ubuntu/Debian) |
| 168 | - CGO enabled |
| 169 | |
| 170 | ```bash |
| 171 | # Install dependencies (Ubuntu/Debian) |
| 172 | sudo apt-get install unixodbc-dev |
| 173 | |
| 174 | # Build |
| 175 | go build -tags cgo |
| 176 | ``` |
| 177 | |
| 178 | ## Testing |
| 179 | |
| 180 | ```go |
| 181 | // Run tests |
| 182 | go test -v ./... |
| 183 | |
| 184 | // Test with specific DSN |
| 185 | DSN="Driver={IBM i Access ODBC Driver};System=pub400.com;..." go test -v |
| 186 | ``` |
| 187 | |
| 188 | ## Comparison with alexbrainman/odbc |
| 189 | |
| 190 | | Feature | alexbrainman/odbc | odbcbridge | |
| 191 | |---------|-------------------|------------| |
| 192 | | SQL0519 Prevention | ❌ Bug causes SQL0519 | ✅ Proper cleanup | |
| 193 | | Statement Reuse | ❌ Creates new each time | ✅ Optimized reuse | |
| 194 | | AS/400 Row Counts | ❌ uint64 (wrong for negative) | ✅ int64 (correct) | |
| 195 | | Error Recovery | ❌ Connection unusable | ✅ Auto-recovery | |
| 196 | | Memory Leaks | ❌ Known issues | ✅ Proper cleanup | |
| 197 | | Context Support | ✅ Yes | ✅ Yes | |
| 198 | | Type Safety | ⚠️ Limited | ✅ Full type info | |
| 199 | |
| 200 | ## License |
| 201 | |
| 202 | Same as Netdata (GPL-3.0-or-later) |