How to execute all sql scripts in a Folder using Java?

Question

I want to execute all sql-scripts in one folder via Java. It does not work because earlier it created a new schema (named like the username) and did not take the one written in the sql script. And now i get 44x the Exception showed below. Connection is working. Oh and im using a DB2.

I have tried the following approach:

public void sqlScripts() throws IOException, SQLException {

        File folder = new File("E:/maProject/sql");
        File[] listOfFiles = folder.listFiles();

        for (File file : listOfFiles) {

            BufferedReader reader = null;
            Statement statement = null;

            try {
                statement = con.createStatement();
                reader = new BufferedReader(new FileReader(file));
                statement.execute(String.valueOf(file));

            } catch (Exception e) {
                e.printStackTrace();

            }
        }
    }

This is the Exception i get 44x:

com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-7, SQLSTATE=42601, SQLERRMC=\;E:, DRIVER=4.26.14

What am I doing wrong?

Thanks in advance

duplicat of https://stackoverflow.com/questions/2071682/how-to-execute-sql-script-file-in-java — Vikram Singh, Apr 30 '20 at 07:25
Is it necessarily to run these files with your java code? Are you allowed to run other java-based tool for that? — Mark Barinstein, Apr 30 '20 at 11:42
You are trying to execute the file _name_ instead of its contents. — mustaccio, Apr 30 '20 at 12:49
@MarkBarinstein i just need to do it in java. The rest ist up to me how i do it. Any suggestions :D — Trinity, May 01 '20 at 11:06
`IBM Data Server Driver Package` contains JDBC-based tool called [CLPPlus](https://www.ibm.com/support/knowledgecenter/SSEPGG_11.5.0/com.ibm.swg.im.dbclient.clpplus.doc/doc/c0053823.html). It has an ability to execute scripts. Check it. — Mark Barinstein, May 01 '20 at 12:48

score 0 · Answer 1 · answered Apr 30 '20 at 09:11

SQLCODE=7 is SQL0007N

The description of the error message is

SQL0007N The statement was not processed because a character that is not supported in SQL statements was included in the SQL statement.

The specified character is not a valid character in SQL statements. The "" runtime token indicates the 20 characters of the SQL statement that preceded the character that is not valid.

So your invalid character is (close to) this text \;E:

I suspect that you are trying to run two statements in one statement. As per e.g https://stackoverflow.com/a/2071724/9525344 you need to execute the statements one at a time

score 0 · Accepted Answer · answered May 06 '20 at 10:47

As @mustaccio said, I tried to execute the file name instead of the contents of the file. So now what I do instead is this:

 public void sqlScripts() {

        try {
            File folder = new File("E:/maProject/sql");
            File[] listOfFiles = folder.listFiles();

            for (File file : listOfFiles) {
                ScriptRunner scriptRunner = new ScriptRunner(con, false, false);

                // Give the input file to Reader
                Reader reader = new BufferedReader(new FileReader(file));
                scriptRunner.runScript(reader);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

How to execute all sql scripts in a Folder using Java?

2 Answers2