CODEWITHSUNDEEP
godb2odbcibm-midrangedb2-400
afboteros
afboteros

Reputation: 371

DB2 AS/400 Querying with Go

I'm trying to connect Golang with an AS/400 DB2 Database, i have iSeries Access Driver Installed on the machine, this is the code by now:

package main

import (
    "odbc"
)

func main() {
    conn, _ := odbc.Connect("DSN=DSN/SCHEME;UID=USER;PWD=PASS")
    stmt, _ := conn.Prepare("SELECT * FROM SCHEME.TABLE")
    stmt.Execute()
    rows, _ := stmt.FetchAll()
    for i, row := range rows {
        println(i, row)
    }
    stmt.Close()
    conn.Close()
}

I was able to do this using Python with a DSN-Less configuration on the ODBC driver with something like this:

con = pyodbc.connect('DRIVER=iSeries Access ODBC Driver;SYSTEM=DSN;UID=USR;PWD=PASS;DBQ=PRUEBA')

I tried on Golang with odbc, mgodbc and db2cli, always getting an invalid memory address or nil pointer dereference error.

Any help is appreciated, Thanks!

Upvotes: 1

Views: 6045

Answers (5)

Vinayak Agrawal
Vinayak Agrawal

Reputation: 128

Have you tried the golang driver available for Db2? https://github.com/ibmdb/go_ibm_db

Upvotes: 0

finalight
finalight

Reputation: 21

Vanessa, you might need to define the value of the port for your connection string. I not sure what is the default port value so just put it in there to be safe.

Upvotes: 0

Vanessa Schissato
Vanessa Schissato

Reputation: 419

I'm using Go + https://bitbucket.org/phiggins/db2cli. This go lib for DB2 is using the DB2 CLI driver, that I found here and configured as described on phiggins page: http://public.dhe.ibm.com/ibmdl/export/pub/software/data/db2/drivers/odbc_cli/

Here is my code:

package main

import (
    "fmt"
    _ "bitbucket.org/phiggins/db2cli"
    "database/sql"
    "log"
    "time"
)

func main() {

    // JDBC conn string = jdbc:as400://127.0.0.1/;libraries=MYCUSTOM,hhpgm,trk35null,GX,TRK35B5ptf,TRK35B5PG,IFWMS,INTERTRK;user=MYUSER;password=MYPASS;naming=system;block criteria="0"
    db, err := sql.Open("db2-cli", "DATABASE=MYCUSTOM; HOSTNAME=127.0.0.1; UID=MYUSER; PWD=MYPASS;")
    if err != nil {
        log.Fatalln(err)
    }
    defer db.Close()
    var t time.Time
    row := db.QueryRow("SELECT current date FROM SYSIBM.SYSDUMMY1;")
    err = row.Scan(&t)
    if err != nil {
        log.Fatalln(err)
    }
    log.Println(t)
}

But I'm not being able to connect. The error that I'm receiving is:

$ go run main.go 
2016/10/27 18:07:46 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: "127.0.0.1".  Communication function detecting the error: "connect".  Protocol specific error code(s): "61", "*", "*".  SQLSTATE=08001

exit status 1

I'm on mac, so I identified that the "protocol specific error code" 61 is "#define ECONNREFUSED 61 /* Connection refused */" according to: http://opensource.apple.com//source/xnu/xnu-1456.1.26/bsd/sys/errno.h

However, I can connect using a SQL client and a JDBC driver. So, the question is, how can I convert the JDBC connection string to the format expected by the DB2 CLI driver?

  • JDBC connection string: jdbc:as400://127.0.0.1/;libraries=MYCUSTOM,hhpgm,trk35null,GX,TRK35B5ptf,TRK35B5PG,IFWMS,INTERTRK;user=MYUSER;password=MYPASS;naming=system;block criteria="0"
  • CLI connection string: "HOSTNAME=127.0.0.1; UID=MYUSER; PWD=MYPASS;"

Upvotes: 0

Justin
Justin

Reputation: 4624

Update – bitbucket.org/phiggins/db2cli is now listed on https://github.com/golang/go/wiki/SQLDrivers

We're using bitbucket.org/phiggins/db2cli, which is based off of the greatcode.google.com/p/odbc library, and it's been working great!

package main

import (
    "database/sql"
    "log"
    "time"

    _ "bitbucket.org/phiggins/db2cli"
)

func main() {
    db, err := sql.Open("db2-cli", "DATABASE=testdb; HOSTNAME=db2.domain.com; PORT=1234; PROTOCOL=TCPIP; UID=user1; PWD=password1;")
    if err != nil {
        log.Fatalln(err)
    }
    defer db.Close()
    var t time.Time
    row := db.QueryRow("SELECT current date FROM sysibm.sysdummy1;")
    err = row.Scan(&t)
    if err != nil {
        log.Fatalln(err)
    }
    log.Println(t)
}

Upvotes: 2

afboteros
afboteros

Reputation: 371

I managed to get it working, first, you need to install mgodbc go package:

go get bitbucket.org/miquella/mgodbc

To install this package you must have a working MinGW installation with gcc on your path (Windows), if you use Linux you should have gcc by installed by default.

Then, using this code i got it working:

package main

import (
    _ "bitbucket.org/miquella/mgodbc"
    "fmt"
    "os"
    "database/sql"
)

var ( 
        db *sql.DB 
        checkError = func(err error, num int) { 
                if err != nil { 
                        fmt.Println(err, num) 
                        os.Exit(1)
                }
        }
) 

func main() {
        // Replace the DBQ value with the name of your ODBC data source.
    db, err := sql.Open("mgodbc", "DRIVER=iSeries Access ODBC Driver;SYSTEM=HOSTNAME;UID=USER;PWD=PASS;DBQ=SCHEMA")
    checkError(err,1)
    rows, err := db.Query("SELECT * FROM TABLE")
    checkError(err,2)
    for rows.Next(){
        fmt.Println(rows)
    }
    defer rows.Close()
    defer db.Close()
}

The only thing missing here, is that sql Go package doesn't cast the Query to String, then you get weird codes when querying, now i'm looking to solve that.

Upvotes: 1

Related Questions