Comments (12)
Don't have any issues with the current implementation, but I wanted to add a quick code example because it took me a little while to piece this together. It looks like the ToSql()
method referenced above doesn't exist anymore, and I didn't realize exactly what the Load method was for in the context of the Insert functionality. So in case this is helpful for anyone else who lands on this issue:
var id int64
err := session.Conn().
InsertInto(<table-name>).
Columns(<column-names>).
Record(<struct-variable>).
Returning("id").
LoadContext(ctx, &id)
log.Printf("Last insert id is %d", id)
from dbr.
It looks kinda hacky and it does not support multiple records. This logic should be somewhere inside the dialect handler.
I have no better option right now though, I currently use this function as a workaround:
func execInsertGetIDs(r dbr.SessionRunner, b *dbr.InsertBuilder, ids []*int) error {
var rids []int
q, vals := b.ToSql()
_, err := r.SelectBySql(q+" RETURNING \"id\"", vals...).Load(&rids)
if err != nil {
return err
}
for i, id := range rids {
*ids[i] = id
}
return nil
}
from dbr.
This issue is resolved with latest master. Thanks!
from dbr.
Appending "RETURNING id" works if the table has the column id. If the driver has last insert id available, dbr will use it.
Because https://golang.org/pkg/database/sql/driver/#RowsAffected.LastInsertId. I try to keep it simple, but all kinds of integer is possible. It will be the library users' responsibilities to ensure the id int types can hold the returned insert id. Do you have a specific use case for this?
from dbr.
pq does not support LastInsertId().
You are correct, the column might not be named "id", but the name can be extracted from InsertBuilder.RecordID.
from dbr.
You are welcomed to try this one: https://github.com/gocraft/dbr/pull/30/files.
from dbr.
dbr tries to provide the minimal abstraction to hide database differences. Although batch auto-inject for postgres is easy with “RETURNING”, it is still hard for mysql (mysql, as far as I know, only has last insert id for this feature).
There are a few obvious choices for dbr:
- support batch insert with auto-inject: for mysql, we need to create a transaction in the library and insert record one by one so that we can use last insert id.
- support single insert with auto-inject: create transaction outside dbr.
I prefer the second one.
from dbr.
I think we'd love to have this work, but I don't think any of us have time right now to address it. If anybody has thoughts on how to best handle this I'd love to open up a discussion here.
from dbr.
I've been poking around returning LastInsertID. Not sure how, but in some circumstances the LastInsertID works just fine (let's say >30%).
At this point I don't see an easy way of getting an ID (it's not guaranteed that the ID even exist), thus one must use RETURNING column0 [, columnX].
I've hacked a bit around and added Return
method to InsertBuilder/InsertStmt
and copied a Load*
from SelectBuilder as RETURNING
may in fact return all columns.
The branch is here: https://github.com/orian/dbr/tree/add-returning
About using auto-inject: in many cases the dbr doesn't know primary key field and in some cases the primary key may not be a single column. It would cause the error to get returned.
from dbr.
There is a way to do this in MySQL, but it has a couple of caveats.
When inserting multiple rows, the value of LastInsertId will be the auto increment value of the first row inserted. The auto increment values for the other rows will be consecutive. So you can loop through the number of rows inserted and increment by the value of @@auto_increment_increment
to get the id of each row.
We are using this technique in a couple of places to implement bulk actions.
Caveats:
-
This is based on the docs for InnoDB. I don't know how it works with other engines.
-
If
innodb_autoinc_lock_mode
is set to "interleaved" and "bulk inserts" are being executed on the table concurrently, then consecutive ids are not guaranteed. (Bulk inserts are defined as statements where the number of rows being inserted is not known up front.)If the only statements executing are “simple inserts” where the number of rows to be inserted is known ahead of time, there will be no gaps in the numbers generated for a single statement, except for “mixed-mode inserts”. However, when “bulk inserts” are executed, there may be gaps in the auto-increment values assigned by any given statement.
-
There are more complicated rules for "mixed-mode inserts" (inserts where you specify the id of some but not all the rows).
from dbr.
I ran into this problem and although it is easily solved by just building your own SQL with the RETURNING keyword, I still wanted (and like) the InsertInto(...).Columns(...) syntax. So I have a diff that adds a Returning function on InsertBuilder struc :
master...dougEfresh:returning
from dbr.
https://github.com/gocraft/dbr/blob/master/example_test.go#L8
it seems like a good example to be added in that file.
@maxgardner
from dbr.
Related Issues (20)
- Using Like() does not escape HOT 2
- The scan loop interrupt without any error passed HOT 2
- Update stmt can't mock test HOT 4
- Use curly braces for Postgres array elements HOT 1
- Where() is missing a type check HOT 3
- Proposal: use Builder constructors for aggregate functions HOT 1
- Cannot get dbr HOT 2
- Iterator support
- Example and test code for sqlite HOT 1
- add force index for mysql HOT 4
- Columns should automatically be populated in InsertStmt when using struct record HOT 1
- Can you support recording prepared statements in TimingKv func? I want to collect prepared statements instead of assembled SQL HOT 1
- Documentation is needed for how to build complex queries HOT 1
- can't load rest data after insert blank record HOT 2
- Panic on nil pointer to driver.Valuer interface HOT 2
- Incorrect time values in PostgreSQL timestamptz fields if database time zone is not UTC HOT 3
- Insert multiple rows in transaction with id BIGSERIAL
- `InterpolateForDialect("SHOW CREATE TABLE ?;"...)` produces invalid MySQL statement HOT 7
- Switch driver dependency
- sess.InsertInto with multicolumn only last column set value
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from dbr.