Connect AEM with MySQL - AEM 6.4

Avatar

Avatar

rajkumark10

Avatar

rajkumark10

rajkumark10

14-05-2019

Hi All,

I am trying to connect with MYSQL. I have given JDBC configuration and used below code. But in below code i am getting datasource pool as null. Can you please help me to find where i am doing mistake?

@Reference

private DataSourcePool ds;

//Returns a connection using the configured DataSourcePool

private Connection getConnection()

{

LOGGER.info("************Inside get connection*********" + ds);

         DataSource dspService = null;

         Connection con = null;

       

         try

         {

        dspService = (DataSource) ds.getDataSource("hsqldbds");

        LOGGER.info("************Inside IF*********");

        if(dspService != null) {

        LOGGER.info("************Inside IF*********");

                 final Connection connection = ((DataSource) dspService).getConnection();

                  final Statement statement = connection.createStatement();

                 final ResultSet resultSet = statement.executeQuery("SELECT * from pet");

                  int r=0;

                  LOGGER.info("************Working*********");

                  while(resultSet.next()){

                     r=r+1;

                  }

                  resultSet.close();

                  }

           

           }

         catch (Exception e)

         {

        LOGGER.info("************ERROR*********" + e.getMessage());

             e.printStackTrace();

         }

             return con;

}

Also, Can you share me driver need to be used for SQL DB in AEM 6.4.

Accepted Solutions (1)

Accepted Solutions (1)

Avatar

Avatar

rajkumark10

Avatar

rajkumark10

rajkumark10

10-06-2019

Hi All,

Above issue got fixed, Driver was the problem. We have installed mssql-jdbc-7.2.2.jre8.jar  driver jar and it is working fine as expected.

Thanks for all your inputs.

Answers (22)

Answers (22)

Avatar

Avatar

Albin_Issac

MVP

Avatar

Albin_Issac

MVP

Albin_Issac
MVP

01-06-2019

Avatar

Avatar

Mayank_Gandhi

Employee

Avatar

Mayank_Gandhi

Employee

Mayank_Gandhi
Employee

11-06-2019

Good to know Raj that it is resolved!

Avatar

Avatar

rajkumark10

Avatar

rajkumark10

rajkumark10

07-06-2019

Hi Mayank Gandhi​,

Sorry, Below is the issue i am getting.

ERROR : com.microsoft.sqlserver.jdbc.SQLServerDriver not found

Avatar

Avatar

Mayank_Gandhi

Employee

Avatar

Mayank_Gandhi

Employee

Mayank_Gandhi
Employee

06-06-2019

Upload the code somewhere.Why are you getting mysql error.

Avatar

Avatar

Mayank_Gandhi

Employee

Avatar

Mayank_Gandhi

Employee

Mayank_Gandhi
Employee

06-06-2019

He is having an issue with Microsoft SQL, MySQL is working fine.

Avatar

Avatar

workflowuser

Employee

Avatar

workflowuser

Employee

workflowuser
Employee

06-06-2019

I just tried on my system

I got the osgi bundle from here and it worked fine

https://search.maven.org/search?q=g:mysql%20AND%20a:mysql-connector-java

Avatar

Avatar

rajkumark10

Avatar

rajkumark10

rajkumark10

06-06-2019

Hi albinissac​ and Mayank Gandhi​,

Thanks for your inputs. As i informed before, I am not facing issue for mysql but for mssql server. Also as you said, i have tried with class.fornames(); and still facing issue. In this method even mysql not able to find driver.

Is there any reference for mssql connectivity from AEM. Kindly share.

ERROR : com.mysql.jdbc.Driver not found by

Regards,

Rajkumar K

Avatar

Avatar

Mayank_Gandhi

Employee

Avatar

Mayank_Gandhi

Employee

Mayank_Gandhi
Employee

03-06-2019

Looks like your code is missing javax.net.ssl package.

See the manifest file used for the below example of MYSQL and try to contemplate your manifest file.

Adobe Experience Manager Help | Creating an Adobe Experience Manager web application that uses MySQL

Avatar

Avatar

rajkumark10

Avatar

rajkumark10

rajkumark10

30-05-2019

Hi All,

For above issue, i have tried using Diver version 7.7.2 for SQL 2014 server with Java 8. The same steps are given in Support ticket created. Now i am getting below error. Ant idea on this error?

MY CODE and ERROR or Below

Getting error when this line Execute ----->   con = dataSource.getConnection();

ERROR:

Caused by: java.lang.NoClassDefFoundError: javax/net/ssl/SSLSocket

at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:2435) [SQLDriver:1.0.0]

at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:2103) [SQLDriver:1.0.0]

at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:1950) [SQLDriver:1.0.0]

at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:1162) [SQLDriver:1.0.0]

at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:735) [SQLDriver:1.0.0]

at java.sql.DriverManager.getConnection(Unknown Source)

at java.sql.DriverManager.getConnection(Unknown Source)

at org.apache.commons.dbcp.DriverManagerConnectionFactory.createConnection(DriverManagerConnectionFactory.java:65) [day.commons.datasource.jdbcpool:1.0.24]

at org.apache.commons.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:294) [day.commons.datasource.jdbcpool:1.0.24]

at org.apache.commons.pool.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:974) [day.commons.datasource.jdbcpool:1.0.24]

at org.apache.commons.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:96) [day.commons.datasource.jdbcpool:1.0.24]

at com.realogy.era.leverage.core.service.impl.DatabaseServiceImp.getConnection(DatabaseServiceImp.java:53) [com.realogy.era.leverage.realogy-era-leverage-core:1.0.0.SNAPSHOT]

... 254 common frames omitted

Caused by: java.lang.ClassNotFoundException: javax.net.ssl.SSLSocket not found by SQLDriver [590]

at org.apache.felix.framework.BundleWiringImpl.findClassOrResourceByDelegation(BundleWiringImpl.java:1639)

at org.apache.felix.framework.BundleWiringImpl.access$200(BundleWiringImpl.java:80)

at org.apache.felix.framework.BundleWiringImpl$BundleClassLoader.loadClass(BundleWiringImpl.java:2053)

at java.lang.ClassLoader.loadClass(Unknown Source)

... 266 common frames omitted

Please help me with this.

Regards,

Rajkumar K

con = dataSource.getConnection();

Avatar

Avatar

rajkumark10

Avatar

rajkumark10

rajkumark10

15-05-2019

Hi,

Thanks for your input. I have raised Support ticket for the same. Will update the status if issue resolved.

Regards,

Rajkumar K

Avatar

Avatar

smacdonald2008

Total Posts

12.7K

Likes

1.4K

Correct Answer

2.3K

Avatar

smacdonald2008

Total Posts

12.7K

Likes

1.4K

Correct Answer

2.3K
smacdonald2008

14-05-2019

There seems to be a bug here. See the following pic:

My OSGi bundle is active that exposes the required database SQL SERVER driver file.

Yet -- there is an error in the LOG file that says it cannot be found. This does not behave the same as the MySQL Driver file.

I recommend opening a SUPPORT Ticket and state the MS SQL Server Driver file cannot be found even though its deployed in an active state,

Error.png

Avatar

Avatar

smacdonald2008

Total Posts

12.7K

Likes

1.4K

Correct Answer

2.3K

Avatar

smacdonald2008

Total Posts

12.7K

Likes

1.4K

Correct Answer

2.3K
smacdonald2008

14-05-2019

I will attempt to get this use case working and make a new AEM/SQL SERVER article that shows everything required here.

Avatar

Avatar

Mayank_Gandhi

Employee

Avatar

Mayank_Gandhi

Employee

Mayank_Gandhi
Employee

14-05-2019

Have you uploaded the bundled JDBC driver for sql server and configured the Datasource?

Avatar

Avatar

smacdonald2008

Total Posts

12.7K

Likes

1.4K

Correct Answer

2.3K

Avatar

smacdonald2008

Total Posts

12.7K

Likes

1.4K

Correct Answer

2.3K
smacdonald2008

14-05-2019

I have only ever used MySQL for the DataSourcePool use case. 

I have SQL Server on my DEV Machine. I will test this out and post back.

I would think this could be a DRiver File issue or COnnection String issue when you setup the configuration required.

Avatar

Avatar

rajkumark10

Avatar

rajkumark10

rajkumark10

14-05-2019

Hi,

I have tried both the URLs. I can connect with MySql without any issues. I am facing issue with SQL database. is there anything i am missing in my configurations?

Avatar

Avatar

Mayank_Gandhi

Employee

Avatar

Mayank_Gandhi

Employee

Mayank_Gandhi
Employee

14-05-2019

Avatar

Avatar

smacdonald2008

Total Posts

12.7K

Likes

1.4K

Correct Answer

2.3K

Avatar

smacdonald2008

Total Posts

12.7K

Likes

1.4K

Correct Answer

2.3K
smacdonald2008

14-05-2019

We have plenty of video and articles on using MySQL with AEM. See:

Adobe Experience Manager Help | Querying MySQL data using an Adobe Experience Manager 6.4 DataSource...

This will show you step by step on how to get this use case implemented. See the video too at the end of this article.

Avatar

Avatar

rajkumark10

Avatar

rajkumark10

rajkumark10

14-05-2019

Hi,

I am using below details and getting the given error. Kindly help me to resolve the same.

JDBC Driver class : com.microsoft.sqlserver.jdbc.SQLServerDriver

JDBC connection : jdbc:sqlserver://<SQL Server URL>

Dependency used:

<dependency>

    <groupId>com.microsoft.sqlserver</groupId>

    <artifactId>mssql-jdbc</artifactId>

    <version>7.3.0.jre8-preview</version>

    <scope>provided</scope>

</dependency>

ERROR : No data source found with name 'test' (after asking 0 providers)

Avatar

Avatar

rajkumark10

Avatar

rajkumark10

rajkumark10

14-05-2019

Hi Mayank,

Thanks for your reply. No forms, i am just trying to connect with SQL database from AEM using datasourcepool. I am able to connect with MYSQL but not able to get connection to SQL. I guess the driver name and JDBC connection will change for SQL. Any idea?

Regards,

Rajkumar K

Avatar

Avatar

Mayank_Gandhi

Employee

Avatar

Mayank_Gandhi

Employee

Mayank_Gandhi
Employee

14-05-2019

Hi Raj,

Do you want to connect Adaptive forms with Mysql?