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

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

    XMLWordPrintable

Details

    • 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
      

      Attachments

        Activity

          People

            rhn-engineering-shawkins Steven Hawkins
            dalex005 Dmitrii Pogorelov
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: