I know there's already a heluvalot of DBIx modules, but I just couldn't help myself. I do not want an ORM, but rather a simple way to call my stored procedures, including the ones with output parameters.

I started with DBIx::LazyMethod and extended it to infer the arguments and support the output parameters for MSSQL stored procedures, but I was unable to contact the author for quite some time (and succeeded only after halfway in the rewrite) to have the changes included and decided to rewrite the stuff to make it quicker. Instead of going through the AUTOLOAD in ever call, I generate the code you'd otherwise write yourself, with the prepare(), bind_param(), bind_param_inout() and execute() and based on the way you want the results even the fetchrow_xxx() and eval"" it so that it gets called directly next time.

use DBIx::Declare
  MyDB => {
    data_source  => "dbi:ODBC:Driver=SQL Server;Server=Some;Database=M
+yDB",
    type => 'MSSQL',
    user => '...',
    pass => '....',
    attr => { 'RaiseError' => 0, 'AutoCommit' => 1, LongReadLen => 655
+36 },
    methods => {
      GetSiteName => {
        return => '$$', # assuming CREATE PROCEDURE GetSiteName(@Id in
+t, @Name varchar(100) OUTPUT) ...
      },
      FetchSiteDetails => {
        return => '_%',
      },
      FetchSiteList => '@%',
      InsertSite => '@%',
      FetchFreeSiteList => {
        sql => 'SELECT Id, Name FROM Sites WHERE Cost = 0 and Name lik
+e '%'+? ORDER BY Name',
        args => ['Name'],
        defaults => {Name => ''},
        return => '@%',
      }
    }
};

my $db = MyDB->new();

my $site_name = $db->GetSiteName( $id);
my $details = $db-> FetchSiteDetails( -SiteId => $id);
print "$details->{Name} costs $details->{Cost}\n";
my @free_sites = $db->FetchFreeSiteList();
my @free_sites_starting_a = $db->FetchFreeSiteList(-Name => 'a');

[download]

You may also use this as a way to keep all your queries on one place if you do not use stored procedures.

It's still beta, though there are test scripts for MSSQL, mysql and SQLite (stored procedures argument inference only works for MSSQL so far). If it looks like you might have a use for a beast like this please have a look at the docs and the module at my pages and let me know.

Update: I expect to add support for stored procedures in mysql as well, but would need help with their support in Oracle or PostgreSQL.

Update 2009-06-05: I just released version 0.01.006 with a lot of tests (and fixes) and the code caching implemented. You can find it on my pages.

Thanks, Jenda
Enoch was right!
Enjoy the last years of Rome.

In reply to RFC: DBIx::Declare - generate code to call stored procs/SQL statements by Jenda

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • 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! —
  • 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:
    & &amp;
    < &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.