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

Make RETURN_GENERATED_KEYS work with views

    XMLWordPrintable

Details

    • Feature Request
    • Status: Resolved (View Workflow)
    • Major
    • Resolution: Done
    • 10.2.1
    • 11.1
    • Query Engine
    • 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.

      Attachments

        Activity

          People

            rhn-engineering-shawkins Steven Hawkins
            lukyer Lukas Lukyer (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: