3

Making a small modular process in Java. However when i call the SQL statements from my website the system comes back with the error:

 java.sql.SQLSyntaxErrorException: Syntax error: Encountered ";" at line 8, column 2.

Below is the first few lines of the SQL file in question as well as the system im using for retrieving the data.

SQL:

 CREATE TABLE "chat_logs"
 (
   "logId" BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
   "logDate" INT DEFAULT NULL,
   "logUser" BLOB,
   "logMessage" BLOB,
   PRIMARY KEY ("logId")
 );

 CREATE TABLE "configs"
 (
   "configId" BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
   "configType" BLOB,
   "configSettings" BLOB,
   PRIMARY KEY ("configId")
 );

 ...

Retrieval Script:

 InputStream in = new URL("http://db.*******.com/derby/version1.00.sql").openStream();
 return org.apache.commons.io.IOUtils.toString(in);

It does not make sense as to how a syntax error can be generated from a place where a semicolon is supposed to be.

Addramyr
  • 254
  • 4
  • 11
  • 2
    *"It does not make sense as to how a syntax error can be generated from a place where a semicolon is supposed to be."* - But it >>does<< make sense if the semicolon is not supposed to be there! – Stephen C Jan 26 '16 at 00:55
  • Try to post the query statement – Abdelhak Jan 26 '16 at 00:58
  • If i post the statement as it is in RazorSQL or SQuirreL SQL it works fine. – Addramyr Jan 26 '16 at 01:00
  • Do i have to process each SQL statement individually? – Addramyr Jan 26 '16 at 01:00
  • 4
    If your code is trying to submit that SQL (the `CREATE` statements) using JDBC, then there should be one statement per JDBC `execute` and there should not be a semicolon after the `)`. – Stephen C Jan 26 '16 at 01:01
  • If you have a script full of SQL statements, and you want Derby to process those statements one-by-one, try this related question and answer: http://stackoverflow.com/questions/30183734/how-to-open-a-command-prompt-and-input-different-commands-from-a-java-program/30197374#30197374 ... and see also this related question: http://stackoverflow.com/questions/6042711/how-to-run-sql-scripts-in-order-to-update-a-derby-schema-from-java-code – Bryan Pendleton Jan 26 '16 at 14:38

1 Answers1

3

Yes you'll need to split the string into individual statements and run each separately:

String[] createTableStatements = allStatements.Split...
for (String createStatement : createTableStatements) {
    try (Statement ps = connection.createStatement()) {
        ps.executeUpdate(createStatement);
    }
}
Fidel
  • 7,027
  • 11
  • 57
  • 81