Cannot insert NULL in BLOB column

[xhit]

• os Version: Windows 10
• cli-driver Version: 11.1
• Db2 Server Version: 11.5.0.1077
• go Version: 1.14.2

go env Output:

$ go env
set GO111MODULE=
set GOARCH=amd64
set GOBIN=
set GOCACHE=C:\Users\sdelacruz\AppData\Local\go-build
set GOENV=C:\Users\sdelacruz\AppData\Roaming\go\env
set GOEXE=.exe
set GOFLAGS=
set GOHOSTARCH=amd64
set GOHOSTOS=windows
set GOINSECURE=
set GONOPROXY=
set GONOSUMDB=
set GOOS=windows
set GOPATH=C:\Users\sdelacruz\Documents\go
set GOPRIVATE=
set GOPROXY=https://proxy.golang.org,direct
set GOROOT=C:\Users\sdelacruz\sdk\go1.14.2
set GOSUMDB=sum.golang.org
set GOTMPDIR=
set GOTOOLDIR=C:\Users\sdelacruz\sdk\go1.14.2\pkg\tool\windows_amd64
set GCCGO=gccgo
set AR=ar
set CC=gcc
set CXX=g++
set CGO_ENABLED=1
set GOMOD=C:\Users\sdelacruz\Documents\go\src\ibmdbissue73\go.mod
set CGO_CFLAGS=-g -O2
set CGO_CPPFLAGS=
set CGO_CXXFLAGS=-g -O2
set CGO_FFLAGS=-g -O2
set CGO_LDFLAGS=-g -O2
set PKG_CONFIG=pkg-config
set GOGCCFLAGS=-m64 -mthreads -fmessage-length=0 -fdebug-prefix-map=C:\Users\SDELAC~1\AppData\Local\Temp\go-build440717174=/tmp/go-build -gno-record-gcc-switches

Steps to Reproduce:

Insert a nil in BLOB column. Code:

package main

import (
	"database/sql"
	"fmt"

	_ "github.com/ibmdb/go_ibm_db"
)

func main() {
	//connecto to DB
	db, err := connectDB2("localhost", "SAMPLE", "SCHEMATEST", "db2admin", "pass", 50000)
	if err != nil {
		panic(err)
	}

	defer db.Close()

	queryDrop := `drop table "issue80";`

	db.Exec(queryDrop)

	queryCreate := `CREATE TABLE "issue80" ("blob_column" BLOB(50));`

	_, err = db.Exec(queryCreate)
	if err != nil {
		panic(err)
	}

	defer db.Exec(queryDrop)

	queryInsert := `insert into "issue80" ("blob_column") values (?);‪`

	_, err = db.Exec(queryInsert, nil)
	if err != nil {
		panic(err)
	}
}

//connect to db2 database
func connectDB2(host, database, schema, user, password string, port int) (*sql.DB, error) {

	//Password cannot contains ;
	connString := fmt.Sprintf("HOSTNAME=%s;DATABASE=%s;PORT=%d;UID=%s;PWD=%s", host, database, port, user, password)

	db, err := sql.Open("go_ibm_db", connString)

	if err != nil {
		return nil, err
	}

	err = db.Ping()

	if err != nil {
		return nil, err
	}

	db.SetMaxOpenConns(1)
	db.SetMaxIdleConns(1)

	//set the schema
	if len(schema) > 0 {
		_, err = db.Exec("SET CURRENT SCHEMA ?", schema)

		if err != nil {
			return nil, err
		}
	}

	return db, nil
}

Error returned: SQLRowCount: {HY010} [IBM][CLI Driver] CLI0125E Function sequence error. SQLSTATE=HY010

[xhit]

For documentation:

I test all datatypes listed here: https://www.ibm.com/support/knowledgecenter/en/SSEPGG_10.1.0/com.ibm.db2.luw.apdv.cli.doc/doc/r0000526.html

Except (for obvious reasons):

• CURSOR
• BLOB LOCATOR
• CLOB LOCATOR
• ROW
• TIMESTAMP WITH TIMEZONE

And the result is, with this driver is not possible to insert NULL (Go nil) in followed datatypes:

• VARBINARY
• BLOB
• BINARY

The error is the same: SQLRowCount: {HY010} [IBM][CLI Driver] CLI0125E Function sequence error. SQLSTATE=HY010

The only way to insert NULL in these types is not using a prepared statement.

[akhilravuri1]

Hi @xhit
Thanks for the sample program.

So, If the user passes nil as a parameter value NULL should be inserted.

When you are inserting NULL without prepared statement means are you passing NULL or nil?

Thanks,
Akhil

[xhit]

So, If the user passes nil as a parameter value NULL should be inserted.

Not for BLOB, VARBINARY and BINARY. PR #81 solve this.

When you are inserting NULL without prepared statement means are you passing NULL or nil?

Without prepared statement works like expected:

insert into "issue80" ("blob_column") values (NULL);