Issue integrating CQ with mySQL

[Sudhin_Baru]

Hi,

 I am a newbie with CQ5. I am trying to follow the exact same steps in the below link. But for some reason am getting an error.

http://helpx.adobe.com/adobe-cq/using/creating-cq-web-application-uses.html

For some reason, it's not able to pick up the bundle. I am getting the error.

"Only a type can be imported. com.adobe.test.DataService resolves to a package".

Please don't tell me to change the *.bnd file. As per the article I made the bundle using eclipse not in CQ5. I can't find the bnd file in that.

Its been very frustrating, I tried everything but in vain.

Any help is appreciated.

Thanks,

Sudhin

[smacdonald2008]

Once you have that bundle in an active state and you Java JDBC code references your MySQL, this code works:
 

<%@include file="/libs/foundation/global.jsp"%>
<%@taglib prefix="cq" uri="http://www.day.com/taglibs/cq/1.0" %>
<%@ page import="com.adobe.test.DataService"  %>
<h1><%= properties.get("title", currentPage.getTitle()) %></h1>
<%
DataService ds = new DataService();
 
%>
<h2>About to write the first name</h2>
 
<h3>The first name in the list is: <%=  ds.getUser()%></h3>

I just executed it with AEM 5.6 and got:

[img]ASCott.png[/img]

Notice it queries the custFirst field from the first record in the Customer table.

In this example -- it queried Scot. 

Here is my Customer table viewed in MySQL workbench:

[img]ASCott2.png[/img]

[smacdonald2008]

This is what you should see in the Felix console:

[img]ADataBaseBundle.png[/img]

[smacdonald2008]

This article does not use CRXDE to create an OSGi bundle - it uses Eclipse Plug-in project. Therefore does not use the .bnd file. 

First off - do you have your OSGi bundle in an active state -- can you submit a screen shot that shows this please. 

[smacdonald2008]

Also be sure to wrap your JDBC Driver file in the OSGi bundle:

4. In the JAR selection dialog, click the Add external button, and browse to the CQJDBC.jar file that you created in the previous step. Also include the MySQL database driver JAR file (for example, mysql-connector-java-5.1.22).

[Sudhin_Baru]

Hi Scott,

 Thank for your prompt reply. Really appreciate it. When I try to create the OSGI Bundle I got an error with some of the packages "-Can not be resolved". Hence I removed them. I tried few other things, like adding a class with the ConnectionHelper ..etc But before that I'm am attaching the screenshots of my code.

<%@ page language="java" contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1"%> <%@include file="/libs/foundation/global.jsp"%> <%@ page import="java.sql.Connection" %> <%@ page import="java.sql.Statement" %> <%@ page import="org.training.test.HelloWorld" %> <%@ page import="com.adobe.test.DataService" %> <%@ page import="java.sql.ResultSet"%> <% DataService ds = new DataService(); %> <h3> Member:<%=ds.getUser()%> end</h3> <h1>hello raju</h1>

[Sudhin_Baru]

Here's mysql result set.

[Sudhin_Baru]

Ok here's what I tried as plan B. Added Connection helper * DataService class in the CRXDE

Created a bundle and consumed the bundle.

package org.training.test; import java.sql.*; import org.slf4j.Logger; import org.slf4j.LoggerFactory; public class DataService { private static final Logger LOG = LoggerFactory.getLogger(DataService.class); public String getUser(){ Connection c = null; String firstName =""; try { // Create a Connection object c =  ConnectionHelper.getConnection(); System.out.println("got connection"); LOG.debug("Connection @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@"); Statement s = c.createStatement(); ResultSet rs = s.executeQuery("Select * from dental.member where idMember=2"); System.out.println("Executed"); rs.next(); firstName = rs.getString("memName"); } catch (Exception e) { e.printStackTrace(); } finally { ConnectionHelper.close(c); } System.out.println("first name:" + firstName); return firstName; } public String getMember(){ Connection c = null; String firstName =""; try { // Create a Connection object c =  ConnectionHelper.getConnection(); System.out.println("got connection"); Statement s = c.createStatement(); ResultSet rs = s.executeQuery("Select * from dental.member where idMember=2"); System.out.println("Executed"); rs.next(); firstName = rs.getString("memName"); } catch (Exception e) { System.out.println("***************In catch***********"); e.printStackTrace(); } finally { ConnectionHelper.close(c); } System.out.println("first name:" + firstName); return firstName; } }

======================================================================

ConnectionHelper.java

package org.training.test; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; public class ConnectionHelper { private String url; private static ConnectionHelper instance; public ConnectionHelper() { try { Class.forName("com.mysql.jdbc.Driver").newInstance(); url = "jdbc:mysql://localhost:3306/dental"; } catch (Exception e) { e.printStackTrace(); } } public static Connection getConnection() throws SQLException { if (instance == null) { instance = new ConnectionHelper(); } try { return DriverManager.getConnection(instance.url, "root", "admin"); } catch (SQLException e) { throw e; } } public static void close(Connection connection) { try { if (connection != null) { connection.close(); } } catch (SQLException e) { e.printStackTrace(); } } }

================================

myjdbc.jsp

<%@ page language="java" contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1"%> <%@ page import="org.training.test.HelloWorld" %> <%@ page import="org.training.test.DataService" %> <%@ page import="javax.sql.DataSource" %> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1"> <title>DENTAL</title> </head> <body> <% DataService ds = new DataService(); %> <h3> DENTAL HOME PAGE</h3> <h3> Member:<%=ds.getUser()%> end</h3> <input type="text" id="memId"> </body> </html>

[smacdonald2008]

Can you paste all of your Java code here too. Need to see it.

There is something wrong with your Java packages -- you should not need  to dropped any. In this article -- the ConnectionHelp class established a connection to your MySQL.

[Sudhin_Baru]

Thanks Scott for your help. I think that was the step I was missing to remove the version next to the import packages in the Manifest file.

arrrgghhh it was driving me NUTS. thx again & you're doing a great job.

Regards,

Sudhin

[smacdonald2008 · Accepted solution]

For those following this thread -- the issue was the version numbers in the Manifest file. Make sure that you remove the version numbers in the MF file in the Eclipse project under the Export-Package and Import-Package sections. For example:

Manifest-Version: 1.0
Bundle-ManifestVersion: 2
Bundle-Name: CustBundle33
Bundle-SymbolicName: CustBundle33
Bundle-Version: 1.0.0
Export-Package: com.adobe.aem.sql,
 com.mysql.jdbc,
 com.mysql.jdbc.authentication,
 com.mysql.jdbc.exceptions,
 com.mysql.jdbc.exceptions.jdbc4,
 com.mysql.jdbc.integration.c3p0,
 com.mysql.jdbc.integration.jboss,
 com.mysql.jdbc.interceptors,
 com.mysql.jdbc.jdbc2.optional,
 com.mysql.jdbc.jmx,
 com.mysql.jdbc.log,
 com.mysql.jdbc.profiler,
 com.mysql.jdbc.util,
 org.gjt.mm.mysql
Import-Package: javax.transaction.xa,
 javax.management,
 javax.xml.transform.sax,
 javax.xml.transform.stream,
 org.xml.sax.helpers,
 javax.xml.transform.stax,
 org.slf4j,
 javax.xml.transform,
 javax.xml.transform.dom,
 javax.naming.spi,
 javax.net,
 javax.xml.stream,
 javax.xml.parsers,
 org.w3c.dom,
 javax.naming,
 javax.sql,
 javax.net.ssl,
 org.xml.sax
Bundle-RequiredExecutionEnvironment: JavaSE-1.6

Once version numbers are removed - CQ uses the version that is available to it. 

Also -- make sure that the bundle is always in an active state before calling its operations from a JSP.