JDBC error during connection to MSSQL | Community
Skip to main content
J
Jakolcz
Level 3
October 16, 2015
Solved

JDBC error during connection to MSSQL

  • October 16, 2015
  • 7 replies
  • 4495 views

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

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by smacdonald2008

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-failed

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

7 replies

smacdonald2008
smacdonald2008
Level 10
October 16, 2015

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. 

J
JakolczAuthor
Level 3
October 16, 2015

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
smacdonald2008
Level 10
October 16, 2015

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. 

J
JakolczAuthor
Level 3
October 16, 2015
        Thanks for help. I can post you my MSSQL driver tomorrow, if you want to.
smacdonald2008
smacdonald2008Accepted solution
Level 10
October 16, 2015

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-failed

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

J
JakolczAuthor
Level 3
October 16, 2015

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-failed

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
smacdonald2008
Level 10
October 16, 2015

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! 

Terms & ConditionsAccessibility statement

Loading footer...