Skip to main content


Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Modeling » Epsilon » jdbc emc problem(etl transformation problem related to a model read from a MySql db by the jdbc emc)
jdbc emc problem [message #1767359] Wed, 05 July 2017 07:48 Go to next message
Gianluigi Proserpio is currently offline Gianluigi ProserpioFriend
Messages: 19
Registered: November 2015
Junior Member
Hi,
I'm using the Jdbc emc code form epsilonlab in order to manage an input from a MySQL db, for an ETL trasformation.

This is the code I put in EtlStandaloneExample.java in order to manage the input model form the MySQL db:

MySqlModel model = new MySqlModel();
model.setServer("192.168.1.4");
model.setDatabase("xxx");
model.setUsername("yyy");
model.setPassword("xxx");
model.setPort(3306);
model.setName("MySQL");
model.setStreamResults(true);
model.load();
models.add(model);

and this is the simply ETL code that use to manage the model data read from the table "Item1" of the MySQL db:

rule MyRule
transform s : MySQL!Item1
to t : adms!GeographicalRegion
{
System.out.println('Rule MySql: ' + s.Time + " " + s.Value);
}

With small tables (less than 2MB) I usually get fine results like:
Rule MySql: 2016-03-23 15:06:40.0 74.2
Rule MySql: 2016-03-23 15:10:00.0 74.2

but when I use a bigger table (i.e. more than 4.5 MB) with the result that seems good, I also get an error like this:
Exception in Result#equals java.sql.SQLException:
Operation not allowed after ResultSet closed
(See attachment forthe log that is repeated many times)

Any idea about this issue ?
Many thanks
Gianluigi

  • Attachment: log.txt
    (Size: 1.49KB, Downloaded 48 times)
Re: jdbc emc problem [message #1767645 is a reply to message #1767359] Sat, 08 July 2017 09:26 Go to previous messageGo to next message
Dimitris Kolovos is currently offline Dimitris KolovosFriend
Messages: 1589
Registered: July 2009
Location: York, UK
Senior Member

Hi Gianluigi,

Thank you for reporting this. Is there any chance you could share a compressed dump of your larger database so that we can reproduce this and investigate?

Cheers,
Dimitris
Re: jdbc emc problem [message #1767682 is a reply to message #1767645] Sun, 09 July 2017 14:04 Go to previous messageGo to next message
Gianluigi Proserpio is currently offline Gianluigi ProserpioFriend
Messages: 19
Registered: November 2015
Junior Member
Hi Dimitris,
I'm using the database of the "openhab" home automation software in order to test the Mysql emc driver.
The physical backup of the mysql directory is available at https://drive.google.com/open?id=0BxEw5jaUahy6TDZKTEZ6RVAtZ00 (12 MB)-
Let me know if this work for you or you need a Logical backup of the Mysql db.
The db name and pwd are both "openhab".
The table Item2 (2.5 MB) works fine, while the bigger Item1 (or Item16) table generate the error.

Thank you for your kind support
Gianluigi
Re: jdbc emc problem [message #1767719 is a reply to message #1767682] Mon, 10 July 2017 10:05 Go to previous messageGo to next message
Dimitris Kolovos is currently offline Dimitris KolovosFriend
Messages: 1589
Registered: July 2009
Location: York, UK
Senior Member

Hi Gianluigi,

It'd help a lot if you could send me a compressed SQL dump I can import in my MySQL server instead of a copy of your MySQL directory (I'm sure I can somehow import the data from what you've shared but I haven't figured out how so far).

Cheers,
Dimitris
Re: jdbc emc problem [message #1767727 is a reply to message #1767719] Mon, 10 July 2017 11:49 Go to previous messageGo to next message
Gianluigi Proserpio is currently offline Gianluigi ProserpioFriend
Messages: 19
Registered: November 2015
Junior Member
Hi Dimitris,
you could find a sql backup at https://drive.google.com/file/d/0BxEw5jaUahy6ZUdnYWh4Q3ZncmM/view?usp=sharing

Gianluigi
Re: jdbc emc problem [message #1767731 is a reply to message #1767727] Mon, 10 July 2017 12:07 Go to previous messageGo to next message
Dimitris Kolovos is currently offline Dimitris KolovosFriend
Messages: 1589
Registered: July 2009
Location: York, UK
Senior Member

Hi Gianluigi,

Thanks for doing this. Running the following code on my setup with the default JVM heap setting throws an OutOfMemory error. However, after increasing the heap size to 1024MB (added -Xmx1024m in the VM arguments of my run configuration), the program terminates without errors.

public static void main(String[] args) throws Exception {
  MySqlModel model = new MySqlModel();
  model.setServer("localhost");
  model.setDatabase("openhab");
  model.setUsername("root");
  model.setPassword("root");
  model.setPort(3306);
  model.setName("MySQL");
  model.setStreamResults(true);
  model.load();
	
  EtlModule module = new EtlModule();
  module.parse("rule R1 transform s : MySQL!Item1 to t : Sequence { System.out.println('Rule MySql: ' + s.Time + ' ' + s.Value); }");
  module.getContext().getModelRepository().addModel(model);
  module.execute();
}


Could you please let me know if this works for you?

Cheers,
Dimitris
Re: jdbc emc problem [message #1767747 is a reply to message #1767731] Mon, 10 July 2017 14:50 Go to previous messageGo to next message
Gianluigi Proserpio is currently offline Gianluigi ProserpioFriend
Messages: 19
Registered: November 2015
Junior Member
Hi Dimitris,
I inserted your code inside the main of the original "org.eclipse.epsilon.emc.mysql" project:

package org.eclipse.epsilon.emc.mysql;
import java.sql.SQLException;
import org.eclipse.epsilon.emc.jdbc.JdbcModel;
import org.eclipse.epsilon.eol.EolModule;
import org.eclipse.epsilon.etl.EtlModule;

import com.mysql.jdbc.Driver;

public class MySqlModel extends JdbcModel {

public static void main(String[] args) throws Exception {
MySqlModel model = new MySqlModel();
model.setServer("localhost");
model.setDatabase("openhab");
model.setUsername("root");
model.setPassword("root");
model.setPort(3306);
model.setName("MySQL");
model.setStreamResults(true);
model.load();

EtlModule module = new EtlModule();
module.parse("rule R1 transform s : MySQL!Item1 to t : Sequence { System.out.println('Rule MySql: ' + s.Time + ' ' + s.Value); }");
module.getContext().getModelRepository().addModel(model);
module.execute();
}
...


But in correspondance to the instruction:

module.parse("rule R1 transform s : MySQL!Item1 to t : Sequence { System.out.println('Rule MySql: ' + s.Time + ' ' + s.Value); }");

I get this error:
The type org.eclipse.epsilon.erl.ErlModule cannot be resolved. It is indirectly referenced from required .class files

I'm missing something in the build path ?

Gianluigi
Re: jdbc emc problem [message #1767748 is a reply to message #1767747] Mon, 10 July 2017 14:55 Go to previous messageGo to next message
Dimitris Kolovos is currently offline Dimitris KolovosFriend
Messages: 1589
Registered: July 2009
Location: York, UK
Senior Member

Hi Gianluigi,

You need to temporarily add org.eclipse.epsilon.etl.engine and org.eclipse.epsilon.erl.engine to the dependencies of the plugin.

Cheers,
Dimitris
Re: jdbc emc problem [message #1767749 is a reply to message #1767748] Mon, 10 July 2017 15:23 Go to previous messageGo to next message
Gianluigi Proserpio is currently offline Gianluigi ProserpioFriend
Messages: 19
Registered: November 2015
Junior Member
I started eclipse from the Wndows shell with >eclipse --vmargs -Xmx1024m , but I still get the same error.
Do I need to specify some other parameter on the eclipse start command ?

Gianluigi
Re: jdbc emc problem [message #1767759 is a reply to message #1767749] Mon, 10 July 2017 18:30 Go to previous messageGo to next message
Gianluigi Proserpio is currently offline Gianluigi ProserpioFriend
Messages: 19
Registered: November 2015
Junior Member
Hi Dimitris,
I've also install the Mysql server on local machine, like your configuration is, but the problem is still there.

The error is generated inside the transformModels of the FastTransformationStrategy class.

The complete eclipse.ini is
-startup
plugins/org.eclipse.equinox.launcher_1.3.100.v20150511-1540.jar
--launcher.library
plugins/org.eclipse.equinox.launcher.win32.win32.x86_64_1.1.300.v20150602-1417
-product
org.eclipse.epp.package.modeling.product
--launcher.defaultAction
openFile
--launcher.XXMaxPermSize
256M
-showsplash
org.eclipse.platform
--launcher.XXMaxPermSize
256m
--launcher.defaultAction
openFile
--launcher.appendVmargs
-vmargs
-Xms1024M
-Xmx2048M
-Dosgi.requiredJavaVersion=1.7

Thanks,
Gianluigi
Re: jdbc emc problem [message #1767762 is a reply to message #1767759] Mon, 10 July 2017 19:20 Go to previous messageGo to next message
Dimitris Kolovos is currently offline Dimitris KolovosFriend
Messages: 1589
Registered: July 2009
Location: York, UK
Senior Member

Hi Gianluigi,

The -Xmx1024m argument needs to go in the "VM arguments" text box of the "Arguments" tab of the Run Configuration of your Java application (within Eclipse) as opposed to the startup properties of Eclipse.

Cheers,
Dimitris
Re: jdbc emc problem [message #1767880 is a reply to message #1767762] Tue, 11 July 2017 20:27 Go to previous messageGo to next message
Gianluigi Proserpio is currently offline Gianluigi ProserpioFriend
Messages: 19
Registered: November 2015
Junior Member
Hi Dimitris,
thanks for your answer (and patience ...).

I added the -Xmx1024m parameter to the specific application run configuration, but anything changes.
I'm uploading the projects in order to better check if there is something wrong or different from yours.
May be you need to change MySQL!item16 in capital I in MySQL!iItem16 in module.parse(...) , depends on the import type of the sql file.

Gianluigi
  • Attachment: mysql.zip
    (Size: 1.57MB, Downloaded 37 times)
Re: jdbc emc problem [message #1767885 is a reply to message #1767880] Tue, 11 July 2017 22:36 Go to previous message
Dimitris Kolovos is currently offline Dimitris KolovosFriend
Messages: 1589
Registered: July 2009
Location: York, UK
Senior Member

Hi Gianluigi,

With 'MySQL!item16' I'm getting a type not found exception - which is the expected behaviour. When I change to 'MySQL!Item16' this works fine on my machine. I'm running out of ideas so how about arranging a screen-sharing session e.g. over Skype to investigate this on your setup? If this would be of interest, please send me an email [1] and we'll sort out something.

Cheers,
Dimitris

[1] https://www-users.cs.york.ac.uk/~dkolovos/contact/
Previous Topic:ETL execution engine
Next Topic:[Epsilon - EOL]
Goto Forum:
  


Current Time: Mon Sep 24 05:16:53 GMT 2018

Powered by FUDForum. Page generated in 0.03395 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software

Back to the top