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

[rareddy]

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

[sanjeewa111]

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

[rareddy]

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

[sanjeewa111]

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

Thanks for the help Ramesh

[sanjeewa111]

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>

[rareddy]

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>

[rareddy]

Also added more documentation now for JIRA [1] at [2]

[1] [TEIID-4872] JSON Functions need more examples - JBoss Issue Tracker

[2] JSON Functions · Teiid Documentation

[sanjeewa111]

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

[rareddy]

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