'insert/update/delete' return values

[q111111]

The user's guide mentions the insert/update/delete returning an 'int'
result for how many rows effected by the queries.

My mapper file does one of each operation and the operations are
running correctly (I check the results of each operation in the
database). I keep getting back a return value of -2147482646
regardless of the operation.
I am using Oracle and Eclipse.

Is there some trick to get back the correct number of rows effected?

[Guy Rouillier]

What version of iBATIS are you using? What version of Oracle? How is
your Java mapper function defined?

--
Guy Rouillier

[q111111]

>What version of iBATIS are you using? What version of Oracle? How is
>your Java mapper function defined?

MyBatis 3.0.1
Oracle 10g
Mapper file:
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="my.path">
<delete id="delete"
delete from MyTable
</delete>
</mapper>

[q111111]

Correction ... make that Oracle 11g.

Sorry about that.

[Guy Rouillier]

And from the original question list: How is your Java mapper function
defined?

--
Guy Rouillier

[q111111]

Is this what you you are looking for?

public int delete(SqlSession session, BaseDomain domainObj)
throws SQLException
{
try {
int nbrRowsDeleted = session.delete("my.path.delete");
return nbrRowsDeleted;
}
catch (Exception e) {}

[Guy Rouillier]

On 6/15/2010 4:41 PM, q111111 wrote:
> Is this what you you are looking for?
>
> public int delete(SqlSession session, BaseDomain domainObj)
> throws SQLException
> {
> try {
> int nbrRowsDeleted = session.delete("my.path.delete");
> return nbrRowsDeleted;
> }
> catch (Exception e) {}
> }

Yes. I've not done it this way, but I don't see why it shouldn't work.
Can you try this using mappers instead? See the section "Using
Mappers" in the documentation. I use those exclusively and they provide
accurate counts.

Your return values look like a 2's complement number. I wonder if
Oracle is returning a long and iBATIS is doing an erroneous conversion
to int.

>
>
> On Jun 15, 1:27 pm, Guy Rouillier<g...@burntmail.com> wrote:
>> On 6/15/2010 8:13 AM, q111111 wrote:
>>
>>>> What version of iBATIS are you using? What version of Oracle? How is
>>>> your Java mapper function defined?
>>
>>> MyBatis 3.0.1
>>> Oracle 10g
>>> Mapper file:
>>> <?xml version="1.0" encoding="UTF-8" ?>
>>> <!DOCTYPE mapper
>>> PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
>>> "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
>>> <mapper namespace="my.path">
>>> <delete id="delete"
>>> delete from MyTable
>>> </delete>
>>> </mapper>
>>
>> And from the original question list: How is your Java mapper function
>> defined?
>>
>> --
>> Guy Rouillier
>

--
Guy Rouillier

[q111111]

I did the switch over to using Java mappers and I am still getting the
incorrect counts.


MyMapper mapper = session.getMapper(MyMapper.class);
return mapper.delete(domainObj);

The underlying query is correctly deleting from the database.
It is just the returning count that is incorrect.
This is still returning number of rows deleted as -2147482646.

Any other suggestions?

If nothing else, I can count the number of rows pre and post delete
and get the difference.

On Jun 15, 5:39 pm, Guy Rouillier <g...@burntmail.com> wrote:
> On 6/15/2010 4:41 PM, q111111 wrote:
>
> > Is this what you you are looking for?
>
> > public int delete(SqlSession session, BaseDomain domainObj)
> > throws SQLException
> > {
> >     try {
> >       int nbrRowsDeleted = session.delete("my.path.delete");
> >       return nbrRowsDeleted;
> >     }
> >     catch (Exception e) {}
> > }
>
> Yes.  I've not done it this way, but I don't see why it shouldn't work.
>   Can you try this using mappers instead?  See the section "Using
> Mappers" in the documentation.  I use those exclusively and they provide
> accurate counts.
>
> Your return values look like a 2's complement number.  I wonder if
> Oracle is returning a long and iBATIS is doing an erroneous conversion
> to int.
>

> - Show quoted text -

[Guy Rouillier]

On 6/16/2010 3:26 PM, q111111 wrote:
> I did the switch over to using Java mappers and I am still getting the
> incorrect counts.
>
>
> MyMapper mapper = session.getMapper(MyMapper.class);
> return mapper.delete(domainObj);
>
> The underlying query is correctly deleting from the database.
> It is just the returning count that is incorrect.
> This is still returning number of rows deleted as -2147482646.
>
> Any other suggestions?
>
> If nothing else, I can count the number of rows pre and post delete
> and get the difference.

That shouldn't be necessary. I just did a quick test, also using Oracle
10g, starting with your code:

XML:

<delete id="delete">
delete from test
</delete>

BTW, the code you posted was missing the closing angle bracket on the
first line. I assume that was just a copy and paste error on your part;
iBATIS threw a read error exception without it.

mapper.java:

int delete();

main.java:

int iRows = mapper.delete();
log.info("Number of rows deleted: " + iRows);
session.commit();

Prints "Number of rows deleted: 30", which is the number of rows in my
test table.

I'm using JDK 1.6.0_16 on Windows. The only other thing I see is that
you are not printing out the rows deleted immediately after invocation,
but are instead returning the value. Print out the value immediately
after invocation, as I've done. Maybe the issue is in the calling
routine and not in the iBATIS invocation.

--
Guy Rouillier

[q111111]

I am using Eclipse, Oracle 11g, JDK 1.6.0 and Windows.

XML:
<delete id="delete">
delete from test
</delete>

Mapper:
public int delete() throws SQLException;

Main.java:
int iRows = mapper.delete();
System.out/println("Number of rows deleted: " + iRows);
session.commit();

Console message:
Number of rows deleted: -2147482646

[Guy Rouillier]

Very odd, I'm running out of ideas. I went back to your original
message. You said you are always seeing this same return value
-2147482646 for insert, update and delete? And for delete you get the
same value regardless of how many rows are deleted?

Are you using ojdbc6.jar from 11g? Make sure no other Oracle JARs exist
on your classpath.

--
Guy Rouillier

[Clinton Begin]

Wow, this sounds odd. Have you tried just writing a quick JDBC test
without ibatis to see if statement.executeUpdate() returns a valid
value?

[q111111]

Odd indeed!

PreparedStatement preparedStatement = con.prepareStatement("delete
from test");
int iRows = preparedStatement.executeUpdate();
System.out.println("iRows is " + iRows);

Output: iRows is 21
That is the correct answer ... 21 rows were in the table.

> > Guy Rouillier- Hide quoted text -

[q111111]

Yes ... only ojdbc6.jar from 11g ... no other Oracle JARs.

> Guy Rouillier- Hide quoted text -
>

[Clinton Begin]

Oh... you know what.... try this:

SqlSession session = sqlMapper.openSession(ExecutorType.SIMPLE);

If that works, you can configure it to be the default.  If the BATCH executor is in use, the update counts are being lost.  I think there's an issue already created for this, although not described this way.

Clinton

[q111111]

Mystery solved!
You are correct ... I did was using BATCH.
Switching to SIMPLE returned the correct number of effected rows.

Thank you!

On Jun 17, 3:15 pm, Clinton Begin <clinton.be...@gmail.com> wrote:
> Oh... you know what.... try this:
>

> SqlSession session = sqlMapper.openSession(*ExecutorType.SIMPLE*);

> >> - Show quoted text -- Hide quoted text -

[Clinton Begin]

Not your fault.  We'll have to consider 3 possible improvements.

* If BATCH is the default, perhaps it shouldn't be.
* We should be able to return the rows affected if there's only one result.
* The documentation should be more clear about this.

Clinton

[Guy Rouillier]

On 6/18/2010 10:52 AM, Clinton Begin wrote:
> Not your fault. We'll have to consider 3 possible improvements.
>
> * If BATCH is the default, perhaps it shouldn't be.

Can't be, or everyone would be getting an invalid rowcount, and you
would have heard about it long before now.

--
Guy Rouillier

[Clinton Begin]

Good point. ;-).

--
Sent from my mobile device

[Andrés Salcedo]

Just to add info about the default. It is SIMPLE. Taken from here.

public Executor newExecutor(Transaction transaction, ExecutorType executorType) {

    executorType = executorType == null ? defaultExecutorType : executorType;

  executorType = executorType == null ? ExecutorType.SIMPLE : executorType;

    Executor executor;

    if (ExecutorType.BATCH == executorType) {

      executor = new BatchExecutor(this,transaction);

    } else if (ExecutorType.REUSE == executorType) {

      executor = new ReuseExecutor(this,transaction);

    } else {

      executor = new SimpleExecutor(this,transaction);

    }

On Friday, February 1, 2013 12:35:22 PM UTC-5, Andrés Salcedo wrote:

The default value to be returned with BATCH is the number reported, as seen here.

Also, the default mode is not BATCH afaik.

The real failure is in docs. They should state it.

[Eduardo Macarron]

There is a reason for that extrange number.

Whe you use the batch executor all updates are cached and they are not sent to the DB untill the session ends, a flushStatements() is called, a select is executed or a commit / rollback is called.

The update method does not know how many records have been changed so it returns a negative number in a non-valid range. Exactly this:

public static final int BATCH_UPDATE_RETURN_VALUE = Integer.MIN_VALUE + 1002;

So, the rule is: any insert/update/delete over a batch session will return -2147482646 instead of the affected rows. It is not a bug or something that has to be fixed but a consecuence of using a batch session.

2013/4/12 Andrii Rubtsov <andrii....@gmail.com>

Hi, I just wanted to confirm that the odd problem with magical number -2147482646 was observed and fixed by switching from BATCH to SIMPLE on the following env:

java version "1.7.0_11"

Java(TM) SE Runtime Environment (build 1.7.0_11-b21)

Java HotSpot(TM) 64-Bit Server VM (build 23.6-b04, mixed mode)

Personal Oracle Database 11g Release 11.2.0.1.0 - 64bit Production

JDBC: ojdbc6 11.2.0.1.0

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mybatis-user...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Michael Plavnik]

Eduardo,

One can regard having return value on the mapper method  as a clear indication for the flush, the same as query in the middle of updates.

Best regards,

Michael

[아미]

Eduardo,

The mybatis batch executor works like adding the query to the batch queue and doing the batch job after.

But i agree with Michael Plavnik, we can get how many row affected when the query was flushed or in some middle time.

And i think a lot of people using mybatis(Simple or Batch or with other Executors) want to know how many rows are inserted/updated to the table.

2014년 7월 16일 수요일 오전 2시 22분 13초 UTC+9, Michael Plavnik 님의 말:

[Iwao AVE!]

Hi,

SqlSession#flushStatement() returns a list of BatchResult.

And BatchResult#getUpdateCount() returns the result of Statement#executeBatch() which is...

> an array of update counts containing one element for each command in the batch.

https://docs.oracle.com/en/java/javase/11/docs/api/java.sql/java/sql/Statement.html#executeBatch()

Regards,

Iwao

To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/5fc5c825-aa0f-4a74-aae3-6181957983a8%40googlegroups.com.