SQL Query Works in phpMyAdmin but not in Java

Question

I have the following SQL Query:

SELECT `Activity`.`userid` , `Goal`.`goalid` , `Goal`.`activityid` , `Goal`.`exercise` , `Exercise`.`name` , `Goal`.`target` , `ActivityData`.`dataid` , `ActivityData`.`current` , MAX( `ActivityData`.`timestamp` ) AS `timestamp`
FROM `Goal`
INNER JOIN `Activity` ON `Activity`.`activityid` = `Goal`.`activityid`
INNER JOIN `Exercise` ON `Exercise`.`exerciseid` = `Goal`.`exercise`
INNER JOIN `ActivityData` ON `ActivityData`.`activityid` = `Goal`.`activityid`
WHERE `Activity`.`userid` =100005545413982
GROUP BY `gymbuddy`.`Goal`.`activityid`;

The above query works in phpMyAdmin but gives me the following stacktrace when I run it with a PreparedStatement.

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 'BY `gymbuddy`.`Goal`.`activityid`' at line 1
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    at com.mysql.jdbc.Util.getInstance(Util.java:386)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1053)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4096)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4028)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2490)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2651)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2734)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)
    at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2322)
    at com.mathanv.gb.dao.BuddyDao.getAllBuddyGoals(BuddyDao.java:387)
    at com.mathanv.gb.controller.BuddyController.doPost(BuddyController.java:95)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:647)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:99)
    at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:936)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:407)
    at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1004)
    at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:589)
    at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:310)
    at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:895)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:918)
    at java.lang.Thread.run(Thread.java:680)

These are the DB Properties:

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:8889/gymbuddy?allowMultiQueries=true

Not sure why it's doing that! help!

EDIT: I'm using a preparedStatement to generate the userid.

WHERE `gymbuddy`.`Activity`.`userid` =?
ps.setLong(1, buddy.getUserID());

EDIT: (Request) Method with the PreparedStatement.

public JSONObject getAllBuddyGoals(Buddy buddy){
        try {
            PreparedStatement ps = connection.prepareStatement(GET_ALL_BUDDY_GOALS);
            ps.setLong(1, buddy.getUserID());
            JSONObject response = new JSONObject();
            response.put("action", "getAllBuddyGoals");
            response.put("success", 1);
            response.put("error", 0);
            response.put("userid", buddy.getUserID());
            JSONArray goals = new JSONArray();
            ResultSet rs = ps.executeQuery();
            while(rs.next()){
                JSONObject goal = new JSONObject();
                goal.put("goalid", rs.getLong("goalid"));
                goal.put("activityid", rs.getLong("activityid"));
                goal.put("exercise", rs.getString("name"));
                goal.put("target", rs.getDouble("target"));
                goal.put("max", rs.getDouble("current"));
                goals.add(goal);
            }
            response.put("goals", goals);
            return response;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        JSONObject error = new JSONObject();
        error.put("action", "getAllBuddyGoals");
        error.put("success", 0);
        error.put("error", 1);
        error.put("error_msg", "error processing request, please contact admin.");
        return error;
    }

What does buddy.getUserID() return?

cheesemacfly
– cheesemacfly

2013-04-11 16:54:49 +00:00
Commented Apr 11, 2013 at 16:54 — cheesemacfly
– cheesemacfly, Commented Apr 11, 2013 at 16:54
a long number for example, 100005545413982

MathanMV
– MathanMV

2013-04-11 16:55:33 +00:00
Commented Apr 11, 2013 at 16:55 — MathanMV
– MathanMV, Commented Apr 11, 2013 at 16:55
Can you show us the whole code for the preparedStatement?

cheesemacfly
– cheesemacfly

2013-04-11 16:59:18 +00:00
Commented Apr 11, 2013 at 16:59 — cheesemacfly
– cheesemacfly, Commented Apr 11, 2013 at 16:59
Yes, sure. Added it in the question.

MathanMV
– MathanMV

2013-04-11 17:04:27 +00:00
Commented Apr 11, 2013 at 17:04 — MathanMV
– MathanMV, Commented Apr 11, 2013 at 17:04

Robbert · Accepted Answer · 2013-04-11 17:04:30Z

4

You might want to check that there is an actual space between the buddy id value and the term Group. If you read the error thrown by Java:

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 'BY `gymbuddy`.`Goal`.`activityid`' at line 1

Mysql errors generally occur immediately prior to where the error says. My guess is that the query you're executing is missing some spaces between 100005545413982 and GROUP, possibly like this:

 WHERE `Activity`.`userid` =100005545413982GROUP BY `gymbuddy`.`Goal`.`activityid`;

answered Apr 11, 2013 at 17:04

Robbert

6,6226 gold badges38 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MathanMV Over a year ago

I just slapped myself. Can't believe one little space can waste a lot of time haha! Thanks a lot mate!

Collectives™ on Stack Overflow

SQL Query Works in phpMyAdmin but not in Java

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related