The only thing that can change for your purposes is the SQL you send to the DBMS as a string, whose syntax may indeed vary with DBMS.

For further information take a look at DBI::FAQ

Also watch out for database functionality differences. For example, with a transactional database (mysql is not) you will either want to add $dbh->commit() statements, or turn on autocommit when you set up the database handle, like:

$dbh = DBI->connect($dsn, $user, $password,
          { RaiseError => 1, AutoCommit => 1 });
[download]

In general, if the goal is to do database manipulation without writing any actual SQL, probably the purest implementation I've seen is Class::DBI.

---

Xaositect - Whitepages.com

Well Class::DBI folows different logic and IMHO somehow it feels strange - as the trs80 said : Class::DBI Intro

Class::DBI can be a powerful way of interacting with your databases, but it really requires a large scale project to reap the benefits and be worth the performance hit you take vs. doing traditional SQL queries. When using Class::DBI you are adding dynamic accessors for much of the data and the overhead of the method calls for all your operations. The benefit is less code, referral integrity (cascading delete), rollbacks in non transaction databases, reduction in lines of code, and increased portability since Class::DBI handles the abstraction across multiple database sources. If you are working on a large scale database centric application or you want to learn more about OO Perl Class::DBI is a good place to start.

I intended this for simple projects ...

Mysql has had transactions for some time now (since version 3.23.17, according to the docs). See http://dev.mysql.com/doc/mysql/en/commit.html.

Yes that's what I was asking - will SQL syntax change. Well now I know that it will :) Thanks.

There are a significant number of database-specific ideosyncracies both in SQL syntax and in driver functionality. A basic select-one-row-by-primary-key should be pretty close to universal, but as your application gets more complicated, more and more of these cases crop up. (For example, limits, joins, unions, sub-queries, blob fields, transactions, schema detection, triggers, procedures, and a dozen other things.)

A number of CPAN modules already address this issue. You could start with the items marked "Y" for Portability in my brief feature matrix of DBI wrappers.

Given how open-ended a task this is, I would encourage you to pool your efforts with other developers rather than building another mousetrap from scratch.

For example, if you think your interface is particularly nice, consider setting it up as an adaptor that drives an underlying layer like DBIx::SQLEngine. Or if there's some feature you need that the other engines lack, think about adding it as a patch.

Update: If you do decide to continue building your own solution, do at least take advantage of DBIx::AnyDBD, which handles automatically loading the necessary subclasses based on which DBI driver you're using.

If you think you'd like to press ahead, perhaps you could clarify why you think your module's strength or focus is going to be... What might some example code look like for the types of queries you support?

As a point of comparison, here's the DBIx::SQLEngine interface for the query types you've described -- in what ways is your module different?

  my $sqldb = DBIx::SQLEngine->new( $dbi_handle_or_dsn );

  $sqldb->do_insert( 
    table => 'mytable', 
    values => { 'name'=>'Dave', 'color'=>'Blue' } 
  );

  $sqldb->do_update( 
    table => 'mytable', 
    values => { 'color'=>'Green' }, 
    where => { 'name'=>'Dave' } 
  );

  my $row = $sqldb->fetch_one_row( 
    table => 'mytable', 
    where => { 'name'=>'Dave' } 
  );
[download]