How can I upgrade in Class :: DBI without first selecting an entry?

Question

To do an insert with Class :: DBI, you can simply do:

my $object = Object::DB->insert({ a => 1, b => 2, c => 3, ...}); 

But there is no such thing for an update. The best I could come up with is to select the record first and then update it:

 my $object = Object::DB->retrieve($id); my $object->set( a => 1, b => 2, c => 3, ...}; $object->update; 

This is not effective since I have to do SELECT first and then UPDATE instead of a single UPDATE.

Is there a better way to do this with Class :: DBI? I do not want to do 42 $ object-> a (1), $ object-> b (2), etc., $ Object-> update;

Answer 1

As far as I know, Class :: DBI has no good way to do this. As you already noted, its update() method is designed to call an object that was previously loaded from the database.

Perhaps you can convince Class :: DBI to do what you want with something like this:

 # Make new "empty" object my $o = My::CDBI::Object->new; # Set the primary key column and discard the change $o->set(your_pk_column => 123); $o->discard_changes; # Set your other columns $o->set(a => 'foo', b => 'bar'); # Do the update $o->update; 

If this feature is important to you and you are not too far from your project, you are definitely lucky with one of the new ORM Perl, such as Rose :: DB :: Object or DBIx :: Class . DBIx :: Class even includes the :: DBI class compatibility level .

Answer 2

One way I found for this is to override the default iterator class for your objects. This allows you to have a collection of individual objects with the update method in the collection. The :: DBI class provides a method for this:

 __PACKAGE__->iterator_class('MyClass::CDBI::Iterator'); 

This then allows you to make the update method in an iterator class that can save all objects in the collection. So your code might look something like this:

 my $collection = Object::DB->search_where({id => {'>=', 0}}); foreach my $obj ($collection->next()) { $obj->a('bob'); $obj->b('tom'); } $collection->update(); 

Which makes for pretty well self-documented code. If you take this route, I also suggest using the is_changed method as an update (). This will save you time by not updating immutable rows.