CODEWITHSUNDEEP
javamysql
ASLilly
ASLilly

Reputation: 39

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

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.

Upvotes: 2

Views: 1479

Answers (2)

HectorLector
HectorLector

Reputation: 1911

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();

Upvotes: 0

Gray
Gray

Reputation: 116888

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();

Upvotes: 3

Related Questions