Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP

Loading…

affected rows for insert/update batch functions #126

Closed
bitbucket-import opened this Issue · 4 comments

4 participants

bitbucket-import Juan José González Andrey Andreev sergey-dev
bitbucket-import

As this post states: http://codeigniter.com/forums/viewthread/186177/
the affected_rows() function cannot be used when doing batch inserts or updates. It would be great if we could get the batch affected rows somehow. Here are two proposals:

  1. Use an internal batch_affected_rows counter which we could retrieve using affected_rows_batch() function. This counter should be handled by the CI_DB_active_record as it needs to be reseted and then use the affected_rows() for every executed batch to increase the counter.

  2. As <> says, the batch functions could return the number of affected rows instead of only TRUE. The problem: If everything worked but no records were affected the function would return 0 which could be confused with the FALSE return when there's an error in the passed parameters.

There could be another solutions which I'm not thinking of right now. But let the people behind CI to decide which one is best.

Juan José González

Ok, I have checked this and I'm getting the behaviour you want. is this a old post? should I close it?

This code prints 2 and 2, as described above.

$this->load->database();
$data = array(
array(
'name' => 'My Name' ,
),
array(
'name' => 'Another Name' ,
)
);

    $this->db->insert_batch('table', $data);
    echo $this->db->affected_rows();

    $data = array(
            array(
                    'id' => 1,
                    'name' => 'My Name 2' ,
            ),
            array(
                    'id' => 2,
                    'name' => 'Another Name 2' ,
            )
    );

    $this->db->update_batch('table', $data, 'id');
    echo $this->db->affected_rows();
Andrey Andreev
Owner

Does it return 2 records for update_batch() when only one of them is actually changed?
What does it return for insert_batch() when more than 100 rows are inserted?

Is it correct for all database platforms? Some databases don't support batch inserts natively and the solutions implemented for them are work-arounds that simply do many inserts. MSSQL/SQLSRV don't support insert_batch() at all for this reason.

sergey-dev

I decided to fix that myself, patched the native CI’s Active Record Class.
From now the batch functions could return the number of affected rows instead of only TRUE. If everything worked but no records were affected the function would return 0. When there's an error in the parameters passed it will return FALSE, so no confusion here.

Patched functions posted here http://webaurum.blogspot.com/2012/10/codeigniter-affected-rows-fix-on-db.html

Andrey Andreev
Owner

@sergey-dev

Thank you for sharing that (and see the referenced commit), this is indeed the only sane way to implement such a functionality (even though it shouldn't be needed for insert_batch(), really). You should submit a pull request next time, so that your improvements can go directly into CI.

@juanjosegzl Just to clarify, the described behavior can only be experienced with batches larger than 100 rows.

Andrey Andreev narfbg closed this
Kyra Zimmer nonchip referenced this issue from a commit in nonchip/CodeIgniter
Andrey Andreev narfbg An alternative to affected_rows() for insert_batch() and update_batch…
…() (ref #126)
16b0f75
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.