# SQL-Script Syntax Error (MySQL, JDBC, Spring)



## LivingHell (28. Aug 2012)

Hallo zusammen,

ich kämpfe zur Zeit an einem sehr, sehr komischen Syntax-Fehler...Genauer geht es darum,ein Skript einzulesen und es aus dem Programm heraus auszuführen.

Hier erstmal das Script zum löschen der Tabellen

```
USE testdb;
DROP TABLE Answers;
DROP TABLE UnitXScenario;
DROP TABLE TestUnit;
DROP TABLE Task;
DROP TABLE TestScenario;
DROP TABLE TestUnitSubCategory;
DROP TABLE TestUnitMainCategory;
```




```
@Component
public class DatabaseManagementDaoImpl extends SimpleJdbcDaoSupport implements
        IDatabaseManagementDao {
    
    private static final String TAG_NEW_LINE = "\n";

    public void dropTables(String ddlScrtiptPath) throws IOException{
        BufferedReader br = new BufferedReader(new FileReader(ddlScrtiptPath));
        StringBuilder sb = new StringBuilder();
        String line;
      
        while ((line = br.readLine()) != null) {
            sb.append(line).append(TAG_NEW_LINE);
        }
        br.close();
        getSimpleJdbcTemplate().update(sb.toString());

    }
}
```

beim Ausführen des Codes bekomme ich folgenden Fehler

```
Exception in thread "main" org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [USE testdb;
DROP TABLE Answers;
DROP TABLE UnitXScenario;
DROP TABLE TestUnit;
DROP TABLE Task;
DROP TABLE TestScenario;
DROP TABLE TestUnitSubCategory;
DROP TABLE TestUnitMainCategory;
]; nested exception is 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 ';
DROP TABLE Answers;
DROP TABLE UnitXScenario;
DROP TABLE TestUnit;
DROP TABLE ' at line 1
	at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:220)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:407)
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:458)
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:466)
	at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:474)
	at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:479)
	at org.springframework.jdbc.core.JdbcTemplate.queryForInt(JdbcTemplate.java:488)
	at org.springframework.jdbc.core.simple.SimpleJdbcTemplate.queryForInt(SimpleJdbcTemplate.java:119)
	at com.unitedinternet.skor.businesslogic.dao.DatabaseManagementDaoImpl.dropTables(DatabaseManagementDaoImpl.java:27)
	at com.unitedinternet.skor.businesslogic.management.DatabaseManagementManagerImpl.dropTables(DatabaseManagementManagerImpl.java:41)
	at com.unitedinternet.skor.businesslogic.management.SkorAdministrationManagerImpl.main(SkorAdministrationManagerImpl.java:62)
Caused by: 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 ';
DROP TABLE Answers;
DROP TABLE UnitXScenario;
DROP TABLE TestUnit;
DROP TABLE ' at line 1
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:525)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
	at com.mysql.jdbc.Util.getInstance(Util.java:386)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1053)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4074)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4006)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2468)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2629)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2713)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2663)
	at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1599)
	at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
	at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
	at org.springframework.jdbc.core.JdbcTemplate$1QueryStatementCallback.doInStatement(JdbcTemplate.java:443)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:396)
	... 9 more
```

Wie ihr sicher erkennen könnt, ist das Script korrekt. Squirrel/QuantumDB schluckt es ohne zu meckern.
Ich checke es einfach nicht, warum es nicht funktioniert.

Vielen Dank schonmal!


----------



## Marcinek (28. Aug 2012)

Weil du hier immer nur ein Statement absetzen kannst pro update()


----------



## SlaterB (28. Aug 2012)

> public class SimpleJdbcTemplate
> extends Object
> implements SimpleJdbcOperations
> 
> ...




die JDBC-Fehlermeldungen sind berühmt für ihre schlechte Verständlichkeit,
der Trick wäre hier zu erkennen, dass das 'USE testdb' akzeptiert, aber beim Semikolon danach schon gemerkt wird,

kein Semikolon, keine mehrere Befehle in einem SimpleJdbcTemplate,
entweder mehrere Ausführungen einzeln, ohne Semikolon, oder irgendwie nach anderen Mitteln schauen


----------



## LivingHell (29. Aug 2012)

Danke erstmal für die Antworten!

Ein neues Problem hat sich gerade aufgetan: 

Ich will einzelne Tabellen leeren mit dem Befehl 
	
	
	
	





```
DELETE FROM tabellen_name
```
.


```
getSimpleJdbcTemplate().update("DELETE FROM Answers");
        getSimpleJdbcTemplate()
                .update("ALTER TABLE Answers AUTO_INCREMENT = 0");
```
Dieser Aufruf geht wunderbar durch. Wenn ich mir jetzt allerdings eine Methode definiere, welche die Tabellennamen als Parameter übernimmt, und ich den dann als Parameter meinem Statement übergebe, bekomme ich erneut einen Fehler.


```
public void resetTable(String tableName){
        logger.info("Resetting table "+tableName+"...");
        getSimpleJdbcTemplate().update("DELETE FROM :table",new MapSqlParameterSource("table",tableName));
        getSimpleJdbcTemplate().update("ALTER TABLE :table AUTO_INCREMENT = 0",new MapSqlParameterSource("table",tableName));
        logger.info("Resetting table "+tableName+"...[done]");
    }
```

Fehler:

```
2012-08-29 11:47:29,636 INFO  [main] com.unitedinternet.skor.businesslogic.dao.DatabaseManagementDaoImpl: Resetting table TestUnitMainCategory...
Exception in thread "main" org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [DELETE FROM ?]; nested exception is 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 ''TestUnitMainCategory'' at line 1
	at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:220)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:607)
	at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:792)
	at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:815)
	at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.update(NamedParameterJdbcTemplate.java:214)
	at org.springframework.jdbc.core.simple.SimpleJdbcTemplate.update(SimpleJdbcTemplate.java:233)
	at com.unitedinternet.skor.businesslogic.dao.DatabaseManagementDaoImpl.resetTable(DatabaseManagementDaoImpl.java:31)
	at com.unitedinternet.skor.businesslogic.management.DatabaseManagementManagerImpl.resetTable(DatabaseManagementManagerImpl.java:36)
	at com.unitedinternet.skor.businesslogic.management.DatabaseManagementManagerImpl.main(DatabaseManagementManagerImpl.java:43)
Caused by: 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 ''TestUnitMainCategory'' at line 1
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:525)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
	at com.mysql.jdbc.Util.getInstance(Util.java:386)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1053)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4074)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4006)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2468)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2629)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2719)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2450)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2371)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2355)
	at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
	at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
	at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:798)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:591)
	... 7 more
```


----------



## SlaterB (29. Aug 2012)

mit PreparedStatement kann man nur Werte parametriesieren, "from Table x where feldY = :wert"
die Struktur an sich mit allen Tabellen und Feldern muss fest vorgegeben sein


----------

