You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by Bob <gn...@gmail.com> on 2006/03/23 02:19:55 UTC
sql server 2k parameterized queries
I'm trying to use ibatis with sql server 2k, and I'm having problems
with parameterized queries. I have many parameterized queries working
with mysql, and I'm really confused as to why this is causing a
problem. The query is set up very similarly to all the mysql ones
(except it is trying to put the results in a list of hashmaps rather
than of beans)
I am recieving the following exception when trying to use the query.
com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred while applying a parameter map.
--- Check the getPayoutOrgData-InlineParameterMap.
--- Check the statement (query failed).
--- Cause: java.sql.SQLException: Incorrect syntax near the keyword 'GROUP'.
Caused by: java.sql.SQLException: Incorrect syntax near the keyword 'GROUP'.
at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback(GeneralStatement.java:185)
at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryForList(GeneralStatement.java:123)
at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:610)
at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:584)
at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForList(SqlMapSessionImpl.java:101)
at com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.queryForList(SqlMapClientImpl.java:78)
SQL Profiler suggests that the following is being executed by Ibatis
create proc #jtds000002 @P0 nvarchar(4000) as SELECT <blah> WHERE @P0
GROUP BY <blah>
Running that query in Query Analyser indeed gives the same error that
there is an incorrect syntax near "GROUP".
However, if I run the query stripping out the proc and @P0 and
replacing it with the actual parameter, it works flawlessly.
Does Ibatis support parameterized queries with SQL Server 2000?
Can anyone think of anything I might be doing wrong to get this error?
Would more information on any part of the problem be helpful?
I would grately appreciate any help or suggestions - I am prepared to
consider anything! I've been really struggling with this and am
stumped at the moment.
Thanks a lot,
Bob Ball
Re: sql server 2k parameterized queries
Posted by Bob <gn...@gmail.com>.
On 23/03/06, Bob <gn...@gmail.com> wrote:
> ... thinking about it, is that the problem? Can I use a parameterized
> statement when an entire where clause is dynamically generated?
Unfortunately, it seems that that was the problem. Oops.
When I use <iterate> in the query to go through the EVENTCD's and
build up the where clause, it appears to complete the query
successfully.
Thanks for helping me question what I was trying to do in the first
place! And thanks to Bruce for having an error in a place where the
mapping file has an example of <iterate> to slap me round the face
with.
Bob Ball
Re: sql server 2k parameterized queries
Posted by Bob <gn...@gmail.com>.
On 23/03/06, Larry Meadors <lm...@apache.org> wrote:
> Can you post the mapped statement?
Certainly - but it is very simple!
<statement id="getPayoutOrgData" resultClass="java.util.HashMap">
SELECT EVENT.EVENTCD,
sum(WTRANS.NETSTAMT) AS NET_VALUE
FROM WTRANS
INNER JOIN EVENT
ON WTRANS.EVENTCD = EVENT.EVENTCD
WHERE WTRANS.TRX_TYPE='S'
AND WTRANS.[FUNCTION]=1
AND #shows#
GROUP BY EVENT.EVENTCD, EVENT.NAME
ORDER BY EVENT.NAME
</statement>
I am querying with:
Map filters = new HashMap();
filters.put("shows", shows);
List ret = getSqlMap().queryForList(
"getPayoutOrgData",
filters);
shows is a string of the following format: (it is dynamically created
and can be quite long, but the example which is falling over has just
one entry)
(WTRANS.EVENTCD="ABC" OR WTRANS.EVENTCD="DEF")
... thinking about it, is that the problem? Can I use a parameterized
statement when an entire where clause is dynamically generated?
Bob
Re: sql server 2k parameterized queries
Posted by Larry Meadors <lm...@apache.org>.
Can you post the mapped statement?
Larry
On 3/22/06, Bob <gn...@gmail.com> wrote:
>
> I'm trying to use ibatis with sql server 2k, and I'm having problems
> with parameterized queries. I have many parameterized queries working
> with mysql, and I'm really confused as to why this is causing a
> problem. The query is set up very similarly to all the mysql ones
> (except it is trying to put the results in a list of hashmaps rather
> than of beans)
>
> I am recieving the following exception when trying to use the query.
>
> com.ibatis.common.jdbc.exception.NestedSQLException:
> --- The error occurred while applying a parameter map.
> --- Check the getPayoutOrgData-InlineParameterMap.
> --- Check the statement (query failed).
> --- Cause: java.sql.SQLException: Incorrect syntax near the keyword
> 'GROUP'.
> Caused by: java.sql.SQLException: Incorrect syntax near the keyword
> 'GROUP'.
> at
> com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback
> (GeneralStatement.java:185)
> at
> com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryForList
> (GeneralStatement.java:123)
> at
> com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(
> SqlMapExecutorDelegate.java:610)
> at
> com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(
> SqlMapExecutorDelegate.java:584)
> at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForList(
> SqlMapSessionImpl.java:101)
> at com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.queryForList(
> SqlMapClientImpl.java:78)
>
> SQL Profiler suggests that the following is being executed by Ibatis
> create proc #jtds000002 @P0 nvarchar(4000) as SELECT <blah> WHERE @P0
> GROUP BY <blah>
>
> Running that query in Query Analyser indeed gives the same error that
> there is an incorrect syntax near "GROUP".
> However, if I run the query stripping out the proc and @P0 and
> replacing it with the actual parameter, it works flawlessly.
>
> Does Ibatis support parameterized queries with SQL Server 2000?
> Can anyone think of anything I might be doing wrong to get this error?
> Would more information on any part of the problem be helpful?
>
> I would grately appreciate any help or suggestions - I am prepared to
> consider anything! I've been really struggling with this and am
> stumped at the moment.
>
> Thanks a lot,
>
> Bob Ball
>