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 marksomaha <ma...@dtn.com> on 2010/03/11 00:28:11 UTC
ibatis 3.0 - @Insert with Insert select fails
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
Re: ibatis 3.0 - @Insert with Insert select fails
Posted by Clinton Begin <cl...@gmail.com>.
@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(@Param("trgUserId") final int trgUserId,
@Param("srcUserId") final
int srcUserId);
The reason you need to do this is that Java cannot introspect upon parameter
names (which is really unfortunate).
But it's still better than using a HashMap...
Clinton
PS: If you don't specify parameter names, they default to #{0}, #{1}, #{2}
etc...
On Wed, Mar 10, 2010 at 10:11 PM, marksomaha <ma...@dtn.com> wrote:
>
> 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 <ma...@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 <ma...@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
>
>
Re: ibatis 3.0 - @Insert with Insert select fails
Posted by marksomaha <ma...@dtn.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 <ma...@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 <ma...@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
Re: ibatis 3.0 - @Insert with Insert select fails
Posted by Clinton Begin <cl...@gmail.com>.
Are you naming the parameters in your method using @Param?
Clinton
On Wed, Mar 10, 2010 at 6:14 PM, marksomaha <ma...@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 <ma...@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
>
>
Re: ibatis 3.0 - @Insert with Insert select fails
Posted by marksomaha <ma...@dtn.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 <ma...@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
Re: ibatis 3.0 - @Insert with Insert select fails
Posted by Iwao AVE! <ha...@gmail.com>.
'USER' is a reserved word.
It would be possible to use doublequotes to escape it (not sure).
Regards,
Iwao
On 2010/03/11, at 8:41, marksomaha <ma...@dtn.com> wrote:
>
> Thanks for the quick reply Nahtan!
>
> However, it is still giving the same "invalid Table name" error.
> Here is the modified annotation per your suggestion.
> @Insert("insert into USER (USERID, USERNAME, PASSWORD,
> SERVER_GROUP) " +
> "select ${trgUserId}, USERNAME, PASSWORD, SERVER_GROUP " +
> "from USER where USERID =
> #{srcUserId,javaType=int,jdbcType=INTEGER}")
> Did I miss something?
>
>
> 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 <ma...@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-tp27857358p27857465.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
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
For additional commands, e-mail: user-java-help@ibatis.apache.org
Re: ibatis 3.0 - @Insert with Insert select fails
Posted by marksomaha <ma...@dtn.com>.
Thanks for the quick reply Nahtan!
However, it is still giving the same "invalid Table name" error.
Here is the modified annotation per your suggestion.
@Insert("insert into USER (USERID, USERNAME, PASSWORD, SERVER_GROUP) " +
"select ${trgUserId}, USERNAME, PASSWORD, SERVER_GROUP " +
"from USER where USERID =
#{srcUserId,javaType=int,jdbcType=INTEGER}")
Did I miss something?
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 <ma...@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-tp27857358p27857465.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
Re: ibatis 3.0 - @Insert with Insert select fails
Posted by Nathan Maves <na...@gmail.com>.
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 <ma...@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
>
>