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: <27859395.post@talk.nabble.com>
Date: Wed, 10 Mar 2010 21:11:21 -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: <16178eb11003101747o5490783awaaa62fe115a5b5db@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>
 <27858161.post@talk.nabble.com>
 <16178eb11003101747o5490783awaaa62fe115a5b5db@mail.gmail.com>


Clinton,

No, I am not using @Param.  I've read the User Guide, but I don't know what
the exact syntax to use.
As I am a newbie to Ibatis, please provide an example of its use.

Thanks
Mark


Clinton Begin wrote:
> 
> Are you naming the parameters in your method using @Param?
> 
> Clinton
> 
> On Wed, Mar 10, 2010 at 6:14 PM, marksomaha <mark.stevens@dtn.com> wrote:
> 
>>
>> 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
>>
>>
> 
> 

-- 
View this message in context: http://old.nabble.com/ibatis-3.0---%40Insert-with-Insert-select-fails-tp27857358p27859395.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