Syntax error: Encountered ";"

Question

I'm trying to output my tic tac toe game results to a database. However I keep running into this error whenever a match between two clients is completed. Here's an excerpt of my code from the server side and the error message:

The Database class:

    import java.sql.*;
    import java.util.*;
    import javax.swing.JOptionPane;

    class Database {

final String JDBC_DRIVER = "org.apache.derby.jdbc.ClientDriver";
final String DB_URL = "jdbc:derby://localhost:1527/Mini Project";

//  Database credentials
final String USER = "Joe";
final String PASS = "plateau";

Connection conn; 
Statement stmt;

public Database() {

    try {
        Class.forName("org.apache.derby.jdbc.ClientDriver");

        System.out.println("Connecting to database...");
        conn = DriverManager.getConnection(DB_URL, USER, PASS);

        //STEP 4: Execute a query
        System.out.println("Creating statement...");
        stmt = (Statement) conn.createStatement();

    } catch (SQLException ex) {
        ex.printStackTrace();

    } catch (ClassNotFoundException ex) {
        ex.printStackTrace();

    }

}

public User getUser(String userName) {

    User user = null;

    try {
        ResultSet resultSet = stmt.executeQuery("SELECT * FROM PLAYERS");

        while (resultSet.next()) {
            //Retrieve by column name
            user = new User(resultSet.getInt(1),
                    resultSet.getString(2),
                    resultSet.getString(3),
                    resultSet.getString(4),
                    resultSet.getString(5),
                    resultSet.getString(6)); 
        }

        resultSet.close(); 

    } catch (SQLException ex) {
        ex.printStackTrace();
        System.err.println(ex.toString());
    }

    return user;

}

public Boolean insertPlayer(User player) {

    Boolean isAdded = false;

    int DUPLICATE_PRIMERY_KEY_ERROR_CODE = 1062;

    String sql = "INSERT INTO PLAYERS(Name,Surname,Username,Password,Email) VALUES('" 
            + player.getName() + "','" + player.getSurname() + "','" + player.getUsername() + "','"
            + player.getPassword() + "','" + player.getEmail() + "','" 
            + "','" + "');";

    try {
        stmt.execute(sql);
        isAdded = true;

    } catch (SQLException ex) {

        isAdded = false;
        ex.printStackTrace();
        System.err.println(ex.toString());

        if (ex.getErrorCode() == DUPLICATE_PRIMERY_KEY_ERROR_CODE) {
            JOptionPane.showMessageDialog(null, "Username with or email already exist");
        }

    }

    return isAdded;
}

public void insertGameResult(Results result) {

    String igr = "INSERT INTO RESULTS(Name1, Surname1, Name2, Surname2, Winner) VALUES('" 
            + result.getName1() + "','" + result.getSurname1() + "','" + result.getName2() + "','" 
            + result.getSurname2() + "','" + result.getWinner() + "');";

    try {
        Boolean added = stmt.execute(igr);                                                   
        if (added) {
            JOptionPane.showMessageDialog(null, "Result added successfuly");
        }
    } catch (SQLException ex) {

        ex.printStackTrace();
        System.err.println(ex.toString());

    }

}
    }

The Player class:

    class Player extends Thread {

    Socket insocket;
    Socket outSocket;

    public Player(Socket sock, Socket opnSock) {
        this.insocket = sock;
        this.outSocket = opnSock;

    }

    @Override
    public void run() {

        try {

            while (true) {

                InputStream is = insocket.getInputStream();
                InputStreamReader isr = new InputStreamReader(is);
                BufferedReader br = new BufferedReader(isr);

                String readData = br.readLine();

                OutputStream os = outSocket.getOutputStream();
                PrintWriter pw = new PrintWriter(os, true);
                pw.println(readData);
                // logTextArea.append(Rdata + "\n");

                if (readData.contains("winner*")) {

                    String[] splitData = readData.split(",");
                    String player1 = splitData[1];
                    String player2 = splitData[2];
                    int winner = Integer.valueOf(splitData[3]);

                    win += 1;
                    if (win == 2) {
                        saveResult(player1, player2, winner);

                        if (winner == 1) {
                            winner = 2;
                        } else if (winner == 2) {
                            winner = 1;
                        }

                        saveResult(player2, player1, winner);
                        win = 0;
                    }

                }
            }
        } catch (Exception ex) {
            ex.printStackTrace();
            jtaLog.append(ex.toString() + "\n");
        }
    }
}

The save result method under the main class:

    public void saveResult(String username1, String username2, int winner) {

    User p1 = database.getUser(username1);
    User p2 = database.getUser(username2);

    Results result = new Results(0, p1.getName(), p1.getSurname(), p2.getName(), p2.getSurname(), winner);
    database.insertGameResult(result); 
    }

Here's the error message I am getting:

java.sql.SQLSyntaxErrorException: Syntax error: Encountered ";" at line 1, column 98.
    at org.apache.derby.client.am.SQLExceptionFactory.getSQLException(Unknown Source)
    at org.apache.derby.client.am.SqlException.getSQLException(Unknown Source)
    at org.apache.derby.client.am.ClientStatement.execute(Unknown Source)
    at tictactoeserver.Database.insertGameResult(Database.java:106)
    at tictactoeserver.TicTacToeServer.saveResult(TicTacToeServer.java:200)
    at tictactoeserver.TicTacToeServer$Player.run(TicTacToeServer.java:317)
Caused by: ERROR 42X01: Syntax error: Encountered ";" at line 1, column 98.
    at org.apache.derby.client.am.ClientStatement.completeSqlca(Unknown Source)
    at org.apache.derby.client.am.ClientStatement.completeExecuteImmediate(Unknown Source)
    at org.apache.derby.client.net.NetStatementReply.parseEXCSQLIMMreply(Unknown Source)
    at org.apache.derby.client.net.NetStatementReply.readExecuteImmediate(Unknown Source)
    at org.apache.derby.client.net.StatementReply.readExecuteImmediate(Unknown Source)
    at org.apache.derby.client.net.NetStatement.readExecuteImmediate_(Unknown Source)
    at org.apache.derby.client.am.ClientStatement.readExecuteImmediate(Unknown Source)
    at org.apache.derby.client.am.ClientStatement.flowExecute(Unknown Source)
    at org.apache.derby.client.am.ClientStatement.executeX(Unknown Source)
    ... 4 more
java.sql.SQLSyntaxErrorException: Syntax error: Encountered ";" at line 1, column 98.

What does Encountered ";" mean, and what can I do to rectify it?

You want us to read 100's of rows to find your syntax error?!? — jarlh, Apr 25 '16 at 13:35
Why don't you just print out the sql statements before you execute them? would make debugging a lot easier. — OH GOD SPIDERS, Apr 25 '16 at 13:35
Please, please rewrite `public Boolean insertPlayer(User player)` and `public void insertGameResult(Results result)` using `PreparedStatement` and `setString()` — Stavr00, Apr 25 '16 at 13:56

score 8 · Accepted Answer · edited May 23 '17 at 12:31

8

What does Encountered ";" mean, and what can I do to rectify it?

It means that your SQL statement has an additional ; character where the SQL parser does not expect one. While you usually terminate SQL commands with a ; in interactive SQL shells, you must not add them when executing SQL commands through JDBC.

In your case, in your INSERT statements, remove the trailing ;, for example in the insertPlayer() method:

...
+ "','" + "');";
             ^

The same is true for your insertGameResult() method (this is the one which the stack trace actually shows):

...  
+ result.getSurname2() + "','" + result.getWinner() + "');";
                                                         ^

Also, as an additional note, do not use String concatenation when building SQL statements. This makes the statements vulnerable to SQL injections. Use bind variables instead.

edited May 23 '17 at 12:31

Community

1
1

answered Apr 25 '16 at 13:36

Andreas Fester

34,015
7
86
113

yes, I have taken off the trailing ; and now its showing me something like java.sql.SQLIntegrityConstraintViolationException: Column 'GAME_NO' cannot accept a NULL value. GAME_NO is one of the columns in my results table. – Azaan Apr 25 '16 at 13:57
You may want to alter your table to add `GAME_NO INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1)` so the database puts in the game number for you. Otherwise you'll have to add the game number the `Results` class. – Stavr00 Apr 25 '16 at 14:01
You have defined `GAME_NO` to be `NOT NULL`, but obviously you are not inserting a value into that column (it is not listed in your `INSERT` statement). Either modify the table and remove the `NOT NULL` constraint or add the column to your `INSERT` statement. – Andreas Fester Apr 25 '16 at 14:02

Syntax error: Encountered ";"

1 Answers1