SQLSyntaxError: Reading queries from text file: derby

Question

I thought it would be fine to read sql queries from a text file. Here is what i did:

1. Get connection (seems fine)
2. Created Statement object (-do-)
3. Set autocommit to false (-do-)
4. Read queries into a Collection from the text file (-do-)
5. Problem: Using execute(String) in Statement...

After that i rollback and close the connection...

Here is my code: placed for now in a single class

    import java.sql.*;
    import java.util.*;
    import java.io.*;

   class Test2{
    private static void execute(Collection<String> queries,
        Statement stmt) throws SQLException{
        for(String query : queries){
            query.trim();
            stmt.execute(query);
        }
    }
    private static Collection<String> getQueries(String s)
        throws IOException{
        InputStream is = new BufferedInputStream
            (new FileInputStream(s));

        int content = 0;
        StringBuilder sb = new StringBuilder();
        while((content = is.read()) != -1){
            sb.append((char)content);
        }

        String s2 = sb.toString();
        Collection<String> queries = Arrays.asList(s2.split(";"));
        return queries;
    }
    private static Connection getConnection()
        throws SQLException{
        Connection conn = DriverManager
            .getConnection("jdbc:derby:testDB", 
            "", "");

        return conn;
    }
    private static void display(ResultSet set, int columnNos) 
        throws SQLException{
        while(set.next()){
            int i = 1;
            while(i <= columnNos){
                String s = set.getString(i);
                System.out.println(s + " ");
                i++;
            }
        }
    }
    public static void main(String args[]) throws Exception{
        Connection conn = getConnection();          
        Statement stmt = conn.createStatement();
        conn.setAutoCommit(false);
        Collection<String> queries = getQueries("queries.txt");
        System.out.println(queries);
        try{
            execute(queries, stmt);
        }
        catch(Exception e){
            System.out.println(e);
            conn.rollback();
        }
        conn.close();
    }
}

Am I overlooking something? Queries passed usually as a String are working fine...

[Create table test(message varchar(20)),
Insert into test values('Hello World'),
Select * from test,
Drop table test,
]
java.sql.SQLSyntaxErrorException: Syntax error: Encountered "<EOF>" at line 1, c
olumn 2.

Answer 1

Your logs show that the list of queries contains a blank string as its last element. A blank String is not a valid SQL query.

Just an advice: this is the kind of problem that is trivial to find using a debugger. Learn using it. It's quite easy and intuitive, and it will save you days and days of your life.

Also, query.trim(); doesn't do anything useful. It needs to be replaced by query = query.trim();.