Thread

I have a question:
I activated Auto_Transactions in Datamapper.

Now I want to control the transaction manually in one situation. Is that possible? Because currently I am using trans_commit() and trans_rollback() but even there is an error before it will still write to the database and not roll back.

Is there a way in case to make it possible to control the transaction manually without deactivating transactions globally?

@Benedikt

I don’t know your database, but databases that fully support transactions will work with nested transactions.  This means you can wrap everything inside one transaction, and then if you rollback the outer transaction, everything inside should rollback as well.

Personally, I don’t see the point in auto transactions.  (It’s a leftover piece from DataMapper.) The only time they do anything at all is when saving relationships to a group of objects.  Otherwise, they have no benefit.  If you need to save the content to more than one object, you still need to wrap everything in a transaction separately.

Make sure you are getting a benefit from using auto-transactions.

Disabling the transactions worked.

Hi, thanks before for this great library. But, i’m very newbie for this, can you explain how to make this query below on DMZ?

$this->db->select('*');
$this->db->from('item');
$this->db->join('category', 'category.id = item.id');
$query = $this->db->get();

foreach ($query->result() as $row) {
echo 'Item Title :'.$row->title.'
';
echo 'Item Description:'.$row->description.'
';
echo 'Category Title :'.$row->category.title.'
';
}

Thanks a lot.

EDIT: Better not to help.

Hi Benedikt, thanks for fast respon. But, it did’nt work…

Then you made a mistake. More details, more help.

@pembelajar

This is the correct way, Benedikt is incorrect:

$items = new Item();
$items->include_related('category', array('title'), TRUE, TRUE);
$items->get();

foreach ( $items as $item ) {
  echo 'Item Title :'.$item->title.'
';
  echo 'Item Description:'.$item->description.'
';
  echo 'Category Title :'.$item->category->title.'<br >';
}

If you think you are going to have a lot of items, you can avoid the instantiation of the Category object, like so:

$items = new Item();
$items->include_related('category', array('title')); // note the difference here
$items->get();

foreach ( $items as $item ) {
  echo 'Item Title :'.$item->title.'
';
  echo 'Item Description:'.$item->description.'
';
  echo 'Category Title :'.$item->category_title.'<br >'; // and note the difference here
}

See include_related in the manual for more information on how it works!

Good luck!  😊

@OverZealous: This is work! Your library is amazing. Many thanks….

I would like to know if this is the right way to use a transaction with 2 objects

$fil = new Film();
$fil->trans_begin();

foreach($this->input->post('titles') as $value){
    $title = new Title();
    $title->name = $value;
    $title->save();
}

$fil->filmName = $this->input->post('filmName');
$fil->save();

if($fil->trans_status() === FALSE){
    $fil->trans_rollback(); // HERE (1)
    $fil->error_message('transaction', 'There was an error');
}else{
    echo 'saved';
}

1) In case it rollback, it will remove the titles?

Thanks

@Alface

Yup, that’s perfectly correct.  Transactions are “global” on the current database connection, as long as you are using pconnect, or you have the configuration option db_params set to FALSE.

If you are not using pconnect, you probably will have a problem with each object getting a different handle to the database, and each handle would have its own transaction set.

I have a fix planned for the pconnect / multiple DB problem, but it won’t be released for a while, because it is a huge change in the way DMZ handles the database connection.

Hopefully that makes sense.

Quick Fix for Multiple Connections

Many of you are aware that using pconnect is, in general, a very bad idea.  This is because it is fundamentally broken and can hold on to connections forever, leading to errors on the server.

However, if you want to use subqueries, you need to have multiple connections to your database.

If you only connect to one database, there is a quick-fix solution/hack you can apply by editing the driver file for your database type.

BE AWARE: IF YOU CONNECT TO MORE THAN ONE DATABASE, THIS WILL NOT WORK!

Say, for example, your database is PostgreSQL.  You would need to edit the system/database/postgre/postgre_driver.php file, and replace the db_connect function with this code:

static $_pg_connection_id = NULL;
function db_connect()
{
    // HACK TO ONLY HAVE ONE CONNECTION PER REQUEST
    if(is_null(CI_DB_postgre_driver::$_pg_connection_id)) {
        CI_DB_postgre_driver::$_pg_connection_id = @pg_connect($this->_connect_string());
    }
    return CI_DB_postgre_driver::$_pg_connection_id;
}

This will create a single, shared connection for the entire request.  This can reduce overhead, prevent errors, and ensure that transactions are global while they are in use.

Here’s the replacement for MySQL:

static $_my_connection_id = NULL;
function db_connect()
{
    if(is_null(CI_DB_mysql_driver::$_my_connection_id))
    {
        if ($this->port != '')
        {
            $this->hostname .= ':'.$this->port;
        }
        CI_DB_mysql_driver::$_my_connection_id = @mysql_connect($this->hostname, $this->username, $this->password, TRUE);
    }
    return CI_DB_mysql_driver::$_my_connection_id;
}

You can probably get the idea for other DBs.  Down the road, I will fix this within DMZ itself.

———————-

Also, DMZ 1.7.0 is on hold temporarily.  I’ve got different task that needs some work, but I’ll be releasing it as soon as I finish this current project.  I currently don’t have any more changes planned, so if you want to use 1.7.0-RC2, the only differences should be the version number and some minor tweaks to the manual, so you won’t actually have to upgrade.

FYI: Significant bug in DMZ 1.6+

I’ve been doing some research on DB connections here.  I was shocked because I had installed PGPOOL-II to increase database connection speed, and I disabled pconnect.  But I kept having connection problems.  I figured it was something misconfigured and did some research.

Well, there’s a simple, but significant, flaw in the multiple DB connections that DMZ uses, combined with the way CodeIgniter handles Database objects: they never get closed!

What this means is that the connections just get used up until they time out.

In the short term, you can avoid this serious bug in one of two ways:

1) Make sure you use pconnect.  This may be as bad as the original problem, but it has worked so far.
2) Disable the new DB functionality by setting db_params to false in your config file.  This will break subqueries and include_related_count, however.

The patch I referred to above should fix it as well, since every single copy of the DB object will reference the same DB connection resource.  This means that when CodeIgniter closes the DB connection on the main connection ($CI->db), it will effectively close them all.

I have some ideas for safe bandaid-style fix that I might be able to roll into 1.7.0 this week.  However, I need to continue to do some testing.

If you have any input on this issue, it would be greatly appreciated.

My current idea to solve the above issue has two parts.

The first is to modify the default case of connecting to only one DB, the default one.  ($db_params = ‘’)  In this case, DMZ now clones $CI->db (instead of creating a new one), which should ensure that all DMZ db objects use the database connection.  This works well, and has already helped the situation.  (This might also have a tiny performance improvement, as cloning can be a little faster than instantiating.)

The second is to register each $db object that is created with a shutdown hook.  This not only fixes the current problem (where creating more than one DB doesn’t shut them down), it also fixes the case where you use redirect() or exit() to exit the application, by ensuring the DB is closed if the application exits.  (The DB won’t get closed more than once, because CI checks for that already.)

I’ve already submitted a bug report to CodeIgniter suggestion that they use register_shudown_function() for all database connections created.

Now, if you connect to multiple databases, the first fix won’t provide much performance benefit, but the second one should ensure that those connections are also closed cleanly.

This fix will be rolled into the 1.7.0 release, as long as I don’t find any other issues with it.

Well, there’s a simple, but significant, flaw in the multiple DB connections that DMZ uses, combined with the way CodeIgniter handles Database objects: they never get closed!

Uh… really? Shouldn’t the PHP Engine take care of closing the DB connections after the script finishes executing?

Just took a quick look at the php manual and it seems that all the {db}_close() methods have the following note attached:

Note: Using {db}_close() is not usually necessary, as non-persistent open connections are automatically closed at the end of the script.