0 Replies Latest reply on May 20, 2003 3:17 AM by sabar

    get an error in my first servlet

    sabar Newbie

      Hi

      i am anewer 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