[Seasar-user:18122] Re: [S2DAO]SQLコメント（PreparedStatementなSQL文）+S2PagerにてSQL_CALC_FOUND_ROWSを使用して件数カウントをするとSQL例外となる
[E-MAIL ADDRESS DELETED]
[E-MAIL ADDRESS DELETED]
2009年 7月 30日 (木) 12:44:35 JST
中村様
早速のレスありがとうございます。
以下当方にて使用している環境です。
S2DAO：1.0.50
DB：MySQL5.1.34
また使用しているSQLについてはご指摘の通り、バインド変数を持つ
SQLを格納したSQLファイルであり、変数を持たないSQLの場合は現象
が発生しません。
現象が発生するSQLは以下のようになります。
select
tbl.public_group_id AS public_id,
tbl.public_group_name AS public_name,
tbl.local_group_id AS local_id,
tbl.local_group_name AS local_name,
tbl.board_id AS spot_id,
tbl.spot_name AS spot_name,
IF (ISNULL(topic_update.mod_datetime),null,topic_update.mod_datetime)
AS edt_date,
topic_update.mod_datetime AS mod_datetime,
topic_update.image_file1 AS image_file
FROM
(
SELECT pblc.public_group_name,
detail.public_group_id,
detail.local_group_name,
detail.local_group_id,
detail.used_flg,
detail.board_id,
detail.spot_name
FROM public_group pblc
LEFT JOIN (
(SELECT locl.public_group_id,
locl.local_group_name,
locl.local_group_id,
locl.used_flg,
board.board_id,
board.spot_name
FROM spot_board board
LEFT OUTER JOIN local_group locl
ON board.local_group_id = locl.local_
group_id ) detail
) ON pblc.public_group_id = detail.public_group_id
) tbl LEFT JOIN (
SELECT main.board_id,
MAX(main.mod_datetime) as mod_datetime,
imginfo.image_file1
FROM spot_board_topic main
LEFT OUTER JOIN
(SELECT image_file.board_id,
image_file.image_file1
FROM spot_board_topic image_file
INNER JOIN
(
SELECT board_id as last_id, MAX(mod_
datetime) as mod_datetime
FROM spot_board_topic
WHERE image_file1 IS NOT NULL
GROUP BY board_id
) last
ON image_file.board_id=last.last_id
AND image_file.mod_datetime=last.mod_datetime)
imginfo
ON main.board_id = imginfo.board_id
GROUP BY main.board_id
) topic_update ON tbl.board_id = topic_update.
board_id
WHERE tbl.used_flg = '1'
/*IF cond.public_id !=0*/
AND tbl.public_group_id = /*cond.public_id*/'none'
/*END*/
/*IF cond.local_id !=0*/
AND tbl.local_group_id = /*cond.local_id*/'none'
/*END*/
ORDER BY topic_update.mod_datetime DESC,tbl.public_group_id,tbl.local_
group_id,tbl.board_id
スタックトレースの内容は以下のようになります。
2009-07-30 12:30:03,750 [http-8080-Processor25] ERROR org.apache.
catalina.core.ContainerBase.[Catalina].[localhost].[/pc2].[action] - サ
ーブレット action のServlet.service()が例外を投げました
java.sql.SQLException: Parameter index out of range (1 > number of
parameters, which is 0).
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926)
at com.mysql.jdbc.PreparedStatement.checkBounds(PreparedStatement.
java:3657)
at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.
java:3641)
at com.mysql.jdbc.PreparedStatement.setString(PreparedStatement.
java:4492)
at org.seasar.extension.jdbc.impl.PreparedStatementWrapper.
setString(PreparedStatementWrapper.java:639)
at org.seasar.extension.jdbc.types.StringType.bindValue(StringType.
java:66)
at org.seasar.extension.jdbc.impl.BasicHandler.bindArgs(BasicHandler.
java:193)
at org.seasar.extension.jdbc.impl.BasicSelectHandler.
execute(BasicSelectHandler.java:209)
at org.seasar.extension.jdbc.impl.BasicSelectHandler.
execute(BasicSelectHandler.java:184)
at org.seasar.dao.pager.AbstractPagingSqlRewriter.
getCountLogic(AbstractPagingSqlRewriter.java:160)
at org.seasar.dao.pager.AbstractPagingSqlRewriter.
setCount(AbstractPagingSqlRewriter.java:142)
at org.seasar.dao.impl.SelectDynamicCommand.
execute(SelectDynamicCommand.java:74)
at org.seasar.dao.interceptors.S2DaoInterceptor.
invoke(S2DaoInterceptor.java:53)
at org.seasar.dao.pager.PagerS2DaoInterceptorWrapper.
invoke(PagerS2DaoInterceptorWrapper.java:71)
at jp.xxxxx.pc.dao.infoDao$$EnhancedByS2AOP$$e8f3eb$$
MethodInvocation$$getInfo2.proceed(MethodInvocationClassGenerator.java)
at org.seasar.extension.tx.DefaultTransactionCallback.
execute(DefaultTransactionCallback.java:58)
at org.seasar.extension.tx.adapter.JTATransactionManagerAdapter.
required(JTATransactionManagerAdapter.java:65)
at org.seasar.extension.tx.RequiredInterceptor.
invoke(RequiredInterceptor.java:41)
at jp.surfguy.pc.dao.WaveinfoDao$$EnhancedByS2AOP$$e8f3eb$$
MethodInvocation$$getInfo2.proceed(MethodInvocationClassGenerator.java)
at org.seasar.framework.aop.interceptors.TraceInterceptor.
invoke(TraceInterceptor.java:73)
at jp.xxxxx.pc.dao.infoDao$$EnhancedByS2AOP$$e8f3eb$$
MethodInvocation$$getSpotsinfo2.proceed(MethodInvocationClassGenerator.
java)
at jp.xxxxx.pc.dao.infoDao$$EnhancedByS2AOP$$e8f3eb.
getSpotsinfo(WaveinfoDao$$EnhancedByS2AOP$$e8f3eb.java)
at jp.xxxxx.pc.service.TopService.setInfo(TopService.java:37)
at jp.xxxxx.pc.service.TopService$$EnhancedByS2AOP$$1073e42.$$
setInfo$$invokeSuperMethod$$(WavetopService$$EnhancedByS2AOP$$1073e42.
java)
at jp.xxxxx.pc.service.TopService$$EnhancedByS2AOP$$1073e42$$
MethodInvocation$$setInfo2.proceed(MethodInvocationClassGenerator.java)
at org.seasar.extension.tx.DefaultTransactionCallback.
execute(DefaultTransactionCallback.java:58)
at org.seasar.extension.tx.adapter.JTATransactionManagerAdapter.
required(JTATransactionManagerAdapter.java:65)
at org.seasar.extension.tx.RequiredInterceptor.
invoke(RequiredInterceptor.java:41)
at jp.xxxxx.pc.service.TopService$$EnhancedByS2AOP$$1073e42$$
MethodInvocation$$setInfo2.proceed(MethodInvocationClassGenerator.java)
at org.seasar.framework.aop.interceptors.TraceInterceptor.
invoke(TraceInterceptor.java:73)
at jp.xxxxx.pc.service.TopService$$EnhancedByS2AOP$$1073e42$$
MethodInvocation$$setInfo2.proceed(MethodInvocationClassGenerator.java)
at jp.xxxxx.pc.service.TopService$$EnhancedByS2AOP$$1073e42.
setInfo(TopService$$EnhancedByS2AOP$$1073e42.java)
at jp.xxxxx.pc.action.topAction.index(WavetopAction.java:60)
at jp.xxxxx.pc.action.topAction$$EnhancedByS2AOP$$9e4a20.$$index$$
invokeSuperMethod$$(TopAction$$EnhancedByS2AOP$$9e4a20.java)
at jp.xxxxx.pc.action.TopAction$$EnhancedByS2AOP$$9e4a20$$
MethodInvocation$$index0.proceed(MethodInvocationClassGenerator.java)
at org.seasar.extension.tx.DefaultTransactionCallback.
execute(DefaultTransactionCallback.java:58)
at org.seasar.extension.tx.adapter.JTATransactionManagerAdapter.
required(JTATransactionManagerAdapter.java:65)
at org.seasar.extension.tx.RequiredInterceptor.
invoke(RequiredInterceptor.java:41)
at jp.xxxxx.pc.action.TopAction$$EnhancedByS2AOP$$9e4a20$$
MethodInvocation$$index0.proceed(MethodInvocationClassGenerator.java)
at org.seasar.framework.aop.interceptors.TraceInterceptor.
invoke(TraceInterceptor.java:73)
at jp.xxxxx.pc.action.TopAction$$EnhancedByS2AOP$$9e4a20$$
MethodInvocation$$index0.proceed(MethodInvocationClassGenerator.java)
at org.seasar.framework.aop.interceptors.ThrowsInterceptor.
invoke(ThrowsInterceptor.java:79)
at jp.xxxxx.pc.action.WavetopAction$$EnhancedByS2AOP$$9e4a20$$
MethodInvocation$$index0.proceed(MethodInvocationClassGenerator.java)
at org.seasar.framework.aop.impl.NestedMethodInvocation.
proceed(NestedMethodInvocation.java:55)
at jp.xxxxx.pc.interceptor.ValidLoginInterceptor.
invoke(ValidLoginInterceptor.java:122)
at org.seasar.framework.aop.impl.NestedMethodInvocation.
proceed(NestedMethodInvocation.java:53)
at org.seasar.framework.container.customizer.AspectCustomizer$
LookupAdaptorInterceptor.invoke(AspectCustomizer.java:198)
at jp.xxxxx.pc.action.TopAction$$EnhancedByS2AOP$$9e4a20$$
MethodInvocation$$index0.proceed(MethodInvocationClassGenerator.java)
at jp.xxxxx.pc.action.TopAction$$EnhancedByS2AOP$$9e4a20.
index(TopAction$$EnhancedByS2AOP$$9e4a20.java)
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)
at org.seasar.framework.util.MethodUtil.invoke(MethodUtil.java:96)
at org.seasar.struts.action.ActionWrapper.execute(ActionWrapper.
java:138)
at org.seasar.struts.action.ActionWrapper.execute(ActionWrapper.
java:86)
at org.apache.struts.action.RequestProcessor.
processActionPerform(RequestProcessor.java:431)
at org.seasar.struts.action.S2RequestProcessor.
process(S2RequestProcessor.java:127)
at org.apache.struts.action.ActionServlet.process(ActionServlet.
java:1196)
at org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:
414)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:627)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
at org.apache.catalina.core.ApplicationFilterChain.
internalDoFilter(ApplicationFilterChain.java:269)
at org.apache.catalina.core.ApplicationFilterChain.
doFilter(ApplicationFilterChain.java:188)
at org.seasar.extension.filter.RequestDumpFilter.
doFilter(RequestDumpFilter.java:127)
at org.apache.catalina.core.ApplicationFilterChain.
internalDoFilter(ApplicationFilterChain.java:215)
at org.apache.catalina.core.ApplicationFilterChain.
doFilter(ApplicationFilterChain.java:188)
at org.seasar.framework.container.filter.S2ContainerFilter.
doFilter(S2ContainerFilter.java:79)
at org.apache.catalina.core.ApplicationFilterChain.
internalDoFilter(ApplicationFilterChain.java:215)
at org.apache.catalina.core.ApplicationFilterChain.
doFilter(ApplicationFilterChain.java:188)
at org.seasar.framework.container.hotdeploy.HotdeployFilter.
doHotdeployFilter(HotdeployFilter.java:86)
at org.seasar.framework.container.hotdeploy.HotdeployFilter.
doFilter(HotdeployFilter.java:67)
at org.apache.catalina.core.ApplicationFilterChain.
internalDoFilter(ApplicationFilterChain.java:215)
at org.apache.catalina.core.ApplicationFilterChain.
doFilter(ApplicationFilterChain.java:188)
at org.apache.catalina.core.ApplicationDispatcher.
invoke(ApplicationDispatcher.java:679)
at org.apache.catalina.core.ApplicationDispatcher.
processRequest(ApplicationDispatcher.java:461)
at org.apache.catalina.core.ApplicationDispatcher.
doForward(ApplicationDispatcher.java:399)
at org.apache.catalina.core.ApplicationDispatcher.
forward(ApplicationDispatcher.java:301)
at org.seasar.struts.filter.RoutingFilter.forward(RoutingFilter.
java:219)
at org.seasar.struts.filter.RoutingFilter.doFilter(RoutingFilter.
java:90)
at org.apache.catalina.core.ApplicationFilterChain.
internalDoFilter(ApplicationFilterChain.java:215)
at org.apache.catalina.core.ApplicationFilterChain.
doFilter(ApplicationFilterChain.java:188)
at org.seasar.framework.container.filter.S2ContainerFilter.
doFilter(S2ContainerFilter.java:79)
at org.apache.catalina.core.ApplicationFilterChain.
internalDoFilter(ApplicationFilterChain.java:215)
at org.apache.catalina.core.ApplicationFilterChain.
doFilter(ApplicationFilterChain.java:188)
at org.seasar.framework.container.hotdeploy.HotdeployFilter.
doHotdeployFilter(HotdeployFilter.java:99)
at org.seasar.framework.container.hotdeploy.HotdeployFilter.
doFilter(HotdeployFilter.java:67)
at org.apache.catalina.core.ApplicationFilterChain.
internalDoFilter(ApplicationFilterChain.java:215)
at org.apache.catalina.core.ApplicationFilterChain.
doFilter(ApplicationFilterChain.java:188)
at org.seasar.extension.filter.EncodingFilter.
doFilter(EncodingFilter.java:69)
at org.apache.catalina.core.ApplicationFilterChain.
internalDoFilter(ApplicationFilterChain.java:215)
at org.apache.catalina.core.ApplicationFilterChain.
doFilter(ApplicationFilterChain.java:188)
at org.apache.catalina.core.StandardWrapperValve.
invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.
invoke(StandardContextValve.java:172)
at org.apache.catalina.core.StandardHostValve.
invoke(StandardHostValve.java:127)
at org.apache.catalina.valves.ErrorReportValve.
invoke(ErrorReportValve.java:117)
at org.apache.catalina.core.StandardEngineValve.
invoke(StandardEngineValve.java:108)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.
java:174)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.
java:875)
at org.apache.coyote.http11.Http11BaseProtocol$
Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:665)
at org.apache.tomcat.util.net.PoolTcpEndpoint.
processSocket(PoolTcpEndpoint.java:528)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.
runIt(LeaderFollowerWorkerThread.java:81)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.
run(ThreadPool.java:689)
at java.lang.Thread.run(Unknown Source)
DEBUG 2009-07-30 12:30:03,765 [http-8080-Processor25] HOT deployを終了し
ました
どうぞよろしくお願い申し上げます。
Toshihiro Nakamura <[E-MAIL ADDRESS DELETED]>さん：
> 中村(taedium)です。
>
> SeasarとS2Daoのバージョンを教えてください。
> お使いのRDBMSはMySQLですか？
>
> > ちなみにPreparedStatementでないSQLファイルを用意して実行したところ
> > 上記現象は起きませんでした。
>
> 「PreparedStatementでないSQLファイル」とは
> バインド変数を1つも持たないSQL（つまり、SQLコメントを
> 使用していないSQL）を格納したSQLファイルという
> ことでしょうか？
>
> 問題となったSQLや
> 例外のスタックトレースを見せてもらえれば
> 原因がわかるかもしれません。
> --
> Toshihiro Nakamura
>
> _______________________________________________
> Seasar-user mailing list
> [E-MAIL ADDRESS DELETED]
> https://ml.seasar.org/mailman/listinfo/seasar-user
>
> __________ ESET Smart Security からの情報, ウイルス定義データベースの
バージョン 4288 (20090729) __________
>
> このメッセージは ESET Smart Security によって検査済みです。
>
> http://canon-its.jp
>
>
Seasar-user メーリングリストの案内