Config Sun ONE Application Server JDBC to link to MySQL Server

[Jonathan Goh]

Recently, i download Sun ONE Application Server 7. I tried to config JDBC resource to link with MySQL server. When I invoke my deployed EAR, it had problem to access MySQL server. Anybody can config Sun ONE Application Server JDBC to link to MySQL Server successfully. Can pls provide the step to config.

[Amol Desai]

Hi,
1. You must run the mySQL server in ANSI mode for CMP to work
(we add quotation marks around the column names)
Add the option "--ansi" to your mySQL command line startup,
or add it in the "ini" options file
2. get the "mysql-connector-java-2.0.14.jar" driver file
3. put it somewhere on your disk, assuming you use windows, f.e. :
C:\mysql\mysql-connector-java-2.0.14.jar
4. Start S1AS 7 and goto the admin webpages.
5. Select your server, and click on the "JVM Settings" tab
Then select the "Path Settings"
In the "Classpath suffix" add a line containing:
C:\mysql\mysql-connector-java-2.0.14.jar
Save, and apply changes
6. Goto the "JDBC", then "Connection Pools" and create a new one.
JNDI name: "mySQLconPool"
(for example)
Datasource classname: "com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource"
Properties needed:
serverName
port
databaseName
user
password
7. Goto the "JDBC", then "JDBC Resources" and create a new one.
JNDI name: "mysqlDataSource"
This is the one to use in lookup()
Pool name: "mySQLconPool"
as defined in (4)
8. Save, apply changes & stop/start server
9. Deploy an app which can then do a :
DataSource ds = (DataSource)ctx.lookup("mysqlDataSource");
Hope this helps.
-Amol

[Jonathan Goh]

Hi Amol,
Thank for your reply. I had compared your steps with mine. It is the same except that I add a Persisent Manager. Therefore, setup should be correct.
Currently, i had trying out Sun Studio 5 which used Sun ONE Application Server. Database is MySQL server. I created a simple container managed EJB. I had to specific the CMR resouce in the web module. I point CMR resouce to Persistence manager of MySQL. It did not work after i deployed EAR. I point CMR resouce to JDBC Resources. It did not work too.
However, I created a simple bean managed EJB. It work.
Can anybody help me on this.

[Amol Desai]

Hi Jonathan,
Yes, you have to point the CMR resouce to Persistence manager of MySQL.
What exceptions are you getting, you could post the server logs after setting the log level to fine.
-Amol

[Jonathan Goh]

Hi Amol,
Thank for your reply. Attached the exception. Hope that you can help me to solve the problem.

08/Jul/2003:19:44:42] INFO ( 2376): Bean ProductInv method ejbFindAll: problems running JDOQL query.
com.sun.jdo.api.persistence.support.JDODataStoreException: Got a JDBC SQLException. Please read the remaining error stack for more information.
NestedException: java.sql.SQLException: Syntax error or access violation, message from server: "You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '"id", t0."price", t0."quantity" from "productinv" t0' at line 1"
at com.sun.jdo.spi.persistence.support.sqlstore.SQLStoreManager.executeQuery(SQLStoreManager.java:655)
at com.sun.jdo.spi.persistence.support.sqlstore.SQLStoreManager.retrieve(SQLStoreManager.java:500)
at com.sun.jdo.spi.persistence.support.sqlstore.impl.PersistenceManagerImpl.retrieve(PersistenceManagerImpl.java:989)
at com.sun.jdo.spi.persistence.support.sqlstore.query.QueryImpl.doExecute(QueryImpl.java:634)
at com.sun.jdo.spi.persistence.support.sqlstore.query.QueryImpl.execute(QueryImpl.java:455)
at CMPServer.ProductInvBean_629131956_ConcreteImpl.ejbFindAll(ProductInvBean_629131956_ConcreteImpl.java:297)
at CMPServer.ProductInvBean_629131956_ConcreteImpl_RemoteHomeImpl.findAll(ProductInvBean_629131956_ConcreteImpl_RemoteHomeImpl.java:90)
at CMPServer._ProductInvHome_Stub.findAll(Unknown Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at com.sun.forte4j.j2ee.ejbtest.webtest.InvocableMethod$MethodIM.invoke(InvocableMethod.java:231)
at com.sun.forte4j.j2ee.ejbtest.webtest.EjbInvoker.getInvocationResults(EjbInvoker.java:96)
at com.sun.forte4j.j2ee.ejbtest.webtest.DispatchHelper.getForward(DispatchHelper.java:189)
at _jasper._dispatch_jsp._jspService(_dispatch_jsp.java:138)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:107)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at com.iplanet.ias.web.jsp.JspServlet$JspServletWrapper.service(JspServlet.java:552)
at com.iplanet.ias.web.jsp.JspServlet.serviceJspFile(JspServlet.java:368)
at com.iplanet.ias.web.jsp.JspServlet.service(JspServlet.java:287)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at org.apache.catalina.core.StandardWrapperValve.invokeServletService(StandardWrapperValve.java:720)
at org.apache.catalina.core.StandardWrapperValve.access$000(StandardWrapperValve.java:118)
at org.apache.catalina.core.StandardWrapperValve$1.run(StandardWrapperValve.java:278)
at java.security.AccessController.doPrivileged(Native Method)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:274)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:505)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:212)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:505)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:203)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:505)
at com.iplanet.ias.web.connector.nsapi.NSAPIProcessor.process(NSAPIProcessor.java:157)
at com.iplanet.ias.web.WebContainer.service(WebContainer.java:598)