Scan error on column index 0, name \"ID\": unsupported Scan, storing driver.Value type i-CodePudding

I am getting the below error when I try to call err = row.Scan(&resourceList, resourceTypeId)

Scan error on column index 0, name "ID": unsupported Scan, storing driver.Value type int64 into type *[]authService.Permission"

type Permission struct {
    ID               int    `json:"id"`
    Name             string `json:"name"`
    Description      string `json:"description"`
    ParentResourceID int    `json:"parentResourceId"`
}

func GetResourcesByResourceTypeId(resourceTypeId string) ([]Permission, string, error) {
db, ctx := db.GetDB()
query := "CALL usp_GetParentResourceListByResourceTypeID(?)"
var resourceList []Permission
stmt, err := db.Prepare(query)
defer stmt.Close()
if err != nil {
    log.Errorln("Error in preparing statement. "   err.Error())
    return nil, "Error in preparing statement.", err
}

row := stmt.QueryRowContext(ctx, resourceTypeId)

err = row.Scan(&resourceList, resourceTypeId)
if err == nil {
    return resourceList, "Resource retrieval.", nil
}

log.Warningln("Resource retrieval failed, ResourceTypeID: "   resourceTypeId   ".")
return resourceList, "Resource retrieval failed.", nil
}

SQL Returns below

ID  Name
15  Applications
16  Subscriptions
17  Payments

The same query works fine when I try to use SQL Server with EXEC statement in the query.

Any idea on whats wrong here? Thanks in advance.

CodePudding user response：

There are a few issues here. Firstly QueryRowContext

executes a query that is expected to return at most one row

Your question indicates that your statement returns multiple results so this is not the right function to use (QueryContext would be more appropriate).

The second issue is as stated in the error:

unsupported Scan, storing driver.Value type int64 into type *[]authService.Permission"

The first column in the result set is an integer (probably the value 15 in this case) and you are trying to scan that into a []Permission. If you changed var resourceList []Permission to var resourceList int that error would be fixed (but the second parameter also needs work).

Take a look at this example in the documentation. Taking that code and applying it to your situation will result in something like the following (untested; just intended to point you in the right direction):

rows, err := db.QueryContext(ctx, "CALL usp_GetParentResourceListByResourceTypeID(?)", resourceTypeId)
if err != nil {
    log.Fatal(err)
}
defer rows.Close()

var resourceList []Permission

for rows.Next() {
    var resource Permission
    if err := rows.Scan(&resource.ID, &resource.Name); err != nil {
        // Check for a scan error.
        // Query rows will be closed with defer.
        log.Fatal(err)
    }
    resourceList = append(resourceList, resource )
}

rerr := rows.Close()
if rerr != nil {
    log.Fatal(rerr)
}

if err := rows.Err(); err != nil {
    log.Fatal(err)
}

Note: Your structure Permission contains four elements but the query returns two columns so I'm not really sure how you intend to fill the other two (or what the mapping is).