Scan error on column index 0, name 'ID': Scan not supported, storing driver.Value of type int64 to type *authService.Permission"

Question

I receive the following error when I try to call err = row.Scan(&resourceList, resourceTypeId)

Scan error on column index 0, name 'ID': Scan not supported, storing driver.Value of type int64 to 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 the following

ID  Name
15  Applications
16  Subscriptions
17  Payments

The same query works fine when I try to use SQL Server with

EXEC statement in query.

Any idea what's going wrong here? Thanks in advance.

Answer 1

There are some issues here. First QueryRowContext

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

The second problem is as stated in the error:

The first column in the result set is an integer (in this case probably the value 15), and you are trying to scan it into []Permission. if you change var resourceList []Permission to var resourceList int The bug will be fixed (but the second parameter also needs to work).

View this example documentation. Taking that code and applying it to your situation will result in something like the following (untested; just 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 quite sure how you intend to populate the other two columns (or what the mapping is) .