Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » scout » Connection to database does not work(SQL Server connection)
Connection to database does not work [message #739513] Mon, 17 October 2011 15:00 Go to next message
Bertin Kiekebosch is currently offline Bertin KiekeboschFriend
Messages: 309
Registered: August 2011
Senior Member
Hi,

Still having problems to connect to SQL Server database. When I execute the statement below (see Server code) the server never gives any result back. Instead it seems to come in some endless loop (see console log below).

I can perfectly connect to the database from a "normal" RCP application, so I think the problem is somewhere in the Scout part.

How can I fix this. Do I have to provide some additional configuration to the SQL service ?

Regards Bertin.



Server code:
  @Override
  public Object[][] getClientTableData() throws ProcessingException {
//    return new Object[][]{{1, 1, "BJ", "Kiekebosch"}, {2, 2, "T", "Gaertner"}};

    return SQL.select("" +
        "SELECT id, piNumber, initials, lastname " +
        " FROM Client");
  }
}


Console log:
!ENTRY org.eclipse.scout.rt.server 2 0 2011-10-17 16:43:23.294
!MESSAGE org.eclipse.scout.rt.server.services.common.jdbc.internal.pool.SqlConnectionPool.leaseConnection(SqlConnectionPool.java:127) closing dirty connection: ConnectionID:545

!ENTRY org.eclipse.scout.rt.server 2 0 2011-10-17 16:43:23.312
!MESSAGE org.eclipse.scout.rt.server.services.common.jdbc.internal.pool.SqlConnectionPool.leaseConnection(SqlConnectionPool.java:127) closing dirty connection: ConnectionID:546

!ENTRY org.eclipse.scout.rt.server 2 0 2011-10-17 16:43:23.325
!MESSAGE org.eclipse.scout.rt.server.services.common.jdbc.internal.pool.SqlConnectionPool.leaseConnection(SqlConnectionPool.java:127) closing dirty connection: ConnectionID:547

!ENTRY org.eclipse.scout.rt.server 2 0 2011-10-17 16:43:23.341
!MESSAGE org.eclipse.scout.rt.server.services.common.jdbc.internal.pool.SqlConnectionPool.leaseConnection(SqlConnectionPool.java:127) closing dirty connection: ConnectionID:548
Re: Connection to database does not work [message #739522 is a reply to message #739513] Mon, 17 October 2011 15:11 Go to previous messageGo to next message
Ivan Motsch is currently offline Ivan MotschFriend
Messages: 100
Registered: March 2010
Senior Member
At the code location that writes that log entry it is running the test statement to check if the connection is alive.

service.callbackTestConnection(candidate.conn);

This calls AbstractSqlService.callbackTestConnection that delegates to:

protected void execTestConnection(Connection conn) throws Throwable {
ISqlStyle s = getSqlStyle();
if (s != null) {
s.testConnection(conn);
}
}

The sql style used is the one returned by ISqlService.getSqlStyle.

Please take special note on the "testConnection" method in the MSSQLStyle.
To start with just leave it empty. That should fix your problem.ยจ
Then find a suitable test statement for MS SQL databases.
In Oracle it is for example "SELECT 1 FROM DUAL". But this does not work in MS SQL.
Please write in this forum, what statement you are currently using.

Does that help?

I assume andy already sent you some code. however here all together:

The MS SQL service code:

public abstract class AbstractMsSqlSqlService extends AbstractSqlService {

  @ConfigProperty(ConfigProperty.SQL_STYLE)
  @Order(80)
  @ConfigPropertyValue("org.eclipse.scout.rt.server.services.common.jdbc.style.MSSQLSqlStyle.class")
  @Override
  protected Class<? extends ISqlStyle> getConfiguredSqlStyle() {
    return org.eclipse.scout.rt.server.services.common.jdbc.style.MSSQLSqlStyle.class;
  }

  @ConfigProperty(ConfigProperty.STRING)
  @Order(100)
  @ConfigPropertyValue("\"com.microsoft.sqlserver.jdbc.SQLServerDriver\"")
  @Override
  protected String getConfiguredJdbcDriverName() {
    return "com.microsoft.sqlserver.jdbc.SQLServerDriver";
  }

  @ConfigProperty(ConfigProperty.STRING)
  @Order(110)
  @ConfigPropertyValue("\"jdbc:sqlserver://[host][:port];DatabaseName=[database]\"")
  @Override
  protected String getConfiguredJdbcMappingName() {
    return "jdbc:sqlserver://[host][:port];DatabaseName=[database]";
  }
}


and the style it references to:

public class MSSQLSqlStyle extends AbstractSqlStyle {
  private static final long serialVersionUID = 1L;

  @Override
  public String getConcatOp() {
    return "+";
  }

  @Override
  public String getLikeWildcard() {
    return "%";
  }

  @Override
  protected int getMaxListSize() {
    return 1000;
  }

  @Override
  public boolean isLargeString(String s) {
    return (s.length() > 4000);
  }

  @Override
  public boolean isBlobEnabled() {
    return true;
  }

  @Override
  public boolean isClobEnabled() {
    return true;
  }

  @Override
  public String createDateTimeIsNow(String attribute) {
    return "TRUNC(" + attribute + ", 'MI')=TRUNC(SYSDATE, 'MI')";
  }

  @Override
  public String createDateTimeIsNotNow(String attribute) {
    return "TRUNC(" + attribute + ", 'MI')!=TRUNC(SYSDATE, 'MI')";
  }

  @Override
  public void testConnection(Connection conn) throws SQLException {
    XXX your code
  }
Re: Connection to database does not work [message #740375 is a reply to message #739522] Tue, 18 October 2011 12:45 Go to previous messageGo to next message
Bertin Kiekebosch is currently offline Bertin KiekeboschFriend
Messages: 309
Registered: August 2011
Senior Member
Thanks,

now it works fine.

This is what I used to test the connection to SQL Server 2008.

public class RidMSSQLStyle extends MSSQLSqlStyle {
  @Override
  public void testConnection(Connection conn) throws SQLException {
    Statement testStatement = null;
    try {
      testStatement = conn.createStatement();
      testStatement.execute("SELECT count(*) FROM dbo.sysobjects");
     }
    finally {
      if (testStatement != null) try {
        testStatement.close();
      }
      catch (Throwable t) {
      }
    }
  }
}


and for the completeness, my specialization of SQLServive. Notice the method getConfiguredSqlStyle()

public class MsSqlService extends AbstractSqlService implements IService {

  @Override
  protected String getConfiguredJdbcDriverName() {
    return "com.microsoft.sqlserver.jdbc.SQLServerDriver";
  }

  @Override
  protected String getConfiguredJdbcMappingName() {
    return "jdbc:sqlserver://localhost";
  }

  @Override
  protected String getConfiguredJdbcProperties() {
    return "database=MPYDB_RID";
  }

  @Override
  protected String getConfiguredPassword() {
    return "XXX";
  }

  @Override
  protected String getConfiguredUsername() {
    return "XXX";
  }

  @Override
  protected Class<? extends ISqlStyle> getConfiguredSqlStyle() {
    //return org.eclipse.scout.rt.server.services.common.jdbc.style.MSSQLSqlStyle.class;
    return MPXRid.server.services.common.sql.RidMSSQLStyle.class;
  }


Re: Connection to database does not work [message #741055 is a reply to message #740375] Wed, 19 October 2011 06:21 Go to previous message
Daniel Wiehl is currently offline Daniel WiehlFriend
Messages: 25
Registered: April 2010
Junior Member
Hi Bertin
For better performance, I would suggest to change the test-statement as follows:
SELECT count(1) FROM dbo.sysobjects


Best Regards
Daniel
Previous Topic:Scout RT - Questions...
Next Topic:Server Plugins
Goto Forum:
  


Current Time: Mon Nov 24 04:19:58 GMT 2014

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

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