You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@ibatis.apache.org by "Claudio Martins (JIRA)" <ib...@incubator.apache.org> on 2005/02/17 23:20:48 UTC
[jira] Created: (IBATIS-76) Problems with Dynamic Queries
Problems with Dynamic Queries
-----------------------------
Key: IBATIS-76
URL: http://issues.apache.org/jira/browse/IBATIS-76
Project: iBatis for Java
Type: Bug
Components: SQL Maps
Versions: 2.0.9
Environment: JRE 1.4.2
Reporter: Claudio Martins
I have a dynamic query on my SqlMap xml file. When I try to execute it, I receive a SQLException with the message :
--- The error occurred in com/globo/billing/conf/sql/SqlMapping-Cobranca.xml.
--- The error occurred while applying a parameter map.
--- Check the select-emissao-recorrente-InlineParameterMap.
--- Check the statement (query failed).
--- Cause: java.sql.SQLException: ORA-00933: SQL command not properly ended
But when I remove the <dynamic> block, the SQL runs perfectly. Can anybody help me?
<statement id="select-emissao-recorrente"
parameterClass="com.globo.billing.core.daos.EmissaoRecorrenteDAO"
resultClass="com.globo.billing.core.daos.EmissaoRecorrenteDAO"
cacheModel="cache-Cobranca">
<![CDATA[
SELECT
INFO.ASSINATURA_ID as codigoAssinatura,
INFO.STATUS_NUM as statusAssinante,
PAG.TIPO_COBRANCA_ID as tipoCobranca,
PAG.MEIO_PAGAMENTO_ID as meioPagamento,
LOGIN.USERNAME_TXT as usernameAssinante,
PAG.DIA_VENC_NUM as diaVencimento,
ASSIN.TITULAR_DEPENDENTE_NUM as titularDependente
FROM
TB_GLB_ISP_INFO_ASSINATURA INFO,
TB_GLB_ISP_PAGANTE PAG,
TB_GLB_ISP_ASSINANTE ASSIN,
TB_GLB_ISP_LOGIN LOGIN
WHERE
INFO.ASSINATURA_ID = PAG.ASSINATURA_ID
AND INFO.ASSINATURA_ID = ASSIN.ASSINATURA_ID
AND LOGIN.USUARIO_ID = ASSIN.USUARIO_ID
AND ASSIN.TITULAR_DEPENDENTE_NUM = 1
<dynamic>
<isGreaterThan prepend="AND" property="diaVencimento" compareValue="0">
PAG.DIA_VENC = #diaVencimento#
</isGreaterThan>
<isGreaterThan prepend="AND" property="tipoCobranca" compareValue="0">
PAG.TIPO_COBRANCA_ID = #tipoCobranca#
</isGreaterThan>
</dynamic>
AND EXISTS (SELECT 1
FROM TB_GLB_ISP_SERVICO_PRESTADO SERV
WHERE
SERV.ASSINATURA_ID = INFO.ASSINATURA_ID
AND SERV.DATA_INICIO_DT < #dataTerminoCompetencia:DATE#
AND SERV.STATUS_COBRANCA_ID IN (1, 2))
ORDER BY
INFO.ASSINATURA_ID
]]>
</statement>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
If you want more information on JIRA, or have a bug to report see:
http://www.atlassian.com/software/jira
[jira] Commented: (IBATIS-76) Problems with Dynamic Queries
Posted by "Clinton Begin (JIRA)" <ib...@incubator.apache.org>.
[ http://issues.apache.org/jira/browse/IBATIS-76?page=comments#action_59360 ]
Clinton Begin commented on IBATIS-76:
-------------------------------------
Patient: "Dr. It hurts when I do this..."
Doctor: "So don't do that!"
The <dynamic> tag isn't doing anything for you anyway. So if it works without it, leave it off....you only need the <dynamic> tag if you need an additional prepend="" attribute for WHERE clauses etc.
Cheers,
Clinton
> Problems with Dynamic Queries
> -----------------------------
>
> Key: IBATIS-76
> URL: http://issues.apache.org/jira/browse/IBATIS-76
> Project: iBatis for Java
> Type: Bug
> Components: SQL Maps
> Versions: 2.0.9
> Environment: JRE 1.4.2
> Reporter: Claudio Martins
>
> I have a dynamic query on my SqlMap xml file. When I try to execute it, I receive a SQLException with the message :
> --- The error occurred in com/globo/billing/conf/sql/SqlMapping-Cobranca.xml.
> --- The error occurred while applying a parameter map.
> --- Check the select-emissao-recorrente-InlineParameterMap.
> --- Check the statement (query failed).
> --- Cause: java.sql.SQLException: ORA-00933: SQL command not properly ended
> But when I remove the <dynamic> block, the SQL runs perfectly. Can anybody help me?
> <statement id="select-emissao-recorrente"
> parameterClass="com.globo.billing.core.daos.EmissaoRecorrenteDAO"
> resultClass="com.globo.billing.core.daos.EmissaoRecorrenteDAO"
> cacheModel="cache-Cobranca">
> <![CDATA[
> SELECT
> INFO.ASSINATURA_ID as codigoAssinatura,
> INFO.STATUS_NUM as statusAssinante,
> PAG.TIPO_COBRANCA_ID as tipoCobranca,
> PAG.MEIO_PAGAMENTO_ID as meioPagamento,
> LOGIN.USERNAME_TXT as usernameAssinante,
> PAG.DIA_VENC_NUM as diaVencimento,
> ASSIN.TITULAR_DEPENDENTE_NUM as titularDependente
> FROM
> TB_GLB_ISP_INFO_ASSINATURA INFO,
> TB_GLB_ISP_PAGANTE PAG,
> TB_GLB_ISP_ASSINANTE ASSIN,
> TB_GLB_ISP_LOGIN LOGIN
> WHERE
> INFO.ASSINATURA_ID = PAG.ASSINATURA_ID
> AND INFO.ASSINATURA_ID = ASSIN.ASSINATURA_ID
> AND LOGIN.USUARIO_ID = ASSIN.USUARIO_ID
> AND ASSIN.TITULAR_DEPENDENTE_NUM = 1
> <dynamic>
> <isGreaterThan prepend="AND" property="diaVencimento" compareValue="0">
> PAG.DIA_VENC = #diaVencimento#
> </isGreaterThan>
> <isGreaterThan prepend="AND" property="tipoCobranca" compareValue="0">
> PAG.TIPO_COBRANCA_ID = #tipoCobranca#
> </isGreaterThan>
> </dynamic>
> AND EXISTS (SELECT 1
> FROM TB_GLB_ISP_SERVICO_PRESTADO SERV
> WHERE
> SERV.ASSINATURA_ID = INFO.ASSINATURA_ID
> AND SERV.DATA_INICIO_DT < #dataTerminoCompetencia:DATE#
> AND SERV.STATUS_COBRANCA_ID IN (1, 2))
> ORDER BY
> INFO.ASSINATURA_ID
> ]]>
> </statement>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
If you want more information on JIRA, or have a bug to report see:
http://www.atlassian.com/software/jira
[jira] Closed: (IBATIS-76) Problems with Dynamic Queries
Posted by "Clinton Begin (JIRA)" <ib...@incubator.apache.org>.
[ http://issues.apache.org/jira/browse/IBATIS-76?page=history ]
Clinton Begin closed IBATIS-76:
-------------------------------
Resolution: Invalid
> Problems with Dynamic Queries
> -----------------------------
>
> Key: IBATIS-76
> URL: http://issues.apache.org/jira/browse/IBATIS-76
> Project: iBatis for Java
> Type: Bug
> Components: SQL Maps
> Versions: 2.0.9
> Environment: JRE 1.4.2
> Reporter: Claudio Martins
>
> I have a dynamic query on my SqlMap xml file. When I try to execute it, I receive a SQLException with the message :
> --- The error occurred in com/globo/billing/conf/sql/SqlMapping-Cobranca.xml.
> --- The error occurred while applying a parameter map.
> --- Check the select-emissao-recorrente-InlineParameterMap.
> --- Check the statement (query failed).
> --- Cause: java.sql.SQLException: ORA-00933: SQL command not properly ended
> But when I remove the <dynamic> block, the SQL runs perfectly. Can anybody help me?
> <statement id="select-emissao-recorrente"
> parameterClass="com.globo.billing.core.daos.EmissaoRecorrenteDAO"
> resultClass="com.globo.billing.core.daos.EmissaoRecorrenteDAO"
> cacheModel="cache-Cobranca">
> <![CDATA[
> SELECT
> INFO.ASSINATURA_ID as codigoAssinatura,
> INFO.STATUS_NUM as statusAssinante,
> PAG.TIPO_COBRANCA_ID as tipoCobranca,
> PAG.MEIO_PAGAMENTO_ID as meioPagamento,
> LOGIN.USERNAME_TXT as usernameAssinante,
> PAG.DIA_VENC_NUM as diaVencimento,
> ASSIN.TITULAR_DEPENDENTE_NUM as titularDependente
> FROM
> TB_GLB_ISP_INFO_ASSINATURA INFO,
> TB_GLB_ISP_PAGANTE PAG,
> TB_GLB_ISP_ASSINANTE ASSIN,
> TB_GLB_ISP_LOGIN LOGIN
> WHERE
> INFO.ASSINATURA_ID = PAG.ASSINATURA_ID
> AND INFO.ASSINATURA_ID = ASSIN.ASSINATURA_ID
> AND LOGIN.USUARIO_ID = ASSIN.USUARIO_ID
> AND ASSIN.TITULAR_DEPENDENTE_NUM = 1
> <dynamic>
> <isGreaterThan prepend="AND" property="diaVencimento" compareValue="0">
> PAG.DIA_VENC = #diaVencimento#
> </isGreaterThan>
> <isGreaterThan prepend="AND" property="tipoCobranca" compareValue="0">
> PAG.TIPO_COBRANCA_ID = #tipoCobranca#
> </isGreaterThan>
> </dynamic>
> AND EXISTS (SELECT 1
> FROM TB_GLB_ISP_SERVICO_PRESTADO SERV
> WHERE
> SERV.ASSINATURA_ID = INFO.ASSINATURA_ID
> AND SERV.DATA_INICIO_DT < #dataTerminoCompetencia:DATE#
> AND SERV.STATUS_COBRANCA_ID IN (1, 2))
> ORDER BY
> INFO.ASSINATURA_ID
> ]]>
> </statement>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
If you want more information on JIRA, or have a bug to report see:
http://www.atlassian.com/software/jira
[jira] Commented: (IBATIS-76) Problems with Dynamic Queries
Posted by "Claudio Martins (JIRA)" <ib...@incubator.apache.org>.
[ http://issues.apache.org/jira/browse/IBATIS-76?page=comments#action_59361 ]
Claudio Martins commented on IBATIS-76:
---------------------------------------
I found that the problem is related to the <!CDATA[ tag.
When I removed it, the dynamic substituions just went fine.
Why is this happening?
> Problems with Dynamic Queries
> -----------------------------
>
> Key: IBATIS-76
> URL: http://issues.apache.org/jira/browse/IBATIS-76
> Project: iBatis for Java
> Type: Bug
> Components: SQL Maps
> Versions: 2.0.9
> Environment: JRE 1.4.2
> Reporter: Claudio Martins
>
> I have a dynamic query on my SqlMap xml file. When I try to execute it, I receive a SQLException with the message :
> --- The error occurred in com/globo/billing/conf/sql/SqlMapping-Cobranca.xml.
> --- The error occurred while applying a parameter map.
> --- Check the select-emissao-recorrente-InlineParameterMap.
> --- Check the statement (query failed).
> --- Cause: java.sql.SQLException: ORA-00933: SQL command not properly ended
> But when I remove the <dynamic> block, the SQL runs perfectly. Can anybody help me?
> <statement id="select-emissao-recorrente"
> parameterClass="com.globo.billing.core.daos.EmissaoRecorrenteDAO"
> resultClass="com.globo.billing.core.daos.EmissaoRecorrenteDAO"
> cacheModel="cache-Cobranca">
> <![CDATA[
> SELECT
> INFO.ASSINATURA_ID as codigoAssinatura,
> INFO.STATUS_NUM as statusAssinante,
> PAG.TIPO_COBRANCA_ID as tipoCobranca,
> PAG.MEIO_PAGAMENTO_ID as meioPagamento,
> LOGIN.USERNAME_TXT as usernameAssinante,
> PAG.DIA_VENC_NUM as diaVencimento,
> ASSIN.TITULAR_DEPENDENTE_NUM as titularDependente
> FROM
> TB_GLB_ISP_INFO_ASSINATURA INFO,
> TB_GLB_ISP_PAGANTE PAG,
> TB_GLB_ISP_ASSINANTE ASSIN,
> TB_GLB_ISP_LOGIN LOGIN
> WHERE
> INFO.ASSINATURA_ID = PAG.ASSINATURA_ID
> AND INFO.ASSINATURA_ID = ASSIN.ASSINATURA_ID
> AND LOGIN.USUARIO_ID = ASSIN.USUARIO_ID
> AND ASSIN.TITULAR_DEPENDENTE_NUM = 1
> <dynamic>
> <isGreaterThan prepend="AND" property="diaVencimento" compareValue="0">
> PAG.DIA_VENC = #diaVencimento#
> </isGreaterThan>
> <isGreaterThan prepend="AND" property="tipoCobranca" compareValue="0">
> PAG.TIPO_COBRANCA_ID = #tipoCobranca#
> </isGreaterThan>
> </dynamic>
> AND EXISTS (SELECT 1
> FROM TB_GLB_ISP_SERVICO_PRESTADO SERV
> WHERE
> SERV.ASSINATURA_ID = INFO.ASSINATURA_ID
> AND SERV.DATA_INICIO_DT < #dataTerminoCompetencia:DATE#
> AND SERV.STATUS_COBRANCA_ID IN (1, 2))
> ORDER BY
> INFO.ASSINATURA_ID
> ]]>
> </statement>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
If you want more information on JIRA, or have a bug to report see:
http://www.atlassian.com/software/jira
[jira] Commented: (IBATIS-76) Problems with Dynamic Queries
Posted by "Claudio Martins (JIRA)" <ib...@incubator.apache.org>.
[ http://issues.apache.org/jira/browse/IBATIS-76?page=comments#action_59357 ]
Claudio Martins commented on IBATIS-76:
---------------------------------------
This is the debug log:
DEBUG 2005-02-17 21:23:05,627 - com.ibatis.common.jdbc.SimpleDataSource.popConnection(SimpleDataSource.java:579) - [main] - Created connection 3743136.
DEBUG 2005-02-17 21:23:05,747 - com.ibatis.common.jdbc.logging.ConnectionLogProxy.<init>(ConnectionLogProxy.java:42) - [main] - {conn-100000} Connection
DEBUG 2005-02-17 21:23:06,118 - com.ibatis.common.jdbc.logging.PreparedStatementLogProxy.invoke(PreparedStatementLogProxy.java:48) - [main] - {pstm-100001} PreparedStatement: SELECT INFO.ASSINATURA_ID as codigoAssinatura, INFO.STATUS_NUM as statusAssinante, PAG.TIPO_COBRANCA_ID as tipoCobranca, PAG.MEIO_PAGAMENTO_ID as meioPagamento, LOGIN.USERNAME_TXT as usernameAssinante, PAG.DIA_VENC_NUM as diaVencimento, ASSIN.TITULAR_DEPENDENTE_NUM as titularDependente FROM TB_GLB_ISP_INFO_ASSINATURA INFO, TB_GLB_ISP_PAGANTE PAG, TB_GLB_ISP_ASSINANTE ASSIN, TB_GLB_ISP_LOGIN LOGIN WHERE INFO.ASSINATURA_ID = PAG.ASSINATURA_ID AND INFO.ASSINATURA_ID = ASSIN.ASSINATURA_ID AND LOGIN.USUARIO_ID = ASSIN.USUARIO_ID AND ASSIN.TITULAR_DEPENDENTE_NUM = 1 <dynamic> <isGreaterThan prepend="AND" property="diaVencimento" compareValue="0"> PAG.DIA_VENC = ? </isGreaterThan> <isGreaterThan prepend="AND" property="tipoCobranca" compareValue="0"> PAG.TIPO_COBRANCA_ID = ? </isGreaterThan> </dynamic> AND EXISTS (SELECT 1 FROM TB_GLB_ISP_SERVICO_PRESTADO SERV WHERE SERV.ASSINATURA_ID = INFO.ASSINATURA_ID AND SERV.DATA_INICIO_DT < ? AND SERV.STATUS_COBRANCA_ID IN (1, 2)) ORDER BY INFO.ASSINATURA_ID
DEBUG 2005-02-17 21:23:06,118 - com.ibatis.common.jdbc.logging.PreparedStatementLogProxy.invoke(PreparedStatementLogProxy.java:49) - [main] - {pstm-100001} Parameters: [10, 0, 2005-03-01]
DEBUG 2005-02-17 21:23:06,128 - com.ibatis.common.jdbc.logging.PreparedStatementLogProxy.invoke(PreparedStatementLogProxy.java:50) - [main] - {pstm-100001} Types: [java.lang.Integer, java.lang.Integer, java.sql.Date]
DEBUG 2005-02-17 21:23:06,198 - com.ibatis.common.jdbc.SimpleDataSource.pushConnection(SimpleDataSource.java:527) - [main] - Returned connection 3743136 to pool.
> Problems with Dynamic Queries
> -----------------------------
>
> Key: IBATIS-76
> URL: http://issues.apache.org/jira/browse/IBATIS-76
> Project: iBatis for Java
> Type: Bug
> Components: SQL Maps
> Versions: 2.0.9
> Environment: JRE 1.4.2
> Reporter: Claudio Martins
>
> I have a dynamic query on my SqlMap xml file. When I try to execute it, I receive a SQLException with the message :
> --- The error occurred in com/globo/billing/conf/sql/SqlMapping-Cobranca.xml.
> --- The error occurred while applying a parameter map.
> --- Check the select-emissao-recorrente-InlineParameterMap.
> --- Check the statement (query failed).
> --- Cause: java.sql.SQLException: ORA-00933: SQL command not properly ended
> But when I remove the <dynamic> block, the SQL runs perfectly. Can anybody help me?
> <statement id="select-emissao-recorrente"
> parameterClass="com.globo.billing.core.daos.EmissaoRecorrenteDAO"
> resultClass="com.globo.billing.core.daos.EmissaoRecorrenteDAO"
> cacheModel="cache-Cobranca">
> <![CDATA[
> SELECT
> INFO.ASSINATURA_ID as codigoAssinatura,
> INFO.STATUS_NUM as statusAssinante,
> PAG.TIPO_COBRANCA_ID as tipoCobranca,
> PAG.MEIO_PAGAMENTO_ID as meioPagamento,
> LOGIN.USERNAME_TXT as usernameAssinante,
> PAG.DIA_VENC_NUM as diaVencimento,
> ASSIN.TITULAR_DEPENDENTE_NUM as titularDependente
> FROM
> TB_GLB_ISP_INFO_ASSINATURA INFO,
> TB_GLB_ISP_PAGANTE PAG,
> TB_GLB_ISP_ASSINANTE ASSIN,
> TB_GLB_ISP_LOGIN LOGIN
> WHERE
> INFO.ASSINATURA_ID = PAG.ASSINATURA_ID
> AND INFO.ASSINATURA_ID = ASSIN.ASSINATURA_ID
> AND LOGIN.USUARIO_ID = ASSIN.USUARIO_ID
> AND ASSIN.TITULAR_DEPENDENTE_NUM = 1
> <dynamic>
> <isGreaterThan prepend="AND" property="diaVencimento" compareValue="0">
> PAG.DIA_VENC = #diaVencimento#
> </isGreaterThan>
> <isGreaterThan prepend="AND" property="tipoCobranca" compareValue="0">
> PAG.TIPO_COBRANCA_ID = #tipoCobranca#
> </isGreaterThan>
> </dynamic>
> AND EXISTS (SELECT 1
> FROM TB_GLB_ISP_SERVICO_PRESTADO SERV
> WHERE
> SERV.ASSINATURA_ID = INFO.ASSINATURA_ID
> AND SERV.DATA_INICIO_DT < #dataTerminoCompetencia:DATE#
> AND SERV.STATUS_COBRANCA_ID IN (1, 2))
> ORDER BY
> INFO.ASSINATURA_ID
> ]]>
> </statement>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
If you want more information on JIRA, or have a bug to report see:
http://www.atlassian.com/software/jira
[jira] Commented: (IBATIS-76) Problems with Dynamic Queries
Posted by "Claudio Martins (JIRA)" <ib...@incubator.apache.org>.
[ http://issues.apache.org/jira/browse/IBATIS-76?page=comments#action_59363 ]
Claudio Martins commented on IBATIS-76:
---------------------------------------
The fact is: I need to compare one column with < operator.
Is there any other way to do it?
Considering that I need to work with both <dynamic> and < operator?
Thanks!
> Problems with Dynamic Queries
> -----------------------------
>
> Key: IBATIS-76
> URL: http://issues.apache.org/jira/browse/IBATIS-76
> Project: iBatis for Java
> Type: Bug
> Components: SQL Maps
> Versions: 2.0.9
> Environment: JRE 1.4.2
> Reporter: Claudio Martins
>
> I have a dynamic query on my SqlMap xml file. When I try to execute it, I receive a SQLException with the message :
> --- The error occurred in com/globo/billing/conf/sql/SqlMapping-Cobranca.xml.
> --- The error occurred while applying a parameter map.
> --- Check the select-emissao-recorrente-InlineParameterMap.
> --- Check the statement (query failed).
> --- Cause: java.sql.SQLException: ORA-00933: SQL command not properly ended
> But when I remove the <dynamic> block, the SQL runs perfectly. Can anybody help me?
> <statement id="select-emissao-recorrente"
> parameterClass="com.globo.billing.core.daos.EmissaoRecorrenteDAO"
> resultClass="com.globo.billing.core.daos.EmissaoRecorrenteDAO"
> cacheModel="cache-Cobranca">
> <![CDATA[
> SELECT
> INFO.ASSINATURA_ID as codigoAssinatura,
> INFO.STATUS_NUM as statusAssinante,
> PAG.TIPO_COBRANCA_ID as tipoCobranca,
> PAG.MEIO_PAGAMENTO_ID as meioPagamento,
> LOGIN.USERNAME_TXT as usernameAssinante,
> PAG.DIA_VENC_NUM as diaVencimento,
> ASSIN.TITULAR_DEPENDENTE_NUM as titularDependente
> FROM
> TB_GLB_ISP_INFO_ASSINATURA INFO,
> TB_GLB_ISP_PAGANTE PAG,
> TB_GLB_ISP_ASSINANTE ASSIN,
> TB_GLB_ISP_LOGIN LOGIN
> WHERE
> INFO.ASSINATURA_ID = PAG.ASSINATURA_ID
> AND INFO.ASSINATURA_ID = ASSIN.ASSINATURA_ID
> AND LOGIN.USUARIO_ID = ASSIN.USUARIO_ID
> AND ASSIN.TITULAR_DEPENDENTE_NUM = 1
> <dynamic>
> <isGreaterThan prepend="AND" property="diaVencimento" compareValue="0">
> PAG.DIA_VENC = #diaVencimento#
> </isGreaterThan>
> <isGreaterThan prepend="AND" property="tipoCobranca" compareValue="0">
> PAG.TIPO_COBRANCA_ID = #tipoCobranca#
> </isGreaterThan>
> </dynamic>
> AND EXISTS (SELECT 1
> FROM TB_GLB_ISP_SERVICO_PRESTADO SERV
> WHERE
> SERV.ASSINATURA_ID = INFO.ASSINATURA_ID
> AND SERV.DATA_INICIO_DT < #dataTerminoCompetencia:DATE#
> AND SERV.STATUS_COBRANCA_ID IN (1, 2))
> ORDER BY
> INFO.ASSINATURA_ID
> ]]>
> </statement>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
If you want more information on JIRA, or have a bug to report see:
http://www.atlassian.com/software/jira
[jira] Commented: (IBATIS-76) Problems with Dynamic Queries
Posted by "Clinton Begin (JIRA)" <ib...@incubator.apache.org>.
[ http://issues.apache.org/jira/browse/IBATIS-76?page=comments#action_59362 ]
Clinton Begin commented on IBATIS-76:
-------------------------------------
Oh wow. I didn't even see that.
Of course you can't have parsable XML tags in a CDATA section. The parser ignores everything within CDATA blocks and passes it through directly.
Remove the CDATA section unless you need it (similarly, you should also remove the dynamic tag unless you need it).
> Problems with Dynamic Queries
> -----------------------------
>
> Key: IBATIS-76
> URL: http://issues.apache.org/jira/browse/IBATIS-76
> Project: iBatis for Java
> Type: Bug
> Components: SQL Maps
> Versions: 2.0.9
> Environment: JRE 1.4.2
> Reporter: Claudio Martins
>
> I have a dynamic query on my SqlMap xml file. When I try to execute it, I receive a SQLException with the message :
> --- The error occurred in com/globo/billing/conf/sql/SqlMapping-Cobranca.xml.
> --- The error occurred while applying a parameter map.
> --- Check the select-emissao-recorrente-InlineParameterMap.
> --- Check the statement (query failed).
> --- Cause: java.sql.SQLException: ORA-00933: SQL command not properly ended
> But when I remove the <dynamic> block, the SQL runs perfectly. Can anybody help me?
> <statement id="select-emissao-recorrente"
> parameterClass="com.globo.billing.core.daos.EmissaoRecorrenteDAO"
> resultClass="com.globo.billing.core.daos.EmissaoRecorrenteDAO"
> cacheModel="cache-Cobranca">
> <![CDATA[
> SELECT
> INFO.ASSINATURA_ID as codigoAssinatura,
> INFO.STATUS_NUM as statusAssinante,
> PAG.TIPO_COBRANCA_ID as tipoCobranca,
> PAG.MEIO_PAGAMENTO_ID as meioPagamento,
> LOGIN.USERNAME_TXT as usernameAssinante,
> PAG.DIA_VENC_NUM as diaVencimento,
> ASSIN.TITULAR_DEPENDENTE_NUM as titularDependente
> FROM
> TB_GLB_ISP_INFO_ASSINATURA INFO,
> TB_GLB_ISP_PAGANTE PAG,
> TB_GLB_ISP_ASSINANTE ASSIN,
> TB_GLB_ISP_LOGIN LOGIN
> WHERE
> INFO.ASSINATURA_ID = PAG.ASSINATURA_ID
> AND INFO.ASSINATURA_ID = ASSIN.ASSINATURA_ID
> AND LOGIN.USUARIO_ID = ASSIN.USUARIO_ID
> AND ASSIN.TITULAR_DEPENDENTE_NUM = 1
> <dynamic>
> <isGreaterThan prepend="AND" property="diaVencimento" compareValue="0">
> PAG.DIA_VENC = #diaVencimento#
> </isGreaterThan>
> <isGreaterThan prepend="AND" property="tipoCobranca" compareValue="0">
> PAG.TIPO_COBRANCA_ID = #tipoCobranca#
> </isGreaterThan>
> </dynamic>
> AND EXISTS (SELECT 1
> FROM TB_GLB_ISP_SERVICO_PRESTADO SERV
> WHERE
> SERV.ASSINATURA_ID = INFO.ASSINATURA_ID
> AND SERV.DATA_INICIO_DT < #dataTerminoCompetencia:DATE#
> AND SERV.STATUS_COBRANCA_ID IN (1, 2))
> ORDER BY
> INFO.ASSINATURA_ID
> ]]>
> </statement>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
If you want more information on JIRA, or have a bug to report see:
http://www.atlassian.com/software/jira
[jira] Commented: (IBATIS-76) Problems with Dynamic Queries
Posted by "Clinton Begin (JIRA)" <ib...@incubator.apache.org>.
[ http://issues.apache.org/jira/browse/IBATIS-76?page=comments#action_59366 ]
Clinton Begin commented on IBATIS-76:
-------------------------------------
Yes, you can use a CDATA block around *just* the code you need, or you can use the < sequence. It's all standard XML.
Cheers,
Clinton
> Problems with Dynamic Queries
> -----------------------------
>
> Key: IBATIS-76
> URL: http://issues.apache.org/jira/browse/IBATIS-76
> Project: iBatis for Java
> Type: Bug
> Components: SQL Maps
> Versions: 2.0.9
> Environment: JRE 1.4.2
> Reporter: Claudio Martins
>
> I have a dynamic query on my SqlMap xml file. When I try to execute it, I receive a SQLException with the message :
> --- The error occurred in com/globo/billing/conf/sql/SqlMapping-Cobranca.xml.
> --- The error occurred while applying a parameter map.
> --- Check the select-emissao-recorrente-InlineParameterMap.
> --- Check the statement (query failed).
> --- Cause: java.sql.SQLException: ORA-00933: SQL command not properly ended
> But when I remove the <dynamic> block, the SQL runs perfectly. Can anybody help me?
> <statement id="select-emissao-recorrente"
> parameterClass="com.globo.billing.core.daos.EmissaoRecorrenteDAO"
> resultClass="com.globo.billing.core.daos.EmissaoRecorrenteDAO"
> cacheModel="cache-Cobranca">
> <![CDATA[
> SELECT
> INFO.ASSINATURA_ID as codigoAssinatura,
> INFO.STATUS_NUM as statusAssinante,
> PAG.TIPO_COBRANCA_ID as tipoCobranca,
> PAG.MEIO_PAGAMENTO_ID as meioPagamento,
> LOGIN.USERNAME_TXT as usernameAssinante,
> PAG.DIA_VENC_NUM as diaVencimento,
> ASSIN.TITULAR_DEPENDENTE_NUM as titularDependente
> FROM
> TB_GLB_ISP_INFO_ASSINATURA INFO,
> TB_GLB_ISP_PAGANTE PAG,
> TB_GLB_ISP_ASSINANTE ASSIN,
> TB_GLB_ISP_LOGIN LOGIN
> WHERE
> INFO.ASSINATURA_ID = PAG.ASSINATURA_ID
> AND INFO.ASSINATURA_ID = ASSIN.ASSINATURA_ID
> AND LOGIN.USUARIO_ID = ASSIN.USUARIO_ID
> AND ASSIN.TITULAR_DEPENDENTE_NUM = 1
> <dynamic>
> <isGreaterThan prepend="AND" property="diaVencimento" compareValue="0">
> PAG.DIA_VENC = #diaVencimento#
> </isGreaterThan>
> <isGreaterThan prepend="AND" property="tipoCobranca" compareValue="0">
> PAG.TIPO_COBRANCA_ID = #tipoCobranca#
> </isGreaterThan>
> </dynamic>
> AND EXISTS (SELECT 1
> FROM TB_GLB_ISP_SERVICO_PRESTADO SERV
> WHERE
> SERV.ASSINATURA_ID = INFO.ASSINATURA_ID
> AND SERV.DATA_INICIO_DT < #dataTerminoCompetencia:DATE#
> AND SERV.STATUS_COBRANCA_ID IN (1, 2))
> ORDER BY
> INFO.ASSINATURA_ID
> ]]>
> </statement>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
If you want more information on JIRA, or have a bug to report see:
http://www.atlassian.com/software/jira