Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » scout » Multiple databases
Multiple databases [message #902247] Thu, 16 August 2012 10:06 Go to next message
Bertin Kiekebosch is currently offline Bertin Kiekebosch
Messages: 300
Registered: August 2011
Senior Member
Hi,

until now I always created an application that worked against one database. I now have a requirement that needs to pull in data from another database. I did a little test and created a second SQL service.

It seems that this works OK, at least for the queries I did. Although before I proceed with this, has anybody experience with this, and can anybody confirm that working with multiple databases works well, included transaction handling, roll back etc.

Regards Bertin
Re: Multiple databases [message #902298 is a reply to message #902247] Thu, 16 August 2012 16:55 Go to previous messageGo to next message
Ken Lee is currently offline Ken Lee
Messages: 97
Registered: March 2012
Member
Hi Bertin,

Yes, this will work perfectly as you assumed. Please have a look at the ISqlService interface and the AbstractSqlService and AbstractSqlStyle classes. There's also a tutorial explaining how to write a SQL service [1].
Furthermore, we have provided a set of JDBC drivers for Oracle 10/11, MySQL and Postgres DBs at the Eclipse Marketplace [2]. Refer to the tutorial [3] for an installation description.

[1] http://wiki.eclipse.org/Scout/HowTo/3.8/Write_a_jdbc_connection_bundle
[2] http://marketplace.eclipse.org/content/jdbc-drivers-eclipse-scout
[3] http://wiki.eclipse.org/Scout/Tutorial/3.8/Add_JDBC_Drivers_for_Eclipse_Scout

[Updated on: Thu, 16 August 2012 17:00]

Report message to a moderator

Re: Multiple databases [message #902322 is a reply to message #902298] Fri, 17 August 2012 01:00 Go to previous messageGo to next message
Jeremie Bresson is currently offline Jeremie Bresson
Messages: 117
Registered: November 2010
Senior Member
Hi Bertin,

If I understood your question correctly, you want to use 2 Sql Services at the same time. This is not a problem at all.

With the SDK you create the services (server > Common Services > Sql Services):
* FirstSqlService
* SecondSqlService

You configure them with the getConfigured*(..) methods or with the config.ini properties.

When you use it, instead of using the SQL convenience class, you can use SERVICES and a direct reference to the service class:
    Object[][] select1 = SERVICES.getService(FirstSqlService.class).select("select * from questions");
    System.out.println(select1.length);

    Object[][] select2 = SERVICES.getService(SecondSqlService.class).select("select * from actors");
    System.out.println(select2.length);


Sidenote:
The SQL convenience class calls SERVICES.getService(ISqlService.class). If you want to ensure which implementation will be returned in this case (FirstSqlService or SecondSqlService), you can use the @Priority annotation to make one of your service primary and the other secondary.

[Updated on: Fri, 17 August 2012 01:06]

Report message to a moderator

Re: Multiple databases [message #902409 is a reply to message #902322] Fri, 17 August 2012 10:01 Go to previous messageGo to next message
Bertin Kiekebosch is currently offline Bertin Kiekebosch
Messages: 300
Registered: August 2011
Senior Member
Thanks for the answers,

The idea is that I pull data from database1 (SQLSever) and store it in database2 (MySql). The records in database1 must be marked if the import in database 2 succeeds. So two databases within one transaction.

From the replies you gave me I think this I going to work, I will give it a try.

Regards Bertin
Re: Multiple databases [message #902428 is a reply to message #902409] Fri, 17 August 2012 10:40 Go to previous messageGo to next message
Jeremie Bresson is currently offline Jeremie Bresson
Messages: 625
Registered: October 2011
Senior Member
The best way to ensure it, is to give it a try...

From what I have seen, both SQL Services are AbstractSqlService. In this class you can find the code that register the Service as member of the ITransaction.

If you get an Exception, this should call ITransaction#rollback() and the BasicTransaction beyond the the ITransaction will propagate rollback() on all its member (the both SQL Services).

Re: Multiple databases [message #902538 is a reply to message #902428] Sat, 18 August 2012 05:22 Go to previous messageGo to next message
Bertin Kiekebosch is currently offline Bertin Kiekebosch
Messages: 300
Registered: August 2011
Senior Member
Hi,

did some testing with inserts in 2 databases (both mySQL) in one process service and it works OK.

Regards Bertin
Re: Multiple databases [message #950929 is a reply to message #902538] Sat, 20 October 2012 04:30 Go to previous messageGo to next message
Stathis Alexopoulos is currently offline Stathis Alexopoulos
Messages: 42
Registered: September 2010
Member
Hi to all,

I have the same requirement with Bertin, and because i am still getting knowing the Scout, i still using the convenient SQL class.

I think that the best solution would be to subclassing the SQL to use another ISqlService. Ufortunatelly the SQL class is final and uses everywhere the
    ISqlService service = SERVICES.getService(usedServiceType);

I would recommend to rewrite it, but who am i to recommend. Anyway, i wrote an SQL2 class which is copy/paste of SQL, with four differences.

  • It is not final.
  • It has a protected constructor.
  • It defines a getSqlService(), which can be extended.
  • Every underlined method uses the getSqlService().

With that implementation someone could extend the SQL2 as following
class YourSql extends Sql2 {
  private YourSql() {
  }
  public ISqlService getSqlService() {
    return SERVICES.getService(YourSqlService.class);
  }
}

and now can use the following syntax.
YourSql.select("....");


For anyone interesting i am uploading the code of SQL2. I would appreciate it if i had comments about other ways to implement it.
  • Attachment: SQL2.java
    (Size: 5.40KB, Downloaded 80 times)

[Updated on: Sat, 20 October 2012 04:33]

Report message to a moderator

Re: Multiple databases [message #954059 is a reply to message #950929] Mon, 22 October 2012 15:07 Go to previous messageGo to next message
Jeremie Bresson is currently offline Jeremie Bresson
Messages: 117
Registered: November 2010
Senior Member
To my mind, your code will not work.

SQL2.getSqlService() is a static method (public static), so you can not override this method in the child class (YourSql, the class that is extending SQL2).



SQL is a convenience class... It is suitable for one SQL Service (the default SQL Service. The one that will be returned by "SERVICES.getService(ISqlService.class)").
Do not expect to do more with this class. It is final, because it is not expected that someone create a subclass. If you have 2 services, you need to handle them separately.

ISqlService mainDb = SERVICES.getService(FirstSqlService.class);
mainDb.selectInto("select question_text from questions where question_id = :questionId into :questionText", formData);

ISqlService archiveDb = SERVICES.getService(SecondSqlService.class);
archiveDb.insert("insert facts(question_id, question_text, f1, f2, f3) values (:questionId, :questionText, :fact1, :fact2, :fact3", formData);


If you want convenience classes for your 2 SQL service, I recommend to write a class that will return the correct service:

public final class MainDb {
	public static ISqlService get() {
		return SERVICES.getService(FirstSqlService.class);
	}
}


and:
public final class ArchiveDb {
	public static ISqlService get() {
		return SERVICES.getService(SecondSqlService.class);
	}
}


In your code, it looks like you are applying the singleton pattern [with get() instead of getInstance()]

And you can use it:

MainDb.get().selectInto("select question_text from questions where question_id = :questionId into :questionText", formData);

ExternalDb.get().insert("insert facts(question_id, question_text, f1, f2, f3) values (:questionId, :questionText, :fact1, :fact2, :fact3", formData);



You also can also combine the 2 service accessors in one class:

public final class SQL2 {
	public static ISqlService main() {
		return SERVICES.getService(FirstSqlService.class);
	}
	
	public static ISqlService archive() {
		return SERVICES.getService(SecondSqlService.class);
	}
}


Your code will look like this:
SQL2.main().selectInto("select question_text from questions where question_id = :questionId into :questionText", formData);

SQL2.archive().insert("insert facts(question_id, question_text, f1, f2, f3) values (:questionId, :questionText, :fact1, :fact2, :fact3", formData);


I hope it helps.
Re: Multiple databases [message #1234389 is a reply to message #954059] Tue, 21 January 2014 16:50 Go to previous messageGo to next message
Chris Monty is currently offline Chris Monty
Messages: 24
Registered: July 2013
Junior Member
Hi Jeremie, thanks for the suggestions. I've tried them successfully. I have a further question.

Is there any way to change default SQL service on a global scale, so that SQL.select(...) used whichever SQLService I specified? You see, I'm toying with switching databases drivers on the fly. While search-replacing all instances of "SQL." with "SERVICES.getService(MySqlService.class)." would work for Outline Services, there is no way (as far as I can tell) to direct Lookup Calls away from the default DB service.

As you mentioned, I could possibly achieve this by switching the @Priority of the SQL services, but it requires code changes.
Re: Multiple databases [message #1234554 is a reply to message #1234389] Wed, 22 January 2014 03:53 Go to previous messageGo to next message
Jeremie Bresson is currently offline Jeremie Bresson
Messages: 625
Registered: October 2011
Senior Member
For you lookup call, I assume you use a AbstractSqlLookupService.

As you see in the source code, this class uses
SQL.selectLimited(preprocessedSql, call.getMaxRowCount(), call);

in execLoadLookupRows.

You can override this method and control the SqlService you want.

---

SQL is a convenience class that delegates all the call to the default SQL service (implementation of org.eclipse.scout.rt.server.services.common.jdbc.ISqlService)

SQL.select(..) is the same as SERVICES.getService(<default SQL service>).select(..)


The default SQL service is defined by the priority ranking of the service.

There is different way to set the priority:
* In plugin.xml (read org.eclipse.scout.service.services service vs. proxy to know the default value)
* @Priority annotation on top of your implementation of the service (MySqlService.java)

---

I hope it helps.

[Updated on: Wed, 22 January 2014 03:54]

Report message to a moderator

Re: Multiple databases [message #1234577 is a reply to message #1234554] Wed, 22 January 2014 04:49 Go to previous messageGo to next message
Bertin Kiekebosch is currently offline Bertin Kiekebosch
Messages: 300
Registered: August 2011
Senior Member
Hi,

I also switch databases dynamically bases on some user settings.

for a Lookup call I did overwrite execLoadLookupRows like below

  @Override
  protected LookupRow[] execLoadLookupRows(String originalSql, String preprocessedSql, LookupCall call) throws ProcessingException {

    ISqlService mpxSqlService = SERVICES.getService(MpxSqlServiceProvider.getSqlServiceClass());
    Object[][] data = mpxSqlService.selectLimited(preprocessedSql, call.getMaxRowCount(), call);
    if (getConfiguredSortColumn() >= 0) {
      sortData(data, getConfiguredSortColumn());
    }
    return createLookupRowArray(data, call);
  }


The call MpxSqlServiceProvider.getSqlServiceClass() returns a class derived from AbstractSqlService.class, see example below;


  public static Class<? extends AbstractSqlService> getSqlServiceClass() throws ProcessingException {

    int databaseNummer = ServerSession.get().getMpxDatabaseNummer();
    //System.out.println("Database nummer in MpxSqlServiceProvider = " + databaseNummer);

    switch (databaseNummer) {
      case 1:
        return Mpx1SqlService.class;
      case 2:
        return Mpx2SqlService.class;
      case 3:
        return Mpx3SqlService.class;
      case 4:
        return Mpx4SqlService.class;
    } 
}


regards Bertin
Re: Multiple databases [message #1236681 is a reply to message #902247] Mon, 27 January 2014 16:05 Go to previous message
Chris Monty is currently offline Chris Monty
Messages: 24
Registered: July 2013
Junior Member
Hey, just to say your suggestions worked. I didn't spot the execLoadLookupRows method until you pointed it out. Thanks for the help.
Previous Topic:Aligning Fields in a Scout Form
Next Topic:User preferences implementation
Goto Forum:
  


Current Time: Thu Jul 24 09:19:47 EDT 2014

Powered by FUDForum. Page generated in 0.08959 seconds