8 Replies Latest reply on Apr 21, 2017 9:16 AM by Ramesh Reddy Branched from an earlier discussion.

    Re: How to connect to MySQL data source using teiid data source builder

    Ramesh Reddy Master

      Create datasource in your WildFly, then use the data source builder.

        • 1. Re: How to connect to MySQL data source using teiid data source builder
          Sanjeewa Ranasinghe Newbie

          Hi,

           

          Thanks for the earlier reply

          I downloaded the teiid-8.12.4 server and I updated the "standalone/configuration standalone-teiid.xml" file and "module.xml" file in "module/..../com/mysql/jdbc/main" as fallowing

           

          <module xmlns="urn:jboss:module:1.0" name="com.mysql.jdbc">

            <resources>

              <resource-root path="mysql-connector-java.jar"/>

            </resources>

            <dependencies>

              <module name="javax.api"/>

            </dependencies>

          </module>

           

          and

           

          <datasources>

                          <datasource jndi-name="java:/accounts-ds" pool-name="AccountsH2" enabled="true" use-java-context="true">

                              <connection-url>jdbc:h2:mem:accounts;INIT=RUNSCRIPT FROM '${jboss.home.dir}/h2files/customer-schema.sql';</connection-url>

                              <driver>h2</driver>

                              <security>

                                  <user-name>sa</user-name>

                                  <password>sa</password>

                              </security>

                          </datasource>

                          <datasource jndi-name="java:jboss/datasources/mysql-ds" pool-name="mysqlDS" enabled="true">

                              <connection-url>jdbc:mysql://localhost:3306/myDb</connection-url>

                              <driver>mysql</driver>

                              <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>

                              <pool>

                                  <min-pool-size>10</min-pool-size>

                                  <max-pool-size>100</max-pool-size>

                                  <prefill>true</prefill>

                              </pool>

                              <security>

                                  <user-name>root</user-name>

                                  <password>ajeewa@114</password>

                              </security>

                              <statement>

                                  <prepared-statement-cache-size>32</prepared-statement-cache-size>

                                  <share-prepared-statements>true</share-prepared-statements>

                              </statement>

                          </datasource>

                          <drivers>

                              <driver name="h2" module="com.h2database.h2">

                                  <xa-datasource-class>org.h2.jdbcx.JdbcDataSource</xa-datasource-class>

                              </driver>

                              <driver name="teiid-local" module="org.jboss.teiid">

                                  <driver-class>org.teiid.jdbc.TeiidDriver</driver-class>

                                  <xa-datasource-class>org.teiid.jdbc.TeiidDataSource</xa-datasource-class>

                              </driver>

                              <driver name="teiid" module="org.jboss.teiid.client">

                                  <driver-class>org.teiid.jdbc.TeiidDriver</driver-class>

                                  <xa-datasource-class>org.teiid.jdbc.TeiidDataSource</xa-datasource-class>

                              </driver>

                              <driver name="mysql" module="com.mysql.jdbc">

                                  <xa-datasource-class>com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</xa-datasource-class>

                              </driver>

                          </drivers>

                      </datasources>

           

          and I get the fallowing errors when starting the teiid server

           

          11:29:32,694 ERROR [org.jboss.as.controller.management-operation] (Controller Boot Thread)  JBAS014612: Operation ("add") failed - address: ([

              ("subsystem" => "datasources"),

              ("data-source" => "mysqlDS")

          ]) - failure description: {"JBAS014771: Services with missing/unavailable dependencies" => [

              "jboss.data-source.java:jboss/datasources/mysql-ds is missing [jboss.jdbc-driver.mysql]",

              "jboss.driver-demander.java:jboss/datasources/mysql-ds is missing [jboss.jdbc-driver.mysql]"

          ]}

          11:29:32,698 ERROR [org.jboss.as.controller.management-operation] (Controller Boot Thread)  JBAS014612: Operation ("enable") failed - address: ([

              ("subsystem" => "datasources"),

              ("data-source" => "mysqlDS")

          ]) - failure description: {"JBAS014879: One or more services were unable to start due to one or more indirect dependencies not being available." => {

              "Services that were unable to start:" => [

                  "jboss.data-source.reference-factory.mysqlDS",

                  "jboss.naming.context.java.jboss.datasources.mysql-ds"

              ],

              "Services that may be the cause:" => ["jboss.jdbc-driver.mysql"]

           

          could you help me with this

          • 2. Re: How to connect to MySQL data source using teiid data source builder
            Ramesh Reddy Master

            Did you copy the JAR file "mysql-connector-java.jar" into the module directory?

            • 3. Re: How to connect to MySQL data source using teiid data source builder
              Sanjeewa Ranasinghe Newbie

              Yes I copied. I sorted out the problem. The problem was with my module.xml file.

              Thanks for the help Ramesh

              • 4. Re: How to connect to MySQL data source using teiid data source builder
                Sanjeewa Ranasinghe Newbie

                How can I get the equivalent JSON format of the fallowing code. Currently it returns the xml type

                 

                <metadata type="DDL"><![CDATA[

                            SET NAMESPACE 'http://teiid.org/rest' AS REST;

                            CREATE VIRTUAL PROCEDURE g1Table(IN p1 integer) RETURNS TABLE (xml_out xml) OPTIONS (UPDATECOUNT 0, "REST:METHOD" 'GET', "REST:URI" 'g1/{p1}')

                            AS

                            BEGIN

                                SELECT XMLELEMENT(NAME "rows", XMLATTRIBUTES (g1Table.p1 as p1), XMLAGG(XMLELEMENT(NAME "row", XMLFOREST(ID, EMPNAME)))) AS xml_out FROM testDataSource3.testTb where ID = p1;

                               

                            END

                            ]]>

                </metadata>

                • 5. Re: How to connect to MySQL data source using teiid data source builder
                  Ramesh Reddy Master

                  You need to use the JSON functions to build your payload. See documentation here JSON Functions · Teiid Documentation

                   

                  I think the documentation does not have enough examples, it does need improvement.

                   

                  <metadata type="DDL"><![CDATA[

                              SET NAMESPACE 'http://teiid.org/rest' AS REST;

                              CREATE VIRTUAL PROCEDURE g1Table(IN p1 integer) RETURNS TABLE (xml_out clob) OPTIONS (UPDATECOUNT 0, "REST:METHOD" 'GET', "REST:URI" 'g1/{p1}')

                              AS

                              BEGIN

                                 SELECT JSONOBJECT(JSONARRAY_AGG(JSONOBJECT(ID, EMPNAME)) as rows) AS xml_out FROM testDataSource3.testTb where ID = p1;

                              END

                              ]]>

                  </metadata>

                  • 7. Re: How to connect to MySQL data source using teiid data source builder
                    Sanjeewa Ranasinghe Newbie

                    Yes I also think It would be better if there is enough examples for JSON in the documentation as well. Problem with my code was that I used "json" as the RETURN type for the result set. It got sorted when I used "clob" as the RETURN type when using JSON functions within PROCEDURE.

                     

                    Below code works just fine.

                     

                    <model name="views" type="VIRTUAL">

                    <metadata type="DDL">

                    <![CDATA[CREATE VIEW DataService3View (

                      RowId integer,

                      ID long,

                      EMPNAME string,

                      PRIMARY KEY(RowId)

                    )

                    AS

                    SELECT ROW_NUMBER() OVER (ORDER BY ID), ID, EMPNAME FROM testTb;

                    ]]>

                     

                     

                    </metadata>

                    </model>

                     

                     

                    <model name="View" type ="VIRTUAL">

                             <metadata type="DDL"><![CDATA[

                                SET NAMESPACE 'http://teiid.org/rest' AS REST;

                                CREATE VIRTUAL PROCEDURE g1Table(IN p1 integer) RETURNS (json_out clob) OPTIONS (UPDATECOUNT 0, "REST:METHOD" 'GET', "REST:URI" 'g1/{p1}')

                                AS

                                BEGIN

                                    SELECT JSONARRAY(ID, EMPNAME) AS json_out FROM testDataSource.testTb where ID = p1;

                                  

                                END

                                ]]>

                                </metadata>

                    </model>

                     

                     

                    <model name="testDataSource" type="PHYSICAL">

                    <source name="testMySQLData" translator-name="mysql" connection-jndi-name="java:/mysqlDS1"/>

                    <metadata type="DDL">

                    <![CDATA[CREATE FOREIGN TABLE testTb (

                      ID long OPTIONS ("NAMEINSOURCE" '`ID`', "NATIVE_TYPE" 'INT'),

                      EMPNAME string(20) OPTIONS ("NAMEINSOURCE" '`EMPNAME`', "NATIVE_TYPE" 'VARCHAR')

                    ) OPTIONS ("UPDATABLE" 'TRUE', "NAMEINSOURCE" '`testTb`');

                    ]]>

                     

                     

                    Thanks for the help Ramesh

                    • 8. Re: How to connect to MySQL data source using teiid data source builder
                      Ramesh Reddy Master

                      Documentation fix already done, let me know if that is not clear for future.