error in my first servlet when connected to mysql
sabar May 20, 2003 3:31 AMHi
i am a newer to ejb, i use eclipse, jboss, mysql, i did the fllowing steps to write my first servlet that acess mysql :
1. create mysql data base [partdb]
2. copy mysql-connector-java-3.0.6-stable-bin.jar to [jboss.dir]/server/default/lib
3. server/default/conf/standardjbosscmp-jdbc.xml, replace the Hyper SQL with these 2:
java:/MySqlDS
<datasource-mapping>mySQL</datasource-mapping>
4. server/default/conf/standardjaws.xml, replace the Hyper SQL with these 2:
java:/MySqlDS
<type-mapping>mySQL</type-mapping>
5. copy docs/examples/jca/mysql-ds.xml into /server/default/deploy
6. update mysql-ds.xml with your MySql login/password, and use the following:
<jndi-name>MySqlDS<jndi-name>
<connection-url>jdbc:mysql://localhost:3306/partdb</connection-url>
7. restart jboss
8. write my servlet as the following
package jdbcexam;
import javax.servlet.*;
import javax.naming.InitialContext;
import javax.servlet.ServletException;
import javax.servlet.http.*;
import javax.sql.DataSource;
import java.sql.*;
import java.io.*;
/**
* @author Administrator
*
* To change this generated comment edit the template variable "typecomment":
* Window>Preferences>Java>Templates.
* To enable and disable the creation of type comments go to
* Window>Preferences>Java>Code Generation.
*/
public class Query extends HttpServlet {
Connection db; // This is the shared JDBC database connection
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws IOException, ServletException
{
try{
InitialContext iContext = new InitialContext();
DataSource ds = (DataSource) iContext.lookup("java:/MySqlDS");
db = ds.getConnection();
}catch(Exception e) {}
response.setContentType("text/html"); // We're outputting HTML
PrintWriter out = response.getWriter(); // Where to output it to
// Output document header and a form for entering SQL queries
// When the form is submitted, this servlet is reloaded
out.println("DB Query\n" +
"<body bgcolor=white><h1>DB Query</h1>\n" +
"Query: " +
"<input type=submit>");
// See if a query was specified in this request.
String query = request.getParameter("q");
if (query != null) {
// display the query text as a page heading
out.println("<h1>" + query + "</h1>");
// Now try to execute the query and display the results in a table
Statement statement = null; // An object to execute the query
try {
// Create a statement to use
statement = db.createStatement();
// Use it to execute the specified query, and get result set
ResultSet results = statement.executeQuery(query);
// Ask for extra information about the results
ResultSetMetaData metadata = results.getMetaData();
// How many columns are there in the results?
int numcols = metadata.getColumnCount();
// Begin a table, and output a header row of column names
out.println("<table border=2>");
for(int i = 0; i < numcols; i++)
out.print("" + metadata.getColumnLabel(i+1) + "");
out.println("");
// Now loop through the "rows" of the result set
while(results.next()) {
// For each row, display the the values for each column
out.print("");
for(int i = 0; i < numcols; i++)
out.print("" + results.getObject(i+1) + "");
out.println("");
}
out.println(""); // end the table
}
catch (SQLException e) {
// If anything goes wrong (usually a SQL error) display the
// error to the user so they can correct it.
out.println("SQL Error: " + e.getMessage());
}
finally { // Whatever happens, always close the Statement object
try { statement.close(); }
catch(Exception e) {}
}
}
// Finally, end the HTML output
out.println("");
}
}
9.deploy it
10. run it
http://localhost:8080/jdbcweb/servlet/jdbcexam.Query
11. when submit the form to query the database partdb i got the following error
SQL Error: Syntax error or access violation, message from server: "You have an error in your SQL syntax near 'q' at line 1"
pls tell me what is the problem
with regards