Skip to main content


Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » Eclipse Scout » Problem retrieving data from multiple databases
Problem retrieving data from multiple databases [message #1355132] Wed, 14 May 2014 15:38 Go to next message
Bertin Kiekebosch is currently offline Bertin KiekeboschFriend
Messages: 330
Registered: August 2011
Senior Member
Hi I have a service in which I have to retrieve data from 3 different databases. Therefore I have created 3 AbstractSqlServices. The configuration of the databases is done through the ini file. The data is in one physical database but spread over three identical schemas. A little simplified my code looks like this:

public Object[][] getCustomers{

    AbstractSqlService mpx1SqlService = SERVICES.getService(Mpx1SqlService.class);
    AbstractSqlService mpx2SqlService = SERVICES.getService(Mpx2SqlService.class);
    AbstractSqlService mpx3SqlService = SERVICES.getService(Mpx3SqlService.class);

    String statement = "Select lastname, firstname, postalcode, city from customer";

    Object[][] resultdb1 = mpx1SqlService.select(statement);
    Object[][] resultdb2 = mpx2SqlService.select(statement);
    Object[][] resultdb3 = mpx3SqlService.select(statement);

    Object[][] totalResult = new Object[resultdb1.length + resultdb2.length + resultdb3.length][];
    System.arraycopy(resultdb1, 0, totalResult, 0, resultdb1.length);
    System.arraycopy(resultdb2, 0, totalResult, resultdb1.length, resultdb2.length);
    System.arraycopy(resultdb3, 0, totalResult, resultdb1.length + resultdb2.length, resultdb3.length);

    return totalresult[];
}


What I expected are the customer form three databases but I got the customers from database 1 en twice the customers from database 2.

I verified that the configuration is correct. I did some test to first copy the data in a temporary list but that also does not work. In the SQL Server activity monitor I can see that all three databases are queried so this assures that the configuration is OK.

If I skipp the database 2 then I get the results from database 3.

I suspect somewhere in Scout the results are messed up. Maybe I have to do something extra because I am working with multiple connections.

Regards Bertin
Re: Problem retrieving data from multiple databases [message #1356850 is a reply to message #1355132] Thu, 15 May 2014 08:47 Go to previous messageGo to next message
Bertin Kiekebosch is currently offline Bertin KiekeboschFriend
Messages: 330
Registered: August 2011
Senior Member
Hi,

did some extra tests. If I rearange the code in the following way that I first get the service for database1, execute the statement on database 1, copy the resultset of database1, and then close the connection on the service for database 1 and do this subsequentely for all databases, the I get the correct results for database 1, the correct results for database 2 but then I get a connection closed error on database 3.

So I think something is wrong with the connection, it only creates unique connections for database 1 and 2, and it looks as if it uses the same connection for the subsequent databases.

Regards Bertin

Re: Problem retrieving data from multiple databases [message #1383189 is a reply to message #1356850] Mon, 26 May 2014 08:45 Go to previous messageGo to next message
Bertin Kiekebosch is currently offline Bertin KiekeboschFriend
Messages: 330
Registered: August 2011
Senior Member
Hi I did found the problem.

In class PreparedStatementCache is a method getPreparedStatement(Connection conn, String s) from this method a PreparedStatement is returned but this prepared statement does NOT have the correct connection.

So, if the same sql statement is executed against different connections it goes wrong there.

Explanation:

I call this method with for example "database_3" and "select id, name from city". Because this statement was already executed against database_1 and database_2, in the line: PreparedStatement ps = m_statementCache.get(s); it finds a prepared statement in the cache but this one has a connection to a different database.

Will make a bug call for it.

Regards Bertin
Re: Problem retrieving data from multiple databases [message #1383217 is a reply to message #1383189] Mon, 26 May 2014 09:01 Go to previous messageGo to next message
Bertin Kiekebosch is currently offline Bertin KiekeboschFriend
Messages: 330
Registered: August 2011
Senior Member
Created a bug for it see

https://bugs.eclipse.org/bugs/show_bug.cgi?id=435751
Re: Problem retrieving data from multiple databases [message #1383920 is a reply to message #1383217] Tue, 27 May 2014 08:23 Go to previous message
Jeremie Bresson is currently offline Jeremie BressonFriend
Messages: 1252
Registered: October 2011
Senior Member
Nice catch !
Here a link to the class: PreparedStatementCache

Have you a solution? (Maybe with your own PreparedStatementCache implementation).

Do you know if the parameter "Connection conn" is always the same for one database? Can it be used in the key of the cache? Something like "Connection + statement"?
Previous Topic:Transfer the Server Stacktrace to the client
Next Topic:Nebula NatTable used in a Scout application
Goto Forum:
  


Current Time: Fri Apr 19 20:00:17 GMT 2024

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

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

Back to the top