Golang: Mysql Prepare Insert statements do not add rows into db table

Question

So I'm trying to use a mysql driver to insert data into a database.

Specifically, I'm using this one:

"github.com/go-sql-driver/mysql"

This is my code

func main() {

    db, err := sql.Open("mysql", "psanker:123@/education_data")
    err = db.Ping()

    if err != nil {
        fmt.Println("Failed to prepare connection to database")
        log.Fatal("Error:", err.Error())
    }

    defer db.Close()

    content, err := ioutil.ReadFile("activities.csv")

    lines := strings.Split(string(content), "\r")

    //only work so long as I have one district
    rows, err := db.Query("SELECT id FROM districts")
    var districtId int
    defer rows.Close()

    for rows.Next() {
        err := rows.Scan(&districtId)
        check(err)
    }


    for i, line := range lines {
        if i > 1 {
            splitStr := strings.Split(line, ",")
            var activityCode string

            if strings.Contains(splitStr[0], "-") {
                //this is an activity
                activityCode = splitStr[0]
                stmt1, _ := db.Prepare("INSERT INTO activities(code) VALUES(?)")
                stmt2, _ := db.Prepare("INSERT INTO activities(name) VALUES(?)")
                res, _ := stmt1.Exec(splitStr[0])
                stmt2.Exec(splitStr[1])
            } else {
                //this is a sub activity
                stmt1, _ := db.Prepare("INSERT INTO sub_activities(code) VALUES(?)")
                stmt2, _ := db.Prepare("INSERT INTO sub_activities(name) VALUES(?)")
                stmt1.Exec(splitStr[0])
                stmt2.Exec(splitStr[1])
                if activityCode != "" {
                    rows, _ := db.Query("SELECT id from activities where code = ?", activityCode)
                    var activityId int
                    for rows.Next() {
                        err := rows.Scan(&activityId)
                        check(err)
                        stmt3, err := db.Prepare("INSERT INTO sub_activities(activity_id) VALUES(?)")
                        stmt3.Exec(activityId)
                    }
                }
                rows, _ := db.Query("SELECT id from sub_activities where code= ?", splitStr[0])
                var sub_activityId int
                for rows.Next() {
                    err := rows.Scan(&sub_activityId)
                    check(err)
                    stmt5, _ := db.Prepare("INSERT INTO sub_activity_expenditure(district_id) VALUES(?)")
                    stmt6, _ := db.Prepare("INSERT INTO sub_activity_expenditure(sub_activity_id) VALUES(?)")
                    stmt7, _ := db.Prepare("INSERT INTO sub_activity_expenditure(expenditure) VALUES(?)")
                    stmt5.Exec(districtId)
                    stmt6.Exec(sub_activityId)
                    stmt7.Exec(splitStr[2])
                }
            }
        }
    }
}

When I check Mysql database, there are no rows inserted into the tables. I think I'm accessing the right database because the initial query that gets id from districts is returning 1, which is correct.

What's going on?

EDIT

I have confirmed I'm in the right db by doing this

rows, err = db.Query("SELECT DATABASE();")
var test string

for rows.Next() {
    rows.Scan(&test)
    fmt.Println(test)
}

Which prints out education_data

got it, it shows that I'm in the right database, which is education_data — praks5432
– praks5432, Commented Jun 4, 2015 at 20:39

Booboo · Accepted Answer · 2019-09-25 17:36:33Z

1

As you are trying to insert into a table, I was reminded (in chat at least) that your columns are non-null and you really meant to create one row when you were in fact creating two. It failed on stmt1 and stmt2 because non-nullable columns were not given parameters to replace for all necessary data for insertion to succeed.

Focus on creating one row for stmt1 and stmt2 (combine them into a single stmt1 to execute with 2 parameters).

Make sure strings are wrapped with a single quote.

edited Sep 25, 2019 at 17:36

Booboo

45.6k4 gold badges46 silver badges74 bronze badges

answered Jun 4, 2015 at 20:44

Drew

25k10 gold badges47 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

praks5432 Over a year ago

So I printed out splitStr[0] and splitStr[1] and they were both string values

Drew Over a year ago

those i can understand. it is the districtId, activityId, and sub_activityId

praks5432 Over a year ago

so I commented everything out apart from the stuff for stmt1 and stmt2, and still nothing. I do a SELECT * from activities, and I get this output - mysql> SELECT * FROM activities; Empty set (0.00 sec)

Drew Over a year ago

i believe that it is in the preparing of the statement (with 0 or no values in key variables) that causes the insert stmt to fail. Can you echo out during normal execution somewhere what the values are of the above 3 variables. i just don't understand where they are being set. For instance, where is activityId being set by you?

Drew Over a year ago

i say this as a non-go programmer so keep that in mind

|

Collectives™ on Stack Overflow

Golang: Mysql Prepare Insert statements do not add rows into db table

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related