Beefy Boxes and Bandwidth Generously Provided by pair Networks
We don't bite newbies here... much

Differences in SQL syntax when using DBI (are there any)? And help on publishing module on CPAN.

by techcode (Hermit)
on Jul 15, 2005 at 09:57 UTC ( #475159=perlquestion: print w/replies, xml ) Need Help??
techcode has asked for the wisdom of the Perl Monks concerning the following question:

Hello fellow monks. I have so many questions at this point and since they are related - I posted them all. Of course if you can help on just some of them - great!

For my own needs I wrote a modules that is used as some sort of Database abstraction. Sort of, as it doesn't do all the bells and whistles that SQL::Abstract and DBIx::Abstract do. But on the other hand, it's much simpler and it does a little bit more in some areas.

It can generate following SQL statements:

  • insert - you send it hash/hashref that you get from say or similar, and tell it in which table to insert it. It's "smart" enough to generate SQL that contains only fields that are in both places (in data you sent it, and table).

  • update - same as previous. Only thing that you send it too, is the primary key filed name. But I believe I can change that so that modules itself determines the primary key field.

  • select - but only a simple one, "SELECT _what_ FROM _table_ WHERE _key_field_ = ?". I seem to either need that simple queries, or much more complicated (several tables) that I dont know how to generate with *::Abstract modules - but I may add a little bit more functionality (to be able to specify more after WHERE)

It also has stuff like prepare and execute, connect, disconnect ...etc.

It was all written with MySQL in mind. But I'm now thinking to get it "in shape" and publish it on CPAN - so naturally I want it done that way so that it can be use with other RDBMS's and not just MySQL ...

So finally we got to the question : When you use DBI - are queries like insert/update/select always the same, no matter what DB you are using? And what about things like describe query which I plan to use to get primary key field name ...

Because if it's not - I have a little extra work. My plan is to make a "sub-module", say Module::MyModule is base one, and then Module::MyModule::mysql would be for mysql specific.

A suggestion of name for the module is also welcome. I was thinking of DBIx::Handy?

O and any suggestion on how to ensure that you can easily make an extension in the future. First thing that comes to my mind is that it can return results of SQL query in a form that is suited for HTML::Template LOOP as I need that often. I know that I can write something like DBIx::Handy::HtmlTemplate that would 'use base qw(DBIx::Handy);' and just add such a method - but I don't know how can I ensure that it would be possible to have several of those "plug-ins" at the same time ...

THANK YOU! PS. I already have the book : "Sam Tregar - Writing Perl Modules for CPAN"
  • Comment on Differences in SQL syntax when using DBI (are there any)? And help on publishing module on CPAN.

Replies are listed 'Best First'.
Re: Differences in SQL syntax when using DBI (are there any)? And help on publishing module on CPAN.
by SimonClinch (Deacon) on Jul 15, 2005 at 10:19 UTC
    DBI is indeed a database independent interface; and indeed none of the perl usage you cite will change for different DBMSs (although there may be special methods for different DBMS's for truly radical variations only).

    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

    One world, one people

      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 });

      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 -
        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 ...
      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' } );

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://475159]
Approved by gellyfish
[Lady_Aleena]: My 15th MonkDay was yesterday.
[Corion]: Congratulations Lady_Aleena ;)

How do I use this? | Other CB clients
Other Users?
Others musing on the Monastery: (1)
As of 2017-04-26 07:34 GMT
Find Nodes?
    Voting Booth?
    I'm a fool:

    Results (469 votes). Check out past polls.