Mysql errors

Hello, me and my friend are trying to code a bukkit plugin, however we are having trouble currently we are getting this syntax error when its trying to read the database:, here is the code that is throwing the error:

Code:

st.execute("INSERT INTO votes (has_voted) VALUES ('true') WHERE (DATEDIFF(NOW(), (SELECT time FROM votes WHERE minecraft_user='" + username + "')) >= 1);");

If someone could help us out, that would be great!

Thanks,
jake

 

Please post the error so that I can help you maybe...

 

Is it even possible to use the WHERE clause in this type of INSERT INTO query?
Also, instead of execute method use the executeUpdate method.

 

Yeah I think you actually want to be using UPDATE rather than INSERT for what it seems like you're trying to do. Insert queries cannot have WHERE clauses

 

Well, they can but only if you want to copy rows of one table into another one.
But I think he should use 2 query's, one that returns the time and one that inserts a new row.

 

Code:

6:03:37 [SEVERE] 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 'WHERE (DATEDIFF(NOW(), (SELECT time FROM votes WHERE minecraft_user='jwnordquist' at line 1
16:03:37 [SEVERE]at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
16:03:37 [SEVERE]at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
16:03:37 [SEVERE]at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
16:03:37 [SEVERE]at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
16:03:37 [SEVERE]at com.mysql.jdbc.Util.handleNewInstance(Util.java:407)
16:03:37 [SEVERE]at com.mysql.jdbc.Util.getInstance(Util.java:382)
16:03:37 [SEVERE]at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052)
16:03:37 [SEVERE]at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3593)
16:03:37 [SEVERE]at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3525)
16:03:37 [SEVERE]at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1986)
16:03:37 [SEVERE]at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2140)
16:03:37 [SEVERE]at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2620)
16:03:37 [SEVERE]at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2570)
16:03:37 [SEVERE]at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:779)
16:03:37 [SEVERE]at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:622)
16:03:37 [SEVERE]at com.munymuny200.acvote.AcVote$playerListener.onPlayerLogin(AcVote.java:179)
16:03:37 [SEVERE]at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
16:03:37 [SEVERE]at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
16:03:37 [SEVERE]at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
16:03:37 [SEVERE]at java.lang.reflect.Method.invoke(Method.java:597)
16:03:37 [SEVERE]at org.bukkit.plugin.java.JavaPluginLoader$1.execute(JavaPluginLoader.java:301)
16:03:37 [SEVERE]at org.bukkit.plugin.RegisteredListener.callEvent(RegisteredListener.java:62)
16:03:37 [SEVERE]at org.bukkit.plugin.SimplePluginManager.callEvent(SimplePluginManager.java:459)
16:03:37 [SEVERE]at net.minecraft.server.ServerConfigurationManager.attemptLogin(ServerConfigurationManager.java:226)
16:03:37 [SEVERE]at net.minecraft.server.NetLoginHandler.b(NetLoginHandler.java:102)
16:03:37 [SEVERE]at net.minecraft.server.NetLoginHandler.a(NetLoginHandler.java:41)
16:03:37 [SEVERE]at net.minecraft.server.NetworkListenThread.a(NetworkListenThread.java:61)
16:03:37 [SEVERE]at net.minecraft.server.MinecraftServer.w(MinecraftServer.java:554)
16:03:37 [SEVERE]at net.minecraft.server.MinecraftServer.run(MinecraftServer.java:452)
16:03:37 [SEVERE]at net.minecraft.server.ThreadServerApplication.run(SourceFile:490)
16:03:37 [INFO] Username: jwnordquist
16:03:37 [INFO] Time: null
16:03:37 [INFO] Has Voted: null
16:03:37 [INFO] jwnordquist [/127.0.0.1:50539] logged in with entity id 450 at ([world] 12.96875, 120.375, -764.53125)

 

Try doing what I said

 

Yes but that's a whole different statement type, ie a INSERT ... SELECT statement

We actually need more info on what you're trying to do to be able to give you a proper solution and also details on the tables.

But really it seems like something which would be better to do with a HashMap rather than accessing and inserting into a database

 

well the plugin is to remind people when to vote, we already have the database working with the site, and a mysql listener. however we can't seem to get this plugin working well with the database. i will give someone the source along as you don't release it publicly.

 

And what exactly is the mysql statement supposed to be doing? Is it supposed to be getting data on if they have voted or is it supposed to be setting data?

 

this plugin is simply getting the date they last voted, and comparing it to the time, if it gos past 24 hours, it's going to remind them.

 

Then you need a select statement rather than an INSERT statement.

For example

Code:

SELECT
IF((DATEDIFF(NOW(),time) >=1, 1, 0 ) as `voted`
FROM
votes
WHERE
`minecraft_user`='" + username + "'

Then fetch the result and check the field voted to see if its 1 or 0

I'd also look into using prepared statements for mysql queries as they are a lot safer