Sqlx: Failing to understand StructScan

[fpolli]

I hope this is not the wrong place, but I have been unable to find documentation that explains or gives examples of how to use StructScan and it's not working for me. I have tried the rows.StructScan and the func StructScan.

I am trying to pass the destination struct into a function that runs the query then scans the results using StructScan, passing the results back to the caller to be converted back into the target type. Let's give an example:

model := Team{}

data := getdata(model)

func getdata(dest interface{}) interface{} {
...
run the query and get the rows
...

for rows.Next() {
    err = rows.StructScan(&dest)
    if err != nil {
        log.Fatalf("Scan error: %s\n",err)
    }
    data=append(data,&dest)
}//rows.Next

rows.Close()
return data

}

This gives me a "panic: reflect: call of github.com/jmoiron/sqlx/reflectx.(*Mapper).TraversalsByName on interface Value" error.

When I try to use the other StructScan, passing the getdata function a []Team, I get the error message that I have to pass a slice, but the docs show interface{} for the argument type, not []interface{}

Some guidance would be great. There just is not a lot of information on how to use this available. Thanks.

[jmoiron]

Hi @fpolli,

What you are doing here is passing a pointer to an interface, which is almost never what you want. In your code, dest is type interface{}, and you are passing type *interface{}, which is being wrapped up in another interface. This kind of indirection could go on forever; I've made sqlx catch these programming errors early so that it doesn't need to spend time traversing potentially endless chains of interfaces to get to the concrete type.

Take this code:

model := Team{}
rows, err := db.Queryx(....)
// handle error
for rows.Next() {
    rows.StructScan(&model)
    // use model here
}

This works because the type passed to struct scan is *Team, which then gets wrapped in an interface{} value as part of StructScan's call signature (it takes interface{} params; the conversion here is implicit and checked by the compiler). An interface is basically a pointer and a type; so in this case, the pointer points to the address of model, and the type points to an entry in a table corresponding to *Team; read this article for more on interfaces

The _other_ struct scan is meant to scan into a slice. The type is interface{} because this is how you a slice of any type; []interface{} is its own type with its own memory layout and does _not_ mean "a slice of any type"; it means "a slice of interface{} types". The function StructScan is meant to simplify this code without requiring the use of sqlx.DB:

teams := make([]Team, 0)
rows, err := db.Query(...)
// handle error
err = sqlx.StructScan(rows, &teams)

There is, by the way, a large comment above StructScan; I'm aware of this confusion:

// FIXME: StructScan was the very first bit of API in sqlx, and now unfortunately
// it doesn't really feel like it's named properly.  There is an incongruency
// between this and the way that StructScan (which might better be ScanStruct
// anyway) works on a rows object.

You could rewrite your code to do rows.StructScan(dest), and it would work, but I don't think this is necessary.

1. It is unwieldy to actually append to a slice which is inside an interface{} without being able to use a type switch; you have to use reflect.
2. Your code is appending the address of the same team to a slice, meaning that at the end you will have N identical pointers in your slice. You probably want to append a value.
3. sqlx already does this all for you:

teams := make([]Team, 0, 10)
err := db.Select(&teams, "SELECT * FROM teams LIMIT 10")
// handle error

I hope this clears things up.

[rvzanten]

So, and what if you would like to use getData as a global function which you pass the appropriate structs that it needs to fill like the original post kind described. In other words, what if you don't know what struct "model" needs to contain?

An example what i mean:

//and you can have as many of these as you have tables, so no switch

func main(){

    //order table
    orderData := order{}
    err := getData("order", orderData)

    if err == nil {
        fmt.Println(orderData.Amount)
    }

    //book table
    bookData := book{}
    err := getData("books", bookData)

    if err == nil {
        fmt.Println(bookData.Name)
    }
}


func getData(table string, dataReceiver interface{}) error {
    rows, err = db.Queryx(....use table parameter to select table)
    //error handling
    for rows.Next() {
        err := rows.StructScan(&dataReceiver)
        //error handling
    }
}

[jmoiron]

You can not use SQL parameters for table names, etc. SQL parameters are meant for things that are in WHERE and VALUES clauses only. You could use fmt.Sprintf to format the query using the table you require, but do not do this for user input tables as it opens you up to injection attacks.

func get10(db *sqlx.DB, table string, data interface{}) error {
    q := fmt.Sprintf(`SELECT * FROM "%s" LIMIT 10`, table)
    err := db.Select(&data, q)
    return err
}

[rvzanten]

I don't really think you understand my question.
The thing is that you can't change a pointer interface{} to the actual struct. I tried a lot of things, but its not possible since it eventually requires casting to the actual struct, which is unknown. Thats why it would be awesome if you could just feed the StructScan an interface pointer to a struct you feed it.

But now it throws:
"call of github.com/jmoiron/sqlx/reflectx.(*Mapper).TraversalsByName on interface Value" :(

[BrianLeishman]

Same as @rvzanten, this also stops you from being able to use dynamic structs