Connect AEM with MySQL - AEM 6.4

[rajkumark10]

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.

[Mayank_Gandhi]

Hi Raj,

Do you want to connect Adaptive forms with Mysql?

[rajkumark10]

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

[Mayank_Gandhi]

You can refer the sample here

Adobe Experience Manager Help | Injecting a DataSourcePool Service into an Adobe Experience Manager ...

Adobe CQ/Adobe AEM: How to Connect to External Data Source (Teradata / MySQL / Oracle) in CQ / AEM

[rajkumark10]

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)

[smacdonald2008]

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.

[Mayank_Gandhi]

https://labs.tadigital.com/index.php/2018/11/29/aem-integration-with-mysql/

Try this approach.

[rajkumark10]

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?

[smacdonald2008]

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.

[Mayank_Gandhi]

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

[smacdonald2008]

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

[smacdonald2008]

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,

[rajkumark10]

Hi,

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

Regards,

Rajkumar K

[rajkumark10]

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();

[Albin_Issac]

The approach explained in the following post will help you - https://www.albinsblog.com/2019/06/javalangnoclassdeffounderror-aem-JDBC-connection-pool-connection-...

[Mayank_Gandhi]

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

[rajkumark10]

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

[workflowuser]

Try this article

https://helpx.adobe.com/experience-manager/kt/forms/using/aem-forms-with-json-schema-and-data-tutori...

[workflowuser]

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

[Mayank_Gandhi]

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

[huyvm62]

Hi,

You can share me your scouce code connect to MySQL with AEM.

Thanks a lots