database/sql, p/pg, BLOB

[Nigel Vickers]

I am having problems writing to and reading from the POSTGRES type  BYTEA. I am trying to manage High Resolution SW images in TIFF. I have not been able to identify the sql type that the abstraction layer requires and the pg driver just returns an empty []bytes and no error. Can someone point me in the right direction?

thanks,

Nigel Vickers

[fauige...@gmail.com]

Here's a program that works for me (I left out the error handling for brevity)

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/lib/pq"
    "log"
)

func main() {
    db, err := sql.Open("postgres", "host=localhost dbname=example sslmode=disable")
    if err != nil {
        log.Fatal(err)
    }
    //db.Exec("drop table t")
    db.Exec("create table t(id serial primary key, b bytea)")
    bytes := []byte("abc")
    db.Exec("insert into t(b) values($1)", bytes)
    rows, err := db.Query("select b from t")
    for rows.Next() {
        rows.Scan(&bytes)
        fmt.Printf("%s\n", bytes) //prints abc
    }
}

[fauige...@gmail.com]

I should add that my program would print abc even if the Scan didn't work because I'm reusing the bytes variable which already contains abc (and there's no error handling). To see that the code actually reads something you'd have to define a new variable:

        var b []byte
        rows.Scan(&b)
        fmt.Printf("%s\n", b) //prints abc

[Nigel Vickers]

Thanks for the reply. The only difference between your's and my code is that my "[]byte" is embedded in a struct and typed. The query is within a method called with the type as receiver. All other fields are working as declared. The TIFF arrays are all individually under 40MB. No errors just empty...  frustrating. The database is executing the query and  the net port says it's responding.

[Rodrigo Kochenburger]

What do you mean by typed? Is it a slice of different type? If so, it won't work.

The database/sql pkg does not know how to unpack a byte string into a slice of arbritary type, and Go does not have type cast (only conversions).

That said you can implement the sql.Scanner interface to specify how the []byte can be unpacked.

Hope it helps.

Cheers

--
You received this message because you are subscribed to the Google Groups "golang-nuts" group.
To unsubscribe from this group and stop receiving emails from it, send an email to golang-nuts...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

--

- RK

[Kamil Kisiel]

Can you post a code example?

[Tamás Gulácsi]

Ppostgres has changes default coding/escaping off bytea fields somewhere around 9.0

[Nigel Vickers]

The fields in the database, apart from the bytea, are being correctly inserted. The []byte array is being populated and passed but the bytea field remains empty. The db log file records no error. There are signs that insert is being carried out twice, once with the correct payload and a second time with an empty []byte. I have as yet been unable to isolate this second call if it exists.

// type Attachment

type MsgAttachment struct{
       Mattachmentnr sql.NullInt64
       Messagefk sql.NullInt64
       Aname sql.NullString
       Mblob []byte
}

// DML vom db

CREATE TABLE msgattachment

(

mattachnr serial NOT NULL,

messagefk int,

aname text,

ablob bytea

 )

//call after declaration and population

msga.InsertAttachment()

func (a MsgAttachment) InsertAttachment() error {

       rhediledb, err := sql.Open("postgres", connectString)
       if err != nil {
                       fmt.Println("in InsertAttachment sql.Open failed", err)
       }

       defer rhediledb.Close()

       stmt, err := rhediledb.Prepare("INSERT INTO msgattachment ( messagefk, aname, ablob) Values($1,$2,$3)")

       if err != nil {
               fmt.Println("in InsertAttachment prepare error:", err)
       }
       res, err := stmt.Exec(a.Messagefk, a.Aname, a.Mblob)
       if err != nil {
               fmt.Println("in InsertAttachment exec error:", res, err)
       }
       return err
}

[shiva...@gmail.com]

I am facing the same issue. We’re you ever able to get it resolved.

[roger peppe]

Hi Nigel,

It all seems to work as expected for me using this code:
https://play.golang.org/p/QGBT6kqxHBS
I'm using Postgres 9.5.12.

Can you distill your problem into some self-contained code that
demonstrates the issue?

cheers,
rog.