And about the portability of Class::DBI, I can be wrong, but you are saying to me that DBI is portable over databases. Well, the SQL is not.

As somebody who fell into the "gotta write my own abstraction layer" trap, I have to disagree here.

Class::DBI exists to allow you to not write SQL; problems with database portability I've run into have been limitations in the DBD layer of DBI and not related to Class::DBI directly. A good example of this is trying to access MS SQL Server; DBD::Sybase doesn't support bind parameters using the FreeTDS library.

Code like this:

my $item = db::Inventory::Item->retrieve( 15 );
if( $item ) {
  $item->quantity( 15 );
}
[download]

works with every database I've tried so far where a Class::DBI driver exists.

If you're worried about database portability, you have to take into account variants of "sequence" methodologies across various databases. For example, both Postgres and Oracle have an object called a sequence, but they access them differently:

-- Oracle: 
INSERT INTO my_table 
VALUES( my_sequence.nextval, 'Some Value' );

-- Postgres:
INSERT INTO my_table
VALUES( nextval( 'my_sequence' ), 'Some Value' );
[download]

Heaven forbid you want to use a database where sequences don't exist but you want autoincrementing fields, say MySQL or SQL Server/Sybase:

--MySQL
INSERT INTO my_table( seq_field, val_field )
VALUES (NULL, 'Some Value');
SELECT LAST_INSERT_ID();

-- SQL Server
INSERT INTO my_table( val_field )
VALUES ( 'Some Value' );
SELECT SCOPE_IDENTITY();
[download]

Not to mention that MySQL will insert a requested value into the sequence field (if it doesn't violate a primary key constraint), where SQL Server will barf if you attempt to place any value into an autoincrementing field.

The code to do this with Class::DBI is exactly the same, no matter what database layer you deal with:

my $newRecord = db::Table->create( { val_field => 'Some Value' } );
[download]

And you don't have to deal with rewriting your SQL to handle different funkiness associated with different back ends. It Just Works.

Granted, there are things I don't like about Class::DBI:

• No really nice way to handle views
• Docs for dealing with different connection settings are, shall we say, sparse. I still can't figure out how to have one set of user/password fields for, say, a web application and another for the command-line processing without duplicating the whole hierarchy
• Relationships can be a bit funky to deal with.
• Transaction support is really funky for long-running processes like mod_perl where you might want some records acted upon immediately and others batched

What I tend to do is use Class::DBI for my user-type interactions where editing and small selection is involved (update my user preferences, edit an item, etc.) and use raw SQL for reporting in the web environment.

For back-end batch processing, I tend to do everything with raw DBI/Perl code unless I have to iterate over every single record and the queries driving my main batch loop aren't JOINed tables.

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.