0

Given this code :

package db;
import java.io.*;
import java.sql.*;


public class Connect 
{


    // creating a table for each type person in the bank 

    public void createTable(Statement state,String tableType) throws SQLException
    {
        state.executeUpdate (

                "CREATE TABLE IF NOT EXISTS "+ tableType +" ("
                + "FirstName CHAR(20), LastName CHAR(20),"
                + "Address CHAR(50), PhoneNumber CHAR(20),"
                + "UserName CHAR(20), Password CHAR(20))");
    }



    public void insertDataToTable(Statement statement , String table 
            ,String firstName,String lastName,String address, String phoneNumber , String userName, String password)
    {
        try
        {
            statement.executeUpdate("INSERT INTO table (`FirstName` ,`LastName` , `Address` , `PhoneNumber` , `UserName` , `Password`) " +
                    "values ( '"+firstName+"','"+lastName+ "','"+address+"','"+phoneNumber+"','"+userName+ "'," +password+")");



        }

        catch(Exception e)
        {
            System.out.println(e.toString());
        }

    }


    public void start()
    {

        System.out.println("Database creation example!");
        Connection con = null;
        try
        {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost","root","root");
            try
            {

                Statement st = con.createStatement();

                // create a database 

                st.executeUpdate("CREATE DATABASE IF NOT EXISTS Personnel"); 
                st.executeUpdate("USE Personnel");

                // create tables

                //Create a table for each user type!
                createTable(st, "ClientsTable");
                createTable(st, "ClerksTable");
                createTable(st, "ManagersTable");
                createTable(st, "AdminsTable");

                this.insertDataToTable(st, "ClientsTable", "my", "name", "is", "erl", "I", "think");

                ResultSet rs = st.executeQuery("SELECT `FirstName` FROM `ClientsTable`");
                while (rs.next() == true)
                { 
                    System.out.println(rs.getString("FirstName")); 
                }



            } // end try  

            catch (SQLException s)
            {
                System.out.println("SQL statement is not executed!");
            }


        } // end try 



        catch (Exception e){
            e.printStackTrace();
        }


    }  // end start



}

When I execute from my Main :

package db;

public class Main {

    public static void main(String [ ] args)
    {

        Connect myConnection = new Connect();

        myConnection.start();
    }

}

And reach that line in start() method :

this.insertDataToTable(st, "ClientsTable", "my", "name", "is", "erl", "I", "think");

I get the following output from the server (using the try/catch) :

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'table (`FirstName` ,`LastName` , `Address` , `PhoneNumber` , `UserName` , `Passw' at line 1

I checked the manual but I can't seem to find the source of the problem . Any idea what's wrong here ? thanks

5
  • 1
    replace tablewith your actual table name and add quotes around your password. Commented Aug 4, 2012 at 6:16
  • @juergend: I did that : statement.executeUpdate("INSERT INTO ClientsTable (FirstName` ,LastName , Address , PhoneNumber , UserName , Password) " + "values ( '"+firstName+"','"+lastName+ "','"+address+"','"+phoneNumber+"','"+userName+ "'," +password+")");` , and not I get com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'think' in 'field list' Commented Aug 4, 2012 at 6:20
  • highly recommend PreparedStatement instead of normal Statement Commented Aug 4, 2012 at 6:25
  • @LiuYan刘研: Can you please explain why ? Commented Aug 4, 2012 at 6:26
  • 1
    you don't need to escaping or quoting the value parameter if using PreparedStatement, this can (1) avoid potential SQL injection. (2) reduce syntax error in cases like yours (3) makes the SQL looks clear : INSERT INTO ClientsTable (FirstName,LastName,Address,PhoneNumber,UserName,Password) VALUES (?,?,?,?,?,?) Commented Aug 4, 2012 at 6:30

4 Answers 4

1

Your table name is a variable I believe. However you have used the String "table" directly. But this isn't an issue.

Your password variable value requires a single quote.

"values ( '"+firstName+"','"+lastName+ "','"+address+"','"+phoneNumber+"','"+userName+ "','" +password+"')");
Sign up to request clarification or add additional context in comments.

Comments

1

You are using tilde "`" instead of quote "'" around your column names.

Since you don't have any separators in them, you can either remove all tilde / quotes from your column names or just replace all of them with quote "'"

Comments

1

Notice that your table name is missing in the statement.

statement.executeUpdate("INSERT INTO table (

Use following instead

statement.executeUpdate("INSERT INTO `" + table + "` (

Comments

0

in your values , password is not enclosed within a quote. Password is a String but your code is treating it as a number.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.