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

Procedure does not get executed (without error) when comment and variable declaration are present

    Details

    • Steps to Reproduce:
      Hide

      1. In postgresql create the testRemove table using the following script:

      CREATE TABLE public.testRemove
      (
        a integer,
        b integer
      );
      INSERT INTO public.testRemove(a, b) VALUES (1, 1);
      

      2. Add postgresql database configuration in standalone-teiid.xml:

                      <datasource jndi-name="java:/test_dwh_pg" pool-name="test_dwh_pg" enabled="true" use-java-context="true">
                          <connection-url>jdbc:postgresql://localhost:5432/test_dwh?charSet=utf8</connection-url>
                          <driver-class>org.postgresql.Driver</driver-class>
                          <driver>org.postgresql</driver>
                          <pool>
                              <min-pool-size>2</min-pool-size>
                              <max-pool-size>70</max-pool-size>
                              <prefill>false</prefill>
                              <use-strict-min>false</use-strict-min>
                              <flush-strategy>FailingConnectionOnly</flush-strategy>
                          </pool>
                          <security>
                              <user-name>XXXXX</user-name>
                              <password>XXXXX</password>
                          </security>
                          <validation>
                              <check-valid-connection-sql>select 0</check-valid-connection-sql>
                          </validation>
                          <timeout>
                              <blocking-timeout-millis>120000</blocking-timeout-millis>
                              <idle-timeout-minutes>5</idle-timeout-minutes>
                          </timeout>
                      </datasource>
      

      3. Add in test-vdb.xml java:/test_tables_pg configured in previous step as datasource:

          <model name="dwh">
              <property name="importer.useFullSchemaName" value="false"/>
              <property name="importer.tableTypes" value="TABLE,VIEW"/>
              <property name="importer.importKeys" value="false"/>
              <source name="test_dwh_pg" translator-name="myPg" connection-jndi-name="java:/test_dwh_pg"/>
          </model>
      

      4. Configure in the test-vdb.xml the following virtual view:

          <model visible = "true" type = "VIRTUAL" name = "procs">
              <metadata type = "DDL"><![CDATA[
                   create virtual procedure deleteTableTest (
      ) AS 
      -- 
      begin
          declare string v;
          execute immediate 'DELETE from dwh.testRemove' without return ;
      end
              ]]>
              </metadata>
          </model>
      

      5. Run the following query:

      call procs.deleteTableTest() ;;
      select * from dwh.testRemove ;;
      

      The deleteTableTest virtual procedure should delete the data from the table. It will do it if you either remove the comment or the variable in the virtual procedure definition:

      create virtual procedure deleteTableTest (
      ) AS 
      begin
          declare string v;
          execute immediate 'DELETE from dwh.testRemove' without return ;
      end
      

      or

      create virtual procedure deleteTableTest (
      ) AS 
      -- 
      begin
          execute immediate 'DELETE from dwh.testRemove' without return ;
      end
      
      Show
      1. In postgresql create the testRemove table using the following script: CREATE TABLE public .testRemove ( a integer , b integer ); INSERT INTO public .testRemove( a , b) VALUES (1, 1); 2. Add postgresql database configuration in standalone-teiid.xml: <datasource jndi-name= "java:/test_dwh_pg" pool-name= "test_dwh_pg" enabled= "true" use-java-context= "true" > <connection-url> jdbc:postgresql://localhost:5432/test_dwh?charSet=utf8 </connection-url> <driver-class> org.postgresql.Driver </driver-class> <driver> org.postgresql </driver> <pool> <min-pool-size> 2 </min-pool-size> <max-pool-size> 70 </max-pool-size> <prefill> false </prefill> <use-strict-min> false </use-strict-min> <flush-strategy> FailingConnectionOnly </flush-strategy> </pool> <security> <user-name> XXXXX </user-name> <password> XXXXX </password> </security> <validation> <check-valid-connection-sql> select 0 </check-valid-connection-sql> </validation> <timeout> <blocking-timeout-millis> 120000 </blocking-timeout-millis> <idle-timeout-minutes> 5 </idle-timeout-minutes> </timeout> </datasource> 3. Add in test-vdb.xml java:/test_tables_pg configured in previous step as datasource: <model name= "dwh" > <property name= "importer.useFullSchemaName" value= "false" /> <property name= "importer.tableTypes" value= "TABLE,VIEW" /> <property name= "importer.importKeys" value= "false" /> <source name= "test_dwh_pg" translator-name= "myPg" connection-jndi-name= "java:/test_dwh_pg" /> </model> 4. Configure in the test-vdb.xml the following virtual view: <model visible = "true" type = "VIRTUAL" name = "procs" > <metadata type = "DDL" > <![CDATA[ create virtual procedure deleteTableTest ( ) AS -- begin declare string v; execute immediate 'DELETE from dwh.testRemove' without return ; end ]]> </metadata> </model> 5. Run the following query: call procs.deleteTableTest() ;; select * from dwh.testRemove ;; The deleteTableTest virtual procedure should delete the data from the table. It will do it if you either remove the comment or the variable in the virtual procedure definition: create virtual procedure deleteTableTest ( ) AS begin declare string v; execute immediate ' DELETE from dwh.testRemove' without return ; end or create virtual procedure deleteTableTest ( ) AS -- begin execute immediate ' DELETE from dwh.testRemove' without return ; end

      Description

      Using the below definition, it should delete the data from the table. It will do it if you either remove the comment or the variable declaration. Interestingly, it looks like the proc is executed, but the data is still present in the test table:

      create virtual procedure deleteTableTest (
      ) AS 
      -- 
      begin
          declare string v;
          execute immediate 'DELETE from dwh.testRemove' without return ;
      end
      

        Gliffy Diagrams

          Attachments

            Activity

              People

              • Assignee:
                shawkins Steven Hawkins
                Reporter:
                dalex005 Dmitrii Pogorelov
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: