New note on page5notebook

[marcos rebelo]

Hi all

I did one more note at:

http://perl5notebook.oleber.com/objects/smart-selects-with-dynamic-response-to-undefiend-method-calls

One example of the use of AUTOLOAD, to do some SQL dinamically.

Comments are well come

Best Regards
Marcos Rebelo

--
Marcos Rebelo
http://oleber.freehostia.com
Milan Perl Mongers leader http://milan.pm.org
Webmaster of http://perl5notebook.oleber.com

[marcos rebelo]

the Idea is to do fast work, FAST. If I just need one exception, DBI
may do that to you.

I seen this pattern to often

eval {
my $dbh = DBI->connect($dsn, $user, $password) or die $DBI::errstr;
my $sth = $dbh->prepare("SELECT * FROM document WHERE id = ?") or
die $DBI::errstr;
$sth->execute($id) // die $DBI::errstr;
my $hash = $sth->fetchrow_hashref;
die $DBI::errstr if $DBI::errstr;
...
};
if ($@) {
...
}

I prefer something like

eval {
my $dbh = DBI->connect($dsn, $user, $password, {'RaiseError' => 1});
my $sth = $dbh->prepare("SELECT * FROM document WHERE id = ?");
my $rs = $sth->execute($id);
my $hash = $sth->fetchrow_hashref;
...
};
if ($@) {
...
}

but for such a simple query, I would prefer

eval {
my $ss = SmartSelect->new($dsn, $user, $password);
my $hash = $ss->select_document_by_id($id)->[0];
...
};
if ($@) {
...
}

For having this in a production environment, I would have to develop
SmartSelect a little. Caching, more expressive queries, ... I didn't
found this in CPAN, I didn't look for it so much, but I would use it.

Like Einstein told: “Any fool can make things bigger, more complex,
and more violent. It takes a touch of genius-and a lot of courage-to
move in the opposite direction.”

Best Regards
Marcos Rebelo

2010/10/7 Gianluca Casati <casati_...@yahoo.it>:
>
> I would use more $DBI::errstr after a prepare, an execute or a connect.
> Something like
>
>
> $dbh = DBI->connect( $NZ_SOURCE , $NZ_USER , $NZ_PASSWORD ) or die
> $log->abort(
> $connect_error_message . $DBI::errstr );
>
> other than that is a very comfortable approach, even if I prefere to keep
> queries in separate .sql files under a sql directory so other people that
> don't know Perl can edit them ( after that they accept the '?' special
> character :)
>
> Bye,
>
> see you the next meeting
>
> ________________________________
> Da: marcos rebelo <ole...@gmail.com>
> A: mila...@pm.org; nl...@amsterdam.pm.org; perl-r...@googlegroups.com;
> pe...@lisbon.pm.org; Perl Begginers <begi...@perl.org>
> Inviato: Mar 5 ottobre 2010, 14:11:01
> Oggetto: [Milan-pm] New note on page5notebook

> _______________________________________________
> Milan-pm mailing list
> Mila...@pm.org
> http://mail.pm.org/mailman/listinfo/milan-pm
>
>
> _______________________________________________
> Milan-pm mailing list
> Mila...@pm.org
> http://mail.pm.org/mailman/listinfo/milan-pm

[marcos rebelo]

My idea is to throw away the SQL almost completely to 90% of the
queries. When the queries are a little bit more complex, I'd prefer to
create a View and use a simple interface.

I never did a Project to CPAN, but If someone is interested in growing
up the code I may help as much as I can +- 5 hours/week.

Best Regards
Marcos Rebelo

On Thu, Oct 7, 2010 at 12:08 PM, Oha <o...@oha.it> wrote:

> On 10/07/2010 11:27 AM, marcos rebelo wrote:
>>
>> but for such a simple query, I would prefer
>>
>> eval {
>>    my $ss = SmartSelect->new($dsn, $user, $password);
>>    my $hash = $ss->select_document_by_id($id)->[0];
>>    ...
>> };
>> if ($@) {
>>    ...
>> }
>>
>

> it's about 6 months i'm working on something like this, here a quick
> example:
>
>        # obtain a single row from db
>        my %row = select_uniq 'select * from foo where id = ?', $id;
>
>        # when needed you may open a transaction
>        tx_new
>        {
>                # if not differently specified, the default conenction will
> be used
>                my %codes = select_map { $_{id} => $_{code} } 'SELECT * FROM
> codes';
>
>                if($code{0}) {
>                        tx_db 'master'; # going to use a specific connection
>
>                        my $new = select_uniq 'SELECT max(id)+1 FROM codes';
>                        tx_do 'UPDATE codes SET id = ? WHERE id = 0', $new;
>                }
>                # now the connection is again the default, cauz tx_db got out
> of scope
>
>                # transaction may be nested, connection will be kept
> separated
>                tx_new
>                {
>                        # if more then 1 rows is fetched, the following will
> confess
>                        my %foo = select_uniq 'SELECT * FROM codes WHERE id =
> ?', 'foo';
>                }
>        } # connection will be committed and released, or rollbacked if an
> error is passing thru
>
> It is somewhat stable and i will probably put it on CPAN, but if you are
> interested i can share the code
>
> I'll gladly ear your thoughts about it.
>
> Oha