8 Replies Latest reply on Sep 23, 2014 5:34 AM by jchidiac

    Teiid Embedded, TIMESTAMPDIFF with MYSQL DataBase

    jchidiac

      Hi,

      I'm using TeiidEmbedded Server with a MYSQL DataBase

       

      when i execute the following query :

                SELECT TIMESTAMPDIFF(SQL_TSI_MONTH, tborders.OrderDate, tborders.ShipDate)

                  FROM OrdersModel.Orders.tborders tborders

            LIMIT 10

       

      i got the following error :

      WARNING: TEIID30020 Processing exception for request or8iFEOILVPu.0 'TEIID30070

      The function 'TIMESTAMPDIFF(SQL_TSI_MONTH, tborders.OrderDate, tborders.ShipDate)' is a valid function form,

      but the arguments do not match a known type signature and cannot be converted using implicit type conversions.'.

      Originally QueryResolverException ResolverVisitor.java:606. Enable more detailed logging to see the entire stacktrace.

       

      When i debug the code, i found that the system found 2 functions with the same Name :

      Class      :: FunctionLibrary.java

      Function :: FunctionDescriptor[] determineNecessaryConversions(String name, Class<?> returnType, Expression[] args, Class<?>[] types, boolean hasUnknownType)

       

      1 - titimestampdiff(string interval, timestamp timestamp1, timestamp timestamp2) : long result

      2 - mysql.timestampdiff(string param1, timestamp param2, timestamp param3) : integer result

       

      and end of the function, the ambiguous variable equal a true and raise a GENERIC_EXCEPTION.

        • 1. Re: Teiid Embedded, TIMESTAMPDIFF with MYSQL DataBase
          shawkins

          It must be that one or both of OrderDate/ShipDate is a date, not a timestamp.  If the types match exactly we automatically give preference to the system function in resolving.  More than likely you are seeing this after [TEIID-3111] Function with multi-part names not resolving correctly - JBoss Issue Tracker correct?

          • 2. Re: Teiid Embedded, TIMESTAMPDIFF with MYSQL DataBase
            jchidiac

            Both OrderDate and ShipDate are  date,

            i tried with the modification, one class  "FunctionTree.java" is modified

             

            and i got the same error.

            • 3. Re: Teiid Embedded, TIMESTAMPDIFF with MYSQL DataBase
              shawkins

              So you are saying that you are seeing this behavior both before and after TEIID-3111?

              • 4. Re: Teiid Embedded, TIMESTAMPDIFF with MYSQL DataBase
                jchidiac

                yes

                • 5. Re: Teiid Embedded, TIMESTAMPDIFF with MYSQL DataBase
                  shawkins

                  I'll put an additional change with TEIID-3111 then to make the resolving more consistent.

                  • 6. Re: Teiid Embedded, TIMESTAMPDIFF with MYSQL DataBase
                    jchidiac

                    It works.

                    Thx.

                    • 7. Re: Teiid Embedded, TIMESTAMPDIFF with MYSQL DataBase
                      sudeepd

                      Joseph,

                      I am trying to get teiid embedded with a mysql connection, and have been running into problems with it. (See help with teiid embedded deployVDBzip)

                      Looks like you have it working with mysql...can you please take a look at my sample on the link mentioned here, and see if anything jumps out ? I am just starting with teiid, and may be missing something very obvious.

                      • 8. Re: Teiid Embedded, TIMESTAMPDIFF with MYSQL DataBase
                        jchidiac

                        I'm using the following codes :

                         

                          public  String getUrl_MYSQL(String ip, Integer port, String catalog) {

                               return "jdbc:mysql://" + ip + ":" + port + "/" + catalog + "?autoReconnect=true";

                          }

                         

                          protected DataSource createMysqlDataSource(String ip, Integer port, String catalog, String user, String pw) {

                          BasicDataSource dataSource = new BasicDataSource();

                          dataSource.setDriverClassName("com.mysql.jdbc.Driver");

                          dataSource.setUrl(getUrl_MYSQL(ip, port, catalog));

                          dataSource.setUsername(user);

                          dataSource.setPassword(pw);

                          dataSource.setValidationQuery("select now()" ) ; 

                          dataSource.setRemoveAbandoned(true);

                          dataSource.setRemoveAbandonedTimeout(60);

                          //

                          return dataSource;

                          }

                         

                         

                          private String getDS(String catalog) {

                          return catalog + "-ds";

                          }

                         

                         

                          private ModelMetaData createModelMetaData(  String serviceName,   String translator ) {

                          ModelMetaData modelMetaData = new ModelMetaData();

                          modelMetaData.setName( serviceName + "Model" );

                          modelMetaData.addProperty("importer.tableTypes", "TABLE,VIEW");

                          //

                          modelMetaData.addSourceMapping(serviceName, translator, getDS(serviceName));

                          return modelMetaData;

                          }

                         

                          public void deploy_MYSQL5( EmbeddedServer ebdServer, String ip, Integer port, String catalog, String user, String pw ) throws Exception {

                          DataSource mysqlDataSource = createMysqlDataSource(ip, port, catalog, user, pw);

                          ConnectionFactoryProvider<DataSource> mysqlCfp = new EmbeddedServer.SimpleConnectionFactoryProvider<DataSource>(mysqlDataSource);

                          ebdServer.addConnectionFactoryProvider(getDS(catalog), mysqlCfp);

                          ModelMetaData mysqlModelMetaData = createModelMetaData(catalog, "mysql5" );

                          ebdServer.deployVDB(catalog + "VDB", mysqlModelMetaData);

                          }

                         

                          public EmbeddedServer initEmbeddedServer() {

                          EmbeddedServer ebdServer = new EmbeddedServer() ;

                         

                         

                          //init Translator

                          MySQL5ExecutionFactory executionFactory = new MySQL5ExecutionFactory();

                          executionFactory.start();

                          ebdServer.addTranslator("mysql5", executionFactory);

                         

                          // start the Server

                          EmbeddedConfiguration embeddedConfiguration = new EmbeddedConfiguration();

                          embeddedConfiguration.setUseDisk(true);

                          ebdServer.start(embeddedConfiguration) ;

                          //

                          return ebdServer ;

                          }