#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    5
    Rep Power
    0

    using java servlet to insert data in MySql


    In my code, I am getting database access and select data from the tabe, but am having problems inserting data.

    I am getting the paramaters from the form in a doGet method using

    TT1 = request.getParameter("T1");
    TT2 = request.getParameter("T2");
    TT3 = request.getParameter("T3");
    TT4 = request.getParameter("T4");
    TT5 = request.getParameter("T5");

    and then trying to insert these into the MySql database. When I use the following string, I can insert into the table.

    String queryStr = "INSERT IGNORE INTO users (index_num, first_name, last_name, username, userpassword,email_address) VALUES('',$TT1,$TT2,$TT5,$TT3,$TT4)";

    The problem is that this inserts the wrong information into the table. When I change the string to try to insert the value of the variable, I get an error message.

    String queryStr = "INSERT IGNORE INTO users (index_num, first_name, last_name, username, userpassword,email_address) VALUES('',TT1,TT2,TT5,TT3,TT4)";


    Error opening JDBC:

    java.sql.SQLException: Column not found, message from server: "Unknown column '$TT1' in 'field list'"

    I almost have this project completed and just need to get this part to work. Can anyone help?
  2. #2
  3. No Profile Picture
    Clueless llama
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Feb 2001
    Location
    Lincoln, NE. USA
    Posts
    2,353
    Rep Power
    117
    Your error message does not correspond with what you are saying. Please post all your code and the exact stack trace for us to see.
    ~Nemi

    Before posting did you try:
    [ Javadocs | Google ]
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    5
    Rep Power
    0

    Thank you - Here is the code


    import javax.servlet.*;
    import javax.servlet.http.*;
    import java.io.*;
    import java.util.*;
    import java.sql.*;


    public class regServlet extends HttpServlet {

    //declare variables
    private String TT1; //first name
    private String TT2; //last name
    private String TT3; //username
    private String TT4; //password
    private String TT5; //email

    private String dataName;
    private String dataPass;
    private String firstName;


    private String driver = "com.mysql.jdbc.Driver";
    private String url = "jdbc:mysql://localhost/mydatabasename";
    private String uName = "myUsername";
    private String uPass = "myPassword";


    public void doGet(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException
    {
    PrintWriter out = response.getWriter();

    TT1 = request.getParameter("T1");
    TT2 = request.getParameter("T2");
    TT3 = request.getParameter("T3");
    TT4 = request.getParameter("T4");
    TT5 = request.getParameter("T5");


    out.println("<html><head><meta http-equiv=\"Content-Language\" content=\"en-us\" charset=ISO-8859-1>");
    out.println("<title>Registered</title></head>");
    out.println("<body bgcolor=\"#CAD3DC\">");
    out.println("<tableborder=0 cellpadding=0 cellspacing=0 width=\"100%\" height=\"100%\">");
    out.println("<table bgcolor=\"#CAD3DC\" width=\"100%\">");
    out.println("<font size=6 face=\"Times New Roman\">");
    out.println("<p>&nbsp;</p><p>&nbsp;</p><p>&nbsp;</p>");
    /*out.println(" Welcome " + TT1);
    out.println(" \n " + TT2);
    out.println(" \n " + TT3);
    out.println(" \n " + TT4);
    out.println(" \n " + TT5);
    */

    openJDBC(out);
    }

    public void openJDBC(PrintWriter out)
    {
    //open the database and output the data
    try
    {

    //String tableStr = "SELECT * from users";
    String queryStr = "INSERT IGNORE INTO users (index_num, first_name, last_name, username, userpassword,email_address) VALUES('',$TT1,$TT2,$TT5,$TT3,$TT4)";

    /*
    ------------------------------------------------
    The above string give the error:

    Error opening JDBC:

    java.sql.SQLException: Column not found, message from server: "Unknown column '$TT1' in 'field list'"

    --------------------------------------------------
    This string works, but inserts the string and not its value

    String queryStr = "INSERT IGNORE INTO users (index_num, first_name, last_name, username, userpassword,email_address) VALUES('','$TT1','$TT2','$TT5','$TT3','$TT4')";


    ------------------------------------------------------
    String queryStr = "INSERT IGNORE INTO users (index_num, first_name, last_name, username, userpassword,email_address) VALUES('',TT1,TT2,TT5,TT3,TT4)";

    This string also produces a column not found error.


    */

    Class.forName( driver );

    Connection con = DriverManager.getConnection( url, uName, uPass );

    //Statement stat = con.createStatement();
    Statement stat2 = con.createStatement();


    //stat.executeQuery(tableStr);
    stat2.executeUpdate(queryStr);


    out.println("<h2>\nThank you for registering.</h2>\n\n");
    out.println("</font></table></body></html>");

    // close our JDBC connection
    con.close();

    }//close try

    //in case of errors in opening the database, catch the exceptions
    catch (SQLException sqle)
    { out.println("<p> Error opening JDBC: </p> <b> " + sqle + "</b>"); }

    catch(ClassNotFoundException cnfe)
    { out.println("<p> Error opening JDBC: </p> <b>" + cnfe + "</b>"); }
    }// close public void JDBC


    public void doPost(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException
    {
    doGet(request, response);

    }

    }
  6. #4
  7. No Profile Picture
    Clueless llama
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Feb 2001
    Location
    Lincoln, NE. USA
    Posts
    2,353
    Rep Power
    117
    You have to concatenate your string variables into the query string.

    Something like
    Code:
    String color = "blue";
    "I am very " + color + " today";
    Given that example, you should be able to change your code to work. Remember, this is not php. You cannot use the dollar sign for variables and they are not automatically parsed in strings.
    ~Nemi

    Before posting did you try:
    [ Javadocs | Google ]
  8. #5
  9. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    5
    Rep Power
    0

    how to tell program to use the value


    Hi,

    Thanks for your reply.

    I tried INSERT ... VALUES('', ''+TT1+'', ...)

    and received:

    Error opening JDBC: java.sql.SQLException: Column not found, message from server: "Unknown column 'TT1' in 'field list'"


    Any suggestions would be appreciated.
  10. #6
  11. No Profile Picture
    Clueless llama
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Feb 2001
    Location
    Lincoln, NE. USA
    Posts
    2,353
    Rep Power
    117
    Well, I am totally stumped. According to the error message, it is comlaining because you are asking to put data into a field called TT1 but according to your sql, your fields are called index_num, first_name, last_name, username, userpassword,email_address. It is acting as if you had the String TT1 in the first part of your query after the "users" table name. If this is indeed all of your code, then I can't help you.

    Maybe try putting that query in directly to the database using a command line client and see what it does.
    ~Nemi

    Before posting did you try:
    [ Javadocs | Google ]
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2003
    Posts
    54
    Rep Power
    12
    try putting single quote in double quote

    INSERT ... VALUES('', + "'" + TT1 + "'", ...)

IMN logo majestic logo threadwatch logo seochat tools logo