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

    • Icon: Bug Bug
    • Resolution: Done
    • Icon: Blocker Blocker
    • 12.2, 12.1.2
    • 12.0
    • Query Engine
    • None
    • 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

      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

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

                Created:
                Updated:
                Resolved: