We help IT Professionals succeed at work.

Connecting to MYSQL using JSP on godaddy.com

milance445
milance445 asked
on
Medium Priority
1,753 Views
Last Modified: 2008-01-16
Hi. I am trying to connect to the mysql database on godaddy.com using JSP's. godaddy.com only provided me with their server name and port number. I was wondering what is the best way to connect to it and keep the connection open always so that all JSP's that need to access the database have connection?
Is it possible to set up the connection settings in the web.xml file and have no code for that in the JSP's whatsoever?
I tried connecting to it with the following code and no luck:
<sql:setDataSource
var="example"
driver="com.mysql.jdbc.Driver"
url="jdbc:mysql://mysql server:port/databsename"
user=""
password=""
/>

 I also tried with the sample code on their website FAQ but I don't think that this keeps the connection open always.
<%@ page contentType="text/html; charset=iso-8859-1" language="java"
import="java.sql.*" errorPage="" %>
<%
String url = "jdbc:mysql://mysql mysql55.secureserver.net:4365/name";
String user= "";
String pass= "";
try{
Class.forName ("com.mysql.jdbc.Driver").newInstance ();
Connection conn = DriverManager.getConnection(url, user, pass);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select * from a_reg;");

while(rs.next())
{
%>
<table> <tr>
<td><%= rs.getString("firstname") %></td>
<td><%= rs.getString("lastname") %></td>
</tr></table>
<%}
rs.close();
conn.close();
}catch(Exception e)
{
out.println(e.toString());
}
%>

Does anyone know the best way to establish a connection so that all JSP's don't have any problems with commands like
<sql:query var="user">
select username from table where username = ?
<sql:param><c:out value='${param.username}' /></sql:param>
</sql:query>
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2006

Commented:
Yes you can set up a datasource if the tomcat version is 5.0 as seen here http://tomcat.apache.org/tomcat-5.0-doc/jndi-datasource-examples-howto.html

this should allow jsps access to a datasource without requiring connection code

Author

Commented:
I saw this example, but I don't have access to the server.xml file on godaddy.com. Only web.xml. Is it possible to set up all the info like server name username,pass in the web.xml and not touch the server.xml file.
Siva Prasanna KumarPrincipal Solutions Architect
CERTIFIED EXPERT
Top Expert 2006

Commented:
http://forum.java.sun.com/thread.jspa?messageID=4159924

this link will surely help you as it has the solution too.

Author

Commented:
I've already posted the code in my Q from the link http://forum.java.sun.com/thread.jspa?messageID=4159924...this is where i got it from also.

Author

Commented:
I have solved my own problem though. I managed to setup the web.xml file to open the connection.
CERTIFIED EXPERT
Top Expert 2006

Commented:
Could you please post the solution so that others can benefit from seeing how you did it.

Author

Commented:
The main problem was the URL and taglib location.

<?xml version="1.0" encoding="ISO-8859-1"?>

<!DOCTYPE web-app
    PUBLIC "-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN"
    "http://java.sun.com/dtd/web-app_2_3.dtd">

<web-app>
    <context-param>
      <param-name>javax.servlet.jsp.jstl.fmt.locale</param-name>
      <param-value>en</param-value>
    </context-param>

    <context-param>
      <param-name>javax.servlet.jsp.jstl.fmt.localizationContext</param-name>
      <param-value>messages</param-value>
    </context-param>

    <context-param>
       <param-name>javax.servlet.jsp.jstl.sql.dataSource</param-name>
       
    <param-value>jdbc:mysql://mysql##.secureserver.net:port/databasename,com.mysql.jdbc.Driver,user,pass</param-value>
    </context-param>

    <taglib>
        <taglib-uri>http://jakarta.apache.org/taglibs/standard/scriptfree</taglib-uri>
        <taglib-location>/WEB-INF/scriptfree.tld</taglib-location>
    </taglib>

    <taglib>
        <taglib-uri>http://jakarta.apache.org/taglibs/standard/permittedTaglibs</taglib-uri>
        <taglib-location>/WEB-INF/permittedTaglibs.tld</taglib-location>
    </taglib>

</web-app>
Commented:
PAQed with points refunded (250)

GranMod
Community Support Moderator

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.