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.

[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?

[jchidiac]

Both OrderDate and ShipDate are  date,

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

and i got the same error.

[shawkins]

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

[jchidiac]

yes

[shawkins]

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

[jchidiac]

It works.

Thx.

[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.

[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 ;

  }