The SQL Script should have comments starting with - or -- only on new lines and each command should end with a ; .
Reading a sql file and putting all of it in a string variable and feeding to the execute command would result in an exception. All instructions must be executed individually. A proper sql script should have all instructions starting on a newline and all comments on a newline. Start with an empty string. Start reading the lines ; if a line has a - as the first character then continue else add it to the query string. Now if the last character of the query string is a ; then a command is complete so execute the query and make the query string empty and loop up. Thats it
Executing an sql script in jdbc with the following code should work ...
//Now read line bye line
String thisLine, sqlQuery;
try {
sqlQuery = "";
while ((thisLine = d.readLine()) != null)
{
//Skip comments and empty lines
if(thisLine.length() > 0 && thisLine.charAt(0) == '-' || thisLine.length() == 0 )
continue;
sqlQuery = sqlQuery + " " + thisLine;
//If one command complete
if(sqlQuery.charAt(sqlQuery.length() - 1) == ';') {
sqlQuery = sqlQuery.replace(';' , ' '); //Remove the ; since jdbc complains
try {
stmt.execute(sqlQuery);
}
catch(SQLException ex) {
JOptionPane.showMessageDialog(null, "Error Creating the SQL Database : " + ex.getMessage());
}
catch(Exception ex) {
JOptionPane.showMessageDialog(null, "Error Creating the SQL Database : " + ex.getMessage());
}
sqlQuery = "";
}
}
}
catch(IOException ex) {
}
catch(Exception ex) {
JOptionPane.showMessageDialog(null, "Error Creating the SQL Database : " + ex.getMessage());
}
where d could be BufferedReader and c could be a Statement object.
