Uploaded image for project: 'Teiid'
  1. Teiid
  2. TEIID-5397

Make RETURN_GENERATED_KEYS work with views

    XMLWordPrintable

    Details

    • Type: Feature Request
    • Status: Resolved (View Workflow)
    • Priority: Major
    • Resolution: Done
    • Affects Version/s: 10.2.1
    • Fix Version/s: 11.1
    • Component/s: Query Engine
    • Labels:
      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:

      1. 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!
      2. 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:

      1. How to get last_insert_id() when inserting into foreign table/views (with/without joins)?
      2. How to get updated rows (UPDATE ... RETURNING *)? At least primary keys of affected rows?

      Thanks for fixing/adding this functionality.

        Gliffy Diagrams

          Attachments

            Activity

              People

              • Assignee:
                shawkins Steven Hawkins
                Reporter:
                lukyer Lukáš Svačina
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: