Golang API-MySQL连接但查询为空

Question

Let me preface this by saying I am new to Golang and working on refactoring an existing Python based API using Golang, so the database and underlying schema already exists and is populated with data.

I have a very basic API setup using Gin and Gorm. During the GET API call it is able to connect to the MySQL 5.7 backend but none of my queries will return anything. I've tried various queries with known serial numbers that are in the database and are being returned when I query outside of this app.

main.go

package main

import (
    "github.com/gin-gonic/gin"
    _ "github.com/go-sql-driver/mysql"
    "github.com/jinzhu/gorm"
    "time"
    "log"
    "fmt"
)

var db *gorm.DB

func InitDB() *gorm.DB{
    var err error
    db, err := gorm.Open("mysql", "api:MyPassword@tcp(10.37.1.1:3306)/opt_db1")
    db.SingularTable(true)

    err = db.DB().Ping()

    if err != nil {
        log.Panic(err)
        fmt.Print(err.Error())
    } else {
        fmt.Printf("Successfully connected to MySQL")
    }

    return db
}

type TestResult struct {
    ID                    int  `db:"id" json:"id"`
    DateAdded             time.Time `db:"date_added" json:"date_added"`
    Serial                string  `db:"serial" json:"serial"`
    SequenceId            int `db:"sequence_id" json:"sequence_id"`
    LastCompletedStage    int `db:"last_completed_stage" json:"last_completed_stage"`
    LastCompletedSequence int `db:"last_completed_sequence" json:"last_completed_sequence"`
    Workorder             string  `db:"workorder" json:"workorder"`
    Product               string `db:"product" json:"product"`
    IsComplete            string `db:"is_complete" json:"is_complete"`
    IsScrapped            string `db:"is_scrapped" json:"is_scrapped"`
    ValueStream           string `db:"value_stream" json:"value_stream"`
    PromiseDate           string `db:"promise_date" json:"promise_date"`
    FailLock              int `db:"fail_lock" json:"fail_lock"`
    SequenceRev           int `db:"sequence_rev" json:"sequence_rev"`
    DateUpdated           time.Time `db:"date_updated" json:"date_updated"`
    Date                  time.Time `db:"date" json:"date"`
    Time                  time.Time `db:"time" json:"time"`
    Ptyp2                 string `db:"ptyp2" json:"ptyp2"`
    WoQty                 int `db:"wo_qty" json:"wo_qty"`
    IsActive              int `db:"is_active" json:"is_active"`
    IsTimeLock            int `db:"is_time_lock" json:"is_time_lock"`
    TimeLockTimestamp     time.Time `db:"time_lock_timestamp" json:"time_lock_timestamp"`
    ScrapReason           string `db:"scrap_reason" json:"scrap_reason"`
    ScrappedBy            int `db:"scrapped_by" json:"scrapped_by"`
}

func main() {
    router := gin.Default()

    opt := router.Group("opt/v2")
    {
        opt.GET("/last-completed-test/:serial", LastTest)
    }

    // Add API handlers here
    router.Run(":3000")
}

func LastTest(c *gin.Context) {
    // Connection to the database
    db := InitDB()
    defer db.Close()

    var result TestResult

    // get param and query
    //serial := c.Params.ByName("serial")

    db.Table("test_result").Where("serial = ?", "124111-0027").Scan(&result)

    if result.ID != 0 {
         //Display JSON result
        c.JSON(200, result)
    } else {
        // Display JSON error
        c.JSON(404, gin.H{"error": "Record not found", "code": 404})
    }
}

Console Output:

[GIN-debug] Listening and serving HTTP on :3000 Successfully connected to MySQL[GIN] 2017/02/15 - 09:05:13 |[97;43m 404 [0m|      4.5005ms | 127.0.0.1 |[97;4 4m  [0m GET     /opt/v2/last-completed-test/124111-0027

Web Output:

{"id":2252920,"date_added":"0001-01-01T00:00:00Z","serial":"","sequence_id":0,"last_completed_stage":0,"last_completed_sequence":0,"workorder":"","product":"","is_complete":"","is_scrapped":"","value_stream":"","promise_date":"","fail_lock":0,"sequence_rev":0,"date_updated":"0001-01-01T00:00:00Z","date":"0001-01-01T00:00:00Z","time":"0001-01-01T00:00:00Z","ptyp2":"","wo_qty":0,"is_active":0,"is_time_lock":0,"time_lock_timestamp":"0001-01-01T00:00:00Z","scrap_reason":"","scrapped_by":0}

Schema from Workbench:

In the above code I have even hard-coded a serial number I know has data to no avail.

Is there something obvious I am missing in trying to query my DB?

Thanks!

Answer 1

You need to initialize the result variable, try:

 db.Table("test_result").Where("serial = ?", "124111-0027").Scan(&result)