Connection Problem with CQ5 with MySql

I faced similar issue. If this is of any help

After adding the driver I was still seeing the error. Once I went to the OSGi config and set the JDBC prpoerties again and hit save. it worked.

It looks like CQ cannot find your Customer DataSource that you configured. Can you double check and make sure that it's configured properly? 

Please attach a Screenshot of it. 

Hi,

I tried implementing as specified, but still getting the error, I have attached the screenshots, errorlog and java code.
I have used mysql-connector-java-5.1.29-bin.jar and json-simple-1.1.1.jar to create a json-mysql bundle.
Please identify am i doing anything wrong. If possible share some sample code and screen shots.

sourcecode of java

package com.adobe.cq.sling.ds; import java.io.BufferedReader; import java.io.IOException; import java.io.InputStream; import java.io.InputStreamReader; import java.io.PrintWriter; import java.net.HttpURLConnection; import java.net.URL; import java.rmi.ServerException; import java.util.Collection; import java.util.Dictionary; import java.util.Iterator; import java.util.List; import org.apache.felix.scr.annotations.Properties; import org.apache.felix.scr.annotations.Property; import org.apache.felix.scr.annotations.Reference; import org.apache.felix.scr.annotations.sling.SlingServlet; import org.apache.sling.api.SlingHttpServletRequest; import org.apache.sling.api.SlingHttpServletResponse; import org.apache.sling.api.servlets.SlingSafeMethodsServlet; import org.apache.sling.commons.osgi.OsgiUtil; import org.apache.sling.jcr.api.SlingRepository; import org.apache.felix.scr.annotations.Reference; import org.osgi.service.component.ComponentContext; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import javax.jcr.Session; import javax.jcr.Node; import org.json.simple.JSONObject; import java.util.UUID; //import MySQL APIs import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.Statement; import java.sql.SQLException; import javax.sql.DataSource; //Import CQ DataSOurcePool import com.day.commons.datasource.poolservice.DataSourcePool; @SlingServlet(paths="/bin/mySearch", methods = "POST", metatype=true) public class HandleClaim extends org.apache.sling.api.servlets.SlingAllMethodsServlet { private static final long serialVersionUID = 2598426539166789515L; protected final Logger log = LoggerFactory.getLogger(this.getClass()); @Reference private DataSourcePool source; @Override protected void doPost(SlingHttpServletRequest request, SlingHttpServletResponse response) throws ServerException, IOException { try { //Get the submitted form data that is sent from the //CQ web page String id = UUID.randomUUID().toString(); String firstName = request.getParameter("firstName"); String lastName = request.getParameter("lastName"); String address = request.getParameter("address"); String cat = request.getParameter("cat"); String state = request.getParameter("state"); String details = request.getParameter("details"); String date = request.getParameter("date"); String city = request.getParameter("city"); log.info("HELLO WORLD"); log.info("$##$#$#$#$#$#$#$#$#$#$"); log.info("HELLO WORLD"); //Persist the Data into MySQL by using connection build with the DataSourcePool int x= injestCustData(firstName, lastName, address, details); log.info("THIS IS X"+x); //Encode the submitted form data to JSON JSONObject obj=new JSONObject(); obj.put("id",id); obj.put("firstname",firstName); obj.put("lastname",lastName); obj.put("address",address); obj.put("cat",cat); obj.put("state",state); obj.put("details",details); obj.put("date",date); obj.put("city",city); //Get the JSON formatted data String jsonData = obj.toJSONString(); //Return the JSON formatted data response.getWriter().write(jsonData); } catch(Exception e) { e.printStackTrace(); } } //Returns a connection using the configured DataSourcePool private Connection getConnection() { DataSource dataSource = null; Connection con = null; try { //Inject the DataSourcePool right here! dataSource = (DataSource) source.getDataSource("Customer"); con = dataSource.getConnection(); return con; } catch (Exception e) { e.printStackTrace(); } return null; } //Adds a new customer record in the Customer table public int injestCustData(String firstName, String lastName, String phone, String desc) { Connection c = null; //Connection connection = null; int rowCount= 0; try { log.info("WE ARE IN INJECT CUSTOMER DATA"); // Create a Connection object c =  getConnection(); //THE FLOW IS NOT WORKING FROM HERE........ //c = ConnectionHelper.getConnection(); ResultSet rs = null; Statement s = c.createStatement(); Statement scount = c.createStatement(); //Use prepared statements to protected against SQL injection attacks PreparedStatement pstmt = null; PreparedStatement ps = null; //Set the query and use a preparedStatement String query = "Select * FROM Customer"; pstmt = c.prepareStatement(query); rs = pstmt.executeQuery(); log.info("******************************************"); while (rs.next()) rowCount++; //Set the PK value int pkVal = rowCount + 2; String insert = "INSERT INTO Customer(custId,custFirst,custLast,custDesc,custAddress) VALUES(?, ?, ?, ?, ?);"; ps = c.prepareStatement(insert); ps.setInt(1, pkVal); ps.setString(2, firstName); ps.setString(3, lastName); ps.setString(4, phone); ps.setString(5, desc); ps.execute(); log.info("END OF CUST DATA"); return pkVal; } catch (Exception e) { e.printStackTrace(); } finally { try { c.close(); } catch (SQLException e) { e.printStackTrace(); } } return 0; } }

Screen Shot for Connection

OSGI BUNDLE Screenshot.

About Mysql and JSON Bundle

There is an issue somewhere in your environment. I copied the code you pasted and it works fine. I even see your log messages:

07.03.2014 12:12:06.022 *INFO* [0:0:0:0:0:0:0:1 [1394212326021] POST /bin/mySearch HTTP/1.1] com.adobe.cq.sling.ds.HandleClaim $##$#$#$#$#$#$#$#$#$#$
07.03.2014 12:12:06.022 *INFO* [0:0:0:0:0:0:0:1 [1394212326021] POST /bin/mySearch HTTP/1.1] com.adobe.cq.sling.ds.HandleClaim HELLO WORLD
07.03.2014 12:12:06.022 *INFO* [0:0:0:0:0:0:0:1 [1394212326021] POST /bin/mySearch HTTP/1.1] com.adobe.cq.sling.ds.HandleClaim WE ARE IN INJECT CUSTOMER DATA
07.03.2014 12:12:06.042 *INFO* [0:0:0:0:0:0:0:1 [1394212326021] POST /bin/mySearch HTTP/1.1] com.adobe.cq.sling.ds.HandleClaim ******************************************
07.03.2014 12:12:06.043 *INFO* [0:0:0:0:0:0:0:1 [1394212326021] POST /bin/mySearch HTTP/1.1] com.adobe.cq.sling.ds.HandleClaim END OF CUST DATA
07.03.2014 12:12:06.043 *INFO* [0:0:0:0:0:0:0:1 [1394212326021] POST /bin/mySearch HTTP/1.1] com.adobe.cq.sling.ds.HandleClaim THIS IS X31

What version of CQ are you using? 

To rule out if there is an issue with DataSourcePool-- can you try doing MySQL interaction without a DataSourcePool. IN this use case -- you code your own connection classes instead of using DataSourcePool. See this article:

http://helpx.adobe.com/experience-manager/using/persisting-cq-data-relational-database.html

IN this article - note use of the ConnectionHelper class.

Try that and let me know if you have better success. Once that works -- i can help you nail down the issue with DataSourcePool. If needed, we can setup a conference call.  

Hi,

It is some problem with conversion of MySQL connector to Bundle.

It is not importing all the packages. Now I created the bundle for the MySQL using Indigo.

Now it is working fine. It is connected to MySQL

Thanks for suggestion.

I am glad that you got it working. Your code was fine!!