How can I run a script file using the "source" command?

Question

I am coding an application that connects to a localhost. When the application first runs I want it to initialize the Database using this method:

public void initDataBase() {
    try {
        Statement stm = con.createStatement();
        stm.executeQuery("source shippingSQLscript.sql");
    } catch (SQLException ex) {
        ex.printStackTrace();
    }
}

Where shippingSQLscript.sql contains the correct sql statements to insert all the data. However when I run it the method throws:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 
You have an error in your SQL syntax; check the manual that corresponds to your 
MySQL server version for the right syntax to use near 'source shippingSQLscript.sql'
at line 1

I have tried using stm.execute() as well but have had the same result.

I think the string you pass in has to be the actual SQL. Try looking for another call which takes the filename perhaps and just pass in the filename portion. could be that based on where the file is located and the current directory when the code runs that it can't find the file. — Nerdtron
– Nerdtron, Commented Dec 8, 2011 at 20:36
I like the idea of having a "source like" command method in Statement which would convert sql text into Java stm.execute()s. I wish I could have found one, but there isn't any :( No matter I can make a method like it myself. — ASLilly
– ASLilly, Commented Dec 8, 2011 at 20:48
check here also, sql->string using apache.common.IOutils, coreyhulen.org/2010/04/07/run-a-sql-script-for-mysql-using-java — HRgiger
– HRgiger, Commented Dec 8, 2011 at 23:31

Gray · Accepted Answer · 2011-12-08 20:56:20Z

You cannot do this with the JDBC driver. source is only a command supported by the MySQL command line tool. See here:

http://forums.mysql.com/read.php?39,406094,406329#msg-406329

Here's the list of commands for the command-line tool. Most are not supported as JDBC query statements.

http://dev.mysql.com/doc/refman/5.5/en/mysql-commands.html

You will have to load your SQL commands from the file in your code and send them to JDBC execute methods. Something like:

Statement stm = con.createStatement();
BufferedReader reader = new BufferedReader(new FileReader(new File(...)));
while (true) {
    String line = reader.readLine();
    if (line == null) {
        break;
    }
    // this is the trick -- you need to pass different SQL to different methods
    if (line.startsWith("SELECT")) {
        stm.executeQuery(line);
    } else if (line.startsWith("UPDATE") || line.startsWith("INSERT")
        || line.startsWith("DELETE")) {
        stm.executeUpdate(line);
    } else {
        stm.execute(line);
    }
}
stm.close();

HectorLector · Accepted Answer · 2011-12-08 23:04:29Z

I used something like this...it works if there is a sql command over several lines. Also there may be problems if the sql-file is too big, for me it worked fine.

  BufferedReader br = new BufferedReader(new FileReader(DBFILE));
  statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
  System.out.println("Reading SQL File...");
  String line="";
  StringBuilder sb = new StringBuilder();

  while( (line=br.readLine())!=null)
  {
     if(line.length()==0 || line.startsWith("--"))
     {
        continue;
     }else
     {
        sb.append(line);
     } 

     if(line.trim().endsWith(";"))
     {
        statement.execute(sb.toString());
        sb = new StringBuilder();
     }

  }
  br.close();

Collectives™ on Stack Overflow

How can I run a script file using the "source" command?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related