Database rows scan + unknown number of columns + insert records

[Shiloh_Goodwin]

Hi,
I am trying to make a program that retrieves data from a database, changes the data and inserts the data into another table. I will know the columns/tables during runtime only. Here is what I have working so far. The only part I have not been able to figure out is how to change the values retrieved. Any help will be appreciated.

func ETL() {
	dbconn, err := sql.Open("mssql", "server=localhost;user id=[gouser];password=[password];database=test;Trusted_Connection=yes;connection timeout=30;")
	if err != nil {
		log.Fatal(err)
	}

	rows, err := dbconn.Query("select name, address1, email from t_people")

	cols, err := rows.Columns()
	vals := make([]interface{}, len(cols))
	for i := range cols {
		var ii interface{}
		vals[i] = &ii
	}
	for rows.Next() {
		err = rows.Scan(vals...)
	}


	// vals["name"] = "New Name" ??????????
	// vals["address1"] = "New Address" ??????????
	// vals["email"] = "New Email" ??????????

	tx, _ := dbconn.Begin()

	stmt, txerr := tx.Prepare("insert into t_people_new(name, address1, email) values (?,?,?)")

	if txerr != nil {
		log.Fatal(txerr)
	}

	_, err = stmt.Exec(vals...)

	if err != nil {
		log.Fatal(err)
	}
}

[radovskyb (Benjamin Radovsky)]

Hi @Shiloh_Goodwin,

I would suggest setting up a specific struct for the table type. Here’s an example of what I believe you’re trying to do:

package main

import (
	"database/sql"
	"log"
)

type Person struct {
	Name  string
	Addr  string
	Email string
}

func main() {
	dbconn, err := sql.Open("mssql", "server=localhost;user id=[gouser];password=[password];database=test;Trusted_Connection=yes;connection timeout=30;")
	if err != nil {
		log.Fatal(err)
	}

	var people []*Person

	rows, err := dbconn.Query("select name, address1, email from t_people")
	if err != nil {
		// Handle error
	}

	for rows.Next() {
		var p Person

		err := rows.Scan(&p.Name, &p.Addr, &p.Email)
		if err != nil {
			// Handle error
		}

		// Update attributes in struct here or further down below.
		// p.Name = "New name"

		people = append(people, &p)
	}

	if err := rows.Err(); err != nil {
		// Handle error
	}

	// Update values of people or person here if not changed above.
	people[0].Name = "New name for first person returned"

	// Do insert here.
}

[Shiloh_Goodwin]

@radovskyb

) type Person struct {

Hi Benjamin,

I am trying to build a tool for businesses to use. I will not know the names of the tables during coding. I will only know it during runtime.

[radovskyb (Benjamin Radovsky)]

Oh ok. In your example you are selecting from t_people and trying to change columns of t_people. How at runtime will you know what fields and table to select from?

[radovskyb (Benjamin Radovsky)]

Also are you only ever retrieving 1 record at a time or multiple? If it’s a single record, you could use a map instead of interface. (Same with multiple but not as straight forward)

[Shiloh_Goodwin]

I will read that from another table or from an init file. For the example I thought it would be easier to hard code the SQL in a string.

[Shiloh_Goodwin]

I will be retrieving multiple records.

[radovskyb (Benjamin Radovsky)]

You could probably use some maps like the following… I changed a few things here to create an actual working example on my comp:

package main

import (
	"database/sql"
	"log"

	_ "github.com/mattn/go-sqlite3"
)

func main() {
	dbconn, err := sql.Open("sqlite3", "dbtest.db")
	if err != nil {
		log.Fatal(err)
	}

	rows, err := dbconn.Query("select name, email from users")
	if err != nil {
		log.Fatalln(err)
	}

	cols, err := rows.Columns()
	if err != nil {
		log.Fatalln(err)
	}

	// Map of column names to value.
	var maps = []map[string]interface{}{}

	for rows.Next() {
		vals := make([]interface{}, len(cols))
		for i := range cols {
			var ii interface{}
			vals[i] = &ii
		}

		err := rows.Scan(vals...)
		if err != nil {
			log.Fatalln(err)
		}

		m := map[string]interface{}{}
		for i, colName := range cols {
			val := vals[i].(*interface{})
			m[colName] = *val
		}

		// Update attributes in map here or further down below.
		m["name"] = "New name"

		maps = append(maps, m)
	}

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

	// Insert here.
}

[radovskyb (Benjamin Radovsky)]

If you don’t need to actually keep the records in memory like in my example, here’s a complete working example that updates records and inserts.

I was showing an example making the table and fields dynamic, but my example isn’t actually sql injection safe, but gives an idea

package main

import (
	"database/sql"
	"fmt"
	"log"
	"strings"

	_ "github.com/mattn/go-sqlite3"
)

func main() {
	tableName := "users"
	fields := []string{"name", "email"}

	dbconn, err := sql.Open("sqlite3", "dbtest.db")
	if err != nil {
		log.Fatal(err)
	}

	rows, err := dbconn.Query("select name, email from " + tableName)
	if err != nil {
		log.Fatalln(err)
	}

	cols, err := rows.Columns()
	if err != nil {
		log.Fatalln(err)
	}

	// tx for inserting.
	tx, err := dbconn.Begin()
	if err != nil {
		log.Fatalln(err)
	}

	query := fmt.Sprintf("insert into %s_new(%s) values (%s)",
		tableName,
		strings.Join(fields, ", "),
		strings.TrimSuffix(strings.Repeat("?,", len(fields)), ","),
	)

	stmt, txerr := tx.Prepare(query)
	if txerr != nil {
		log.Fatal(txerr)
	}

	for rows.Next() {
		vals := make([]interface{}, len(cols))
		for i := range cols {
			var ii interface{}
			vals[i] = &ii
		}

		err := rows.Scan(vals...)
		if err != nil {
			log.Fatalln(err)
		}

		m := map[string]interface{}{}
		for i, colName := range cols {
			val := vals[i].(*interface{})
			m[colName] = *val
		}

		var args []interface{}

		for _, field := range fields {
			if _, found := m[field]; !found {
				continue
			}

			// Update attributes in map here or further down below.
			m[field] = fmt.Sprintf("%s (New)", m[field])

			args = append(args, m[field])
		}

		_, err = stmt.Exec(args...)
		if err != nil {
			log.Fatal(err)
		}
	}

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

	// Save commits.
	if err := tx.Commit(); err != nil {
		log.Fatalln(err)
	}
}

[Shiloh_Goodwin]

Hi Benjamin,

I will try this out tonight when I get home from work(and the kids are asleep). Thanks for taking the time to reply to my post. I really appreciate it.

[Shiloh_Goodwin]

@radovskyb

tx, err := dbconn.Begin() if err != nil { log.Fatalln(err) }

Thanks this worked.

[radovskyb (Benjamin Radovsky)]

No worries! Glad to help

[Shiloh_Goodwin]

@radovskyb

I had another question for you. How would I deal with a blob field? They are usually returned as a []byte array.