INSERT INTO statement in java servlet.
Hiya
Was wondering if anyone knew how to use variables from an html form into a sql insert into statement? The constants work ok below, its just getting the variables to work.
//constants work ok.
rs = stmt.executeQuery("INSERT INTO ACCOUNTS " + " VALUES ('un', 'test2', 'test2', 'test2', 'test', 'test', 'test', 'test', 'test')");
//doesn't do anything no errors.
rs = stmt.executeQuery("INSERT INTO ACCOUNTS " + " VALUES ( '"+uname+"'," + "'"+fname+"'," + "'"+sname+"'," + "'"+address1+"'," + "'"+address2+"'," + "'"+town+"'," + "'"+county+"'," + "'"+postcode+"')");
[644 byte] By [
Emmaqa] at [2007-11-26 19:04:43]
# 1
> Hiya
>
> Was wondering if anyone knew how to use variables
> from an html form into a sql insert into statement?
> The constants work ok below, its just getting the
> variables to work.
>
Maybe I am missing something, what does html have to do with it?
>
> //constants work ok.
> rs = stmt.executeQuery("INSERT INTO ACCOUNTS " + "
> VALUES ('un', 'test2', 'test2', 'test2', 'test',
> 'test', 'test', 'test', 'test')");
>
> //doesn't do anything no errors.
>
You should be using a PreparedStatement for this.
> rs = stmt.executeQuery("INSERT INTO ACCOUNTS " + "
> VALUES ( '"+uname+"'," + "'"+fname+"',"
> + "'"+sname+"'," + "'"+address1+"'," +
> "'"+address2+"'," + "'"+town+"'," + "'"+county+"',"
>+ "'"+postcode+"')");
So doesn't do anything means no rows inserted?
# 9
<html>
<head>
<title>
CreateAccount
</title>
</head>
<body>
<form method=get action=/servlet/website.CreateAccount>
Username:
<input type=text name="username">
Password:
<input type=text name="password">
First Name:
<input type=text name="firstname">
Surname:
<input type=text name="surname">
1st Line of Address:
<input type=text name="1address">
2nd Line of Address:
<input type=text name="2address">
Town:
<input type=text name="town">
County:
<input type=text name="county">
Postcode:
<input type=text name="postcode">
<input type=submit>
</form>
</body>
</html>
package website;
import java.io.*;
import java.sql.*;
import javax.servlet.*;
import javax.servlet.http.*;
public class CreateAccount extends HttpServlet {
private static final String CONTENT_TYPE = "text/html";
/**Initialize global variables*/
public void init(ServletConfig config) throws ServletException {
super.init(config);
}
/**Process the HTTP Get request*/
public void doGet(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException {
ResultSet rs = null;
Connection con = null;
res.setContentType("text/html");
PrintWriter out = res.getWriter();
//get the variables entered in the form
String uname = req.getParameter("username");
String pwd = req.getParameter("password");
String fname = req.getParameter("firstname");
String sname = req.getParameter("surname");
String address1 = req.getParameter("1address");
String address2 = req.getParameter("2address");
String town = req.getParameter("town");
String county = req.getParameter("county");
String postcode = req.getParameter("postcode");
try {
// Load the database driver
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
// Get a Connection to the database
con = DriverManager.getConnection("jdbc:odbc:account", "", "");
//Add the data into the database
try
{
String sql = "INSERT INTO ACCOUNTS " + " VALUES (?,?,?,?,?,?,?,?)";
PreparedStatement statement = con.prepareStatement(sql);
statement.setString(1, uname);
statement.setString(2, fname);
statement.setString(3, sname);
statement.setString(4, address1);
statement.setString(5, address2);
statement.setString(6, town);
statement.setString(7, county);
statement.setString(8, postcode);
int numRowsChanged = statement.executeUpdate(sql);
statement.close();
//Statement stmt = null;
//stmt = con.createStatement();
//Create a Statement object
//constants work ok.
//rs = stmt.executeQuery("INSERT INTO ACCOUNTS " + " VALUES ('uname', 'test2', 'test2', 'test2', 'test', 'test', 'test', 'test', 'test')");
}
catch (Exception e)
{
}
// show that the new account has been created
out.println(" New account created:
");
out.println(" '"+uname+"'");
}
catch(ClassNotFoundException e) {
out.println("Couldn't load database driver: " + e.getMessage());
}
catch(SQLException e) {
out.println("SQLException caught: " + e.getMessage());
}
finally {
// Always close the database connection.
try {
if (con != null) con.close();
}
catch (SQLException ignored) { }
}
}
}
ok now the regular statement with constant values inserts data into the database (the regular statement is being used with a result set )but the prepared statement does not - there are no error messages but it does not insert any data either. The data is going into the variables due to the system.out.println, but is it going into the prepared statement? I believe the prepared statement is being executed with the executeupdate method.
