Mailing-List: contact user-java-help@ibatis.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user-java@ibatis.apache.org
Received-SPF: pass (nike.apache.org: domain of lists@nabble.com designates
 216.139.236.158 as permitted sender)
Message-ID: <27858161.post@talk.nabble.com>
Date: Wed, 10 Mar 2010 17:14:30 -0800 (PST)
From: marksomaha <mark.stevens@dtn.com>
To: user-java@ibatis.apache.org
Subject: Re: ibatis 3.0 - @Insert with Insert select fails
In-Reply-To: <2f55db671003101531qe86a037gf1323e1a8d71129e@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
References: <27857358.post@talk.nabble.com>
 <2f55db671003101531qe86a037gf1323e1a8d71129e@mail.gmail.com>


Follow-up:  Embarrassed to say the "Invalid table name" was my own typo. 
"Insert into USER..." should have been "Insert into USERS..."

Now, it runs, but it is still not cloning the Users row.  In fact, it is
quite interesting to see what Oracle thinks is being executed on the DB
server.

@Insert("insert into USERS (USERID, USERNAME, PASSWORD, SERVER_GROUP) " +
            "select ${trgUserId}, USERNAME, PASSWORD, SERVER_GROUP " +
            "from USERS where USERID =
#{srcUserId,javaType=int,jdbcType=INTEGER}")
    void cloneUser(final int trgUserId, final int srcUserId);
Called with: mapper.cloneUser(6, 1);

In Oracle it appears as:
insert into USERS (USERID, USERNAME, PASSWORD, SERVER_GROUP) select null,
USERNAME, PASSWORD, SERVER_GROUP from USERS where USERID = :1

When I use the #{trgUserId} syntax, I get the following:
@Insert("insert into USERS (USERID, USERNAME, PASSWORD, SERVER_GROUP) " +
            "select #{trgUserId,javaType=int,jdbcType=INTEGER}, USERNAME,
PASSWORD, SERVER_GROUP " +
            "from USERS where USERID =
#{srcUserId,javaType=int,jdbcType=INTEGER}")
    void cloneUser(final int trgUserId, final int srcUserId);

In Oracle it appears as:
	insert into USERS (USERID, USERNAME, PASSWORD, SERVER_GROUP) select :1,
USERNAME, PASSWORD, SERVER_GROUP from USERS where USERID = :2

Somehow, I'm not getting the correct values passed as bind variable values.
Ideas?  I'm stumped!
Mark


nmaves wrote:
> 
> Sure do :)  Great error message eh....
> 
> use ${trgUserId} in the select portion and leave #{trgUserId} in the where
> clause.   # infers a true jdbc parameter which are not allowed in the
> select
> portion of a statement.
> 
> Nahtan
> 
> On Wed, Mar 10, 2010 at 4:28 PM, marksomaha <mark.stevens@dtn.com> wrote:
> 
>>
>> I am trying to do a insert that copies rows from an existing user.
>> Essentially cloning a user, but change userid for the new record.
>>
>> Here is the syntax:
>>    @Insert("insert into USER (USERID, USERNAME, PASSWORD, SERVER_GROUP) "
>> +
>>            "select #{trgUserId,javaType=int,jdbcType=INTEGER}, USERNAME,
>> PASSWORD, SERVER_GROUP " +
>>            "from USER where USERID =
>> #{srcUserId,javaType=int,jdbcType=INTEGER}")
>>    void cloneUser(final int trgUserId, final int srcUserId);
>>
>> When I try this syntax, I get the following error back:
>>
>> org.apache.ibatis.exceptions.IbatisException:
>> ### Error updating database.  Cause: java.sql.SQLException: ORA-00903:
>> invalid table name
>>
>> ### The error may involve testibatis3.UserMapper.cloneUser-Inline
>> ### The error occurred while setting parameters
>> ### Cause: java.sql.SQLException: ORA-00903: invalid table name
>>
>>        at
>>
>> org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:8)
>>        at
>>
>> org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:100)
>>        at
>>
>> org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:87)
>>        at
>> org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:54)
>>        at
>> org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:35)
>>
>> Any ideas as to what would cause an "Invalid table name"?  The USERS
>> table
>> exists and I have verified the SQL statement in SQLPLUS.
>> --
>> View this message in context:
>> http://old.nabble.com/ibatis-3.0---%40Insert-with-Insert-select-fails-tp27857358p27857358.html
>> Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
>> For additional commands, e-mail: user-java-help@ibatis.apache.org
>>
>>
> 
> 

-- 
View this message in context: http://old.nabble.com/ibatis-3.0---%40Insert-with-Insert-select-fails-tp27857358p27858161.html
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.


---------------------------------------------------------------------
To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
For additional commands, e-mail: user-java-help@ibatis.apache.org