JDBC error during connection to MSSQL

[Jakolcz]

Hi,

I'm trying to connect to MSSQL database but i get several errors during this. I have MSSQL driver installed in osgi and JDBC connection pool set with these values

JDBC Driver class = com.microsoft.sqlserver.jdbc.SQLServerDriver JDBC connection URI = jdbc:sqlserver://myserver;databaseName=mydb Datasource name = mssqlTest

and my code looks like this

try{ DataSourcePool dspService = sling.getService(DataSourcePool.class); PreparedStatement preparedStatement = null; Connection connection = null; DataSource ds = (DataSource) dspService.getDataSource("mssqlTest"); if(ds != null){ log.info("getting connection"); connection = ds.getConnection();    // here the script fails, this is line 32 log.info("got connection!"); preparedStatement = connection.prepareStatement(SQL); /* execute the SQL statement */ log.info("gonna execute query"); ResultSet rs = preparedStatement.executeQuery(); log.info("query executed!"); if(rs.first()){ log.info("Some data here!"); } }else{ log.error("DataSource = null!!!!"); } }catch(SQLException e){ log.error("SQLException :"+e.getMessage()); }catch(Exception e){ log.error("Undefined exception: "+e.getMessage()); }

The exceptions I get are

25.09.2013 10:12:15.392 *ERROR* [10.224.133.98 [1380096735273] GET /content/TEST/302104/mssql.html HTTP/1.1] com.day.cq.wcm.core.impl.WCMDebugFilter Exception: An exception occurred processing JSP page /apps/TEST/components/mssql_test/mssql_test.jsp at line 32 org.apache.sling.api.scripting.ScriptEvaluationException: An exception occurred processing JSP page /apps/TEST/components/mssql_test/mssql_test.jsp at line 32 Caused by: org.apache.sling.api.SlingException: An exception occurred processing JSP page /apps/TEST/components/mssql_test/mssql_test.jsp at line 32 Caused by: java.lang.NoClassDefFoundError: javax/net/ssl/SSLSocket Caused by: java.lang.ClassNotFoundException: javax.net.ssl.SSLSocket not found by com.microsoft.sqlserver.jdbc [341]

The entire exception is here
http://pastebin.com/tbheRKjt

Thanks for any help

[smacdonald2008]

Try injecting a DataSourcePool into an OSGi bundle. See this community articles for details:

http://scottsdigitalcommunity.blogspot.ca/2013/09/injecting-datasourcepool-service-into.html

However - from your exception - it looks like the exception is based on a driver file error. You are connecting to MSSQL - not MYSQL. Do you have access to a MySQL database - can you try that to rule out  a driver file issue. 

[Jakolcz]

smacdonald2008 wrote...

Try injecting a DataSourcePool into an OSGi bundle. See this community articles for details:

http://scottsdigitalcommunity.blogspot.ca/2013/09/injecting-datasourcepool-service-into.html

However - from your exception - it looks like the exception is based on a driver file error. You are connecting to MSSQL - not MYSQL. Do you have access to a MySQL database - can you try that to rule out  a driver file issue. 

Thank you for answer.

I'm doing it as it is in the link you've posted(with the values I've posted).

And yes - I'm connecting to MSSQL database not MySQL. MySQL connection works fine. The problem is just with the MSSQL.

[smacdonald2008]

That is interesting -- let me investigate and see if someone else within Adobe Eng has come across this issue with this specific driver file. I will also try and get this driver file and see if i can duplicate your findings. 

[Jakolcz]

        Thanks for help. I can post you my MSSQL driver tomorrow, if you want to.

[smacdonald2008 · Accepted solution]

Here is another approach that you an take. IN this article -- it talks about how to connect to a relational database using your own ConnecitonHelper Java class (you do not need to create a DataSourcePool):

http://scottsdigitalcommunity.blogspot.ca/2013/01/persisting-adobe-cq-data-in-relational.html

For your workflow, use a ConnectionHelper class to connect to Microsoft SQL server. For example:
 

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

  
public class ConnectionHelper
{
  private String url;
  private static ConnectionHelper instance;
  private ConnectionHelper()
  {
    try {
      Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver").newInstance();
    
    }
    catch (Exception e) {
      e.printStackTrace();
    }
  }
  public static Connection getConnection() throws SQLException {
    if (instance == null) {
      instance = new ConnectionHelper();
    }
    try {
      return DriverManager.getConnection("jdbc:sqlserver://127.0.0.1:1433;databaseName=[master].[dbo].[Customer]","user=sa","password=admin");
    }
    catch (SQLException e) {
      throw e;
    }
  }
  public static void close(Connection connection)
  {
    try {
      if (connection != null) {
      connection.close();
   }
  }
    catch (SQLException e) {
      e.printStackTrace();
    }
  }

Make sure that you still place the driver file into an OSGi bundle. This gives you finer control of how to connect from AEM. 

Here is a good thread to talk about issues you may encounter from Java to SQL server:

http://stackoverflow.com/questions/18841744/jdbc-connection-failed-error-tcp-ip-connection-to-host-f...

Once you get this working from your ConnectionHelper - you will be able to work with AEM to SQL server. 

[Jakolcz]

smacdonald2008 wrote...

Here is another approach that you an take. IN this article -- it talks about how to connect to a relational database using your own ConnecitonHelper Java class (you do not need to create a DataSourcePool):

http://scottsdigitalcommunity.blogspot.ca/2013/01/persisting-adobe-cq-data-in-relational.html

For your workflow, use a ConnectionHelper class to connect to Microsoft SQL server. For example:
 

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

  
public class ConnectionHelper
{
  private String url;
  private static ConnectionHelper instance;
  private ConnectionHelper()
  {
    try {
      Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver").newInstance();
    
    }
    catch (Exception e) {
      e.printStackTrace();
    }
  }
  public static Connection getConnection() throws SQLException {
    if (instance == null) {
      instance = new ConnectionHelper();
    }
    try {
      return DriverManager.getConnection("jdbc:sqlserver://127.0.0.1:1433;databaseName=[master].[dbo].[Customer]","user=sa","password=admin");
    }
    catch (SQLException e) {
      throw e;
    }
  }
  public static void close(Connection connection)
  {
    try {
      if (connection != null) {
      connection.close();
   }
  }
    catch (SQLException e) {
      e.printStackTrace();
    }
  }

Make sure that you still place the driver file into an OSGi bundle. This gives you finer control of how to connect from AEM. 

Here is a good thread to talk about issues you may encounter from Java to SQL server:

http://stackoverflow.com/questions/18841744/jdbc-connection-failed-error-tcp-ip-connection-to-host-f...

Once you get this working from your ConnectionHelper - you will be able to work with AEM to SQL server. 

Thanks. Should I put this code into my component(.jsp file) or create and OSGi bundle with this code?

[smacdonald2008]

My advice would be code in this a standard Eclipse project. Get the code working and make sure all the configuration settings are working. Then place the Java in an OSGi bundle following the instructions in the article. Good luck!