trouble connecting to mysql

[naveen]

hello friends
i have just touched jsp programming and i m having trouble connecting
to mysql database.
I m using tomcat 6

the connection part of the code of my jsp (HelloMySql.jsp) is-->
______________________________________________________
<%@ page import="java.sql.*" %>
<%@ page import="java.io.StringWriter" %>
<%@ page import="java.io.PrintWriter" %>
.
.
.
<form name="sqlaccess" action="HelloMysql.jsp">
......
</form>

<%
String dbURL="jdbc:mysql:localhost\\neo";
String username="root";
String passwd="abc";
try{
Class.forName("com.mysql.jdbc.Driver");
Connection conn=DriverManager.getConnection(dbURL,username,passwd);
Statement stat=null;
try{
stat=conn.createStatement();
if(varSql.length() !=0 ){
%>
.........
_____________________________________________

i m using this driver -->
mysql-connector-java-5.1.6-bin.jar
and have put it in this location
C:\Program Files\Apache Software Foundation\Tomcat 6.0\lib

further i have appended this to the Classpath environment variable -->
C:\Program Files\Apache Software Foundation\Tomcat 6.0\lib\mysql-
connector-java-5.1.6-bin.jar

but the sever returns error that
No suitable driver found

please tell me what else i am missing

thanks

[Arne Vajhøj]

naveen wrote:
> hello friends
> i have just touched jsp programming and i m having trouble connecting
> to mysql database.
> I m using tomcat 6
>
> the connection part of the code of my jsp (HelloMySql.jsp) is-->

> String dbURL="jdbc:mysql:localhost\\neo";

> String username="root";
> String passwd="abc";
> try{
> Class.forName("com.mysql.jdbc.Driver");
> Connection conn=DriverManager.getConnection(dbURL,username,passwd);

> i m using this driver -->

> mysql-connector-java-5.1.6-bin.jar
> and have put it in this location
> C:\Program Files\Apache Software Foundation\Tomcat 6.0\lib
>
> further i have appended this to the Classpath environment variable -->
> C:\Program Files\Apache Software Foundation\Tomcat 6.0\lib\mysql-
> connector-java-5.1.6-bin.jar
>
> but the sever returns error that
> No suitable driver found

Unless you want to use container managed connection pool you
should put the jar file in your web apps WEB-INF/lib.

The connection URL looks wrong as well. It should be
something like:

"jdbc:mysql://localhost/neo"

Arne

[Lew]

Also, it's pretty pointless to keep calling
Class.forName("com.mysql.jdbc.Driver");
after the first time in a program run. Once the driver is loaded, it's
loaded, and loading it again is redundant.

Also, move your Java source code ("scriptlet") out of the JSP and into a Java
servlet source file. Research the "Model-View-Controller" (MVC) pattern and
what Sun calls the "Model 2" web-app architecture. Among other things, it
will refactor your database connection code back two layers where it belongs.

--
Lew

[naveen]

well i have put it the jar file there too.

> The connection URL looks wrong as well. It should be
> something like:
>
> "jdbc:mysql://localhost/neo"

well i thought that you have to provide the location of the database
in this place "jdbc:mysql://localhost/neo"
though i have tried putting database as //localhostneo also
but this didnt work too

please suggest something
or if you could give me a working example it would be very kind of
you
thanks

[joe.we...@gmail.com]

It has to be "jdbc:mysql://localhost/neo" not //localhostneo.

Do this code:

Driver d = new com.mysql.jdbc.Driver();
System.out.println("Will the mysql driver accept the URL '" + dbURL +
"'? " + d.acceptsURL(dbURL) );

When you get it to return true, then you have a better chance of
connecting...
Joe

[Arne Vajhøj]

naveen wrote:
> On Apr 7, 12:07 am, Arne Vajhøj <a...@vajhoej.dk> wrote:
>> Unless you want to use container managed connection pool you
>> should put the jar file in your web apps WEB-INF/lib.
>
> well i have put it the jar file there too.

Not too. Only there.

You should not put jar files all over the system.

>> The connection URL looks wrong as well. It should be
>> something like:
>>
>> "jdbc:mysql://localhost/neo"
>
> well i thought that you have to provide the location of the database
> in this place "jdbc:mysql://localhost/neo"

No. You connect to localhost (on port 3306) and select database
neo. The MySQL servers knows where it is actually stored.

> though i have tried putting database as //localhostneo also
> but this didnt work too

Unless you have a host called localhostneo then it will not work.

> please suggest something
> or if you could give me a working example it would be very kind of

<%
Class.forName("com.mysql.jdbc.Driver");
Connection con =
DriverManager.getConnection("jdbc:mysql://localhost/Test", "", "");
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM T1");
...

works for me.

Arne

[naveen]

> > though i have tried putting database as //localhostneo also
> > but this didnt work too

well i am soory ..my mistake i meant //localhost/neo

> <%
> Class.forName("com.mysql.jdbc.Driver");
> Connection con =
> DriverManager.getConnection("jdbc:mysql://localhost/Test", "", "");
> Statement stmt = con.createStatement();
> ResultSet rs = stmt.executeQuery("SELECT * FROM T1");
> ...
>
> works for me.

well i have used:
________________________________________________________
...
<%
String varSql = request.getParameter("sql");
if (varSql == null) {
varSql = "";
}
varSql = java.net.URLDecoder.decode(varSql).trim();
%>
...
<%
String dbURL="jdbc:mysql://localhost/neo";

String username="root";
String passwd="abc";
try{
Class.forName("com.mysql.jdbc.Driver");
Connection conn=DriverManager.getConnection(dbURL,username,passwd);

Statement stat=null;
try{
stat=conn.createStatement();
if(varSql.length() !=0 ){
%>

<pre> <%= varSql %> </pre>
<%
if (stat.execute(varSql)) {
java.sql.ResultSet rs = stat.getResultSet();
try {
java.sql.ResultSetMetaData metaData = rs.getMetaData();
int colCount =metaData.getColumnCount();
%>
...
________________________________________________________

now i must be making a mistake somewhere since the browser shows this:

_____________________________________________________
An Exception was caught while connecting to the database.

java.sql.SQLException: Communication link failure:
java.io.IOException, underlying cause: Unexpected end of input stream

** BEGIN NESTED EXCEPTION **

java.io.IOException
MESSAGE: Unexpected end of input stream

STACKTRACE:

java.io.IOException: Unexpected end of input stream
at com.mysql.jdbc.MysqlIO.readPacket(MysqlIO.java:1096)
at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:626)
at com.mysql.jdbc.Connection.createNewIO(Connection.java:1562)
at com.mysql.jdbc.Connection.(Connection.java:491)
at
com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:
346)
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at org.apache.jsp.HelloMysql_jsp._jspService(HelloMysql_jsp.java:98)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
at
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:
393)
at
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:
320)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:266)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:
290)
at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:
206)
at
org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:
230)
at
org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:
175)
at
org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:
128)
at
org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:
104)
at
org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:
109)
at
org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:
261)
at
org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:
844)
at org.apache.coyote.http11.Http11Protocol
$Http11ConnectionHandler.process(Http11Protocol.java:581)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:
447)
at java.lang.Thread.run(Unknown Source)
____________________________________________________

what should i do about this?
please help

[Arne Vajhøj]

naveen wrote:
>>> though i have tried putting database as //localhostneo also
>>> but this didnt work too
>
> well i am soory ..my mistake i meant //localhost/neo

As a general rule: copy paste when you post to avoid typos.

> <%
> String varSql = request.getParameter("sql");
> if (varSql == null) {
> varSql = "";
> }
> varSql = java.net.URLDecoder.decode(varSql).trim();
> %>
> ...
> <%
> String dbURL="jdbc:mysql://localhost/neo";
> String username="root";
> String passwd="abc";
> try{
> Class.forName("com.mysql.jdbc.Driver");
> Connection conn=DriverManager.getConnection(dbURL,username,passwd);
> Statement stat=null;
> try{
> stat=conn.createStatement();

> java.sql.SQLException: Communication link failure:

> java.io.IOException, underlying cause: Unexpected end of input stream
>
> ** BEGIN NESTED EXCEPTION **
>
> java.io.IOException
> MESSAGE: Unexpected end of input stream
>
> STACKTRACE:
>
> java.io.IOException: Unexpected end of input stream
> at com.mysql.jdbc.MysqlIO.readPacket(MysqlIO.java:1096)
> at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:626)
> at com.mysql.jdbc.Connection.createNewIO(Connection.java:1562)
> at com.mysql.jdbc.Connection.(Connection.java:491)
> at
> com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:
> 346)
> at java.sql.DriverManager.getConnection(Unknown Source)
> at java.sql.DriverManager.getConnection(Unknown Source)
> at org.apache.jsp.HelloMysql_jsp._jspService(HelloMysql_jsp.java:98)
> at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
> at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)

You have loaded the driver OK, you have established a TCP connection
to the server OK, but the JDBC driver and the MySQL server will not
talk to each other. It look like the MySQL server closes the connection.

Check that the JDBC driver you are using are newer than the MySQL
server version you are using.

Check in the MySQL side for errors.

Arne

[naveen]

> You have loaded the driver OK, you have established a TCP connection
> to the server OK, but the JDBC driver and the MySQL server will not
> talk to each other. It look like the MySQL server closes the connection.
>
> Check that the JDBC driver you are using are newer than the MySQL
> server version you are using.
>

i am using |mysql-connector-java-5.1.6| connector
and |mysql-5.0.51a-win32| mysql database
i think these are latest versions and probably should comply with each
other.

> Check in the MySQL side for errors.
>

how do i do that ? please elucidate
other than this, i tried connecting to mysql
using jndi as described in the apache tomcat documentation

but it's example jsp uses jstl
the test.jsp is of form:
________________________________
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>

<sql:query var="rs" dataSource="jdbc/TestDB">
select id, foo, bar from testdata
</sql:query>

<html>
<head>
<title>DB Test</title>
</head>
<body>

<h2>Results</h2>

<c:forEach var="row" items="${rs.rows}">
Foo ${row.foo}<br/>
Bar ${row.bar}<br/>
</c:forEach>

</body>
</html>
____________________________________________

the described jndi example works but i dont know jstl
and i need to use simple jsp coding

now if anyone would do me a favor by converting the test.jsp as shown
to a simple jsp (i.e. without using the jstl) then my problem would
stand
solved for now

somebody please help

[Lew]

Arne advised (and naveen neglected to attribute):

>> Check in the MySQL side for errors.

naveen wrote:
> how do i [sic] do that ? please elucidate

www.mysql.com

> the described jndi [sic] example works but i [sic] dont know jstl [sic]

<http://java.sun.com/javaee/5/docs/tutorial/doc/bnakc.html>

> now if anyone would do me a favor by converting the test.jsp as shown
> to a simple jsp (i.e. without using the jstl) then my problem would
> stand solved for now

That's funny!

--
Lew

[naveen]

>
> > now if anyone would do me a favor by converting the test.jsp as shown
> > to a simple jsp (i.e. without using the jstl) then my problem would
> > stand solved for now
>
> That's funny!
>

man! u really have got a big funny bone :-)
however thanks guys at last i made the cut.
though through jndi

thanks ..again