java - SQL error: check the manual that corresponds to your MySQL server version for the right syntax -
upon attempting save data database, i'm receiving error , it's not saving data database. did little bit of research see if having error matched similar code mine , didn't find me. decided post problem , code in hope me it.
the main error:
com.mysql.jdbc.exceptions.jdbc4.mysqlsyntaxerrorexception: have error in sql syntax; check manual corresponds mysql server version right syntax use near 'where
playername='nick''at line 1
here full stacktrace:
[7/14/15 11:49 pm]: [registered]: nick [7/14/15 11:49 pm]: there's problem when saving data of player nick. [7/14/15 11:49 pm]: com.mysql.jdbc.exceptions.jdbc4.mysqlsyntaxerrorexception: have error in sql syntax; check manual corresponds mysql server version right syntax use near 'where `playername`='nick''at line 1 [7/14/15 11:49 pm]: @ sun.reflect.nativeconstructoraccessorimpl.newinstance0(native method) [7/14/15 11:49 pm]: @ sun.reflect.nativeconstructoraccessorimpl.newinstance(unknown source) [7/14/15 11:49 pm]: @ sun.reflect.delegatingconstructoraccessorimpl.newinstance(unknown source) [7/14/15 11:49 pm]: @ java.lang.reflect.constructor.newinstance(unknown source) [7/14/15 11:49 pm]: @ com.mysql.jdbc.util.handlenewinstance(util.java:406) [7/14/15 11:49 pm]: @ com.mysql.jdbc.util.getinstance(util.java:381) [7/14/15 11:49 pm]: @ com.mysql.jdbc.sqlerror.createsqlexception(sqlerror.java:1030) [7/14/15 11:49 pm]: @ com.mysql.jdbc.sqlerror.createsqlexception(sqlerror.java:956) [7/14/15 11:49 pm]: @ com.mysql.jdbc.mysqlio.checkerrorpacket(mysqlio.java:3491) [7/14/15 11:49 pm]: @ com.mysql.jdbc.mysqlio.checkerrorpacket(mysqlio.java:3423) [7/14/15 11:49 pm]: @ com.mysql.jdbc.mysqlio.sendcommand(mysqlio.java:1936) [7/14/15 11:49 pm]: @ com.mysql.jdbc.mysqlio.sqlquerydirect(mysqlio.java:2060) [7/14/15 11:49 pm]: @ com.mysql.jdbc.connectionimpl.execsql(connectionimpl.java:2542) [7/14/15 11:49 pm]: @ com.mysql.jdbc.preparedstatement.executeinternal(preparedstatement.java:1734) [7/14/15 11:49 pm]: @ com.mysql.jdbc.preparedstatement.executeupdate(preparedstatement.java:2019) [7/14/15 11:49 pm]: @ com.mysql.jdbc.preparedstatement.executeupdate(preparedstatement.java:1937) [7/14/15 11:49 pm]: @ com.mysql.jdbc.preparedstatement.executeupdate(preparedstatement.java:1922) [7/14/15 11:49 pm]: @ server.util.sql.savehighscore(sql.java:145) [7/14/15 11:49 pm]: @ server.model.mobile.players.client.logout(client.java:580) [7/14/15 11:49 pm]: @ server.model.mobile.players.packets.clicking.clickingbuttons.processpacket(clickingbuttons.java:2190) [7/14/15 11:49 pm]: @ server.model.mobile.players.packethandler.processpacket(packethandler.java:118) [7/14/15 11:49 pm]: @ server.model.mobile.players.client.processqueuedpackets(client.java:774) [7/14/15 11:49 pm]: @ server.model.mobile.players.playerhandler.process(playerhandler.java:191) [7/14/15 11:49 pm]: @ server.server$1.execute(server.java:103) [7/14/15 11:49 pm]: @ server.task.task2.tick(task2.java:105) [7/14/15 11:49 pm]: @ server.event.taskscheduler.run(taskscheduler.java:100) [7/14/15 11:49 pm]: @ java.util.concurrent.executors$runnableadapter.call(unknown source) [7/14/15 11:49 pm]: @ java.util.concurrent.futuretask.runandreset(unknown source) [7/14/15 11:49 pm]: @ java.util.concurrent.scheduledthreadpoolexecutor$scheduledfuturetask.access$301(unknown source) [7/14/15 11:49 pm]: @ java.util.concurrent.scheduledthreadpoolexecutor$scheduledfuturetask.run(unknown source) [7/14/15 11:49 pm]: @ java.util.concurrent.threadpoolexecutor.runworker(unknown source) [7/14/15 11:49 pm]: @ java.util.concurrent.threadpoolexecutor$worker.run(unknown source) [7/14/15 11:49 pm]: @ java.lang.thread.run(unknown source) false [7/14/15 11:49 pm]: not save nick [7/14/15 11:49 pm]: logged out: nick heres code saving tables:
public boolean savehighscore(client c) throws sqlexception { long totallvlexp = (long) (c.playerxp[0]) + (c.playerxp[1]) + (c.playerxp[2]) + (c.playerxp[3]) + (c.playerxp[4]) + (c.playerxp[5]) + (c.playerxp[6]) + (c.playerxp[7]) + (c.playerxp[8]) + (c.playerxp[9]) + (c.playerxp[10]) + (c.playerxp[11]) + (c.playerxp[12]) + (c.playerxp[13]) + (c.playerxp[14]) + (c.playerxp[15]) + (c.playerxp[16]) + (c.playerxp[17]) + (c.playerxp[18]) + (c.playerxp[19]) + (c.playerxp[20]); int totallevell = (int) (c.getlevelforxp(c.playerxp[0]) + c.getlevelforxp(c.playerxp[1]) + c.getlevelforxp(c.playerxp[2]) + c.getlevelforxp(c.playerxp[3]) + c.getlevelforxp(c.playerxp[4]) + c.getlevelforxp(c.playerxp[5]) + c.getlevelforxp(c.playerxp[6]) + c.getlevelforxp(c.playerxp[7]) + c.getlevelforxp(c.playerxp[8]) + c.getlevelforxp(c.playerxp[9]) + c.getlevelforxp(c.playerxp[10]) + c.getlevelforxp(c.playerxp[11]) + c.getlevelforxp(c.playerxp[12]) + c.getlevelforxp(c.playerxp[13]) + c.getlevelforxp(c.playerxp[14]) + c.getlevelforxp(c.playerxp[15]) + c.getlevelforxp(c.playerxp[16]) + c.getlevelforxp(c.playerxp[17]) + c.getlevelforxp(c.playerxp[18]) + c.getlevelforxp(c.playerxp[19]) + c.getlevelforxp(c.playerxp[20])); boolean result = true; string updateskillssql = "update skills" + " set `attacklvl`=?," + " `attackxp`=?," + " `defencelvl`=?," + " `defencexp`=?," + " `strengthlvl`=?," + " `strengthxp`=?," + " `hitpointslvl`=?," + " `hitpointsxp`=?," + " `rangelvl`=?," + " `rangexp`=?," + " `prayerlvl`=?," + " `prayerxp`=?," + " `magiclvl`=?," + " `magicxp`=?," + " `cookinglvl`=?," + " `cookingxp`=?," + " `woodcuttinglvl`=?," + " `woodcuttingxp`=?," + " `fletchinglvl`=?," + " `fletchingxp`=?," + " `fishinglvl`=?," + " `fishingxp`=?," + " `firemakinglvl`=?," + " `firemakingxp`=?," + " `craftinglvl`=?," + " `craftingxp`=?," + " `smithinglvl`=?," + " `smithingxp`=?," + " `mininglvl`=?," + " `miningxp`=?," + " `herblorelvl`=?," + " `herblorexp`=?," + " `agilitylvl`=?," + " `agilityxp`=?," + " `thievinglvl`=?," + " `thievingxp`=?," + " `slayerlvl`=?," + " `slayerxp`=?," + " `farminglvl`=?," + " `farmingxp`=?," + " `runecraftlvl`=?," + " `runecraftxp`=?" + " `playername`=?"; string updateskillsoverallsql = "update skillsoverall" + " set `lvl`=?," + " `xp`=?," + " `playername`=?"; string updateplayerrightssql = "update playerrights" + " set `rank` = ?," + " `playername`=?"; try(connection con = drivermanager.getconnection("jdbc:mysql://localhost/highscores","root","root")) { con.setautocommit(false); try(preparedstatement pstmt = con.preparestatement(updateskillssql);preparedstatement pstmt2 = con.preparestatement(updateskillsoverallsql);preparedstatement pstmt3 = con.preparestatement(updateplayerrightssql);) { setparameters(pstmt, c.playerlevel[0], c.playerxp[0], c.playerlevel[1], c.playerxp[1], c.playerlevel[2], c.playerxp[2], c.playerlevel[3], c.playerxp[3], c.playerlevel[4], c.playerxp[4], c.playerlevel[5], c.playerxp[5], c.playerlevel[6], c.playerxp[6], c.playerlevel[7], c.playerxp[7], c.playerlevel[8], c.playerxp[8], c.playerlevel[9], c.playerxp[9], c.playerlevel[10], c.playerxp[10], c.playerlevel[11], c.playerxp[11], c.playerlevel[12], c.playerxp[12], c.playerlevel[13], c.playerxp[13], c.playerlevel[14], c.playerxp[14], c.playerlevel[15], c.playerxp[15], c.playerlevel[16], c.playerxp[16], c.playerlevel[17], c.playerxp[17], c.playerlevel[18], c.playerxp[18], c.playerlevel[19], c.playerxp[19], c.playerlevel[20], c.playerxp[20], c.playername); setparameters(pstmt2, totallevell, totallvlexp, c.playername); setparameters(pstmt3, c.getpermission().getid(), c.playername); pstmt.executeupdate(); pstmt2.executeupdate(); pstmt3.executeupdate(); } catch (exception e) { system.out.println(string.format("there's problem when saving data of player %s.", c.playername)); e.printstacktrace(system.out); con.rollback(); result = false; } if (result) { con.commit(); con.setautocommit(true); } } catch (exception e) { system.out.println(string.format("there's problem when saving data of player %s.", c.playername)); e.printstacktrace(system.out); result = false; } return result; } private void setparameters(preparedstatement pstmt, object ... args) { int = 0; try { (object arg : args) { pstmt.setobject(++i, arg); } } catch(exception e) { e.printstacktrace(); } } } here tables:
create table `skills` ( `playername` varchar(15) not null default '', `attacklvl` double default null, `attackxp` double default null, `defencelvl` double default null, `defencexp` double default null, `strengthlvl` double default null, `strengthxp` double default null, `hitpointslvl` double default null, `hitpointsxp` double default null, `rangelvl` double default null, `rangexp` double default null, `prayerlvl` double default null, `prayerxp` double default null, `magiclvl` double default null, `magicxp` double default null, `cookinglvl` double default null, `cookingxp` double default null, `woodcuttinglvl` double default null, `woodcuttingxp` double default null, `fletchinglvl` double default null, `fletchingxp` double default null, `fishinglvl` double default null, `fishingxp` double default null, `firemakinglvl` double default null, `firemakingxp` double default null, `craftinglvl` double default null, `craftingxp` double default null, `smithinglvl` double default null, `smithingxp` double default null, `mininglvl` double default null, `miningxp` double default null, `herblorelvl` double default null, `herblorexp` double default null, `agilitylvl` double default null, `agilityxp` double default null, `thievinglvl` double default null, `thievingxp` double default null, `slayerlvl` double default null, `slayerxp` double default null, `farminglvl` double default null, `farmingxp` double default null, `runecraftlvl` double default null, `runecraftxp` double default null, index (playername), constraint fk_playername_rights foreign key (playername) references players (playername) on delete restrict on update restrict ) engine=myisam; alter table skills add index idx_playername (playername); create table `playerrights` ( `playername` varchar(15) not null default '', `rank` int(2) default null, index (playername), constraint fk_playername_rights foreign key (playername) references players (playername) on delete restrict on update restrict ) engine=myisam; alter table playerrights add index idx_playername (playername); create table `skillsoverall` ( `playername` varchar(15) not null default '', `lvl` int(11) default null, `xp` bigint(11) default null, index (playername), constraint fk_playername_rights foreign key (playername) references players (playername) on delete restrict on update restrict ) engine=myisam; alter table skillsoverall add index idx_playername (playername);
the incorrect queries are:
string updateskillsoverallsql = "update skillsoverall" + " set `lvl`=?," + " `xp`=?," + " `playername`=?"; string updateplayerrightssql = "update playerrights" + " set `rank` = ?," + " `playername`=?"; should be:
string updateskillsoverallsql = "update skillsoverall" + " set `lvl`=?," + " `xp`=?" + " `playername`=?"; string updateplayerrightssql = "update playerrights" + " set `rank` = ?" + " `playername`=?"; there , after set xp , set rank. happens everybody.
p.s. simpler way data might be:
create table skills ( skill_name varchar(200), skill_level int, player_name varchar(200) ); then can set 1 skill (skill_name) , when have new skills not need alter table , have less code maintain.
Comments
Post a Comment