Details
-
Feature Request
-
Resolution: Done
-
Major
-
10.2.1
-
None
Description
VDB:
<vdb name="_GENERATED_form_shoes" version="2"> <model name="_INTERNAL_internalModel" type="PHYSICAL"> <source name="internal_postgresql" translator-name="postgresql" connection-jndi-name="java:/internal"/> </model> <model name="GEN_view" type="VIRTUAL"> <metadata type="DDL"> <![CDATA[ CREATE VIEW "_view_workflow_data" OPTIONS (UPDATABLE 'true') AS SELECT "public"."form_shoes_2"."size" AS "size", "public"."form_shoes_2"."model" AS "model", "public"."form_shoes_2"."id" AS "id" FROM "public"."form_shoes_2" ]]> </metadata> </model> </vdb>
TABLE:
name: form_shoes_2
columns: id (SERIAL) | size (INTEGER NULLABLE) | model (VARCHAR NULLABLE)
PROBLEM:
Connected into VDB using JDBC like:
final PreparedStatement statement = c.prepareStatement(...INSERT..., Statement.RETURN_GENERATED_KEYS); statement.executeUpdate(); final ResultSet generatedKeys = statement.getGeneratedKeys();
generatedKeys is empty if:
- INSERT INTO "form_shoes_2" ( "model" ) VALUES ( 'adidas x1' ) ...... e.g. not all columns are enumerated ... if so, you can provide NULL values to optional columns and generated keys WORK!
- INSERT INTO "_view_workflow_data" (id, name, size) VALUES (42, 'adidas x2', 12 ) ....... e.g. insering into view (1:1, no joins involved) even when all columns ARE enumerated ... probably no way how to get generated keys here?
QUESTIONS:
- How to get last_insert_id() when inserting into foreign table/views (with/without joins)?
- How to get updated rows (UPDATE ... RETURNING *)? At least primary keys of affected rows?
Thanks for fixing/adding this functionality.