java.jdbc

Prepare-statement should support passing an array of column names to return auto-generated keys

Details

  • Type: Enhancement Enhancement
  • Status: Closed Closed
  • Priority: Minor Minor
  • Resolution: Completed
  • Affects Version/s: None
  • Fix Version/s: None
  • Component/s: None
  • Labels:
    None

Description

https://docs.oracle.com/javase/7/docs/api/java/sql/Connection.html#prepareStatement(java.lang.String,%20java.lang.String[])

This would be essential for proper Oracle integration as the current use of java.sql.Statement/RETURN_GENERATED_KEYS only returns the Oracle RowID.

Activity

Hide
Sean Corfield added a comment -

This could be supported by allowing :return-keys to be a vector (of String) – in addition to just a boolean – but I will note that you can already create the PreparedStatement directly yourself and pass whatever arguments you want and then use that object in other calls (so I'm lowering the priority). It's a good enhancement suggestion tho'!

Show
Sean Corfield added a comment - This could be supported by allowing :return-keys to be a vector (of String) – in addition to just a boolean – but I will note that you can already create the PreparedStatement directly yourself and pass whatever arguments you want and then use that object in other calls (so I'm lowering the priority). It's a good enhancement suggestion tho'!
Hide
Sean Corfield added a comment -

Looks like several databases support this (not Derby tho') so I'm going to add this to prepare-statement.

Show
Sean Corfield added a comment - Looks like several databases support this (not Derby tho') so I'm going to add this to prepare-statement.
Hide
Sean Corfield added a comment -

In 0.4.2, prepare-statement's :return-keys may be a vector of column names to return. Or just truthy, which will use the default return generated keys behavior, or falsey which will not return keys.

Show
Sean Corfield added a comment - In 0.4.2, prepare-statement's :return-keys may be a vector of column names to return. Or just truthy, which will use the default return generated keys behavior, or falsey which will not return keys.
Hide
Tatu Tarvainen added a comment -

I think this should also be a parameter to db-do-prepared-return-keys function. Current implementation just uses :return-keys true.

Show
Tatu Tarvainen added a comment - I think this should also be a parameter to db-do-prepared-return-keys function. Current implementation just uses :return-keys true.
Hide
Sean Corfield added a comment -

That's a low-level function that I wouldn't really expect you to use directly. Instead use prepare-statement to create a PreparedStatement – with a :return-keys vector of column names – and then pass that to db-do-prepared which, like most of java.jdbc, accepts a PreparedStatement in place of the SQL string.

Show
Sean Corfield added a comment - That's a low-level function that I wouldn't really expect you to use directly. Instead use prepare-statement to create a PreparedStatement – with a :return-keys vector of column names – and then pass that to db-do-prepared which, like most of java.jdbc, accepts a PreparedStatement in place of the SQL string.
Hide
Tatu Tarvainen added a comment -

I was under the impression that db-do-prepared-return-keys is part of the API as it is public. Yesql uses it directly.

If I'm reading it correctly, it looks to me that db-do-prepared only returns the update count or a batch of update counts. So it really isn't the same as db-do-prepared-return-keys.

Show
Tatu Tarvainen added a comment - I was under the impression that db-do-prepared-return-keys is part of the API as it is public. Yesql uses it directly. If I'm reading it correctly, it looks to me that db-do-prepared only returns the update count or a batch of update counts. So it really isn't the same as db-do-prepared-return-keys.
Hide
Sean Corfield added a comment -

OK, it's been a while since I've had to dig around in the guts of java.jdbc and I tend to think in terms of query / execute! / insert! etc since that's where I work most of the time – and so, since insert! is the only piece that calls db-do-prepared-return-keys and it constructs its own SQL, I could see what you were after...

But db-do-prepared-return-keys is the only function that actually returns the keys so the problem is really that you cannot pass it a PreparedStatement like you can with db-do-prepared – and that's the hole in the API. If you could pass in a PreparedStatement (instead of just SQL and parameters), then you could do what you needed. Yes, I can see that being a valuable change. I'll create a ticket for it.

Show
Sean Corfield added a comment - OK, it's been a while since I've had to dig around in the guts of java.jdbc and I tend to think in terms of query / execute! / insert! etc since that's where I work most of the time – and so, since insert! is the only piece that calls db-do-prepared-return-keys and it constructs its own SQL, I could see what you were after... But db-do-prepared-return-keys is the only function that actually returns the keys so the problem is really that you cannot pass it a PreparedStatement like you can with db-do-prepared – and that's the hole in the API. If you could pass in a PreparedStatement (instead of just SQL and parameters), then you could do what you needed. Yes, I can see that being a valuable change. I'll create a ticket for it.
Hide
Sean Corfield added a comment -

See JDBC-115 which will update db-do-prepared-return-keys to allow a PreparedStatement. Hopefully that will address your concern.

Show
Sean Corfield added a comment - See JDBC-115 which will update db-do-prepared-return-keys to allow a PreparedStatement. Hopefully that will address your concern.
Hide
Tatu Tarvainen added a comment -

Thanks, Sean. That should address my use case perfectly.

Show
Tatu Tarvainen added a comment - Thanks, Sean. That should address my use case perfectly.
Hide
Sean Corfield added a comment -

I've kicked off the release process for 0.5.0 – should be on Maven by tomorrow.

Show
Sean Corfield added a comment - I've kicked off the release process for 0.5.0 – should be on Maven by tomorrow.

People

Vote (0)
Watch (1)

Dates

  • Created:
    Updated:
    Resolved: