Hi, I am using next.jdbc and am doing a batch insert using next.jdbc.prepare/execute-batch!
It is working as expected but then following the instructions here https://cljdoc.org/d/seancorfield/next.jdbc/1.1.569/doc/getting-started/prepared-statements#batched-parameters
I and trying to use .getGeneratedKeys to get the results set back and it only returns the last batch to be inserted not the entire set.
Is this expected behaviour? (edited)
sofra 21:59
I sort of understand why, it is stateful and .getGeneratedKeys would need to be called in between each call to .executeBatch?
seancorfield 22:05
@sofra Yeah, it's a bit of an edge case. Different databases behave differently on this, unfortunately.
sofra 22:05
:thumbsup:
seancorfield 22:05
Some databases will return all generated keys (from all batches executed), some don't return any keys at all.
sofra 22:06
Thanks @seancorfield, makes sense
seancorfield 22:07
(hence all the caveats on that page :slightly_smiling_face: )
22:10
How big is the batch you're inserting @sofra?
seancorfield 22:16
If it's large enough to cause multiple .executeBatch calls, I can understand you only getting one set of keys back. I guess I could modify execute-batch! so that if :return-keys is truthy, it could attempt to call .getGeneratedKeys each time internally and return the result sets joined together. It seems like a reasonable enhancement, although I'd need to ensure it doesn't break existing behavior (so, probably another option as well as :return-keys I suspect).
From sofra on Slack:
Hi, I am using next.jdbc and am doing a batch insert using next.jdbc.prepare/execute-batch! It is working as expected but then following the instructions here https://cljdoc.org/d/seancorfield/next.jdbc/1.1.569/doc/getting-started/prepared-statements#batched-parameters I and trying to use .getGeneratedKeys to get the results set back and it only returns the last batch to be inserted not the entire set. Is this expected behaviour? (edited)
sofra 21:59 I sort of understand why, it is stateful and .getGeneratedKeys would need to be called in between each call to .executeBatch?
seancorfield 22:05 @sofra Yeah, it's a bit of an edge case. Different databases behave differently on this, unfortunately.
sofra 22:05 :thumbsup:
seancorfield 22:05 Some databases will return all generated keys (from all batches executed), some don't return any keys at all.
sofra 22:06 Thanks @seancorfield, makes sense
seancorfield 22:07 (hence all the caveats on that page :slightly_smiling_face: ) 22:10 How big is the batch you're inserting @sofra?
seancorfield 22:16 If it's large enough to cause multiple .executeBatch calls, I can understand you only getting one set of keys back. I guess I could modify execute-batch! so that if :return-keys is truthy, it could attempt to call .getGeneratedKeys each time internally and return the result sets joined together. It seems like a reasonable enhancement, although I'd need to ensure it doesn't break existing behavior (so, probably another option as well as :return-keys I suspect).