Search code examples
javasqlderby

SQL not adding file contents to table

Currently I am trying to pass new data to an SQL table via Apache Derby. I am to read from a file with given text, & add each token read to its respective column.

However, upon trying to do this, I'm getting numerous errors indicating that the token is not a column... But this is not what I am trying to do.

carmpg.txt contents:

Toyota  Prius   52  23475
Kia Optima  31  22600
Hyundai     Sonata  31  22050
Nissan  Altima  31  23260
Chevrolet   Malibu  30  21680
Honda   Accord  30  23570
Mazda   6   29  21945
Subaru  Legacy  29  22195
Toyota  Camry   27  23495
Chrysler    200 27  22115
Ford    Fusion  27  22120
Volkswagen  Passat  27  22995
Volkswagen  CC  25  34475
Chevrolet   Impala  25  27895
Buick   LaCrosse    25  29565
Nissan  Maxima  25  33270
Buick   Regal   24  27065
Lincoln MKZ 26  3560

So, Toyota should go into the Manufacturer column, Prius should go into the Model column, and so on.

Here is the code: 

 try(Connection conn = SimpleDataSource.getConnection();Statement stat = 
conn.createStatement()) 
  {
     try
     {
        stat.execute("DROP TABLE Car");
     }
     catch(SQLException e)
     {
         // get exception if table doesn't exist yet
     }

     stat.execute("CREATE TABLE Car (Manufacturer VARCHAR(20),"
           + "Model VARCHAR(20), " + "Efficiency DECIMAL(6,2), " + "Price DECIMAL(8,2)) ");


     String inputFileName = "carmpg.txt";
     File inputFile = new File(inputFileName);
     Scanner in = new Scanner(inputFile);

     // COMPLETE THIS WHILE LOOP to insert all cars from the input text file
     while (in.hasNextLine()){

         stat.execute("INSERT INTO Car VALUES " + in.next());


     }

     ResultSet rs = stat.executeQuery("SELECT * FROM Car");
     rs.next();
     System.out.println(rs.getString("Model"));

And the related stacktrace:

Exception in thread "main" java.sql.SQLSyntaxErrorException: Column 'TOYOTA' 
is either not in any table in the FROM list or appears within a join 
specification and is outside the scope of the join specification or appears 
in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or 
ALTER TABLE  statement then 'TOYOTA' is not a column in the target table.
at org.apache.derby.impl.jdbc.SQLExceptionFactory.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 CarDB.main(CarDB.java:49)
Caused by: ERROR 42X04: Column 'TOYOTA' is either not in any table in the 
FROM list or appears within a join specification and is outside the scope of 
the join specification or appears in a HAVING clause and is not in the GROUP 
BY list. If this is a CREATE or ALTER TABLE  statement then 'TOYOTA' is not a 
column in the target table.
at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
at org.apache.derby.impl.sql.compile.ColumnReference.bindExpression(Unknown 
Source)
at org.apache.derby.impl.sql.compile.ColumnReference.bindExpression(Unknown 
Source)
at org.apache.derby.impl.sql.compile.ResultColumn.bindExpression(Unknown 
Source)
at org.apache.derby.impl.sql.compile.ResultColumnList.bindExpressions(Unknown 
Source)
at org.apache.derby.impl.sql.compile.RowResultSetNode.bindExpressions(Unknown 
Source)
at org.apache.derby.impl.sql.compile.DMLStatementNode.bindExpressions(Unknown 
Source)
at org.apache.derby.impl.sql.compile.InsertNode.bindStatement(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)
... 3 more

Thanks for any help

Solution

  • The syntax of the INSERT INTO statement is: 

    INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

    Also your mistake is that you try to insert the whole line from the text file without splitting it into 4 parts which represent the 4 different columns.
    The error you get is triggered because you try to execute this invalid statement: 

    INSERT INTO Car VALUES Toyota Prius   52  23475

    it should be 

    INSERT INTO Car (Manufacturer, Model, Efficiency, Price) VALUES ('Toyota', 'Prius', 52, 23475)