从Go应用程序到Oracle数据库的SQL查询引发错误？

Question

I use next article to connect Golang application with remote Oracle database. Application successfully connected to database with the help of library goracle. I also ping it successfully. Application raise error when I make sql query. Where is mistake?

I notice that if remove all code from controller file (organizations.go) to connection file (Oracle.go) it works. How exactly I need to refactor?

ERROR:

2019/03/13 18:15:47 http: panic serving [::1]:28363: runtime error: invalid memory address or nil pointer dereference
goroutine 54 [running]:
net/http.(*conn).serve.func1(0xc0000dee60)
    C:/Go/src/net/http/server.go:1746 +0xd7
panic(0x874bc0, 0x7ff060)
    C:/Go/src/runtime/panic.go:513 +0x1c7
database/sql.(*DB).conn(0x0, 0x95d140, 0xc0000120a8, 0x1, 0x0, 0xc0000dec60, 0xc00037d950)
    C:/Go/src/database/sql/sql.go:1081 +0x41
database/sql.(*DB).query(0x0, 0x95d140, 0xc0000120a8, 0x8fde32, 0x65, 0x0, 0x0, 0x0, 0x6b2801, 0x8, ...)
    C:/Go/src/database/sql/sql.go:1514 +0x6d
database/sql.(*DB).QueryContext(0x0, 0x95d140, 0xc0000120a8, 0x8fde32, 0x65, 0x0, 0x0, 0x0, 0x30, 0x30, ...)
    C:/Go/src/database/sql/sql.go:1496 +0xda
database/sql.(*DB).Query(0x0, 0x8fde32, 0x65, 0x0, 0x0, 0x0, 0x78, 0x80, 0xc000446100)
    C:/Go/src/database/sql/sql.go:1510 +0x89
questionnaire/controllers.glob..func11(0x95cd80, 0xc000448000, 0xc00016e400)
    C:/Users/NNogerbek/go/src/questionnaire/controllers/organizations.go:18 +0x6f
net/http.HandlerFunc.ServeHTTP(0x9031f8, 0x95cd80, 0xc000448000, 0xc00016e400)
    C:/Go/src/net/http/server.go:1964 +0x4b
github.com/gorilla/mux.(*Router).ServeHTTP(0xc0003da300, 0x95cd80, 0xc000448000, 0xc000444000)
    C:/Users/NNogerbek/go/src/github.com/gorilla/mux/mux.go:212 +0xd7
github.com/gorilla/handlers.(*cors).ServeHTTP(0xc0000d8900, 0x95cd80, 0xc000448000, 0xc000444000)
    C:/Users/NNogerbek/go/src/github.com/gorilla/handlers/cors.go:54 +0xa95
net/http.serverHandler.ServeHTTP(0xc000054b60, 0x95cd80, 0xc000448000, 0xc000444000)
    C:/Go/src/net/http/server.go:2741 +0xb2
net/http.(*conn).serve(0xc0000dee60, 0x95d100, 0xc000178000)
    C:/Go/src/net/http/server.go:1847 +0x64d
created by net/http.(*Server).Serve
    C:/Go/src/net/http/server.go:2851 +0x2fc

structure:

- database
    Oracle.go
- routes
    routes.go
- controllers
    organizations.go
main.go

main.go:

import (
    "github.com/gorilla/handlers"
    "github.com/gorilla/mux"
    "github.com/joho/godotenv"
    "log"
    "net/http"
    "project/database"
    "project/routes"
    "project/utils"
)

func main()  {
    err := godotenv.Load(".env")
    if err != nil {
        panic(err)
    }

    database.ConnectOracle()
    defer database.DisconnectOracle()

    router := mux.NewRouter()

    headers := handlers.AllowedHeaders([]string{"X-Requested-With", "Content-Type", "Authorization"})
    methods := handlers.AllowedMethods([]string{"GET", "POST", "PATCH", "PUT", "DELETE", "OPTIONS"})
    origins := handlers.AllowedOrigins([]string{"*"})

    router.StrictSlash(true)

    routes.Handle(router)

    port := utils.CheckEnvironmentVariable("APPLICATION_PORT")

    log.Printf("RESTful web service is running on %s port.", port)

    log.Fatal(http.ListenAndServe(":" + port, handlers.CORS(headers, methods, origins)(router)))
}

Oracle.go:

package database

import (
    "database/sql"
    "fmt"
    "github.com/joho/godotenv"
    _ "gopkg.in/goracle.v2"
    "log"
    "questionnaire/utils"
)

var OracleDB *sql.DB

func ConnectOracle() {
    err := godotenv.Load(".env")
    if err != nil {
        log.Println(err)
        panic(err)
    }

    databaseUser := utils.CheckEnvironmentVariable("ORACLE_USER")
    databasePassword := utils.CheckEnvironmentVariable("ORACLE_PASSWORD")
    databaseHost := utils.CheckEnvironmentVariable("ORACLE_HOST")
    databasePort := utils.CheckEnvironmentVariable("ORACLE_PORT")
    databaseName := utils.CheckEnvironmentVariable("ORACLE_DATABASE_NAME")

    databaseURL:= fmt.Sprintf("%s/%s@%s:%s/%s", databaseUser, databasePassword, databaseHost, databasePort, databaseName)

    OracleDB, err := sql.Open("goracle", databaseURL)
    if err != nil {
        log.Println(err)
        panic(err)
    }

    err = OracleDB.Ping()
    if err != nil {
        log.Println(err)
        panic(err)
    }

    log.Println("RESTful web service successfully connected to remote ORACLE database with the help of \"database/sql\" package.")
}

func DisconnectOracle() error {
    return OracleDB.Close()
}

routes.go:

package routes

import (
    "github.com/gorilla/mux"
    "project/controllers"
)

func Handle(router *mux.Router) {
    router.HandleFunc("/api/organizations", controllers.GetOrganizations).Methods("GET")
}

organizations.go:

package controllers

import (
    "log"
    "net/http"
    "project/database"
    "project/utils"
)

type Organisation struct {
    OrganizationID int `json:"organization_id"`
    ParentOrganizationID int `json:"parent_organization_id"`
    OrganizationName string `json:"organization_name"`
    OrganizationRang int `json:"organization_name"`
}

var GetOrganizations = func(responseWriter http.ResponseWriter, request *http.Request) {
    rows, err := database.OracleDB.Query("SELECT ORGANIZATION_ID, PARENT_ORGANIZATION_ID, ORGANIZATION_NAME, ORGANIZATION_RANG FROM ORG_STR")
    if err != nil {
        log.Println(err)
        utils.ResponseWithError(responseWriter, http.StatusInternalServerError, err.Error())
        return
    }
    defer rows.Close()

    var organizations []Organisation

    for rows.Next() {
        var organization Organisation

        if err := rows.Scan(&organization.OrganizationID, &organization.ParentOrganizationID, &organization.OrganizationName, &organization.OrganizationRang); err != nil {
            log.Println(err)
            utils.ResponseWithError(responseWriter, http.StatusInternalServerError, err.Error())
            return
        }

        organizations = append(organizations, organization)
    }

    utils.Response(responseWriter, http.StatusOK, organizations)
}

Answer 1

The solution which adviced @mkopriva:

Change OracleDB, err := sql.Open("goracle", databaseURL) to OracleDB, err = sql.Open("goracle", databaseURL), and search for "go shadowing variables". To explain, your global OracleDB is never set because you use := in the local scope to "assign" it, but := declares and initializes a new variable named OracleDB that shadows the global one.