When trying to Insert a struct containing a slice, I get this error:

sql: converting argument #1's type: unsupported type []float64, a slice

The struct field is []float64 and the type of the column in Postgres is real[].

Does gorp support slices and this error is particular to my program or is this yet unimplemented?

First thank you very much for nice ORM, now it's one of the most documented DB libraries for Go and one of a few up-to-date.

Go's native type "bool" is not supported. Consider this:

type PersistentUser struct {
Key int32
Id string
PassedTraining bool
}

this code will panic (panic: unknown value type for bind)

pu := &PersistentUser{43,"33r",false}

// Insert your rows
err := dbmap.Insert(pu)
if err!=nil {
panic(err)
}

Windows 7/x86, MySql 5.1, Go weekly.2011-12-22 11071

I have a user model, I want to use Insert(list ...interface{}) error,but it returns an error type;

gorp provides an Exec(query string, args ...interface{}) (sql.Result, error) method, but its args are string , may you provides a method which args are list inerface{} and return an sql.Result ?

From @sqs comment in #40

If you try to select into var foo []_MyType instead of a var foo *[]_MyType (e.g., you forget the & in the arg), then you get an error message that is no longer applicable with this new feature:
gorp: Cannot SELECT into non-struct type: []*MyType

Trying to build the current tip fails thusly:

./gorp.go:699: too many arguments in call to insert
./gorp.go:699: insert(m, m, list...) used as value
./gorp.go:713: too many arguments in call to update
./gorp.go:713: not enough arguments to return
./gorp.go:746: too many arguments in call to get
./gorp.go:746: not enough arguments to return
./gorp_test.go:783: insert redeclared in this block
previous declaration at ./gorp.go:1339
./gorp_test.go:790: update redeclared in this block
previous declaration at ./gorp.go:1290
./gorp_test.go:807: get redeclared in this block
previous declaration at ./gorp.go:1188
./gorp_test.go:860: rawselect redeclared in this block
previous declaration at ./gorp.go:1032
./gorp.go:746: too many errors
FAIL gorp [build failed]

If a struct has an unsigned ID, then CreateAllTables generates incorrect SQL. E.g.

type Person struct {
    Id               uint32
    Name             string
    Created          int64
    Updated          int64
}

The trace statement says

gorptest: 20:28:16.303405 create table if not exists `person` (`Id` varchar(255) not null primary key auto_increment, `Name` varchar(255), `Created` bigint, `Updated` bigint)  engine=InnoDB charset=UTF8; [[]]

So a varchar(255) has been incorrectly substituted instead of the 'int(10) unsigned' that would be expected.

The same problem applies with uint and uint64 too.

It also seems to happen with all uint, uint32 or uint64 fields, regardless of whether they are primary keys.

In Go a string is basically an immutable []byte. This means a string type can contain binary data. This is not the same thing as CHAR, VARCHAR, or TEXT. In many cases it would be preferable to use BINARY, VARBINARY or BLOB.

There is a pull request that wants to use tags to indicate primary key and auto increment: #26

This is great, but I would also like to have types + lengths in mind as well.

Here's a small sample use-case:

type Field struct {
    Token string `db:",bytes:=16,primary"` // BINARY(16)
    Data []byte `db:",bytes"` // BLOB
}

The tag format would essentially be this:

db:"[name],[type[:[=]length]],[primary/autoincrement/unique/index]"

Notes:

• "=" is used to indicate CHAR/BINARY verses VARCHAR/VARBINARY
• Commas are required for specifying an nth argument. So for specifying "primary" and not "name" or "type" you would do ",,primary".
• A panic would happen if "autoincrement" was specified for a non-int type.

Types would resemble Go types:

• bytes ([]byte -> binary/varbinary/blob)
• string (-> char/varchar/text)
• int

Custom type conversion (structs, etc...) would be handled similar to encoding/json's type Marshaler interface and type Unmarshaler interface:

type IntMarshaler {
    MarshalGorpInt() (int64, error)
}
type IntUnmarshaler {
    UnmarshalGorpInt(int64) error
}
type StringMarshaler {
    MarshalGorpInt() (string, error)
}
type StringUnmarshaler {
    UnmarshalGorpString(string) error
}
type BytesMarshaler {
    MarshalGorpBytes() ([]bytes, error)
}
type BytesUnmarshaler {
    UnmarshalGorpBytes([]bytes) error
}
// .... other types
// Just two interfaces might be easier:
type Marshaler {
    MarshalGorp() (interface{}, error)
}
type Unmarshaler {
    UnmarshalGorp(interface{}) error
}

The type of Marshaler/Unmarshaler would correspond to the tagged type. The go type in the struct would have to implement the corresponding Marshaler/Unmarshaler. If it does not and the primitive type is supported, then no serialization is done. Other types that aren't normally supported would panic without a Marshaler, as it does now.

Example:

type Data struct {
// ... stuff
}

type Field struct {
    Token string `db:",bytes:=16,primary"` // BINARY(16)
    Data `db:",bytes"` // BLOB
}

func (d *Data) MarshalGorpBytes() ([]bytes, error) {
// Encode with encoding/gob
}

func (d *Data) UnmarshalGorpBytes([]bytes) error {
// Decode with encoding/gob
}

A usage example or two would be helpful

Hi

I am unable to get embedded structs to work with Postgres. I tried the "WithEmbeddedStruct" example straight from the Readme and the gorp_test.go. Unfortunately, I get the following errors

INFO  2013/10/19 17:18:46 gorp.go:908: [gorp] insert into "embeddedstruct" ("id","names") values (default,$1) returning id; [[{Alice Smith}]]
INFO  2013/10/19 17:18:46 myService.go:61: Error in Create of AppointmentService sql: converting Exec argument #0's type: unsupported type models.Names, a struct

Versions
Postgres: 9.3
Go : 1.1
OS : Mountain Lion

Am I doing something wrong? Your time is appreciated

Thanks
-Rv

One thing that seems like it could become problematic is:
http://godoc.org/github.com/coopernurse/gorp#DbMap.Select

"Returns an error if one or more columns in the result do not match"

I think the common case will be someone writing

type Model struct {
  // many fields
}
dbm.Select(&models, "SELECT * FROM models WHERE ...")

Presently, as soon as a field is added to the table, the Select will start returning errors. This requires the developer to type out the entire field list for every select to be correct, otherwise there is no way to alter the table without trouble.

Two solutions could be:

• Don't return an error if you don't have a place for a returned column (bad, but easy)
• Provide a syntax for inserting the list of fields that you expect (better)

The latter could work like this:

dbm.Select(&models, "SELECT ... FROM models")

or

dbm.Select(&models, "SELECT {} FROM models")

and that could be replaced by the list of fields expected in type Model.

Hi!

I have problems using gorp because when i connect to database and insert one row, the app just crash. My program gives this error:

panic: runtime error: invalid memory address or nil pointer dereference
[signal 0xb code=0x1 addr=0x20 pc=0x68199]

goroutine 1 [running]:
sync/atomic.CompareAndSwapUint32(0x20, 0x100000000, 0x200000000, 0x159ac, 0x198df, ...)
    /private/tmp/bindist454984655/go/src/pkg/sync/atomic/asm_amd64.s:12 +0xd
sync.(*Mutex).Lock(0x20, 0x200000001)
    /usr/local/go/src/pkg/sync/mutex.go:40 +0x35
database/sql.(*DB).conn(0x0, 0x17abb, 0x70ff8, 0x42179270)
    /usr/local/go/src/pkg/database/sql/sql.go:236 +0x2d
database/sql.(*DB).exec(0x0, 0x42178b40, 0x25, 0x421792c0, 0x100000001, ...)
    /usr/local/go/src/pkg/database/sql/sql.go:344 +0x65
database/sql.(*DB).Exec(0x0, 0x42178b40, 0x100000025, 0x42179280, 0x100000001, ...)
    /usr/local/go/src/pkg/database/sql/sql.go:335 +0x124
github.com/coopernurse/gorp.(*DbMap).Exec(0x421a5000, 0x42178b40, 0x25, 0x42179280, 0x100000001, ...)
    /usr/local/go/src/pkg/github.com/coopernurse/gorp/gorp.go:713 +0x105
github.com/coopernurse/gorp.insert(0x421a5000, 0x421a7000, 0x421a5000, 0x44218af68, 0x100000001, ...)
    /usr/local/go/src/pkg/github.com/coopernurse/gorp/gorp.go:1063 +0x2e0
github.com/coopernurse/gorp.(*DbMap).Insert(0x421a5000, 0x44218af68, 0x100000001, 0x421774a0, 0x421a6000, ...)
    /usr/local/go/src/pkg/github.com/coopernurse/gorp/gorp.go:636 +0x6e
main.probarGORP()
    /Users/micho/Proyectos/DiagnosticoPDS-server-go/main.go:25 +0x303
main.main()
    /Users/micho/Proyectos/DiagnosticoPDS-server-go/main.go:9 +0x18

goroutine 2 [syscall]:
created by runtime.main
    /private/tmp/bindist454984655/go/src/pkg/runtime/proc.c:221

when i run this code

package main

import (
    "database/sql"
    "github.com/coopernurse/gorp"
)

func main() {
    probarGORP()
}

func probarGORP() {
    // construct a gorp DbMap
    //dbmap.TraceOn("", log.New(os.Stdout, "gorptest: ", log.Lmicroseconds))
    db, err := sql.Open("mysql", "pds:123456@localhost:3306/pds")
    dbmap := &gorp.DbMap{Db: db, Dialect: gorp.MySQLDialect{"InnoDB", "UTF8"}}
    table := dbmap.AddTableWithName(Sexo{}, "Sexo").SetKeys(true, "Id")
    table.ColMap("Id").Rename("id")
    table.ColMap("Nombre").Rename("nombre")
    p1 := &Sexo{3, "otro"}
    if err == nil || p1 == nil {

    }

    dbmap.Insert(p1) // Version is now 1
}

type Sexo struct {
    Id     int64
    Nombre string
}

The Mysql table is this:

CREATE TABLE IF NOT EXISTS `Sexo` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `nombre` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

My setup is a Mountain Lion macbook pro using go1.0.2. Is this a gorp issue ? How should i debug this ? I googled and it seems that "sync/atomic/asm_amd64.s:12" appears often in bugs.

thanks

Hello and thank you for providing Gorp! :)

Now, I have a model with several foreign keys that are allowed to be set to NULL.

type MyModel struct {
    Id             int    `db:"id"`
    Name           string `db:"name"`
    ProjectId      int    `db:"project_id"`    // Allows NULL
    CustomerId     int    `db:"customer_id"`    // Allows NULL
}

AddTableWithName(MyModel{}, "mymodels").SetKeys(true, "Id").SetKeys(false, "ProjectId", "CustomerId")

(Tables are created with SQL-scripts; not with CreateTables). I insert new rows like so

db.Insert(&MyModel{Name:"Hello"})

This will return an error:

test:19:17:47.078572 [gorp 3] insert into "mymodels" ("id","name","project_id","customer_id") values (default,$1,$2,$3) returning id; [[Hello 0 0]]

As there is not project or customer with id=0, Postgres will yell loudly. In this case I want to make Gorp drop project_id and customer_id from the sql. Problem is, how do I do that?

Since I'm testing in PostgreSQL I always get an error from this file. Perhaps it can be removed from the repo?

I want to create a connection pool ,it has many dbmap,and eache dbmap has a new connection. the dbmap has the same tables. so , some dbmaps should share they tables.but DbMap don't has dbmap.SetTables([]*TableMap) function to support do it.

I initialize the first dbmap1 ,and create tables ( use AddTableWithName() and CreateTables()),when I creat the next dbmap2,I do not neet to create tables,and

    dbmap1 := &DbMap{Db: connect(), Dialect: dialect}
dbmap2 := &DbMap{Db: connect(), Dialect: dialect}
dbmap3 := &DbMap{Db: connect(), Dialect: dialect}

tables := new([]*TableMap)

dbmap1.SetTables(tables)
dbmap2.SetTables(tables)
dbmap3.SetTables(tables)

hpgood@9b1aa8f
hpgood@f19396d

I would like to propose adding a function that takes a "Query" type and returns a set of records, similar to the way the framework/model package in sawsij does:

http://godoc.org/bitbucket.org/jaybill/sawsij/framework/model#Table.FetchAll
http://godoc.org/bitbucket.org/jaybill/sawsij/framework/model#Query

The function would take a Query, generate a select statement based on it and return the results. This would of course have to take into account the dialect of SQL being used.

This probably shouldn't be called "fetchAll" and it shouldn't replace the existing functionality, just augment it.

Hello! It's very interesting project - now I'm studying his abilities. And I cannot understand, how can I implement foreign key at the model level?

For example:

type First struct {
    Id int
    Name string
    SecondId int
}
type Second struct {
    Id int
    Name string
}

If I understand correctly, selection of the First object with the Second may looks like this:

first, err := dbmap.Get(First{}, first_id)
first = first.(*First)
second, err := dbmap.Get(Second{}, first.SecondId)
second = second.(*Second)

Is there any way to do it like in Django Orm (model.ForeignKey(Second)):

first, err := dbmap.Get(First{}, first_id)
first = first.(*First)
second := first.Second

Running a "select count(*) from Foo" does not seem to be possible using Gorp (.Exec returns a sql.Result, which does not seem to provide access to any Row data). This would not be a problem if the Gorp Transaction type provided access to the underlying sql.Transaction, but unfortunately it does not. What do you think about making that a public member, or exposing forwarded Query / QueryRow methods?

I'm not sure if this is something you want to support, but Sqlite (and presumably other databases) does not support a formal datetime type. It should be possible to .Format() and .Parse() the time.Time field from a String type. I'm not sure how this would be configured, though, or if it's too niche of a feature.

FWIW, I tried to implement time.Time support in the go-sqlite3 driver, but I couldn't find a way to do so within the constraints of database/sql

I have a data structure:

type Times struct {
    One time.Time
    Two time.Time
}

type EmbeddedTime struct {
    Id string
    Times
}

Using this structure I'm able to create a table and insert structs into it, but when calling get, I get an error:

[CreateDb] select "id","one","two" from "embeddedstruct" where "id"=$1; [[testId1]]
Error getting from db! sql: Scan error on column index 5: unsupported driver -> Scan pair: []uint8 -> *time.Time

This seems like an error in the SQL module or in the way that GORP represents these time fields to the Scanner..

When selecting a list of ids, it would be ideal to select into a []int, (or names into a []string, etc). This does not appear to be possible at the moment, without creating a struct type with a single int field.

When I run a lot of queries in a single connection, PostgreSQL's memory usage steadily increases (up to 13 GB after ~100k queries, at which point my computer locks up). I think this is because it does not garbage-collect prepared statements that are created in a connection until the connection is closed. The following patch fixes the issue; PostgreSQL memory usage is drastically lower after running 100k queries.

Has anybody else had the same issue?

diff --git a/gorp.go b/gorp.go
index 4086b6b..b7bbfc6 100644
--- a/gorp.go
+++ b/gorp.go
@@ -931,6 +931,7 @@ func (t *Transaction) Exec(query string, args ...interface{}) (sql.Result, error
        if err != nil {
                return nil, err
        }
+       defer stmt.Close()
        return stmt.Exec(args...)
 }

Currently CreateTables() interprets all string properties as varchar. Please add support for annotating that the column should be type TEXT instead.

Im getting a panic message from the reflection lib

panic: reflect.Value.Interface: cannot return value obtained from unexported field or method

ConfigCMap  :=      dbmap.AddTableWithName(ConfigClient{},"config_clients").SetKeys(true,"client_id")

obj, err := dbmap.Get(ConfigClient{}, 1000)

it panic a t the Get call.

the table and struct had the same names(except for 1 field named type)
looks some kind of mismatch but i dont know how to debug any further

CreateTables is very useful for testing. For completeness, it would be useful to have either

• a new CreateTablesIfNotExists function that does what you'd expect from its name, or
• a new boolean parameter on CreateTables that enables/disables an "if not exists" clause.

One thing that sqlx has is the ability to bind named query arguments from a struct or map. How do you feel about extending the existing query functions to something like this:

Select(&rowObjs, query, params…)

if len(params) == 1 {
  if params is a struct ? bind query from struct fields
  if params is a map[string]interface ? bind query from map
} else if each param is a simple value {
  // existing behavior
  bind to placeholders
} else {
  return error
}

I have been using the Select family of methods for basically everything. One limitation is that selecting a single row is a bit awkward, since we have to select into a slice (or a value).

It would be nice to be able to do this:

var result struct {
  Foo int
  Bar string
}
dbm.Select(&result, "select foo, bar from baz where id = ?", id) 

Potentially, that could also unify the SelectVal functions as well. Instead of SelectInt, SelectStr, etc, developers could always use Select:

var result int64 
dbm.Select(&result, "..")

It may make sense to have separate singular and plural forms, where the singular form panics if more than 1 row comes back (or maybe that should be an error?)

var result Foo
dbm.SelectOne(&result, "...")

Thoughts?

I spent quite a while banging my head against the wall trying to figure out what was wrong with my program because .Insert() threw this error:

panic: reflect: call of reflect.Value.SetInt on string Value

which it turns out traced back to gorp.go:1363:

elem.Field(bi.autoIncrIdx).SetInt(id)

Turns out I had done .SetKeys(true, "a_string_column"). This was especially hard for me to track down due to logical complexities with my program.

gorp should throw a more helpful error if you try to set autoincrement on a primary key column that's not numeric.

type Base struct {
    Created int64   `db:"created"`
    Updated int64   `db:"updated"`
}

func (b *Base) PreInsert(s gorp.SqlExecutor) error {
    b.Created = time.Now().UnixNano()
    b.Updated = b.Created
    return nil
}

func (b *Base) PreUpdate(s gorp.SqlExecutor) error {
    b.Updated = time.Now().UnixNano()
    return nil
}

type Post struct {
    Id int  `db:"id"`
    Name string `db:"name"`
    Title string    `db:"title"`
    Content string  `db:"content"`
    *Base
}

dbmap.createTables creates Post table with a member Base varchar(255). Does gorp support embedding structs? If yes, how do I do it?

If this is not the right place to file this please excuse me, I couldn't find a mailing list or something.

I'm using Gorp on top of Postgres. I'm seeing an issue where, if the query is constructed a certain way, the ORDER BY clause does not get reflected in the resulting slice.

Here's the following code to exemplify the bug:

DbMap = &gorp.DbMap{Db: db, Dialect: gorp.PostgresDialect{}}
DbMap.AddTableWithName(Order{}, "orders").SetKeys(true, "Id")

// In this case, it works just fine.
var orders []*Order
_, err := DbMap.Select(&orders, "SELECT * FROM orders ORDER BY id ASC")

// But if I construct the query in a different way, 
// the order of the resulting slice is incorrect.
field := "id"
_, err := DbMap.Select(&orders, "SELECT * FROM orders ORDER BY $1 ASC", field)

Any ideas about why this might happen?

Is there any way to make this code more concise? I don't know if I'm using gorp incorrectly, and it seems like a lot of code for a simple task:

func (u *User) NumRecords(s gorp.SqlExecutor) int {
    type Count struct {
        Count int
    }
    rows, err := s.Select(Count{}, "SELECT COUNT(*) as Count FROM Queue WHERE User_Id = $1", u.Id)
    // (handle err)
    count := rows[0].(*Count)
    return count.Count
}           

Also, I'm confused as to the purpose of passing the Count{} as an argument to Select, if I have to type assert it later anyway.

I couldn't use .SelectInt like I wanted since it's a SqlExecutor and not a Transaction. Any advice appreciated.

This causes problems where fields have upper case names. Like "Username". I can work around this with a tag like Username string db:"username"`` but that shouldn't be necessary, right?

See comment here: coopernurse@d0f6657#commitcomment-2610413

Presently I have to manually muck around with the SQL for queries that involve an IN. It would be nice if this worked:

select * from foo where id in (?)

where the corresponding parameter is a slice.

Since slices are not allowed as parameters, the implementation for this could avoid touching the normal flow, and it could only activate when a slice parameter is detected.

I think it would have to iterate through the parameter placeholders in the query -- when it gets to the one corresponding to the slice, it would have to expand it to the length of the slice, e.g. (?, ?, ?). Since some DBs have numbered parameters, it would also have to renumber all subsequent parameter placeholders.

Only thing is that the implementation seems a little bit challenging -- for example, is it safe to assume any '?' is a placeholder, regardless of context? Maybe not. Would need a full test suite.

How about a SelectDate to go along with SelectInt and friends?

(Arguably should be called SelectTime since that's the Go type name.)

Question (request for doc enhancement :)

I have a database which contains NULL values (mysql). When I try to fill a struct using

 obj, err := dbmap.Get(mystruct{}, id)

I get this error:

sql: Scan error on column index 11: converting string "<nil>" to a int: strconv.ParseInt: parsing "<nil>": invalid syntax

Which makes sense, since I have NULL values in the table which cannot be converted to an int (the requested column / struct type is int).

Is there any automatic way to treat NULL values as 0 (for example) without changing the database?

Now that we have CreateTablesIfNotExists(), it seems appropriate to add the related DropTablesIfExists() function.

A cursory glance of the mysql/postgresql/sqlite docs suggests the syntax is supported and identical in all three databases.

In tests you use mysql driver "github.com/ziutek/mymysql/godrv". With this driver
db, err := sql.Open("mysql", "myuser:mypassword@localhost:3306/dbname") // at https://github.com/coopernurse/gorp
doesn't work.

Either documentation should be fixed to
db, err := sql.Open("mymysql", "gomysql_test/gomysql_test/abc123")
or please point at mysql driver to use for current version of documentation

And sorry, don't know how to ask other way. In docs nothing is said about multithreading. I have many goroutines. Can I share
two global variables
var db *sql.DB
var dbmap *gorp.DbMap
among goroutines ? Or should I create single dbmap per goroutine (but single db *sql.DB)? Couple of lines in documentation would be very useful

I have a query that looks like this:

halfhourago := time.Now().UTC().Add(-30 * time.Minute)
var caseIds []int64
_, err := controllers.Dbm.Select(&caseIds,
    "SELECT id FROM cases WHERE status = 1 AND created < $1", halfhourago)

This fails after 071ca90 with a message like this:
pq: S:"ERROR" C:"42P02" M:"there is no parameter $1" P:"53" F:"parse_expr.c" L:"822" R:"transformParamRef" and the trace looks like this:
[gorp] SELECT id FROM cases WHERE status = 1 AND created < $1 [[]]
As you can see there is no argument.

I think the problem is maybeExpandNamedQuery which sees the time argument as a struct and tries to run expandNamedQuery.

Should I rewrite my query or should maybeExpandNamedQuery be modified to recognise time.Time?

I use grop insert a record into postgresql with postgresql driver pq,but i get an error when call
LastInsertId.The error like this

pq: R:"RangeVarGetRelidExtended" S:"ERROR" F:"namespace.c" L:"407" P:"16" M:"relation "invoice_test_id_seq" does not exist" C:"42P01"

I found that the invoice_test_id_seq dons't exist.

note:i use grop.CreateTable() create table to database.

how to change database? not create a new dbmap. thanks.

Presently, when I get a list of results back, I have to loop through them to convert the individual elements to the right type:

    results, _ := dbm.Select(Fortune{}, "SELECT * FROM Fortune")

    var fortunes []*Fortune
    for _, r := range results {
        fortunes = append(fortunes, r.(*Fortune))
    }

I think this work could be avoided if instead the caller could pass the slice to populate, e.g.

    var fortunes []*Fortune
    _, err := dbm.Select(&fortunes, "SELECT * FROM Fortune")

Any thoughts?

(Thanks for the great library!)

can you add an example how we can bundle multiple update or insert in a single transaction?

I think it'd be nice to map a struct field named

FooBarId to a column foo_bar_id

Would you be open to either a) changing the default mapping scheme or b) opening this up via some flag/api/configuration?

my bad

type Jack struct {
Id rune
Created int64
Updated int64
Nom string
Email string
Skill_Jack_Id []rune
Carac_Jack_Id []rune
Item_Jack_Id []rune
Skill_Jack_Private_Id []rune
Item_Jack_Private_Id []rune
Password string
Key_public string
Key_private string
Message_Id []rune
Statut string
Avatar string
}

func initdb() {
db, err := sql.Open("sqlite3", "foo.db")

// construct a gorp DbMap
dbmap := &gorp.DbMap{Db: db, Dialect: gorp.SqliteDialect{"InnoDB", "UTF8"}}
t1 := dbmap.AddTableWithName(Jack{}, "Jack_test").SetKeys(true, "Id")

}

with https://github.com/mattn/go-sqlite3 it gaves me ::

command-line-arguments

./Jackpoint.go:166: implicit assignment of unexported field 'suffix' in gorp.SqliteDialect literal
./Jackpoint.go:166: too many values in struct initializer

Regards

How to use the Delete and Select complex conditions.

dbmap.CreateTables() returns sql error. Driver is mymysql

My code:

package robo

import(
"fmt"
"meteor"
"exp/sql"
"github.com/coopernurse/gorp"
_ "github.com/ziutek/mymysql/godrv"
)

//var db mysql.Conn
var db *sql.DB
var dbmap *gorp.DbMap

func Startup(){

db, err := sql.Open("mymysql", "dbname/user/pwd")
meteor.CheckError(err)

// construct a gorp DbMap
dbmap = &gorp.DbMap{Db: db, Dialect: gorp.MySQLDialect{"InnoDB", "UTF8"}}

dbmap.AddTable(PersistentUser{}).SetKeys(true, "Key")


//meteor.CheckError(dbmap.DropTables())
meteor.CheckError(dbmap.CreateTables())   // <-----------------  error here

}

func TestDao(){
testInsert()
}

func testInsert(){

Startup()

pu := &PersistentUser{-1,"33r",false}

for i:=0;i<10;i++{
    pu.Id = fmt.Sprintf("user%d",i)
    pu.PassedTraining = false;      
    err := dbmap.Insert(pu)     
    meteor.CheckError(err)
}

Shutdown()

}

func Shutdown(){
if db!=nil {
db.Close()
}

db = nil
dbmap = nil

}

and

type PersistentUser struct {
Key int32
Id string
PassedTraining bool
}

after line meteor.CheckError(dbmap.CreateTables()) we get this:
panic: Fatal error: Received #1064 error from MySQL server: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'int not null auto_increment, Id varchar(255), PassedTraining varchar(255), prima' at line 1"

If you have a struct like

type Foo struct {
 A, B string
}

and you call .AddTable(Foo{}).SetKeys(false, "A", "B"), then the following invalid SQL gets emitted for UPDATEs (notice the dangling where):

log:18:16:04.189098 [sql] update "foo" set  where "a"=$1 and "b"=$2; [[myval1 myval2]]

This is probably a rare case, since the update is a no-op from gorp's POV, but the update query could cause SQL triggers to be issued, so it should not fail.

README currently shows examples:

// select single int64 from db:
i64, err := dbmap.SelectInt("select count(*) from foo where blah=?", blahVal)

// select single string from db:
s, err := dbmap.SelectStr("select name from foo where blah=?", blahVal)

These work with MySQL but not withj lib/pq (the ? doesn't seem to get replaced with the parameter value).

I'm not actually sure whether this is an old/incorrect way of parameterizing or a bug, but I was only able to to get this to work with this kind of syntax:

s, err := dbmap.SelectStr("select name from foo where blah=$1", blahVal)

This is one of the 3 items from #71

Adding here because d9421a6 with the fix for this has been merged into develop, with a target for v1.1

We've encountered a crippling problem in gorp's Postgres support while using it at Braintree.

The problem

Currently, when a row with an autoincrementing primary key is inserted, grop returns the newly generated ID to the struct by running SELECT currval on the table that it just INSERTED into. This is only correct if the SELECT is guaranteed to be in the same session as the INSERT, since currval is only defined after an INSERT. If the INSERT -> SELECT sequence is not done in the same session, the user will either get an error like PostgresDialect: select currval('<table name>_id_seq') did not return a row or, worse, an incorrect value for the ID.

Why did this look like it works?

Under the hood, Go's database/sql package has a connection pool with exactly 2 connections. These are managed with a stack-like data structure; the connection most recently returned to the pool will be the one used next. So under low load, events will happen in the following sequence:

• gorp grabs the next database connection; it's connection 1.
• gorp runs the INSERT query on connection 1.
• gorp returns connection 1 to the pool.
• gorp grabs the next database connection; it's connection 1.
• gorp runs the SELECT currval query on connection 1. This works, because connection 1 still has the same session as it did when it executed the insert.
• gorp returns connection 1 to the pool.

However, under high enough load, this won't work. Instead, this will happen:

• gorp grabs the next database connection; it's connection 1.
• gorp runs the INSERT query on connection 1.
• gorp returns connection 1 to the pool.
• Another piece of the application grabs the next database connection; it's connection 1.
• gorp grabs the next database connection; it's connection 2.
• gorp runs the SELECT currval query on connection 2. It fails, since currval is not defined for this session, or worse, it succeeds with the wrong ID (ie get's the ID from an older INSERT).

This is totally broken. In the worst case, it can return the wrong ID up to the application, where it could potentially be used as a foreign key, which would completely corrupt the database.

The fix

The idiomatic way to get the functionality we need here (returning the new ID up to the application) is with an INSERT ... RETURNING query. You can see an example of this here: https://github.com/eaigner/hood/blob/master/postgres.go#L64

At Braintree we forked gorp internally and hacked in our own implementation of this, but it looks like generalizing this fix takes significant work. We will try to work on a pull request, but in the mean time we wanted to raise the issue here to prevent other developers from getting burned by this problem.

This is the same issue as https://github.com/coopernurse/gorp/issues/19