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
>