Pass struct interface to sqlite exec

Question

In Go, I'm trying to pass an interface{} to the statement.Exec() function from go-sqlite3. I'm sure this is a solved problem, but I cannot figure it out.

Basically I have a struct with the row data which I want to pass to a function that will insert it to a sqlite db. The thing is I want to be able to programmatically control what goes into the statement.Exec() function

Here is an excerpt:


type hostRows struct {
    domain   string
}

type clientRows struct {
    name   string
}

func main() {

    ...

    data := hostRows{domain: "dom.com"}
    insertRow(sqliteDatabase, data)

    data2 := clientRows{name: "bob"}
    insertRow(sqliteDatabase, data2)

    ...

}

func insertRow(db *sql.DB, row interface{}) {

    insertSQL := "INSERT INTO table(col) VALUES (?)"
    statement, _ := db.Prepare(insertSQL)
    
    statement.Exec(row) // here's the issue, how can I extract the element in the interface to pass it to the function for Exec to understand
    
}

I know that in this example, I could hard code the row type to the struct and type statement.Exec(row.(hostRows).domain), but now the code will break to when the client struct is passed.

here is the deceleration for the Exec function

func (s *Stmt) Exec(args ...interface{}) (Result, error)

I've tried playing with reflect but it hasn't worked for me so far. My only solution for the moment is using a switch condition that could check and prepare the right command for Exec, but this is less than dodgy.

type hostRows struct {
    domain   string
}

type clientRows struct {
    name   string
}

func main() {

    ...

    data := hostRows{domain: "dom.com"}
    insertRow(sqliteDatabase, 1, data)

    data2 := clientRows{name: "bob"}
    insertRow(sqliteDatabase, 2, data2)

    ...

}

func insertRow(db *sql.DB, i int, row interface{}) {

    insertSQL := "INSERT INTO table(col) VALUES (?)"
    statement, _ := db.Prepare(insertSQL)
    
    // This basically could be a working solution, but I'm sure there is a better one
    switch i {
        case 1:
            data := row.(hostRows)
            statement.Exec(data.domain)
        case 2:
            data := row.(clientRows)
            statement.Exec(data.name)
    }
    
}

edit: corrected the INSERT statement ; forget the columns. corrected statement.Exec(row.domain) to statement.Exec(row.(hostRows).domain) edit2: added second example

What SQL statement exactly should be generated for insertRow with a hostRows? What with clientRow? Please give an example as struct cannot be used as arguments to Exec. — Volker, Mar 04 '21 at 21:03
That's basically my question. How can I pass the elements of a struct when the name of the variables in the struct can change depending on which one I send to `insertRow`? — will.mendil, Mar 04 '21 at 21:08
@Volker I added a second example of the type of behaviour I'm looking for though the solution is boilerplate bad... — will.mendil, Mar 04 '21 at 21:14

Pablo Flores · Accepted Answer · 2021-03-08T17:31:33.230

2

Remember that in order for reflect.Interface() to work, you must export the fields. To achieve what you want using reflection, you could try something like this:

type hostRows struct {
    //Should export field to read it using reflect.Value.Interface()
    Domain string
}

type clientRows struct {
    //Should export field to read it using reflect.Value.Interface()
    Name string
}

func insertRow(db *sql.DB, i int, row interface{}) {
    rv := reflect.ValueOf(row)
    var args []interface{}
    for i := 0; i < rv.NumField(); i++ {
        args = append(args, rv.Field(i).Interface())
    }
    db.Exec("Insert Satement...", args...)
}

edited Mar 08 '21 at 17:31

answered Mar 04 '21 at 21:51

Pablo Flores

1,350
13
15

Genius, thanks, I guess I was on the right track but I had forgotten about exporting the fields from the struct. This is still a concept I struggle to work with. Thanks @Pablo – will.mendil Mar 04 '21 at 22:13

Pass struct interface to sqlite exec

1 Answers1