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

LivingHell

Mitglied
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
SQL:
USE testdb;
DROP TABLE Answers;
DROP TABLE UnitXScenario;
DROP TABLE TestUnit;
DROP TABLE Task;
DROP TABLE TestScenario;
DROP TABLE TestUnitSubCategory;
DROP TABLE TestUnitMainCategory;



Java:
@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
Code:
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!
 
S

SlaterB

Gast
public class SimpleJdbcTemplate
extends Object
implements SimpleJdbcOperations

Java-5-based convenience wrapper for the classic Spring JdbcTemplate, taking advantage of varargs and autoboxing, and exposing only the most commonly required operations in order to simplify JdbcTemplate usage.

Use the getJdbcOperations() method (or a straight JdbcTemplate) if you need to invoke less commonly used template methods. This includes any methods specifying SQL types, methods using less commonly used callbacks such as RowCallbackHandler, updates with PreparedStatementSetters rather than argument arrays, and stored procedures as well as batch operations.


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

Mitglied
Danke erstmal für die Antworten!

Ein neues Problem hat sich gerade aufgetan:

Ich will einzelne Tabellen leeren mit dem Befehl
SQL:
DELETE FROM tabellen_name
.

Java:
 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.

Java:
  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:
Code:
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
 
S

SlaterB

Gast
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
 

Neue Themen


Oben