Eclipse Community Forums: Epsilon

Home » Modeling » Epsilon » jdbc emc problem(etl transformation problem related to a model read from a MySql db by the jdbc emc)

Show: Today's Messages :: Show Polls :: Message Navigator

jdbc emc problem [message #1767359]

Wed, 05 July 2017 07:48

Gianluigi Proserpio

Messages: 30
Registered: November 2015

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 190 times)

Report message to a moderator

Re: jdbc emc problem [message #1767645 is a reply to message #1767359]

Sat, 08 July 2017 09:26

Dimitris Kolovos

Messages: 2162
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

Report message to a moderator

Re: jdbc emc problem [message #1767682 is a reply to message #1767645]

Sun, 09 July 2017 14:04

Gianluigi Proserpio

Messages: 30
Registered: November 2015

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

Report message to a moderator

Re: jdbc emc problem [message #1767719 is a reply to message #1767682]

Mon, 10 July 2017 10:05

Dimitris Kolovos

Messages: 2162
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

Report message to a moderator

Re: jdbc emc problem [message #1767727 is a reply to message #1767719]

Mon, 10 July 2017 11:49

Gianluigi Proserpio

Messages: 30
Registered: November 2015

Member

Hi Dimitris,
you could find a sql backup at https://drive.google.com/file/d/0BxEw5jaUahy6ZUdnYWh4Q3ZncmM/view?usp=sharing

Gianluigi

Report message to a moderator

Re: jdbc emc problem [message #1767731 is a reply to message #1767727]

Mon, 10 July 2017 12:07

Dimitris Kolovos

Messages: 2162
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

Report message to a moderator

Re: jdbc emc problem [message #1767747 is a reply to message #1767731]

Mon, 10 July 2017 14:50

Gianluigi Proserpio

Messages: 30
Registered: November 2015

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

Report message to a moderator

Re: jdbc emc problem [message #1767748 is a reply to message #1767747]

Mon, 10 July 2017 14:55

Dimitris Kolovos

Messages: 2162
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

Report message to a moderator

Re: jdbc emc problem [message #1767749 is a reply to message #1767748]

Mon, 10 July 2017 15:23

Gianluigi Proserpio

Messages: 30
Registered: November 2015

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

Report message to a moderator

Re: jdbc emc problem [message #1767759 is a reply to message #1767749]

Mon, 10 July 2017 18:30

Gianluigi Proserpio

Messages: 30
Registered: November 2015

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

Report message to a moderator

Re: jdbc emc problem [message #1767762 is a reply to message #1767759]

Mon, 10 July 2017 19:20

Dimitris Kolovos

Messages: 2162
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

Report message to a moderator

Re: jdbc emc problem [message #1767880 is a reply to message #1767762]

Tue, 11 July 2017 20:27

Gianluigi Proserpio

Messages: 30
Registered: November 2015

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 146 times)

Report message to a moderator

Re: jdbc emc problem [message #1767885 is a reply to message #1767880]

Tue, 11 July 2017 22:36

Dimitris Kolovos

Messages: 2162
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/

Report message to a moderator

Previous Topic:	ETL and ATL
Next Topic:	[Epsilon - EOL]

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 18 16:55:41 GMT 2024

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter