How to use array passed from executeWithArray(Object[]) in sql Query

Question

Iam using the executeWIthArray() to pass a list. But when i try to use this array in Query it shows error.This is my code

QueryHelper.setSortOrderZero(pm).executeWithArray(list);

And in QueryHelper i have defined the function setSortOrderZero as follows:

public static Query setSortOrderZero(PersistenceManager pm) {
final Query query =  pm.newQuery("javax.jdo.query.SQL","update TABLENAME set SORTORDER = 0 where ID in list");

return query;

}

But iam getting error.So is this the correct way to access the array????

ERROR:

   Exception in ProtectedFilter: Error executing SQL query "update    PROFILEARTADS set SORTORDER = 0 where PROFILE_ID in list".
  Mar 19, 2015 4:51:44 PM com.sun.jersey.spi.container.ContainerResponse    mapMappableContainerException
     SEVERE: The RuntimeException could not be mapped to a response, re-  throwing to the HTTP container
    javax.jdo.JDODataStoreException: Error executing SQL query "update PROFILEARTADS set SORTORDER = 0 where PROFILE_ID in list".
    at org.datanucleus.api.jdo.NucleusJDOHelper.getJDOExceptionForNucleusException(NucleusJDOHelper.java:422)
    at org.datanucleus.api.jdo.JDOQuery.executeWithArray(JDOQuery.java:321)
    at com.giri.artsite.server.per.PersistenceDelegate.deleteAllArtsduplicate(PersistenceDelegate.java:8499)
    at com.giri.artsite.server.res.PersonServiceResource.deleteAllArts(PersonServiceResource.java:4681)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    NestedThrowablesStackTrace:
    com.mysql.jdbc.exceptions.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 'list' at line 1
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2985)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723)
    at com.mysql.jdbc.Connection.execSQL(Connection.java:3256)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1313)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1585)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1500)
    at c

Answer 1

As said, in SQL a "parameter" has to be a "?" in the query; you have no parameter so no point passing in some array of parameters. What would make more sense would be

q = pm.newQuery("javax.jdo.query.SQL", "update TABLENAME set SORTORDER = 0 where ID in ?");
List values = ...
q.execute(values);