Sqlx: MapScan values are garbled

Created on 29 May 2016 · 16Comments · Source: jmoiron/sqlx

I'm implementing a SQL server and I got a strange result from using MapScan. It looks garbled:

{
   "id": "Mg==",
   "name": "Sm9lIFBlc2Np"
}

I wonder if there is something I'm missing about how to use MapScan? Can you help me understand what's going wrong here?

Here is my code:

type Row map[string]interface{}
var scannedRows []Row
...

i := 0
rows, _ := h.db.Queryx(q[0])

for rows.Next() {
  results := Row{}
  err := rows.MapScan(results)
  checkErr(err, w)
  scannedRows[i] = results
  i++
}

bug cannot reproduce

Source

volkanunsal

👍3

Most helpful comment

I also encountered this problem, so I try transform the field like this。 It works :

        tmp := make(map[string]interface{})
        rows.MapScan(tmp)
        for k, encoded := range tmp {
            switch encoded.(type) {
            case []byte:
                tmp[k] = string(encoded.([]byte))
            }
        }

wupeaking on 27 Dec 2017

🎉14

All 16 comments

Can someone help me with this? I found a related issue here, but it looks like this should have been solved by now. Maybe I'm just not getting it. Any help would be appreciated.

volkanunsal on 4 Jun 2016

Hi @volkanunsal, can you replicate on an sqlite3 database?

I received a similar report about base64 encoded values in mapScan in the past (https://github.com/jmoiron/sqlx/issues/191), but I wasn't really sure how to replicate. If you can give me some more detail about your database, schema, query, I can look into it, especially if the database is one of the 3 in the sqlx test suite (postgres, sqlite3 or mysql).

jmoiron on 15 Jun 2016

@jmoiron It happens with postgres. I'm not exactly sure how to create a test case for it in sqlite, but I'll give it a shot this weekend.

volkanunsal on 16 Jun 2016

The schema of the table is very simple.

      Table "public.users"
│ id     │ numeric │           │
│ name   │ text    │           │

The query is SELECT * from users;

This is the part where I'm encoding it into JSON. Could this be where the encoding is getting messed up?

type rowMap map[string]interface{}

type fieldMap map[string]string

type fieldsMap map[string]fieldMap

type successPayload struct {
    Rows      []rowMap  `json:"rows"`
    Fields    fieldsMap `json:"fields"`
    Elapsed   float64   `json:"time"`
    TotalRows int64     `json:"total_rows"`
}

res := successPayload{scannedRows, fields, elapsed, count}

// Write it back to the client.
w.Header().Set("Content-Type", "application/json; charset=utf-8")
json.NewEncoder(w).Encode(res)

volkanunsal on 16 Jun 2016

I just tried it with sqlite3 using the following schema.

CREATE TABLE `users` (
    `id` INTEGER,
    `name` VARCHAR(64) NULL
  );

In sqlite3, the id field works, but the name field is still encoded in base64. Here is my test case:

https://gist.github.com/volkanunsal/e5b84aef87317fb4dff75c97f4c875a8

volkanunsal on 19 Jun 2016

@jmoiron Have you had a chance to look at the example above? Do you see any clues as to what might be happening? Is there anything I can do to fix this on my own end? Thanks.

volkanunsal on 3 Jul 2016

definately seeing this with a mysql backend

JonathanFraser on 20 Feb 2017

👍2

I have the same problem with SliceScan(), it will return []byte for the string values which the JSON encoder then will convert to base64. My workaround is manually convert them to string before JSON.

ChristophPech on 15 Mar 2017

I got the same error with mysql for MapScan() and for SliceScan() as well.

PumpkinSeed on 9 May 2017

This may be related to prepared queries vs non-prepared queries. Try preparing the queries first and see if it fixes the problem.

JonathanFraser on 9 May 2017

When performing a SELECT combined with Rows.mapScan, it base64 encodes the Postgres JSON data type instead of either a) parsing it or b) returning it as a string unaltered.

Any advice?

brandonros on 9 Jun 2017

👍1

The std encode/json encode byte slice with base64.

reproduce sample

SHOW FULL COLUMNS FROM _tablename_

go: 1.9
mysql driver: github.com/go-sql-driver/mysql (8fefef06)

// rows.Scan => []uint8

type dummy struct {
        Field      *string `db:"Field"`
        Type       *string `db:"Type"`
        Collation  *string `db:"Collation"`
        Null       *string `db:"Null"`
        Key        *string `db:"Key"`
        Default    *string `db:"Default"`
        Extra      *string `db:"Extra"`
        Privileges *string `db:"Privileges"`
        Comment    *string `db:"Comment"`
}

Fields with *string _json marshal_ as expected, with interface{} not.

Doc for row#Scan database/sql/sql.go

elvuel on 24 Nov 2017

👍1

I also encountered this problem, so I try transform the field like this。 It works :

        tmp := make(map[string]interface{})
        rows.MapScan(tmp)
        for k, encoded := range tmp {
            switch encoded.(type) {
            case []byte:
                tmp[k] = string(encoded.([]byte))
            }
        }

wupeaking on 27 Dec 2017

🎉14

I'm implementing a SQL server and I got a strange result from using MapScan. It looks garbled:
{
   "id": "Mg==",
   "name": "Sm9lIFBlc2Np"
}
I wonder if there is something I'm missing about how to use MapScan? Can you help me understand what's going wrong here?

Here is my code:
type Row map[string]interface{}
var scannedRows []Row
...

i := 0
rows, _ := h.db.Queryx(q[0])

for rows.Next() {
  results := Row{}
  err := rows.MapScan(results)
  checkErr(err, w)
  scannedRows[i] = results
  i++
}

how to solve this problem?

LuckyChen666 on 19 Mar 2020

columns, _ := rows.Columns()
columnTypes, _ := rows.ColumnTypes()
columnPointers := make([]interface{}, len(columns))

for i := 0; i < len(columns); i++ {
    t := columnTypes[i].ScanType()
    ...
    if t.Name() == "RawBytes" {
        columnPointers[i] = new(sql.RawBytes)
    }
        ...
}
...
for col, result := range results {
    switch result.(type) {
        case *sql.RawBytes:
           v, _ := result.(*sql.RawBytes)
           results[col] = string(*v)
        ....
    }
}

I'm implementing a SQL server and I got a strange result from using MapScan. It looks garbled:
{
   "id": "Mg==",
   "name": "Sm9lIFBlc2Np"
}
I wonder if there is something I'm missing about how to use MapScan? Can you help me understand what's going wrong here?
Here is my code:
type Row map[string]interface{}
var scannedRows []Row
...

i := 0
rows, _ := h.db.Queryx(q[0])

for rows.Next() {
  results := Row{}
  err := rows.MapScan(results)
  checkErr(err, w)
  scannedRows[i] = results
  i++
}
I'm implementing a SQL server and I got a strange result from using MapScan. It looks garbled:
{
   "id": "Mg==",
   "name": "Sm9lIFBlc2Np"
}
I wonder if there is something I'm missing about how to use MapScan? Can you help me understand what's going wrong here?
Here is my code:
type Row map[string]interface{}
var scannedRows []Row
...

i := 0
rows, _ := h.db.Queryx(q[0])

for rows.Next() {
  results := Row{}
  err := rows.MapScan(results)
  checkErr(err, w)
  scannedRows[i] = results
  i++
}
how to solve this problem?