[go-nuts] database/sql, p/pg, BLOB

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
     }
}

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

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.

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

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

Can you post a code example?

*


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
}
*