6 Replies Latest reply on Jan 14, 2015 7:51 AM by gadeyne.bram

    using foreign (temporary) tables

    gadeyne.bram Master



      For some queries we'd like to use some physical (temporary) tables.


      I found that this is supported with the create foreign temporary table syntax.


      I created a MySQL database tmptables that can hold these temporary tables. I've also configured the user's role to have create, read, update, delete, execute and alter priviliges on the model. The role can also create temporary tables.


      If I understand the documentation correctly it is necessary to create te table in the mysql database but a reference can be obtained using this syntax. So I created the table in the tmptables database. The I executed the following command:


      create foreign temporary table tmptbl_bram_test(

           patientid inteher

           OPTIONS (UPDATABLE true)

      ) on tmptables;


      The create foreign temporary table command executes without an error.


      Now I try to add something to the table. But this fails with a message user <bram@teiid-security> is not entitled to action <CREATE> for 1 or more of the groups/elements/procedures.


      insert into tmptbl_bram_test(patientid) values(1);


      Is my understanding of this command correct? Where should I set this create action privilege?


      With kind regards