Solved SQLibrary, syntax error near ',' when inserting multiple values into a table in one line

Discussion in 'Plugin Development' started by Resolver, Sep 29, 2015.

Thread Status:
Not open for further replies.
  1. Offline

    Resolver

    I'm able to reproduce the error (SQLException) by doing:
    Code:
    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:
    Code:
    [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:
    Code:
    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. Offline

    mine-care

    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. Offline

    blablubbabc

  4. Offline

    Resolver

    I'm using SQLite, removing "OR IGNORE" gives me no error

    it 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. Offline

    mythbusterma


    That isn't SQL. I recommend you read up on SQL syntax before you try to write SQL.
     
  6. Offline

    Resolver

    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
    Code:
    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
    ferrybig likes this.
  7. Offline

    mythbusterma

    @Resolver

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

    BaconStripzMan

    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. Offline

    Resolver

    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:

    Code:
    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. Offline

    blablubbabc

    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. Offline

    Resolver

    It returns 3.7.2

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

Share This Page