2

So I have a JDBC call to insert data into a database.

I'd like to be able to view the statement as a whole (so, you know, "insert into some_table (colA, colB) values (1,2)").

But I would also like to see the statement as what it really is - that is, 

insert into some_table (id, colA, colB) values (8,1,2)

where the id is the primary key (or any other auto_increment field) with what sql thingy would determine the auto-incremented the values to be!

Is this possible, or not?

benRollag
  • 1,219
  • 4
  • 16
  • 21
bharal
  • 15,461
  • 36
  • 117
  • 195
  • the question makes no sense to me! No matter how many times I read it! – Aniket Inge Mar 10 '13 at 21:19
  • 4
    @Aniket the question makes perfect sense to me. – Marlin Pierce Mar 10 '13 at 21:20
  • please explain @MarlinPierce – Aniket Inge Mar 10 '13 at 21:21
  • What I would do is: 1) fetch the just inserted row 2) list all relevant fields of that row. – 11684 Mar 10 '13 at 21:21
  • @bharal the auto increment is handled by the database on the server side. I don't think JDBC has a way of determining the value of the auto increment without another query to get the value. However, you might have success querying the database for the highest value in the auto increment field. – Marlin Pierce Mar 10 '13 at 21:22
  • if autoincrement is set to increment by x value each time, then it is possible to get the last inserted id and then increment by x. `select id from some_table order by id desc` – Aniket Inge Mar 10 '13 at 21:23
  • @MarlinPierce And what when two clients update the db around the same time? I smell a race condition. – 11684 Mar 10 '13 at 21:24
  • @Aniket when you have an auto increment field, as is frequent practice for a primary key, you may have three fields, id, colA, and colB, but when you insert values for colA and colB, values are set in the new record for all three fields. Likewise, fields may have defaults and be set, even if not specified in the insert statement. – Marlin Pierce Mar 10 '13 at 21:25
  • @11684 Any DBMS worth its salt (or complying with the ACID specs) would be able to handle the situation where two clients update around the same time. –  Mar 10 '13 at 21:28
  • @MarlinPierce although it is something to look out for(and hence why I would go with 11684's idea of selecting required fields from the last inserted row), I would still use something like triggers - atleast they are thread safe. – Aniket Inge Mar 10 '13 at 21:28
  • @AndersUP if A inserts a row, B also inserts a row, now when A tries to retrieve the last inserted data - it would incorrectly show B's data. A trigger on the other hand will be a callback, which will prevent B's data to be inserted(hence it will be queued) before the completion of A's trigger. giving you the correct result – Aniket Inge Mar 10 '13 at 21:31
  • @11684 yes this would be a race condition. Determining beforehand what the next auto increment would be also has a race condition. I don't offer this advice as an answer, just a comment. A transaction might work for querying the max after the fact, but before the end of the transaction. I think the ACID standard would have to assign different IDs if the database allowed two inserts to process simultaneously when they were in a transaction. – Marlin Pierce Mar 10 '13 at 21:31
  • Also, @11684 is there something different you meant by fetching the just inserted row? How would you do that except by selecting the row with the max value? – Marlin Pierce Mar 10 '13 at 21:34
  • @Aniket a trigger is not a solution. A trigger can execute code on the server when the insert happens, but what bharal wants is the id in the client which did the insert. – Marlin Pierce Mar 10 '13 at 21:36
  • @Aniket 1) Why would a trigger be a callback? 2) A would not necessarily retrieve B's data. Depending on your code structure and your DBMS, there are many, many ways to ensure that you retrieve the correct ID. –  Mar 10 '13 at 21:37
  • @AndersUP a trigger _is_ a callback because it is chained with the `insert`(if it is an insert trigger - usually called event-callback mechanism). And there is every chance of a race condition when trying to retrieve the id of the last inserted record. – Aniket Inge Mar 10 '13 at 21:39
  • @MarlinPierce a trigger is a(one of the) solution to race condition – Aniket Inge Mar 10 '13 at 21:39
  • @AndersUP come to think of it, I'm not sure my transactions solution, or your suggestion that an ACID compliant DBMS would handle the situation. After all, what is meant by handle the situation. An ACID DBMS would handle multi-user inserts, but what about getting the new ID value? If we try to insert and then query for the max id in a transaction, but another transaction, let's call it T2, inserted a record after our insert but before our query, and committed, then when we do our query, we will see the record from T2, and it, rather than our record will have the max id. – Marlin Pierce Mar 10 '13 at 21:42
  • @AndersUP most people think of a callback as something that would notify the client. A trigger does not do that. – Marlin Pierce Mar 10 '13 at 21:43
  • @Aniket To my knowledge, a trigger is only a callback if you explicitly make it so - and I really don't see why you would want to do that in this case. On Oracle I would probably use the `RETURNING` clause, in SQL Server I would use `SCOPE_IDENTITY()` –  Mar 10 '13 at 21:46
  • @MarlinPierce My point exactly regarding the triggers. Regarding whether DBMS would handle it, see my reply to Aniket. I haven't used Oracle for a while, so I'm not 100% about the returning-clause - but Scope_identity would serve the purpose here. –  Mar 10 '13 at 21:48
  • @MarlinPierce Fetching the last row is simple if you have another unique field (apart from id), for example email address or username. Usually you don't want two people using the same username/email address, so if you insert a row in the DB you should keep one of those unique fields around and then use that to fetch the row you just inserted. If all your fields can have duplicates, this would be, of course, a bit harder. – 11684 Mar 11 '13 at 08:50
  • @11684 I didn't want to make the assumption that there was another candidate key. Nothing in the question allows us to assume that there is. – Marlin Pierce Mar 11 '13 at 11:22
  • If there are no unique keys apart from ID you can't identify a row. @MarlinPierce – 11684 Mar 11 '13 at 11:39

1 Answers1

3

The statement does not change. If you have an autoincrementing key (ID) in your table, it never shows up in a statement, but is added by the DB-engine as the statement is executed.

So it is not possible to see a statement with the ID-column in, but depending on your DBMS, there are different techniques to determine what the automatically generated value was.

Ie. in SQL Server, you can use SCOPE_IDENTITY(). On Oracle, you can use RETURNING INTO. For Postgres, you can take a look at Can I use return value of INSERT...RETURNING in another INSERT?

Community
  • 1
  • 1
  • 3
    With JDBC it is possible to use [getGeneratedKeys](http://docs.oracle.com/javase/6/docs/api/java/sql/Statement.html#getGeneratedKeys()), see also http://stackoverflow.com/questions/1915166/how-to-get-the-insert-id-in-jdbc – beny23 Mar 10 '13 at 22:27