what did I do wrong with java and sql database?

Question

I'm trying to create a table with jdbc:derby database.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

public class DBCONNECT {

    Connection con;

    public DBCONNECT()
    {
        connect();
    }

    private void connect()
    {
        try {             
            con= DriverManager.getConnection("jdbc:derby:relative/db/dubbydata.db;create=true;","APP","");
            PreparedStatement st= con.prepareStatement("SELECT * FROM WF");
            ResultSet res=st.executeQuery();
            while(res.next()){}
            st.close();
            System.out.println("dubbyloop.DBCONNECT.connect()");
        } catch (SQLException ex) {          
            try { 
                 Statement stmt = con.createStatement();
                 stmt.executeQuery("CREATE TABLE WF (FILEPATH CHAR(254) NOT NULL, FILESIZE LONG VARCHAR, WFDATA CLOB(2147483647), PRIMARY KEY (FILEPATH));");
                 System.out.println("CREATED");
            } catch (SQLException ex1) {
                Logger.getLogger(DBCONNECT.class.getName()).log(Level.SEVERE, null, ex1);
            }

            System.out.println(ex);
        }
    }



}

But i always get this error:

SCHWERWIEGEND: null
java.sql.SQLSyntaxErrorException: Syntaxfehler: Encountered ";" at line 1, column 118.
    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.executeQuery(Unknown Source)
    at myproject.DBCONNECT.connect(DBCONNECT.java:48)

I have no idea what i'm doing wrong. i changed everything in the create query.I removed the semikolon.i removed the size of the Clob or even the whole Clob.It doesn't matter what i do i get always the same error with no idea what is wrong.

Answer 1

    stmt.executeUpdate("CREATE TABLE WF ("
            + "FILEPATH VARCHAR(254) NOT NULL, "
            + "FILESIZE LONG, "
            + "WFDATA CLOB(2147483647), "
            + "PRIMARY KEY (FILEPATH))");

There was one VARCHAR too much, semicolon not needed. CHAR is padded with blanks, so better VARCHAR.

And @MickMnemonic rightly mentioned one should call executeUpdate instead of executeQuery.