Adobe Experience Manager Sites & More

mohitaem · 4/9/20

Hi Team,

I am trying to connect with Oracle database 19c. I have given JDBC configuration and used the below code. But in the below code, I am getting datasource pool as "No data source found with name 'AppCloudDB' (after asking 0 providers)".

Can you please help me to find where I am doing a mistake?


@SlingServlet(paths = "/bin/getDB", methods = "GET", metatype=true, label = "AppCloud Database Connectivity",
description = "AppCloud Database Connectivity")
public class TestingDB extends SlingAllMethodsServlet{
   
   /**
    * 
    */
   private static final long serialVersionUID = 1L;
   protected static final Logger LOGGER = LoggerFactory.getLogger(TestingDB.class);
   
   @Reference
   public DataSourcePool dataSourcePoolService;
   
   ResultSet rs = null;
   PreparedStatement pstmt = null;
   
   PrintWriter writer = null;
   
   @Override
   protected void doGet(SlingHttpServletRequest request, SlingHttpServletResponse response)
         throws ServletException, IOException {
      writer = response.getWriter();
      LOGGER.debug("Entering appcloud test DB class");
      int results = getData();
      if(results > 0){
         writer.write("Connection to DB Successful. Demo Query retrieves "+results+ " items.");
      }else{
         writer.write("\n\nConnection to DB UnSuccessful. Demo Query retrieves "+results+ " items.");
      }
      LOGGER.error("--> Total Results: "+results);
   }
            
      public int getData(){
         int rowCount = 0;
         Connection c = null;
         try {
            c = getConnection();
            String query = "Select * FROM ac_content_type";
              pstmt = c.prepareStatement(query); 
              rs = pstmt.executeQuery();
              
              while(rs.next()){
                 rowCount++;
              }
              LOGGER.debug("Total Rows Retrieved"+rowCount);
              LOGGER.debug("Exiting appcloud test DB class");
         } catch (DataSourceNotFoundException e) {
            LOGGER.error("DataSourceNotFoundException while attempting to find a data source. make sure data source exists in AEM configuration.");
            writer.write("DataSourceNotFoundException while attempting to find a data source. make sure data source exists in AEM configuration. Exception received is: "+e);
         } catch (SQLException e) {
            LOGGER.error("SQLException while connecting to AppCloud DB.");
            writer.write("SQLException while connecting to AppCloud DB. Exception received is: "+e);
         } finally {
            try {
               c.close();
            } catch (SQLException e) {
               LOGGER.error(AppCloudConstants.SQL_EXCEPTION, e);
            }
         }
         return rowCount;
      }

      private Connection getConnection() throws DataSourceNotFoundException, SQLException{
         DataSource ds = null;
         Connection c = null;
         ds = (DataSource) dataSourcePoolService.getDataSource("AppCloudDB");
         c = ds.getConnection();
         return c;
      }
}

We are using ojdbc14.jar with it.

We were using 6.3 till now and things were working fine. With 6.5, its not creating data source.
Do we have to do anything in 6.5 for datasource.

vanegi · 7/14/20

Please make sure the Connections Pool service configuration is correct. Verify the property names, values, and data types. You can use the following example JSP code that obtains an instance of the hsqldbds data source, executes a simple SQL query, and displays the number of results that are returned.

<%@include file="/libs/foundation/global.jsp"%><%
%><%@page session="false"%><%
%><%@ page import="com.day.commons.datasource.poolservice.DataSourcePool" %><%
%><%@ page import="javax.sql.DataSource" %><%
%><%@ page import="java.sql.Connection" %><%
%><%@ page import="java.sql.SQLException" %><%
%><%@ page import="java.sql.Statement" %><%
%><%@ page import="java.sql.ResultSet"%><%
%><html>
<cq:include script="head.jsp"/>
<body>
<%DataSourcePool dspService = sling.getService(DataSourcePool.class);
  try {
     DataSource ds = (DataSource) dspService.getDataSource("hsqldbds");
     if(ds != null) {
         %><p>Obtained the datasource!</p><%
         %><%final Connection connection = ds.getConnection();
          final Statement statement = connection.createStatement();
          final ResultSet resultSet = statement.executeQuery("SELECT * from INFORMATION_SCHEMA.SYSTEM_USERS");
          int r=0;
          while(resultSet.next()){
             r=r+1;
          }
          resultSet.close();
          %><p>Number of results: <%=r%></p><%
      }
   }catch (Exception e) {
        %><p>error! <%=e.getMessage()%></p><%
    }
%></body>
</html>

Adobe Experience Manager Sites & More

AEM Oracle database connectivity not working

Learn

Documentation

Community

Support

Resources

Adobe account

Adobe