Eclipse Community Forums: Eclipse Scout

Home » Eclipse Projects » Eclipse Scout » Multiple databases

Thu, 16 August 2012 10:06

Eclipse User

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

Eclipse User

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] by Moderator

Re: Multiple databases [message #902322 is a reply to message #902298]

Fri, 17 August 2012 01:00

Eclipse User

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] by Moderator

Re: Multiple databases [message #902409 is a reply to message #902322]

Fri, 17 August 2012 10:01

Eclipse User

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

Eclipse User

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

Eclipse User

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

Eclipse User

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

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

Re: Multiple databases [message #954059 is a reply to message #950929]

Mon, 22 October 2012 15:07

Eclipse User

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

Eclipse User

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

Eclipse User

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] by Moderator

Re: Multiple databases [message #1234577 is a reply to message #1234554]

Wed, 22 January 2014 04:49

Eclipse User

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

Eclipse User

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:

-=] Back to Top [=-

Current Time: Wed Jul 23 14:13:23 EDT 2025

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter