Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » 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 Kiekebosch
Messages: 303
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 Kiekebosch
Messages: 303
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 Kiekebosch
Messages: 303
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 Kiekebosch
Messages: 303
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 Bresson
Messages: 697
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:Docx4Support NoClassDefFoundError
Goto Forum:
  


Current Time: Tue Sep 16 19:28:43 GMT 2014

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

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