Eclipse Community Forums: Eclipse Scout » Connection to database does not work

Help

Home

Home » Eclipse Projects » Eclipse Scout » Connection to database does not work(SQL Server connection)

Show: Today's Messages :: Show Polls :: Message Navigator

Connection to database does not work [message #739513]

Mon, 17 October 2011 15:00

Bertin Kiekebosch

Messages: 330
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

Report message to a moderator

Re: Connection to database does not work [message #739522 is a reply to message #739513]

Mon, 17 October 2011 15:11

Ivan Motsch

Messages: 154
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
  }

Report message to a moderator

Re: Connection to database does not work [message #740375 is a reply to message #739522]

Tue, 18 October 2011 12:45

Bertin Kiekebosch

Messages: 330
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;
  }

Report message to a moderator