Sql error or missing database near syntax error

INFO: beforeExecuteUpdate org.sqlite.jdbc4.JDBC4Statement@29176cc1 sql:
drop table if exists QueryItem 
Sep 06, 2018 1:49:41 PM io.requery.sql.LoggingListener afterExecuteUpdate
INFO: afterExecuteUpdate 0
Sep 06, 2018 1:49:41 PM io.requery.sql.LoggingListener beforeExecuteUpdate
INFO: beforeExecuteUpdate org.sqlite.jdbc4.JDBC4Statement@29176cc1 sql:
drop table if exists Beer 
Sep 06, 2018 1:49:41 PM io.requery.sql.LoggingListener afterExecuteUpdate
INFO: afterExecuteUpdate 0
Sep 06, 2018 1:49:41 PM io.requery.sql.LoggingListener beforeExecuteUpdate
INFO: beforeExecuteUpdate org.sqlite.jdbc4.JDBC4Statement@29176cc1 sql:
drop table if exists Details 
Sep 06, 2018 1:49:41 PM io.requery.sql.LoggingListener afterExecuteUpdate
INFO: afterExecuteUpdate 0
Sep 06, 2018 1:49:41 PM io.requery.sql.LoggingListener beforeExecuteUpdate
INFO: beforeExecuteUpdate org.sqlite.jdbc4.JDBC4Statement@29176cc1 sql:
create table Details (id integer primary key autoincrement not null, description varchar(255), details varchar(255), productID varchar(255), stock integer not null)
Sep 06, 2018 1:49:41 PM io.requery.sql.LoggingListener afterExecuteUpdate
INFO: afterExecuteUpdate 0
Sep 06, 2018 1:49:41 PM io.requery.sql.LoggingListener beforeExecuteUpdate
INFO: beforeExecuteUpdate org.sqlite.jdbc4.JDBC4Statement@29176cc1 sql:
create table Beer (id integer autoincrement not null, parseDate integer not null, details integer, image varchar(255), name varchar(255), price float not null, url varchar(255), foreign key (details) references Details (id) on delete cascade, primary key (id, parseDate))
Exception in thread "main" io.requery.sql.TableModificationException: org.sqlite.SQLiteException: [SQLITE_ERROR] SQL error or missing database (near "autoincrement": syntax error)
	at io.requery.sql.SchemaModifier.createTables(SchemaModifier.java:162)
	at io.requery.sql.SchemaModifier.createTables(SchemaModifier.java:128)
	at MainKt.main(main.kt:18)
Caused by: org.sqlite.SQLiteException: [SQLITE_ERROR] SQL error or missing database (near "autoincrement": syntax error)
	at org.sqlite.core.DB.newSQLException(DB.java:909)
	at org.sqlite.core.DB.newSQLException(DB.java:921)
	at org.sqlite.core.DB.throwex(DB.java:886)
	at org.sqlite.core.NativeDB.prepare_utf8(Native Method)
	at org.sqlite.core.NativeDB.prepare(NativeDB.java:127)
	at org.sqlite.core.DB.prepare(DB.java:227)
	at org.sqlite.jdbc3.JDBC3Statement.execute(JDBC3Statement.java:60)
	at io.requery.sql.SchemaModifier.createTables(SchemaModifier.java:153)
	... 2 more

    val datasource = SQLiteDataSource()
    datasource.url = "jdbc:sqlite:beers.db"

    val config = ConfigurationBuilder(datasource, Models.DEFAULT)
            .useDefaultLogging()
            .build()

    val tables = SchemaModifier(config)
    tables.createTables(TableCreationMode.DROP_CREATE)

    val dataStore = KotlinEntityDataStore<Persistable>(config)
}

interface Beer : Persistable {

    @get:Key
    @get:Generated
    var id: Int

    @get:Key
    var parseDate: Long

    var price: Float
    var name: String
    var url: String
    var image: String

    @get:OneToOne(mappedBy = "beer")
    @get:ForeignKey
    var details: Details

    @get:OneToMany(mappedBy = "beer")
    var queryResults: Set<QueryItem>
}

@Entity
interface Details : Persistable {
    @get:Key
    @get:Generated
    var id: Int

    @get:OneToOne(mappedBy = "details")
    var beer: Beer

    var productID: String
    var stock: Int
    var description: String
    var details: MutableMap<String, String>
}

@Entity
interface QueryItem : Persistable {

    @get:Key
    @get:Generated
    var id: Int

    @get:Key
    var queryDate: Long

    @get:ManyToOne
    var beer: Beer

    var name: String
    var style: String
    var styleScore: Float
    var overallScore: Float
    var averageRating: Float
    var ratingCount: Int
    var imageUrl: String
}

• Remove From My Forums

• Question

•     

              SQLiteCommand command = new SQLiteCommand(connection);
              command.CommandText = "UPDATE glasba SET Naslov ='" + textBox1.Text+"', Izvajalec = '"+textBox2.Text+"', Zanr ='"+textBox3.Text+"', Ocena ='"+textBox4.Text+"') WHERE (Naslov = '" + imes + "') AND (Izvajalec = '" + imea + "') ";
              connection.Open();
              command.ExecuteNonQuery();
              connection.Close();
              this.Hide();

  System.Data.SQLite.SQLiteException
    HResult=0x80004005
    Message=SQL logic error or missing database
  near «=»: syntax error

  I dont know what to do, this error getting up even though i tried adding parameters and so on…

Thread Status:

Not open for further replies.

1. I’m able to reproduce the error (SQLException) by doing:

   database.query("CREATE TABLE PLAYERDATA (NAME TEXT PRIMARY KEY NOT NULL, POINTS INT DEFAULT 0 NOT NULL);");
   database.query("INSERT OR IGNORE INTO PLAYERDATA (NAME, POINTS) VALUES ('a', 1)");
   database.query("INSERT OR IGNORE INTO PLAYERDATA (NAME, POINTS) VALUES ('b', 2), ('c', 3);");

   upon debugging I noticed that line 1 executed properly, line 2 executed properly, but line 3 doesn’t.

   error message:

   [18:29:36 WARN]: java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (near ",": syntax error)
   [18:29:36 WARN]:  at org.sqlite.DB.newSQLException(DB.java:383)
   [18:29:36 WARN]:  at org.sqlite.DB.newSQLException(DB.java:387)
   [18:29:36 WARN]:  at org.sqlite.DB.throwex(DB.java:374)
   [18:29:36 WARN]:  at org.sqlite.NativeDB.prepare(Native Method)
   [18:29:36 WARN]:  at org.sqlite.DB.prepare(DB.java:123)
   [18:29:36 WARN]:  at org.sqlite.Stmt.execute(Stmt.java:113)
   [18:29:36 WARN]:  at lib.PatPeter.SQLibrary.Database.query(Database.java:242)
   [18:29:36 WARN]:  at ye.reproduce.output.MainClass.onEnable(MainClass.java:33)
   [18:29:36 WARN]:  at org.bukkit.plugin.java.JavaPlugin.setEnabled(JavaPlugin.java:321)
   [18:29:36 WARN]:  at org.bukkit.plugin.java.JavaPluginLoader.enablePlugin(JavaPluginLoader.java:340)
   [18:29:36 WARN]:  at org.bukkit.plugin.SimplePluginManager.enablePlugin(SimplePluginManager.java:405)
   [18:29:36 WARN]:  at org.bukkit.craftbukkit.v1_8_R3.CraftServer.loadPlugin(CraftServer.java:357)
   [18:29:36 WARN]:  at org.bukkit.craftbukkit.v1_8_R3.CraftServer.enablePlugins(CraftServer.java:317)
   [18:29:36 WARN]:  at org.bukkit.craftbukkit.v1_8_R3.CraftServer.reload(CraftServer.java:741)
   [18:29:36 WARN]:  at org.bukkit.Bukkit.reload(Bukkit.java:535)
   [18:29:36 WARN]:  at org.bukkit.command.defaults.ReloadCommand.execute(ReloadCommand.java:25)
   [18:29:36 WARN]:  at org.bukkit.command.SimpleCommandMap.dispatch(SimpleCommandMap.java:141)
   [18:29:36 WARN]:  at org.bukkit.craftbukkit.v1_8_R3.CraftServer.dispatchCommand(CraftServer.java:641)
   [18:29:36 WARN]:  at org.bukkit.craftbukkit.v1_8_R3.CraftServer.dispatchServerCommand(CraftServer.java:627)
   [18:29:36 WARN]:  at net.minecraft.server.v1_8_R3.DedicatedServer.aO(DedicatedServer.java:412)
   [18:29:36 WARN]:  at net.minecraft.server.v1_8_R3.DedicatedServer.B(DedicatedServer.java:375)
   [18:29:36 WARN]:  at net.minecraft.server.v1_8_R3.MinecraftServer.A(MinecraftServer.java:654)
   [18:29:36 WARN]:  at net.minecraft.server.v1_8_R3.MinecraftServer.run(MinecraftServer.java:557)
   [18:29:36 WARN]:  at java.lang.Thread.run(Unknown Source)

   here’s the minimal code to reproduce the output:

   main class:

   package ye.reproduce.output;
   
   import lib.PatPeter.SQLibrary.Database;
   import lib.PatPeter.SQLibrary.SQLite;
   import org.bukkit.Bukkit;
   import org.bukkit.plugin.java.JavaPlugin;
   
   import java.sql.SQLException;
   import java.util.logging.Level;
   
   public class MainClass extends JavaPlugin {
     private Database sql = new SQLite(getServer().getLogger(),
     "[error_reproducer]",
     this.getDataFolder().getAbsolutePath(),
     "er",
     ".db");
   
     @Override
     public void onEnable() {
     if (!Bukkit.getPluginManager().isPluginEnabled("SQLibrary")) {
       Bukkit.getLogger().log(
       Level.SEVERE,
       "Plugin was unable to start | SQLibrary is not installed"
       );
   
       this.setEnabled(false);
       return;
     }
   
     try {
       if (sql.open()) {
       sql.query("CREATE TABLE PLAYERDATA (NAME TEXT PRIMARY KEY NOT NULL, POINTS INT DEFAULT 0 NOT NULL);");
       sql.query("INSERT OR IGNORE INTO PLAYERDATA (NAME, POINTS) VALUES ('a', 1), ('b', 2);");
       }
     } catch (SQLException e) {
       e.printStackTrace();
       }
     }
   
     @Override
     public void onDisable() {
   
     }
   }

   here’s the (raw) snippet in case you’re experiencing whitespacing issue: http://pastebin.com/raw.php?i=Tytb1LC5

   Thanks for your help!

   Last edited: Sep 29, 2015

2. I recomend you use this syntax checker:
   http://www.piliapp.com/mysql-syntax-check/
   that can pinpoint the syntax error.
   I am not an SQL guru so i dont think i can help further than telling you that line 33 is the one with the issue.

   Good luck.

3. Might be that your version of sqlite is not supporting inserting multiple rows with your syntax. See here: http://stackoverflow.com/questions/…multiple-rows-at-a-time-in-an-sqlite-database

   You can find out the version of your sqlite database with «select sqlite_version();»

4. I’m using SQLite, removing «OR IGNORE» gives me no errorit gives me NPE. What I do remember though is I CAN insert multiple values into the table before. I’ve tried creating another plain plugin without all the stuff except the query one, but it didn’t work, which is really confusing.

   okay so before I created this thread I tried the UNION method, it didn’t work. But now it does. weird.

   (I still want to do the ‘,’ method, if no solution is found, then I will mark this as [Solved])

   Last edited: Sep 29, 2015

5. That isn’t SQL. I recommend you read up on SQL syntax before you try to write SQL.

6. from my understanding you’re saying that the syntax is incorrect. In SQLite you can do that, see: https://www.sqlite.org/lang_insert.html

   The following is when the above query is executed on sqlite3

   sqlite> CREATE TABLE PLAYERDATA (NAME TEXT PRIMARY KEY NOT NULL, POINTS INT DEFAULT 0 NOT NULL);
   sqlite> INSERT OR IGNORE INTO PLAYERDATA (NAME, POINTS) VALUES ('a', 1), ('b', 2);
   sqlite> SELECT * FROM PLAYERDATA;
   a|1
   b|2
   sqlite>

   Last edited: Sep 29, 2015

7. @Resolver

   Why don’t you use VALUES (‘a’, ‘b’)? Like I can’t understand what sort of relation you’re trying to represent.

8. 1. [18:29:36 WARN]: at ye.reproduce.output.MainClass.onEnable(MainClass.java:33)

   Please post this line of code.

   EDIT: Never mind found it.

   That’s not proper MySQL code I believe…

9. Right, I’m sorry my table sounds vaguely presented. So I created a table that stores player’s name (text) and their points (int). That’s it and only it for now. I want to insert multiple columns in one line. Which should be fine in SQLite with:

   INSERT OR IGNORE INTO TABLENAME (NAME, POINTS) VALUES ('playername1', 1500), ('playername2', 2500);

   I’ve done this before and I’m sure it executed properly without any error. But when I do it again this time, it returns an error. The error is located near ‘,’ (at NAME,POINTS)I’m using SQLite. And it’s a proper SQLite code I believe.

10. Were you able to check the sqlite version already, which your plugin is using, as I suggested above? (run the query «select sqlite_version();»)

11. It returns 3.7.2

    try {
       if (sql.open()){
         getLogger().log(Level.INFO, sql.query("SELECT sqlite_version();").getString(1));
      }
    } catch (SQLException e) {
       e.printStackTrace();
    } finally {
       sql.close();
    }

    EDIT: I’m marking this as solved because the sqlite version doesn’t support inserting multiple rows (needs to be above 3.7.11). I was pretty sure that I’ve done that before and it was working. But now I’m confused.

    Thanks, everyone!

    Last edited: Sep 30, 2015

Thread Status:

Not open for further replies.