Hi, I apply your instruction for linux environment in dockerfile like that.

RUN go run /go/src/github.com/ibmdb/go_ibm_db/installer/setup.go
ENV DB2HOME /go/src/github.com/ibmdb/go_ibm_db/installer/clidriver
ENV CGO_CFLAGS -I$DB2HOME/include
ENV CGO_LDFLAGS -L$DB2HOME/lib
ENV LD_LIBRARY_PATH /go/src/github.com/ibmdb/go_ibm_db/installer/clidriver/lib

but I got this error when I build the image
/vendor/github.com/ibmdb/go_ibm_db/api
/usr/bin/ld: cannot find -ldb2
/usr/bin/ld: cannot find -ldb2
collect2: error: ld returned 1 exit status

Can you give me a solution to pass this error? Thanks

Hi,
I have a table T with a BOOLEAN column IS_ACTIVE. If i run SELECT IS_ACTIVE FROM T; I get "unsupported column type 16" error. INSERT works fine.
I tried running SELECT CAST(IS_ACTIVE AS BOOLEAN) FROM T; - that doesn't work either.
As a temporary fix i'm running SELECT CAST(IS_ACTIVE AS CHAR(1)) FROM T;.

I'm on go1.13, Windows. This issue doesn't reproduce via jdbc driver, so I assume it's go_ibm_db.

Select DB error font

data": {
"jobs": [
{
"id": 3,
"name": "N\ufffdng-l\ufffdm-ng\u001a nghi\u001ap"
},
{
"id": 9,
"name": "Thi\u001at k\u001a"
},
{
"id": 20,
"name": "Truy\u001an th\ufffdng - Marketing"
},
{
"id": 22,
"name": "IT-Ph\u001an m\u001am"
},
{
"id": 23,
"name": "IT-Ph\u001an C\u001ang"
},
{
"id": 25,
"name": "T\ufffdi ch\ufffdnh"
},

Dear team,

I am connecting to BigSQL with go_ibm_db. For now got an issue when executing sql 'CREATE SCHEMA XXX'
The code like,

tx, err := db.Begin()
	if err != nil {
		return err
	}
	defer tx.Rollback()

	//sql1
	sql := "CREATE SCHEMA \"" + schemaName + "\""

	_, err = tx.Exec(sql)

	if err != nil {
		log.Println(err.Error())
		return err
	}

	err = tx.Commit()
	if err != nil {
		return err
	}

As test result, I found,

1. If sql statement set to 'CREATE SCHEMA XXX', the first call got error ' [IBM][CLI Driver][DB2/LINUXX8664] SQL0142N The SQL statement is not supported.\n"', but a second same call immediately got successfully.
2. If sql statement set to 'CREATE SCHEMA XXX;'(with a ; at the end). This way, the sql always got successfully. Not any error returned, even there should be error returned.
   Such as, when creating a schema name to 'DD2', if the first run got successfully, the second run should get an error like, '[IBM][CLI Driver][DB2/LINUXX8664] SQL0601N The name of the object to be created is identical to the existing name "DD2" of type "SCHEMA". SQLSTATE=42710\n"'. But this way,(with a ; at the end of the sql), the call always got successful response.

Can you please advise how to identify or fix the issue? Thanks.

Is your feature request related to a problem? Please describe.
DB2 allows to insert or update a column CHAR () FOR BIT DATA with a string. This package only allows insert with arguments to this data type column a []byte. For projects with customizable input-output is a regression in performance to know if the string to pass to a DB is correct for the destination data type.

Describe the solution you'd like
Internally in the package when string is passed like argument to CHAR () FOR BIT DATA then convert to []byte.

Note: Only this data type. BINARY, VARBINARY, BLOB doesn't need this.

Note 2: In the project I found how deal with this. If this request can be realized with regression in performance for other data types then this request cannot be proceed.

err := db.Query("select itsdout from xxx")
itsdout column is clob

I have a stored procedure that has a user-defined array IN parameter. I am unable to pass multiple values when I call the stored procedure.

The stored procedure is:
CREATE PROCEDURE \"REST_TESTPROC66_1_0\" (IN @arrayValues INTARRAY)\nLANGUAGE SQL\nDYNAMIC RESULT SETS 1\nBEGIN\nDECLARE C1 CURSOR WITH RETURN FOR \n-- BEGIN SQL STATEMENT\nSELECT * FROM TEST2 WHERE PKCOL IN (SELECT T.i FROM UNNEST(@arrayValues) as T(i));\n-- END SQL STATEMENT\nOPEN C1;\nEND\n"

I was able to get output when I used the Db2 CLI using the following command:
db2 "call REST_TESTPROC66_1_0(array[1,2,3])

Hi team,

When i try to run go build in Travis.

I am receiving this error:

github.com/ibmdb/go_ibm_db/api

../../../../../pkg/mod/github.com/ibmdb/[email protected]/api/api.go:13:9: undefined: SQLSMALLINT
../../../../../pkg/mod/github.com/ibmdb/[email protected]/api/api.go:14:9: undefined: SQLUSMALLINT
../../../../../pkg/mod/github.com/ibmdb/[email protected]/api/api.go:15:9: undefined: SQLUSMALLINT

Seems api.go was used instead of api_unix.go.
Any idea how to fix this?

Thank you!

Hi, I am using dockerfile to build one application using go_ibm_db.

The application works well in my local mac, and I am using:
export DB2HOME=/Users/zac/go/src/github.com/ibmdb/go_ibm_db/installer/clidriver
export CGO_CFLAGS=-I$DB2HOME/include
export DYLD_LIBRARY_PATH=$DB2HOME/lib
export CGO_LDFLAGS=-L$DB2HOME/lib

however, I set these env in the dockerfile, and can not work well.
dockerfile:
ENV DB2HOME=/Users/zac/go/src/github.com/ibmdb/go_ibm_db/installer/clidriver
ENV CGO_CFLAGS=-I$DB2HOME/include
ENV DYLD_LIBRARY_PATH=$DB2HOME/lib
ENV CGO_LDFLAGS=-L$DB2HOME/lib

I print these env values out before I run the application in the dock images.
cd /go/src/demowithJS

echo $DB2HOME
echo $CGO_CFLAGS
echo $DYLD_LIBRARY_PATH
echo $CGO_LDFLAGS

bee run

There is the error:
zac$ docker run -it -p 8081:8081 ocvtest
/Users/zac/go/src/github.com/ibmdb/go_ibm_db/installer/clidriver
-I/Users/zac/go/src/github.com/ibmdb/go_ibm_db/installer/clidriver/include
/Users/zac/go/src/github.com/ibmdb/go_ibm_db/installer/clidriver/lib
-L/Users/zac/go/src/github.com/ibmdb/go_ibm_db/installer/clidriver/lib

| ___
| |/ / ___ ___
| ___ \ / _ \ / _
| |/ /| /| /
_/ _| __| v1.10.0
2019/12/10 02:16:52 INFO ▶ 0001 Using 'demowithJS' as 'appname'
2019/12/10 02:16:52 INFO ▶ 0002 Initializing watcher...
github.com/ibmdb/go_ibm_db/api
github.com/astaxie/beego/config
github.com/astaxie/beego/utils
github.com/astaxie/beego/vendor/gopkg.in/yaml.v2

github.com/ibmdb/go_ibm_db/api

../github.com/ibmdb/go_ibm_db/api/api_unix.go:12:11: fatal error: sqlcli1.h: No such file or directory
// #include <sqlcli1.h>
^~~~~~~~~~~
compilation terminated.

Please help us, thanks!

• os Version: Windows 10
• cli-driver Version: 11.1
• Db2 Server Version: 11.5.0.1077
• go Version: 1.13.5
• 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 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:\go
set GOSUMDB=sum.golang.org
set GOTMPDIR=
set GOTOOLDIR=c:\go\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\sdi\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-build970289982=/tmp/go-build -gno-record-gcc-switches

Steps to Reproduce:

1. Select a column with datatype XML

Run successfully in a normal directory such as home.But run the successful binaries in a Shared folder and report an error.
SQLDriverConnect: {HY000} [IBM][CLI Driver] SQL10007N Message "0" could not be retrieved. Reason code: "3".
SQLDriverConnect: {HY000} [IBM][CLI Driver] SQL10007N Message "0" could not be retrieved. Reason code: "3".

SQLDriverConnect: {HY000} [IBM][CLI Driver] SQL10007N Message "0" could not be retrieved. Reason code: "3".

panic: runtime error: invalid memory address or nil pointer dereference
[signal SIGSEGV: segmentation violation code=0x1 addr=0x0 pc=0x4a6a30]

When the security is set , for example, to Client, we must specify securityMechanism=4.

In JDBC this is done by setting "properties".

What is the equivalent with the go_ibm driver ?

Hi,
It seams that your library does not work with a DB2 iSeries (AS400).
Error message is : SQLDriverConnect: {08001} [IBM][CLI Driver] SQL30081N A communication error has been detected. Communication protocol being used: "TCP/IP". Communication API being used: "SOCKETS". Location where the error was detected: "<ip address>". Communication function detecting the error: "connect". Protocol specific error code(s): "110", "*", "*". SQLSTATE=08001

The database is firewalled, using these rules : https://render-prd-trops.events.ibm.com/support/pages/tcpip-ports-required-ibm-i-access-and-related-functions

Which parameters of the connectionstring may be missing ?

Hi,

I’m a newbie for golang. I followed the doc to install windows version. When I ran the first example, I got the error below. Could you please help me? Thank you very much.

goroutine 1 [running]:
github.com/ibmdb/go_ibm_db.NewError(0x4ee1c5, 0xe, 0x4c3c20, 0x5c6fe0, 0xc00005a780, 0xc00007bf58)
                C:/gospace/src/github.com/ibmdb/go_ibm_db/error.go:58 +0x54b
github.com/ibmdb/go_ibm_db.initDriver(0x0, 0x0)
                C:/gospace/src/github.com/ibmdb/go_ibm_db/driver.go:29 +0x1f9
github.com/ibmdb/go_ibm_db.init.0()
                C:/gospace/src/github.com/ibmdb/go_ibm_db/driver.go:53 +0x29
Process finished with exit code 2

Hi,

I am running into an issue when a table has a column type of CLOB or TIME. Ex:

queryString := "SELECT * FROM mytable;"
rows, err := db.Query(queryString)

CLOB Error:
ERRO[0479] unsupported column type -99

TIME Error:
ERRO[2544] unsupported column type 92

Is there a way to correctly read these values?

Thanks!

Hi,

Encountered an issue when creating a view which is a duplicate schema/name pair. The CREATE VIEW statement should fail as the view already exists within the database. Below is a sample program (Suppose there exists a table USER999.WEB_PAGE):

package main

import (
	"database/sql"
	"fmt"
	_ "github.com/ibmdb/go_ibm_db"
)

func main() {
	con := "HOSTNAME=<host>;PORT=<port>;DATABASE=<db>;UID=<username>;PWD=<password?"
	db, err := sql.Open("go_ibm_db", con)
	if err != nil {
		fmt.Println(err)

	}
	defer db.Close()
	
       createViewStmt := "CREATE VIEW \"USER999\".\"NATE_TEST\" as SELECT * from \"USER999\".\"WEB_PAGE\";"

       _, err := db.Exec(createViewStmt)
       if err == nil {
	    fmt.Println("err object is nil")
       } else {
	    fmt.Println("detected err", err)
       }

       _, err2 := db.Exec(createViewStmt)
       if err2 == nil {
	   fmt.Println("err2 object is nil")
       } else {
	   fmt.Println("detected err2", err2)
       }
}

The output is:
err object is nil
err2 object is nil

The first view is created correctly. However the 2nd execution of this statement should fail as this view already exists.

Hi all,

German umlaute are not converted correctly by the Scan() function in our environment. Everything else is working fine.

I don't know on which hardware our DB2 instances are running. Could be quite old UNIX, or new Linux.

When using the AQT client (http://www.querytool.com), all German umlaute are displayed correctly (but I don't know the encoding). When exporting from this tool to a CSV and loading to Visual Studio Code, the data is displayed correctly using the UTF-16LE encoding.

Is it possible that the character encoding of our DB2 instance is UTF-16LE whereas the driver is using plain UTF-16? Or big endianess? Sorry, I'm not the biggest expert in encodings and endianess.

If the issue could come from the driver using an encoding that does not fit our servers, is there a way to work around this? Can I convert the data somehow in my Go code?

All opinions are appreciated! Thanks a lot,

Jan

Hi,

I am trying to run a query which is selecting a uuid from a VARBINAR type column. I am reading the data into a byte slice and then converting it to a string. The issue is that some of the rows contain invalid utf-8 characters, so converting to a string returns a garbage result. See below:

Converting the byte slice to a uuid looks like it returns a correct result:

So my application needs to know the type of the column so it can convert it to a uuid. I am calling the ScanType() function, but it is always returning an empty interface.

Is this something that can be fixed at the driver level or is there a workaround for reading a uuid?

Thank you

When we developed API with go_ibm_db, we found the result in API was different from the value read from the database. Here is what we found.

The result from API is shown as the following. The field is_docker is '\u0000'

[
    {
        "agent_class": "F",
        "dscount": "0",
        "hostname": "dv-0.dv.zen.svc.cluster.local",
        "is_docker": "\u0000",
        "node_description": "Not specified",
        "node_name": "AdminNode",
        "os_user": "bigsql",
        "port": "6414"
    },
]

However, the value read from database directly should be the following.

The value of IS_DOCKER is 'N'

The code within the setup.go uses a very simplistic technique to determine whether or not to install the cli driver for Db2:

func main() {
...
	value, errDir := os.LookupEnv("DB2HOME")
	if errDir {
		if runtime.GOOS == "windows" {
			fmt.Println("clidriver is already present in this path ", value)
			fmt.Println("Please add this path to PATH environment variable")
			os.Exit(1)
		} else {
			fmt.Println("clidriver is already present in this path ", value)
			fmt.Println("Please set CGO_CFLAGS, CGO_LDFLAGS and LD_LIBRARY_PATH or DYLD_LIBRARY_PATH environment variables")
			os.Exit(1)

If I already have the environment variables set in my .profile, it will assume that everything is already installed and quits the installation. I was trying to update the driver to the latest version, and I can only do this if I remove all of the settings in .profile and basically restart the session. Any chance of having the code modified slightly so that it can prompt the user whether or not they really want to refresh the driver? The message is misleading since the entire directory had been deleted and recreated and the CLI libraries did not exist. This will also become an issue in the future when we update the CLI libraries and the process for doing this will be cumbersome if you rely on people removing their environment settings to refresh the code.

Hi Team,
We got one issue and the Go code returns error message,

2020/02/18 06:09:47 Serving dvapi at https://[::]:3300
time="2020-02-18T06:13:36Z" level=error msg="SQLDriverConnect: {HY507} [IBM][CLI Driver] CLI0157E  Error opening a file. SQLSTATE=HY507"
time="2020-02-18T06:13:36Z" level=error msg="SQLDriverConnect: {HY507} [IBM][CLI Driver] CLI0157E  Error opening a file. SQLSTATE=HY507"
time="2020-02-18T06:13:37Z" level=error msg="SQLDriverConnect: {HY507} [IBM][CLI Driver] CLI0157E  Error opening a file. SQLSTATE=HY507"
time="2020-02-18T06:13:43Z" level=error msg="SQLDriverConnect: {HY507} [IBM][CLI Driver] CLI0157E  Error opening a file. SQLSTATE=HY507"
time="2020-02-18T06:13:43Z" level=error msg="SQLDriverConnect: {HY507} [IBM][CLI Driver] CLI0157E  Error opening a file. SQLSTATE=HY507"

This is only occurred when the server startup. After a few minutes, it works well.
And it only happens in the test cluster(inside docker container), we could not re-produce in local dev env.
Is there any configuration to change the log level and print more log message?
Thanks.

Hi team,
Got one exception 'index out of range'. Can you please have a check?

Code sample:

sql := "call user666.setValue('DESCRIPTION', '" + nodeDesc + "', '" + nodeName + "', ?, ?);"

	var numUpdated int
	var diags string
	_, err = db.Exec(sql, numUpdated, diags)
	if err != nil {
		return "error", err
	}

Got exception in runtime,

2019/05/29 05:15:15 http: panic serving 127.0.0.1:33442: runtime error: index out of range
goroutine 32 [running]:
net/http.(*conn).serve.func1(0xc0000c4c80)
        /opt/go/src/net/http/server.go:1746 +0xd0
panic(0xa259a0, 0x1196eb0)
        /opt/go/src/runtime/panic.go:513 +0x1b9
github.com/ibmdb/go_ibm_db.(*Parameter).BindValue(0xc0000c28e8, 0xc000010001, 0x1, 0x9e9a00, 0x1202d60, 0x0, 0x0)
        /opt/gospace/src/github.com/ibmdb/go_ibm_db/param.go:56 +0x8f7
github.com/ibmdb/go_ibm_db.(*ODBCStmt).Exec(0xc0000c2910, 0xc0003cc6e0, 0x2, 0x2, 0x2, 0x9e9a00)
        /opt/gospace/src/github.com/ibmdb/go_ibm_db/odbcstmt.go:105 +0xb5
github.com/ibmdb/go_ibm_db.(*Stmt).Exec(0xc0001f6b10, 0xc0003cc6e0, 0x2, 0x2, 0x0, 0x0, 0x0, 0x0)
        /opt/gospace/src/github.com/ibmdb/go_ibm_db/stmt.go:62 +0xc9
database/sql.ctxDriverStmtExec(0xb9c1c0, 0xc00002e0b0, 0xb9c480, 0xc0001f6b10, 0xc0000c2960, 0x2, 0x2, 0x2, 0x2, 0x0, ...)
        /opt/go/src/database/sql/ctxutil.go:77 +0x16b
database/sql.resultFromStatement(0xb9c1c0, 0xc00002e0b0, 0xb9ad80, 0xc000562550, 0xc00065f420, 0xc00065f760, 0x2, 0x2, 0x0, 0x0, ...)
        /opt/go/src/database/sql/sql.go:2342 +0x13e
database/sql.(*DB).execDC(0xc000468180, 0xb9c1c0, 0xc00002e0b0, 0xc0003c8900, 0xc00065f598, 0xc0004ea4e0, 0x5c, 0xc00065f760, 0x2, 0x2, ...)
        /opt/go/src/database/sql/sql.go:1487 +0x2df
database/sql.(*DB).exec(0xc000468180, 0xb9c1c0, 0xc00002e0b0, 0xc0004ea4e0, 0x5c, 0xc00065f760, 0x2, 0x2, 0xad4f01, 0x9, ...)
        /opt/go/src/database/sql/sql.go:1448 +0x139

It seems that the fields in the statement were parsed incorrectly.
It happens in the current code level in master("ccf5f6964a2a66adeca163cd6a224f860fc23a41"), but the SP works well in commit "65246ebaf88e37ea97ba64aebc919e1c9fd1a06e".

Thank you.

I'm tryint to grant an user into a database but, is it possible that can not be created with the db.Exec() API? Just like it was happening at creating a database.

/*
[Shell]

db2 CREATE DATABASE demo_dev
net user demo demo /ADD
*/

package main

import (
	"database/sql"
	"fmt"
	"log"

	_ "github.com/ibmdb/go_ibm_db"
)

const (
	//service = "DB2"
	dbname = "demo_dev"

	instUser = "db2admin"
	instPass = "***"

	dbUser = "demo"
	dbPass = "demo"
)

func main() {
	db, err := connectDB()
	if err != nil {
		log.Fatal("Connect Error:\n" + err.Error())
	}
	if err = db.Ping(); err != nil {
		log.Fatal("Ping Error:\n" + err.Error())
	}
	defer db.Close()
	fmt.Println("Connected: ok")

	stmt := fmt.Sprintf("GRANT DBADM ON DATABASE TO %s;", dbUser)
	if _, err = db.Exec(stmt); err != nil {
		log.Fatal("Grant Error:\n" + err.Error())
	}
	fmt.Println("Grant: ok")
}

func connectDB() (*sql.DB, error) {
	driver := "go_ibm_db"
	connection := fmt.Sprintf(
		"Hostname=127.0.0.1;Protocol=tcpip;Port=50000;Database=%s;Uid=%s;Pwd=%s",
		dbname, instUser, instPass,
		//"Hostname=127.0.0.1;Protocol=tcpip;Port=50000;Database=%s;Uid=%s;Pwd=%s",
		//dbname, dbUser, dbPass,
	)
	return sql.Open(driver, connection)
}

SQLSTATE=42502

I can't execute any query and receive the following error:

SQLExecute: {42601} [IBM][CLI Driver][DB2] SQL0104N An unexpected token " " was found following "". Expected tokens may include: ". JOIN INNER LEFT RIGHT FULL CROSS , HAVING GROUP". SQLSTATE=42601

When using direct ODBC (github.com/alexbrainman/odbc), the query is executed as expected.

The sample program I'm trying to execute is this:
`package main

import (
"database/sql"
"fmt"

_ "github.com/ibmdb/go_ibm_db"

)

func main() {
var text string
db, err := connectDB()
if err != nil {
println("Connect Error:\n" + err.Error())
return
}
if err = db.Ping(); err != nil {
println("Ping Error:\n" + err.Error())
return
}
err = db.QueryRow("SELECT GETVARIABLE('SYSIBM.VERSION') FROM SYSIBM.SYSDUMMY1").Scan(&text)
if err != nil {
println("Query Error:\n" + err.Error())
return
}
println(text)
db.Close()
}

func connectDB() (banco *sql.DB, err error) {
var driver, connection string
driver = "go_ibm_db"
connection = fmt.Sprintf("DATABASE=%s;HOSTNAME=%s;PORT=%d;PROTOCOL=TCPIP;UID=%s;PWD=%s", "host", "ip", port, "user", "password")
return sql.Open(driver, connection)
}`

Does go_ibm_db support running the following CREATE OR REPLACE FUNCTIONS:

-------------------------------------------------------------------------------------
-- Storage Procedure for returning a substring of a string before a
-- specified number of delimiter occurs.
--   Parameters
--   in - The original string
--   delimiter - The delimiter to search for
--   n - The number of times to search for the delimiter.
--       Can be both a positive or negative number. If it is a positive number,
--       this function returns all to the left of the delimiter. If it is a negative
--       number, this function returns all to the right of the delimiter.
-------------------------------------------------------------------------------------

--#SET TERMINATOR ;;
CREATE OR REPLACE FUNCTION SUBSTRING_INDEX(in varchar(2000), delimit varchar(200), n Int)
                  returns varchar(2000)
                  deterministic
                    no external action
                    language sql
                    contains sql
                    begin atomic
                        declare out varchar(2000);
                        declare dem varchar(2000);
                        declare num int;
                        declare pos int;
                        declare temp varchar(2000);

                        set dem=delimit;
                        set temp=In;
                        set num=n;
                        set pos=1;

                        if(num<0) then
                            while(locate(delimit,temp)!=0) do
                                set temp=substr(temp,locate(delimit,temp)+1);
                                set num=num+1;
                            end while;
                            set num=num+1;
                            set temp=In;
                        end if;

                        while (num>0) do
                            set pos=pos+locate(delimit,temp)-1;
                            set temp=substr(temp,locate(delimit,temp)+1);
                            set num=num-1;
                        end while;

                        if(n>0) then
                            return substr(In,1,pos);
                        else
                            return substr(In,pos+1);
                        end if;
                    end;;
--#SET TERMINATOR ;

-------------------------------------------------------------------------
-- Storage Procedure for converting Db2 timestamp into UTC milliseconds
-------------------------------------------------------------------------

--#SET TERMINATOR ;;
CREATE OR REPLACE FUNCTION utcmillis (in db2ts timestamp)
    returns bigint
    language sql
    deterministic
    no external action
    return (
        bigint((days(db2ts - current timezone) - days('1970-01-01-00.00.00.000000')) * 86400 + midnight_seconds(db2ts - current timezone))*1000 + microsecond(db2ts)/1000
    );;
--#SET TERMINATOR ;

Hi team,

When running the Go code, I got the error message back,

2019/09/24 06:46:11 sql: Scan error on column index 5, name "TDEF": unsupported Scan, storing driver.Value type <nil> into type *string

It could not be re-produced stable, until now, I have not find the root cause. Seeking your help if any comments.

The code is simple,

var (
		cids       string
		tschem     string
		tname      string
		numNodes   int64
		numSources int64
		tdef       string
)

...
for rows.Next() {
		err := rows.Scan(&cids, &tschem, &tname, &numNodes, &numSources, &tdef)
...

And I ran the sql with SQLEditor, seems the output is good. 2231 rows returned, and I checked the TDEF column text, no NULL or blank found, all returned with text.

Any idea what could be possible causing this issue? Thanks.

./pandabi/src/github.com/ibmdb/go_ibm_db/api/api_unix.go:12:22: fatal error: sqlcli1.h: No such file or directory
// #include <sqlcli1.h>

when running the code snippet I get this error:

I installed the driver as per the instruction in the readme. the lib folder has the file libdb2.dylib but it complains about it:

  Referenced from: /var/folders/_h/4trwzcd908g703_vdw2q1yn00000gn/T/go-
build774492263/b001/exe/db2conn
  Reason: image not found
signal: abort trap```

the code I am using is:

package main

import (
_ "github.com/ibmdb/go_ibm_db"
"database/sql"
"fmt"
)

func main(){
con:="HOSTNAME=host;DATABASE=db;PORT=50000;UID=u;PWD=pwd"
db, err:=sql.Open("go_ibm_db", con)
if err != nil{

	fmt.Println(err)
}
db.Close()

}

(1) When cli driver is used directly on db2 server without credentials it does not work similar to java type-2 driver (meaning, only DB name is required and not user credentials)
(2) Error is thrown during query execution and not during connection

Sample code

package main

import (
    _ "github.com/ibmdb/go_ibm_db"
    "database/sql"
    "fmt"
)

func main(){
    //con:="HOSTNAME=localhost;DATABASE=METADB;PORT=50000;UID=username;PWD=password"
    con:="HOSTNAME=localhost;DATABASE=METADB;PORT=50000"
    fmt.Println("Connection string=", con)
    db, conErr:=sql.Open("go_ibm_db", con)
    if conErr != nil{
      fmt.Println("Error while connecting DB", conErr)
    }else{
      _, qErr := db.Query("select min(db_conn_time) as db_conn_time,current timestamp as db_current_time from table (MON_GET_DATABASE(0));")
        if qErr != nil {
          fmt.Println("Error while executing query=", qErr)
        }
      db.Close()
    }
}

Scenario-1
When CLI driver is used on db2 server without userid/password it throws error
Anticipated behavior

1. CLI driver works as type-2 driver when used on DB2 server (no uid/password required)
2. Error should be thrown during connection and not during query execution.
3. Error should meaningful

export LD_LIBRARY_PATH=$HOME/clidriver/lib
env CGO_CFLAGS=-I$HOME/clidriver/include CGO_LDFLAGS=-L$HOME/clidriver/lib go run -v db_connection.go -d METADB -t localhost -r 50000
command-line-arguments
Connection string= HOSTNAME=localhost;DATABASE=METADB;PORT=50000
Error while executing query= SQLDriverConnect: {08001} [IBM][CLI Driver] SQL30082N  Security processing failed with reason "17" ("UNSUPPORTED FUNCTION").  SQLSTATE=08001

Scenario-2
When db2 libraries is used with driver on db2 server without userid/password it throws meaningful error
Anticipated behavior

1. CLI driver works as type-2 driver when used on DB2 server (no uid/password required)
2. Error should be thrown during connection and not during query execution.

export LD_LIBRARY_PATH=$HOME/sqllib/lib
env CGO_CFLAGS=-I$HOME/clidriver/include CGO_LDFLAGS=-L$HOME/clidriver/lib go run -v db_connection.go -d METADB -t localhost -r 50000
Connection string= HOSTNAME=localhost;DATABASE=METADB;PORT=50000
Error while executing query= SQLDriverConnect: {08001} [IBM][CLI Driver] SQL30082N  Security processing failed with reason "3" ("PASSWORD MISSING").  SQLSTATE=08001

• os Version: CentOS 7.0

• cli-driver Version: v.0.2.0

• Db2 Server Version: 11.1.4

• go Version: 1.13.6 linux/amd64

$ go env
O111MODULE=""
GOARCH="amd64"
GOBIN=""
GOCACHE="/home/db2inst1/.cache/go-build"
GOENV="/home/db2inst1/.config/go/env"
GOEXE=""
GOFLAGS=""
GOHOSTARCH="amd64"
GOHOSTOS="linux"
GONOPROXY=""
GONOSUMDB=""
GOOS="linux"
GOPATH="/home/db2inst1/src"
GOPRIVATE=""
GOPROXY="https://proxy.golang.org,direct"
GOROOT="/home/db2inst1/go"
GOSUMDB="sum.golang.org"
GOTMPDIR=""
GOTOOLDIR="/home/db2inst1/go/pkg/tool/linux_amd64"
GCCGO="gccgo"
AR="ar"
CC="gcc"
CXX="g++"
CGO_ENABLED="1"
GOMOD=""
CGO_CFLAGS="-g -O2"
CGO_CPPFLAGS=""
CGO_CXXFLAGS="-g -O2"
CGO_FFLAGS="-g -O2"
CGO_LDFLAGS="-g -O2"
PKG_CONFIG="pkg-config"
GOGCCFLAGS="-fPIC -m64 -pthread -fmessage-length=0 -fdebug-prefix-map=/tmp/go-build189927149=/tmp/go-build -gno-record-gcc-switches"

Steps to Reproduce:

1. Install driver on Db2 server
2. Run go test program as described above

Dear team,

I got one issue and the go code return error message,

2020/02/04 06:12:17 sql: ALTER TABLE SYSIBM.SYSTABLES DEACTIVATE ROW ACCESS CONTROL;
2020/02/04 06:12:17 [Error]: SQLExecute: {     } [IBM][CLI Driver][DB2/LINUXX8664] SQL0142N  The SQL statement is not supported.

I have monitored for a while, but there is not any error message reported in backend database server(BigSQL, there is no error message in db2diag log at the time the issue occurred).

I am asking for your help. If the go_ibm_db driver could return the error message before reaching to database?

Also, this issue only occurred when the test server provisioned and run the go code first time. Since the second time, the go code run successfully, there is not any problem.
Thanks.

I'm having the following issue running on the Mac. Any ideas?

panic: runtime error: cgo argument has Go pointer to Go pointer

goroutine 1 [running]:
github.com/ibmdb/go_ibm_db/api.SQLBindCol.func1(0x4000100010001, 0xc00009c1b8, 0xc000000004, 0xc00009c198, 0x40ecc80)
/Users/fonseca/Development/gocode/src/github.com/ibmdb/go_ibm_db/api/zapi_unix.go:26 +0x4c
github.com/ibmdb/go_ibm_db/api.SQLBindCol(0x4000100010001, 0xc00009c1b8, 0x4, 0xc00009c198, 0x0)
/Users/fonseca/Development/gocode/src/github.com/ibmdb/go_ibm_db/api/zapi_unix.go:26 +0x59
github.com/ibmdb/go_ibm_db.(*BufferLen).Bind(0xc00009c198, 0x10001, 0x0, 0x4030004, 0xc00009c1b8, 0x4, 0x8, 0xc0000902a0)
/Users/fonseca/Development/gocode/src/github.com/ibmdb/go_ibm_db/column.go:29 +0x62
github.com/ibmdb/go_ibm_db.(*BindableColumn).Bind(0xc00009c180, 0x10001, 0x0, 0xc00009c180, 0x0, 0x0)
/Users/fonseca/Development/gocode/src/github.com/ibmdb/go_ibm_db/column.go:202 +0x6c
github.com/ibmdb/go_ibm_db.(*ODBCStmt).BindColumns(0xc0000c2000, 0x41d8650, 0x0)
/Users/fonseca/Development/gocode/src/github.com/ibmdb/go_ibm_db/odbcstmt.go:148 +0x1eb
github.com/ibmdb/go_ibm_db.(*Stmt).Query(0xc000094270, 0x41d8650, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0)
/Users/fonseca/Development/gocode/src/github.com/ibmdb/go_ibm_db/stmt.go:93 +0xe5
database/sql.ctxDriverStmtQuery(0x4111840, 0xc00009a008, 0x4111940, 0xc000094270, 0x41d8650, 0x0, 0x0, 0x0, 0x0, 0x0, ...)
/usr/local/Cellar/go/1.11.5/libexec/src/database/sql/ctxutil.go:94 +0x16b
database/sql.rowsiFromStatement(0x4111840, 0xc00009a008, 0x4111700, 0xc000090270, 0xc00009c140, 0x0, 0x0, 0x0, 0x0, 0x0, ...)
/usr/local/Cellar/go/1.11.5/libexec/src/database/sql/sql.go:2511 +0x159
database/sql.(*Stmt).QueryContext(0xc0000c4000, 0x4111840, 0xc00009a008, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0)
/usr/local/Cellar/go/1.11.5/libexec/src/database/sql/sql.go:2463 +0x203
database/sql.(*Stmt).Query(0xc0000c4000, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0)
/usr/local/Cellar/go/1.11.5/libexec/src/database/sql/sql.go:2501 +0x65
main.main()
/Users/fonseca/Development/gocode/src/github.ibm.com/IROC/db2_go/main.go:24 +0x183

If db2 cli path is not setted, then the app panic in Windows because NewLazyDLL can't find the db2cli64.dll (or db2cli.dll).

If an app need to do actions depending if db2cli.dll is setted or not, It can't because the panic.

Recover from panic when init() and not register the driver is a solution, and leave the developer to manage the error returned by database/sql when a driver is not registered.

I cann't connect to the system database (to create a database), getting the next error:

ERROR [08004][IBM] SQL30061N The database alias or database name "SYSIBM          " was not found a the remote node. SQLSTATE=08004.

The sample program I'm trying to execute is:

package main

import (
	"database/sql"
	"fmt"

	_ "github.com/ibmdb/go_ibm_db"
)

func main() {
	db, err := connectDB()
	if err != nil {
		println("Connect Error:\n" + err.Error())
		return
	}
	if err = db.Ping(); err != nil {
		println("Ping Error:\n" + err.Error())
		return
	}
	db.Close()
}

func connectDB() (*sql.DB, error) {
	driver := "go_ibm_db"
	connection := fmt.Sprintf(
		"Hostname=127.0.0.1;Protocol=tcpip;Port=50000;Database=%s;Uid=%s;Pwd=%s",
		"SYSIBM",
		"db2admin",
		"***",
	)
	return sql.Open(driver, connection)
}

Also, I've tested it with different database names (DB2, DBCOPY1, DB2INST1, DB2LUW) getting the same error.

At the error message, it shows "SYSIBM ", so it is adding spaces to the given name. Does the error could be due to it?

I can only use the command 'db2' to create a database using db2 CREATE DATABASE [name] but I want to access to the system database to be able to create it from there, as can be done from other databases (MariaDB, MySQL, PostgreSQL, SQL Server).

Hi,

I seem to be having this issue. I noticed that someone else also had this issue and I tried to do what you guys suggested in the comments but that did not help. I followed the steps in the readme as well.

Any help is greatly appreciated!

There seems to be in the current macos64_odbc_cli.tar.gz file:

• clidriver/lib/libDB2xml4c.58.0.dylib
• clidriver/lib/libDB2xml4c.58.dylib

Untarring the symlinks are not supported by setup.go.

This creates a warning during running the setup.go installation:

john$ go run setup.go 
darwin
macos64_odbc_cli.tar.gz
Downloading...
download successful
Creating directory : clidriver/

... (lot of files untarred) ...

Creating directory : clidriver/lib/icc/
Untarring : clidriver/lib/libdb2.dylib
Untarring : clidriver/lib/libdb2clixml4c.dylib
Unable to untar type : 2 in file clidriver/lib/libDB2xml4c.58.0.dylib
Unable to untar type : 2 in file clidriver/lib/libDB2xml4c.58.dylib

... (lot of files untarred) ...

Using booleans as query parameters on macOS always gives the error:

[IBM][CLI Driver] CLI0100E  Wrong number of parameters. SQLSTATE=07001

This does not happen on Linux. To reproduce, create a table like this:

CREATE TABLE booltest(
    id varchar(100) not null primary key,
    active boolean
)

Attempt to run this on macOS and observe the failure:

package main

import (
	"database/sql"

	_ "github.com/ibmdb/go_ibm_db"
)

func main() {
	db, err := sql.Open("go_ibm_db", "<dsn>")
	if err != nil {
		panic(err)
	}

	_, err = db.Exec(
		`INSERT INTO booltest VALUES(?, ?)`,
		"test", true)
	if err != nil {
		panic(err)
	}
}

Run the same code on Linux and observe that the insert is performed correctly.

Note that changing the boolean values in the Go code to integers fixes the issue, but this should not be necessary.

I got one error when I am following the readme instructions:

zac$ go run db2connect1.go

runtime/cgo

clang: error: -l/home/zac/go/src/github.com/ibmdb/go_ibm_db/installer/clidriver/include: 'linker' input unused [-Werror,-Wunused-command-line-argument]

How can I fix it?

db2connect1.go:

package main

import (
_ "github.com/ibmdb/go_ibm_db"
"database/sql"
"fmt"
)

func main(){
con:="HOSTNAME=dashdb.services.eu-gb.bluemix.net;DATABASE=BLUDB;PORT=50000;UID=username;PWD=password"
db, err:=sql.Open("go_ibm_db", con)
if err != nil{

	fmt.Println(err)
}
db.Close()

}

--------I am using Mac, and here is the go env:

ruihuaz-MacBook-Pro:src zac$ go env
GOARCH="amd64"
GOBIN=""
GOCACHE="/Users/zac/Library/Caches/go-build"
GOEXE=""
GOFLAGS=""
GOHOSTARCH="amd64"
GOHOSTOS="darwin"
GOOS="darwin"
GOPATH="/Users/zac/go"
GOPROXY=""
GORACE=""
GOROOT="/usr/local/go"
GOTMPDIR=""
GOTOOLDIR="/usr/local/go/pkg/tool/darwin_amd64"
GCCGO="gccgo"
CC="clang"
CXX="clang++"
CGO_ENABLED="1"
GOMOD=""
CGO_CFLAGS="-l/home/zac/go/src/github.com/ibmdb/go_ibm_db/installer/clidriver/include"
CGO_CPPFLAGS=""
CGO_CXXFLAGS="-g -O2"
CGO_FFLAGS="-g -O2"
CGO_LDFLAGS="-L/home/zac/go/src/github.com/ibmdb/go_ibm_db/installer/clidriver/lib"
PKG_CONFIG="pkg-config"
GOGCCFLAGS="-fPIC -m64 -pthread -fno-caret-diagnostics -Qunused-arguments -fmessage-length=0 -fdebug-prefix-map=/var/folders/hx/0bd3dc5j2f95jy7lxjf51w5c0000gn/T/go-build020472211=/tmp/go-build -gno-record-gcc-switches -fno-common"

The I using the go_ibm_db version 0.1.1 in our golang project.

I get the following error when trying to run a sql query.

panic: runtime error: cgo argument has Go pointer to Go pointer

goroutine 34 [running]:
github.com/ibmdb/go_ibm_db/api.SQLGetData.func1(0x8000900010001, 0xc0001ae7b8, 0x8, 0xc0001ae798, 0xc0001ca200)
        /go/pkg/mod/github.com/ibmdb/[email protected]/api/zapi_unix.go:81 +0x4c
github.com/ibmdb/go_ibm_db/api.SQLGetData(0x8000900010001, 0xc0001ae7b8, 0xc000000008, 0xc0001ae798, 0x40)
        /go/pkg/mod/github.com/ibmdb/[email protected]/api/zapi_unix.go:81 +0x59
github.com/ibmdb/go_ibm_db.(*BufferLen).GetData(...)
        /go/pkg/mod/github.com/ibmdb/[email protected]/column.go:25
github.com/ibmdb/go_ibm_db.(*BindableColumn).Value(0xc0001ae780, 0xc000010001, 0x8, 0xa3e840, 0xc0001a0960, 0x0, 0x0)
        /go/pkg/mod/github.com/ibmdb/[email protected]/column.go:268 +0x283
github.com/ibmdb/go_ibm_db.(*Rows).Next(0xc000214050, 0xc0001f62d0, 0x9, 0x9, 0xc000137040, 0x3)
        /go/pkg/mod/github.com/ibmdb/[email protected]/rows.go:99 +0xca
database/sql.(*Rows).nextLocked(0xc000222180, 0xc0001a0000)
        /usr/local/go/src/database/sql/sql.go:2680 +0xbc
database/sql.(*Rows).Next.func1()
        /usr/local/go/src/database/sql/sql.go:2658 +0x3c
database/sql.withLock(0xbf0380, 0xc0002221b0, 0xc0001370c0)
        /usr/local/go/src/database/sql/sql.go:3097 +0x63
database/sql.(*Rows).Next(0xc000222180, 0xc0001f61b0)
        /usr/local/go/src/database/sql/sql.go:2657 +0x88

The same SQL query runs fine on DB2WH UI.

So not sure what is going on.
Any help would be appreciated .
Thanks

Hi team,
In Travis I need to run sudo go run setup.go to install Cli.
The clidriver was installed in the root level src directory:
/src/github.com/ibmdb/go_ibm_db/installer
instead of
/gopath/src/github.com/ibmdb/go_ibm_db/installer

Please advice. Thank you.

Can we cross compile a Mac build of our binary which uses go_ibm_db on a Linux machine?

When running with CGO_ENABLED=1:

+ CGO_ENABLED=1 GOOS=linux go build -a -installsuffix cgo -o agent-linux .
+ CGO_ENABLED=1 GOOS=windows go build -a -installsuffix cgo -o agent-windows .
+ CGO_ENABLED=1 GOOS=darwin go build -a -installsuffix cgo -o agent-darwin .
# net
../../tools/org.jenkinsci.plugins.golang.GolangInstallation/1.13/src/net/cgo_bsd.go:15:72: could not determine kind of name for C.AI_MASK
# os/user
../../tools/org.jenkinsci.plugins.golang.GolangInstallation/1.13/src/os/user/getgrouplist_darwin.go: In function ‘mygetgrouplist’:
../../tools/org.jenkinsci.plugins.golang.GolangInstallation/1.13/src/os/user/getgrouplist_darwin.go:16:11: warning: implicit declaration of function ‘getgrouplist’ [-Wimplicit-function-declaration]
  int rv = getgrouplist(user, (int) group, buf, ngroups);

Is it possible to cross-compile the package without building the driver into the binary? And allow the appropriate driver to be present at runtime ?

We are importing and using the driver like this:

import (
	"database/sql"

	_ "github.com/ibmdb/go_ibm_db"
)

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

We would like to use a single Linux environment to build all binaries for Windows, Mac, and Linux.

Call a stored procedure with input, out parameter
Some stored procedure will return multi-resultset

It's not explicitly stated.

How to set connect timeout and execution timeout？

1. SSL
   2.API Key
2. Kerbos

HI
Query most tables did not report errors, one of the tables reported errors.

• os Version: Windows 10
• cli-driver Version: 11.1
• Db2 Server Version: 11.5.0.1077
• go Version: 1.13.5
• 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 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:\go
set GOSUMDB=sum.golang.org
set GOTMPDIR=
set GOTOOLDIR=c:\go\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\sdi\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-build970289982=/tmp/go-build -gno-record-gcc-switches

Steps to Reproduce:

1. Select a column with datatype DECFLOAT

go 1.13, Windows
go_ibm_db v0.1.1

I run the following code

markNotificationAsReadStmt, err := db.Prepare(`
    UPDATE NOTIFICATION SET IS_READ = TRUE WHERE DEVICE_UUID = ? AND UUID = ?;`)
if err != nil {
    ...
}

res, err := markNotificationAsReadStmt.Exec(deviceUUID, notificationUUID)
if err != nil {
    ...
}
affected, err := res.RowsAffected()

DEVICE_UUID and UUID form a unique pair, so I expect affected to have either 0 or 1, but i get 4294967295 and 4294967296 instead, which are uint32max and uint32max+1.
err is nil in both cases

Please, could you to add support to create and drop a database with db.Exec() API?

Thanks in advance!