[ https://issues.jboss.org/browse/TEIID-5474?page=com.atlassian.jira.plugin... ] dalex dalex updated TEIID-5474: ------------------------------- Steps to Reproduce: 1. Add MySQL (in my case I use adventureworks test database) database configurations in standalone-teiid.xml: {code:xml} <datasource jndi-name="java:/adventureworks" pool-name="adventureworks" enabled="true" use-java-context="true"> <connection-url>jdbc:mysql://localhost:3306/adventureworks?zeroDateTimeBehavior=convertToNull</connection-url> <driver>mysql</driver> <new-connection-sql>set SESSION sql_mode = 'ANSI'</new-connection-sql> <pool> <min-pool-size>2</min-pool-size> <max-pool-size>70</max-pool-size> </pool> <security> <user-name>root</user-name> <password>xxxxxx</password> </security> <validation> <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLValidConnectionChecker"/> <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLExceptionSorter"/> </validation> <timeout> <blocking-timeout-millis>120000</blocking-timeout-millis> <idle-timeout-minutes>5</idle-timeout-minutes> </timeout> </datasource> {code} 2. Add in test-vdb.xml java:/adventureworks configured in previous step as a data source: {code:xml} <model name="adventureworks"> <property name="importer.useFullSchemaName" value="false"/> <property name="importer.tableTypes" value="TABLE,VIEW"/> <property name="importer.importKeys" value="false"/> <source name="adventureworks" translator-name="mylobs" connection-jndi-name="java:/adventureworks"/> </model> <translator name="mylobs" type="mysql5"> <property name="CopyLobs" value="true" /> <property name="SupportsNativeQueries" value="true"/> </translator> {code} 3. Add in test-vdb.xml "createDateDimensionsTable" virtual procedure: {code:xml} <model visible = "true" type = "VIRTUAL" name = "views"> <metadata type = "DDL"><![CDATA[ CREATE PROCEDURE createDateDimensionsTable( IN startdate date NOT NULL OPTIONS (ANNOTATION 'Start date for table.'), IN enddate date OPTIONS (ANNOTATION 'End date for table. If NULL, uses current date.') ) RETURNS ( "month_start" date ) AS BEGIN select CURDATE() as month_start; END ]]> </metadata> </model> {code} 4. Run the test query: {code:sql} SELECT (exec "SYSADMIN.logMsg"( "level" => 'INFO', "context" => 'DEBUG.FOO.BAR', "msg" => tccd.bank_account_holder_name)) as something FROM ( SELECT c.city AS "bank_account_holder_name" FROM "adventureworks.address" c JOIN ( select ca.addressid from "adventureworks.customeraddress" ca cross JOIN ( WITH latest_exchange_rates AS ( SELECT exchange_rate_date AS month_begin FROM ( SELECT CURDATE() as exchange_rate_date )t ) SELECT DISTINCT dt.month_start AS month_begin FROM ( CALL views.createDateDimensionsTable( "startdate" => TIMESTAMPADD(SQL_TSI_MONTH, 1, (SELECT month_begin FROM latest_exchange_rates)), "enddate" => TIMESTAMPADD(SQL_TSI_YEAR, 15, (SELECT month_begin FROM latest_exchange_rates)) ) ) AS dt ) fx ) ci ON ci.addressid = c.addressid ) tccd ;; {code} was: 1. Add MySQL (in my case I use adventureworks test database) database configurations in standalone-teiid.xml: {code:xml} <datasource jndi-name="java:/adventureworks" pool-name="adventureworks" enabled="true" use-java-context="true"> <connection-url>jdbc:mysql://localhost:3306/adventureworks?zeroDateTimeBehavior=convertToNull</connection-url> <driver>mysql</driver> <new-connection-sql>set SESSION sql_mode = 'ANSI'</new-connection-sql> <pool> <min-pool-size>2</min-pool-size> <max-pool-size>70</max-pool-size> </pool> <security> <user-name>root</user-name> <password>xxxxxx</password> </security> <validation> <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLValidConnectionChecker"/> <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLExceptionSorter"/> </validation> <timeout> <blocking-timeout-millis>120000</blocking-timeout-millis> <idle-timeout-minutes>5</idle-timeout-minutes> </timeout> </datasource> {code} 2. Add in test-vdb.xml java:/adventureworks configured in previous step as data source: {code:xml} <model name="adventureworks"> <property name="importer.useFullSchemaName" value="false"/> <property name="importer.tableTypes" value="TABLE,VIEW"/> <property name="importer.importKeys" value="false"/> <source name="adventureworks" translator-name="mylobs" connection-jndi-name="java:/adventureworks"/> </model> <translator name="mylobs" type="mysql5"> <property name="CopyLobs" value="true" /> <property name="SupportsNativeQueries" value="true"/> </translator> {code} 3. Add "createDateDimensionsTable" virtual procedure: {code:xml} <model visible = "true" type = "VIRTUAL" name = "views"> <metadata type = "DDL"><![CDATA[ CREATE PROCEDURE createDateDimensionsTable( IN startdate date NOT NULL OPTIONS (ANNOTATION 'Start date for table.'), IN enddate date OPTIONS (ANNOTATION 'End date for table. If NULL, uses current date.') ) RETURNS ( "month_start" date ) AS BEGIN select CURDATE() as month_start; END ]]> </metadata> </model> {code} 4. Run the test query: {code:sql} SELECT (exec "SYSADMIN.logMsg"( "level" => 'INFO', "context" => 'DEBUG.FOO.BAR', "msg" => tccd.bank_account_holder_name)) as something FROM ( SELECT c.city AS "bank_account_holder_name" FROM "adventureworks.address" c JOIN ( select ca.addressid from "adventureworks.customeraddress" ca cross JOIN ( WITH latest_exchange_rates AS ( SELECT exchange_rate_date AS month_begin FROM ( SELECT CURDATE() as exchange_rate_date )t ) SELECT DISTINCT dt.month_start AS month_begin FROM ( CALL views.createDateDimensionsTable( "startdate" => TIMESTAMPADD(SQL_TSI_MONTH, 1, (SELECT month_begin FROM latest_exchange_rates)), "enddate" => TIMESTAMPADD(SQL_TSI_YEAR, 15, (SELECT month_begin FROM latest_exchange_rates)) ) ) AS dt ) fx ) ci ON ci.addressid = c.addressid ) tccd ;; {code} -- This message was sent by Atlassian JIRA (v7.5.0#75005)