Get row data from MySQL/Go table

In this article, php editor Xiaoxin will introduce to you how to get row data from a MySQL/Go table. MySQL is a popular relational database management system, and Go is a powerful development language. During the development process, we often need to obtain data from the database and process it. This article will introduce in detail how to use Go language to connect to the MySQL database and obtain row data from the table through query statements. Whether you are a beginner or an experienced developer, this article will provide you with helpful guidance and sample code. let's start!

Question content

First it reads the code so you understand its logic, when running the stored procedure I captured it it brings me a file with the data I have to return The table, the column names does bring it up for me, but the column data doesn't bring me anything, I can't create the model, and the response of the stored procedure has n columns with n different names, But the difference in the columns is having int data and string data, I need you to capture the correct data from the column because everything is working fine but the data in the column is not working:

package controllers

import (
    "database/sql"
    "encoding/json"
    "fmt"
    "net/http"

    "github.com/gin-gonic/gin"
)

type RequestData struct {
    FromData map[string]interface{} `json:"fromData"`
    Call     string                 `json:"Call"`
}

func HandleDatos(c *gin.Context) {
    var requestData RequestData

    if err := c.ShouldBindJSON(&requestData); err != nil {
        c.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
        return
    }

    fmt.Printf("Ejecutando procedimiento almacenado: CALL %s\n", requestData.Call)
    fmt.Printf("Parámetros: %v\n", requestData.FromData)

    var rows *sql.Rows
    var err error

    // Verifica si FromData contiene valores
    if len(requestData.FromData) > 0 {
        // Si hay valores en FromData, crea una consulta con parámetros
        query := "CALL " + requestData.Call + "("
        params := []interface{}{}
        for _, value := range requestData.FromData {
            query += "?, "
            params = append(params, value)
        }
        query = query[:len(query)-2] + ")"

        rows, err = db.Raw(query, params...).Rows()
    } else {
        // Si no hay valores en FromData, ejecuta el procedimiento almacenado sin parámetros
        rows, err = db.Raw("CALL " + requestData.Call).Rows()
    }

    if err != nil {
        c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
        return
    }
    defer rows.Close()

    // Convierte los resultados en un mapa
    result := make(map[string]interface{})
    columns, err := rows.Columns()
    if err != nil {
        c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
        return
    }

    fmt.Printf("Columnas: %v\n", columns) // Punto de impresión

    data := [][]interface{}{} // Almacena los datos de filas
    for rows.Next() {
        values := make([]interface{}, len(columns))
        for i := range columns {
            values[i] = new(interface{})
        }

        if err := rows.Scan(values...); err != nil {
            c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
            return
        }

        fmt.Printf("Valores escaneados: %v\n", values) // Punto de impresión

        row := make(map[string]interface{})
        for i, col := range columns {
            val := *(values[i].(*interface{}))
            row[col] = val
        }

        fmt.Printf("Fila escaneada: %v\n", row) // Punto de impresión

        // Agrega esta fila al resultado
        data = append(data, values)
    }

    fmt.Printf("Datos finales: %v\n", data) // Punto de impresión

    if len(data) > 0 {
        result["columns"] = columns
        result["data"] = data
    } else {
        // Si no hay datos, establece un mensaje personalizado
        result["message"] = "Sin datos"
    }

    // Convierte el resultado en JSON y devuelve la respuesta
    responseJSON, err := json.Marshal(result)
    if err != nil {
        c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
        return
    }

    c.JSON(http.StatusOK, string(responseJSON))
}

This is what it returns to me, it says "columns": ["idPunto", "nombre"] That part is ok, but the row containing the data is not what I expected:

SOLVED Method

Scanning rows into interface {} does not automatically convert SQL types to Go types. Instead, using the ColumnTypes method will get the data type of each column, allowing you to dynamically assign the correct Go type. (The following content has not been tested and is for reference only.) For example

for i := range columns {
    // Use the column types to determine the appropriate scan type
    switch columnTypes[i].DatabaseTypeName() {
    case "INT", "TINYINT", "SMALLINT", "MEDIUMINT", "BIGINT":
        scanArgs[i] = new(int64)
    default:
        scanArgs[i] = new(string)
    }

    values[i] = scanArgs[i]
}

In your script:

package controllers

import (
    "database/sql"
    "encoding/json"
    "fmt"
    "net/http"

    "github.com/gin-gonic/gin"
)

type RequestData struct {
    FromData map[string]interface{} `json:"fromData"`
    Call     string                 `json:"Call"`
}

func HandleDatos(c *gin.Context) {
    var requestData RequestData

    if err := c.ShouldBindJSON(&requestData); err != nil {
        c.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
        return
    }

    fmt.Printf("Ejecutando procedimiento almacenado: CALL %s\n", requestData.Call)
    fmt.Printf("Parámetros: %v\n", requestData.FromData)

    var rows *sql.Rows
    var err error

    // Verifica si FromData contiene valores
    if len(requestData.FromData) > 0 {
        // Si hay valores en FromData, crea una consulta con parámetros
        query := "CALL " + requestData.Call + "("
        params := []interface{}{}
        for _, value := range requestData.FromData {
            query += "?, "
            params = append(params, value)
        }
        query = query[:len(query)-2] + ")"

        rows, err = db.Raw(query, params...).Rows()
    } else {
        // Si no hay valores en FromData, ejecuta el procedimiento almacenado sin parámetros
        rows, err = db.Raw("CALL " + requestData.Call).Rows()
    }

    if err != nil {
        c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
        return
    }
    defer rows.Close()

    // Convierte los resultados en un mapa
    result := make(map[string]interface{})
    columns, err := rows.Columns()
    if err != nil {
        c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
        return
    }

    fmt.Printf("Columnas: %v\n", columns) // Punto de impresión

    data := []map[string]interface{}{} // Almacena los datos de filas

    // Get the column types
    columnTypes, err := rows.ColumnTypes()
    if err != nil {
        c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
        return
    }

    for rows.Next() {
        values := make([]interface{}, len(columns)
        scanArgs := make([]interface{}, len(columns))

        for i := range columns {
            // Use the column types to determine the appropriate scan type
            switch columnTypes[i].DatabaseTypeName() {
            case "INT", "TINYINT", "SMALLINT", "MEDIUMINT", "BIGINT":
                scanArgs[i] = new(int64)
            default:
                scanArgs[i] = new(string)
            }

            values[i] = scanArgs[i]
        }

        if err := rows.Scan(values...); err != nil {
            c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
            return
        }

        fmt.Printf("Valores escaneados: %v\n", values) // Punto de impresión

        row := make(map[string]interface{})
        for i, col := range columns {
            // Cast the scanned values to the appropriate data types
            switch columnTypes[i].DatabaseTypeName() {
            case "INT", "TINYINT", "SMALLINT", "MEDIUMINT", "BIGINT":
                row[col] = *(scanArgs[i].(*int64))
            default:
                row[col] = *(scanArgs[i].(*string))
            }
        }

        fmt.Printf("Fila escaneada: %v\n", row) // Punto de impresión

        // Agrega esta fila al resultado
        data = append(data, row)
    }

    fmt.Printf("Datos finales: %v\n", data) // Punto de impresión

    if len(data) > 0 {
        result["columns"] = columns
        result["data"] = data
    } else {
        // Si no hay datos, establece un mensaje personalizado
        result["message"] = "Sin datos"
    }

    // Convierte el resultado en JSON y devuelve la respuesta
    responseJSON, err := json.Marshal(result)
    if err != nil {
        c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
        return
    }

    c.JSON(http.StatusOK, string(responseJSON))
}

nb: You should be able to extend this logic for other data types you may encounter.

The above is the detailed content of Get row data from MySQL/Go table. For more information, please follow other related articles on the PHP Chinese website!