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 Evan McQuinn <Ev...@noaa.gov> on 2009/08/26 00:15:04 UTC
Problem substituting inline parameter value in alter table statement
Hello -
I'm trying to run an alter table command to swap partitions in a large
Oracle table. I haven't found any documentation regarding alter table in
iBatis specifically, so I'm using the update mapping. Seems
straightforward enough, but for some reason my inline #value# parameter
is not being substituted into the final statement (I think...).
Here's what I've got:
--------
Container: Tomcat 6.0.16
Database: Oracle 10.2.0.4
Driver: ojdbc6-11.1.0.7.0
iBatis Version: 2.3.4.726
SQL MAP (Point.xml):
<sqlMap namespace="Point">
<sql id="pointTable">TRACKLINE_PT</sql>
<sql id="tempTable">TRACKLINE_PT_SWAP</sql>
<update id="swapTempToPart" parameterClass="java.lang.String">
alter table <include refid="Point.pointTable"/>
exchange partition #value#
with table <include refid="Point.tempTable"/>
without validation
update global indexes
</update>
</sqlMap>
JAVA:
public class PointIbatisWriteDAO extends SqlMapClientDaoSupport {
. . .
public void deletePoints() {
SqlMapClient sqlMap = getSqlMapClient();
sqlMap.update("Point.swapTempToPart", "TRACKLINE_PART_01");
}
}
ERROR:
--- The error occurred in gov/noaa/ngdc/mgg/geodas/Point.xml.
--- The error occurred while applying a parameter map.
--- Check the Point.swapTempToPart-InlineParameterMap.
--- Check the statement (update failed).
--- Cause: java.sql.SQLException: ORA-14006: invalid partition name
STACK (rebuilt via debugging in Eclipse):
T4C8Oall.receive() line: 484 [local variables unavailable]
T4CPreparedStatement.doOall8(boolean, boolean, boolean, boolean) line: 216
T4CPreparedStatement.executeForRows(boolean) line: 955
T4CPreparedStatement(OracleStatement).doExecuteWithTimeout() line: 1168
[local variables unavailable]
T4CPreparedStatement(OraclePreparedStatement).executeInternal() line:
3285 [local variables unavailable]
T4CPreparedStatement(OraclePreparedStatement).execute() line: 3390
[local variables unavailable]
NativeMethodAccessorImpl.invoke0(Method, Object, Object[]) line: not
available [native method]
NativeMethodAccessorImpl.invoke(Object, Object[]) line: 39
DelegatingMethodAccessorImpl.invoke(Object, Object[]) line: 25
Method.invoke(Object, Object...) line: 575
PreparedStatementLogProxy.invoke(Object, Method, Object[]) line: 62
$Proxy2.execute() line: not available [local variables unavailable]
SqlExecutor.executeUpdate(StatementScope, Connection, String, Object[])
line: 80
UpdateStatement(MappedStatement).sqlExecuteUpdate(StatementScope,
Connection, String, Object[]) line: 216
UpdateStatement(MappedStatement).executeUpdate(StatementScope,
Transaction, Object) line: 94
SqlMapExecutorDelegate.update(SessionScope, String, Object) line: 457
SqlMapSessionImpl.update(String, Object) line: 90
SqlMapClientImpl.update(String, Object) line: 66
PointIbatisWriteDAO.deletePoints(SqlMapClient, String, Integer, boolean)
line: 209
. . .
(This is being kicked off by a JUnit test down here)
LOG:
DEBUG 2009-08-25 10:09:49,290 Connection - {conn-100014} Connection
DEBUG 2009-08-25 10:10:30,048 Connection - {conn-100014} Preparing
Statement: alter table TRACKLINE_PT exchange partition ?
with table TRACKLINE_PT_SWAP without validation update global
indexes
DEBUG 2009-08-25 10:11:17,793 PreparedStatement - {pstm-100015}
Executing Statement: alter table TRACKLINE_PT exchange
partition ? with table TRACKLINE_PT_SWAP without
validation update global indexes
DEBUG 2009-08-25 10:11:18,113 PreparedStatement - {pstm-100015}
Parameters: [TRACKLINE_PT_PART_01]
DEBUG 2009-08-25 10:11:18,377 PreparedStatement - {pstm-100015} Types:
[java.lang.String]
--------
If I run the the statement with "TRACKLINE_PART_01" hardcoded into the
mapping it works fine, and the logs resemble those from other sql
mappings which use inline #value# substitution and also work. Why would
that substitution not take place? Do I need to do something different
with statements like 'alter table'? Am I missing something else?
Thanks for you help.
- Evan
---------------------------------------------------------------------
To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
For additional commands, e-mail: user-java-help@ibatis.apache.org
Re: Problem substituting inline parameter value in alter table
statement
Posted by Nathan Maves <na...@gmail.com>.
you need to use the string substitution option.
$value$
nathan
On Tue, Aug 25, 2009 at 4:15 PM, Evan McQuinn <Ev...@noaa.gov> wrote:
> Hello -
>
> I'm trying to run an alter table command to swap partitions in a large
> Oracle table. I haven't found any documentation regarding alter table in
> iBatis specifically, so I'm using the update mapping. Seems straightforward
> enough, but for some reason my inline #value# parameter is not being
> substituted into the final statement (I think...).
>
> Here's what I've got:
>
> --------
>
> Container: Tomcat 6.0.16
> Database: Oracle 10.2.0.4
> Driver: ojdbc6-11.1.0.7.0
> iBatis Version: 2.3.4.726
>
> SQL MAP (Point.xml):
> <sqlMap namespace="Point">
>
> <sql id="pointTable">TRACKLINE_PT</sql>
> <sql id="tempTable">TRACKLINE_PT_SWAP</sql>
>
> <update id="swapTempToPart" parameterClass="java.lang.String">
> alter table <include refid="Point.pointTable"/>
> exchange partition #value#
> with table <include refid="Point.tempTable"/>
> without validation
> update global indexes
> </update>
>
> </sqlMap>
>
> JAVA:
> public class PointIbatisWriteDAO extends SqlMapClientDaoSupport {
> . . .
> public void deletePoints() {
> SqlMapClient sqlMap = getSqlMapClient();
> sqlMap.update("Point.swapTempToPart", "TRACKLINE_PART_01");
> }
> }
>
> ERROR:
> --- The error occurred in gov/noaa/ngdc/mgg/geodas/Point.xml.
> --- The error occurred while applying a parameter map.
> --- Check the Point.swapTempToPart-InlineParameterMap.
> --- Check the statement (update failed).
> --- Cause: java.sql.SQLException: ORA-14006: invalid partition name
>
> STACK (rebuilt via debugging in Eclipse):
> T4C8Oall.receive() line: 484 [local variables unavailable]
> T4CPreparedStatement.doOall8(boolean, boolean, boolean, boolean) line: 216
> T4CPreparedStatement.executeForRows(boolean) line: 955
> T4CPreparedStatement(OracleStatement).doExecuteWithTimeout() line: 1168
> [local variables unavailable]
> T4CPreparedStatement(OraclePreparedStatement).executeInternal() line: 3285
> [local variables unavailable]
> T4CPreparedStatement(OraclePreparedStatement).execute() line: 3390 [local
> variables unavailable]
> NativeMethodAccessorImpl.invoke0(Method, Object, Object[]) line: not
> available [native method]
> NativeMethodAccessorImpl.invoke(Object, Object[]) line: 39
> DelegatingMethodAccessorImpl.invoke(Object, Object[]) line: 25
> Method.invoke(Object, Object...) line: 575
> PreparedStatementLogProxy.invoke(Object, Method, Object[]) line: 62
> $Proxy2.execute() line: not available [local variables unavailable]
> SqlExecutor.executeUpdate(StatementScope, Connection, String, Object[])
> line: 80
> UpdateStatement(MappedStatement).sqlExecuteUpdate(StatementScope,
> Connection, String, Object[]) line: 216
> UpdateStatement(MappedStatement).executeUpdate(StatementScope, Transaction,
> Object) line: 94
> SqlMapExecutorDelegate.update(SessionScope, String, Object) line: 457
> SqlMapSessionImpl.update(String, Object) line: 90
> SqlMapClientImpl.update(String, Object) line: 66
> PointIbatisWriteDAO.deletePoints(SqlMapClient, String, Integer, boolean)
> line: 209
> . . .
> (This is being kicked off by a JUnit test down here)
>
> LOG:
> DEBUG 2009-08-25 10:09:49,290 Connection - {conn-100014} Connection
> DEBUG 2009-08-25 10:10:30,048 Connection - {conn-100014} Preparing
> Statement: alter table TRACKLINE_PT exchange partition ? with
> table TRACKLINE_PT_SWAP without validation update global indexes
> DEBUG 2009-08-25 10:11:17,793 PreparedStatement - {pstm-100015} Executing
> Statement: alter table TRACKLINE_PT exchange partition ? with
> table TRACKLINE_PT_SWAP without validation update global indexes
> DEBUG 2009-08-25 10:11:18,113 PreparedStatement - {pstm-100015} Parameters:
> [TRACKLINE_PT_PART_01]
> DEBUG 2009-08-25 10:11:18,377 PreparedStatement - {pstm-100015} Types:
> [java.lang.String]
>
> --------
>
> If I run the the statement with "TRACKLINE_PART_01" hardcoded into the
> mapping it works fine, and the logs resemble those from other sql mappings
> which use inline #value# substitution and also work. Why would that
> substitution not take place? Do I need to do something different with
> statements like 'alter table'? Am I missing something else?
>
> Thanks for you help.
>
> - Evan
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
> For additional commands, e-mail: user-java-help@ibatis.apache.org
>
>
Re: Problem substituting inline parameter value in alter table
statement
Posted by Evan McQuinn <Ev...@noaa.gov>.
Answered my own question:
I just discovered that I'd missed the subtle syntax difference between
dynamically provided parameters and actual chunks of sql; I need to use
$value$ instead of #value#.
Sorry 'bout that.
- Evan
Evan McQuinn wrote:
> Hello -
>
> I'm trying to run an alter table command to swap partitions in a large
> Oracle table. I haven't found any documentation regarding alter table
> in iBatis specifically, so I'm using the update mapping. Seems
> straightforward enough, but for some reason my inline #value#
> parameter is not being substituted into the final statement (I think...).
>
> Here's what I've got:
>
> --------
>
> Container: Tomcat 6.0.16
> Database: Oracle 10.2.0.4
> Driver: ojdbc6-11.1.0.7.0
> iBatis Version: 2.3.4.726
>
> SQL MAP (Point.xml):
> <sqlMap namespace="Point">
>
> <sql id="pointTable">TRACKLINE_PT</sql>
> <sql id="tempTable">TRACKLINE_PT_SWAP</sql>
>
> <update id="swapTempToPart" parameterClass="java.lang.String">
> alter table <include refid="Point.pointTable"/>
> exchange partition #value#
> with table <include refid="Point.tempTable"/>
> without validation
> update global indexes
> </update>
>
> </sqlMap>
>
> JAVA:
> public class PointIbatisWriteDAO extends SqlMapClientDaoSupport {
> . . .
> public void deletePoints() {
> SqlMapClient sqlMap = getSqlMapClient();
> sqlMap.update("Point.swapTempToPart", "TRACKLINE_PART_01");
> }
> }
>
> ERROR:
> --- The error occurred in gov/noaa/ngdc/mgg/geodas/Point.xml.
> --- The error occurred while applying a parameter map.
> --- Check the Point.swapTempToPart-InlineParameterMap.
> --- Check the statement (update failed).
> --- Cause: java.sql.SQLException: ORA-14006: invalid partition name
>
> STACK (rebuilt via debugging in Eclipse):
> T4C8Oall.receive() line: 484 [local variables unavailable]
> T4CPreparedStatement.doOall8(boolean, boolean, boolean, boolean) line:
> 216
> T4CPreparedStatement.executeForRows(boolean) line: 955
> T4CPreparedStatement(OracleStatement).doExecuteWithTimeout() line:
> 1168 [local variables unavailable]
> T4CPreparedStatement(OraclePreparedStatement).executeInternal() line:
> 3285 [local variables unavailable]
> T4CPreparedStatement(OraclePreparedStatement).execute() line: 3390
> [local variables unavailable]
> NativeMethodAccessorImpl.invoke0(Method, Object, Object[]) line: not
> available [native method]
> NativeMethodAccessorImpl.invoke(Object, Object[]) line: 39
> DelegatingMethodAccessorImpl.invoke(Object, Object[]) line: 25
> Method.invoke(Object, Object...) line: 575
> PreparedStatementLogProxy.invoke(Object, Method, Object[]) line: 62
> $Proxy2.execute() line: not available [local variables unavailable]
> SqlExecutor.executeUpdate(StatementScope, Connection, String,
> Object[]) line: 80
> UpdateStatement(MappedStatement).sqlExecuteUpdate(StatementScope,
> Connection, String, Object[]) line: 216
> UpdateStatement(MappedStatement).executeUpdate(StatementScope,
> Transaction, Object) line: 94
> SqlMapExecutorDelegate.update(SessionScope, String, Object) line: 457
> SqlMapSessionImpl.update(String, Object) line: 90
> SqlMapClientImpl.update(String, Object) line: 66
> PointIbatisWriteDAO.deletePoints(SqlMapClient, String, Integer,
> boolean) line: 209
> . . .
> (This is being kicked off by a JUnit test down here)
>
> LOG:
> DEBUG 2009-08-25 10:09:49,290 Connection - {conn-100014} Connection
> DEBUG 2009-08-25 10:10:30,048 Connection - {conn-100014} Preparing
> Statement: alter table TRACKLINE_PT exchange partition ?
> with table TRACKLINE_PT_SWAP without validation update
> global indexes
> DEBUG 2009-08-25 10:11:17,793 PreparedStatement - {pstm-100015}
> Executing Statement: alter table TRACKLINE_PT exchange
> partition ? with table TRACKLINE_PT_SWAP without
> validation update global indexes
> DEBUG 2009-08-25 10:11:18,113 PreparedStatement - {pstm-100015}
> Parameters: [TRACKLINE_PT_PART_01]
> DEBUG 2009-08-25 10:11:18,377 PreparedStatement - {pstm-100015} Types:
> [java.lang.String]
>
> --------
>
> If I run the the statement with "TRACKLINE_PART_01" hardcoded into the
> mapping it works fine, and the logs resemble those from other sql
> mappings which use inline #value# substitution and also work. Why
> would that substitution not take place? Do I need to do something
> different with statements like 'alter table'? Am I missing something
> else?
>
> Thanks for you help.
>
> - Evan
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
> For additional commands, e-mail: user-java-help@ibatis.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
For additional commands, e-mail: user-java-help@ibatis.apache.org