I am trying to create a table using java derby EmbeddedDriver but i don't understand what these errors mean?

Question

errors:

run:
Loaded the appropriate driver
Created and connected to database localDB2

----- SQLException -----
  SQL State:  42X01
  Error Code: 30000
  Message:    Syntax error: Encountered "user" at line 1, column 14.

___ DBTest completed ___
java.sql.SQLSyntaxErrorException: Syntax error: Encountered "user" at line 1, column 14.
    at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
    at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)
    at localDataBase.start(localDataBase.java:56)
    at localDataBase.main(localDataBase.java:17)
Caused by: java.sql.SQLException: Syntax error: Encountered "user" at line 1, column 14.
    at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
    ... 10 more
Caused by: ERROR 42X01: Syntax error: Encountered "user" at line 1, column 14.
    at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
    at org.apache.derby.impl.sql.compile.ParserImpl.parseStatement(Unknown Source)
    at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
    at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
    at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown Source)
    ... 4 more
BUILD SUCCESSFUL (total time: 0 seconds)

Code:

import java.sql.*;
import java.util.ArrayList;
import java.util.Properties;
import java.util.Date;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Calendar;

public class localDataBase {
    private String driver =   "org.apache.derby.jdbc.EmbeddedDriver";
    private String protocol = "jdbc:derby:";
    private String dbName =   "localDB2";
    private DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
    private Date date = new Date();
    private Calendar cal = Calendar.getInstance();
    public static void main(String[] args) {
        new localDataBase().start();
        System.out.println("\n___ DBTest completed ___");
    }

    void start() {
    // Load derby driver to start derby engine 
    try {
        Class.forName(driver);
            System.out.println("Loaded the appropriate driver");
    } catch (ClassNotFoundException cnfe) {
            System.err.println("\nUnable to load the JDBC driver " + driver);
            System.err.println("Please check your CLASSPATH.");
            cnfe.printStackTrace(System.err);
        System.exit(1);
        }

    // establishing a connection to it

    // Initializers; declared and set to nulls before the try-block
    // to be accessible after the try-block scope
    Connection conn = null;
    PreparedStatement psInsertUser = null;
        PreparedStatement psInsertAdmin = null;
        PreparedStatement psInsertBill = null;
    PreparedStatement psUpdate = null;
    Statement stmt = null;
    ResultSet rs = null;

    try {

        conn = DriverManager.getConnection(protocol + dbName +
                           ";create=true", "test", "test");

            System.out.println("Created and connected to database " + dbName);

            stmt = conn.createStatement();


            // Create User table
            stmt.execute("create table user(" +
             "id int, " +
             "firstName varchar(20), " +
             "lastName varchar(20)" +
                         "userName varchar(20))");

            System.out.println("\n___ Created table user ___");

            // Create Administrator table
            stmt.execute("create table administrator(" +
             "id int, " +
             "firstName varchar(20), " +
             "lastName varchar(20)" +
                         "userName varchar(20))");

            System.out.println("\n___ Created administrator user ___");

            // Create Bill table
            stmt.execute("create table bill(" +
                         "yearOfProduction int, " +
             "serialNumber varchar(20), " +
                         "value int, " +
             "location varchar(20)" +
                         "dateAndTimeCounted varchar(20))");

            System.out.println("\n___ Created table bill ___");

            //PreparedStatement to insert into User table
            psInsertUser = conn.prepareStatement
        ("insert into user values (?, ?, ?, ?)");

            // User1 Data
            psInsertUser.setInt(1, 1);  //insert ID
        psInsertUser.setString(2, "Davy ");    //insert firstName
        psInsertUser.setString(3, "Jones");    //insert lastName
            psInsertUser.setString(3, "User1");    //insert userName
            psInsertUser.executeUpdate();

        System.out.println("\n ____ Inserted: User, Davy Jones");



            //PreparedStatement to insert into Administrator table
            psInsertAdmin = conn.prepareStatement
        ("insert into administrator values (?, ?, ?, ?)");

            // Admin1 Data
            psInsertAdmin.setInt(1, 1);  //insert ID
        psInsertAdmin.setString(2, "Cutler ");    //insert firstName
        psInsertAdmin.setString(3, "Beckett");    //insert lastName
            psInsertAdmin.setString(3, "Admin1");    //insert userName
            psInsertAdmin.executeUpdate();

        System.out.println("\n ____ Inserted: Administrator, Cutler Beckett");


            //PreparedStatement to insert into Bill table
            psInsertBill = conn.prepareStatement
        ("insert into bill values (?, ?, ?, ?, ?)");

            // Bill1 Data
            psInsertBill.setInt(1, 2011);  //insert year of production
        psInsertBill.setString(2, "XY3215616 ");    //insert serial number
        psInsertBill.setInt(3, 50);    //insert value
            psInsertBill.setString(4, "Jordan-Irbid-JUST-CairoAmmanBank");    //insert         Location
            psInsertBill.setString(5, dateFormat.format(cal.getTime()).toString()); //insert date and time
            psInsertBill.executeUpdate();

        System.out.println("\n ____ Inserted: Bill, XY3215616 50");



        //Show user table data
        rs = stmt.executeQuery
        ("SELECT * FROM user order by id");

        System.out.printf("\n\n%4s%20s%20s%20s\n",
                   "Id", "First Name", "Last Name", "User Name");
        while (rs.next())
        System.out.printf("%4d%20s%20s%20s\n",
                   rs.getInt("id"),
                   rs.getString("firstName"),
                                   rs.getString("lastName"),
                                   rs.getString("userName"));

            //Show administrator table data
        rs = stmt.executeQuery
        ("SELECT * FROM administrator order by id");

        System.out.printf("\n\n%4s%20s%20s%20s\n",
                   "Id", "First Name", "Last Name", "User Name");
        while (rs.next())
        System.out.printf("%4d%20s%20s%20s\n",
                   rs.getInt("id"),
                   rs.getString("firstName"),
                                   rs.getString("lastName"),
                                   rs.getString("userName"));

            //Show user bill data
        rs = stmt.executeQuery
        ("SELECT * FROM bill order by serialNumber");

        System.out.printf("\n\n%4s%20s%20s%20s\n",
                   "year Of Production", "Serial Number", "Value", "Location", "date and time");
        while (rs.next())
        System.out.printf("%4d%20s%20s%20s%20s\n",
                   rs.getInt("id"),
                   rs.getString("firstName"),
                                   rs.getInt("value"),
                                   rs.getString("location"),
                                   rs.getString("dateAndTimeCounted"));


        // Commit the transaction. Any changes will be persisted to the database now.
            conn.commit();
            System.out.println("Committed the transaction");

        try {
        // the shutdown=true attribute shuts down Derby
        DriverManager.getConnection("jdbc:derby:;shutdown=true");
        System.out.println("Derby shut down normally");
        }
        catch (SQLException se) {
        System.err.println("Derby did not shut down normally");
        }
    }
    catch (SQLException sqle) {
            printSQLException(sqle);
        } finally {
            // release all open resources to avoid unnecessary memory usage
            // Connection
            try {
                if (conn != null) {
                    conn.close();
                    conn = null;
                }
            } catch (SQLException sqle) {
                printSQLException(sqle);
            }
        }
    } // start()

    public static void printSQLException(SQLException e) {
    System.err.println("\n----- SQLException -----");
    System.err.println("  SQL State:  " + e.getSQLState());
    System.err.println("  Error Code: " + e.getErrorCode());
    System.err.println("  Message:    " + e.getMessage());
    // for stack traces, you may refer to derby.log
    e.printStackTrace(System.err);
    }
}

`user` is a reserved word. Try a different name, e.g. `users` — , Mar 25 '14 at 20:01
i tried userLocal now it says userName is the issue, changed that to userNameLocal and that did not fix it. — user3378922, Mar 25 '14 at 20:16
You are missing a comma before the `username` column. Really: run your statements in a SQL client before putting them into the java code. — , Mar 25 '14 at 20:59

score 1 · Answer 1 · answered Mar 01 '19 at 09:14

1

I had the same issue. My issue was resolved when I changed the table name to User1 from user. User is a reserved keyword so it will throw error in Derby but on MySql workbench it will work fine because it uses'' as escaping sequence.

answered Mar 01 '19 at 09:14

Kshitiz Bathwal

87
1
10

Eagle_Eye · Answer 2 · 2015-05-12T13:23:05.113

0

There are many errors in this code.

Forgot ',' in Create Table Statements.
Can not use 'user' as it's a reserved word.
Set the length of the columns 20 and you tried to insert data greater than that.
Used wrong column names which does not exist in the table.

edited May 12 '15 at 13:23

answered May 12 '15 at 12:54

Eagle_Eye

1,044
1
14
26

I am trying to create a table using java derby EmbeddedDriver but i don't understand what these errors mean?

2 Answers2