blogs.perl.org

Fetching Data

In part 4 we look at queries that do not return rows now we are going to have a good look at fetching data.

You might remember in Part 1 you saw the fetchrow() method used to get at our data -- or "record set", to use the proper RDBMS term -- that has been returned via the Driver.

This is of course familiar to all RDBMS programmers as it is the similar to a cursor, in that we iterate though the returned records sequentially, processing each as we move along until none are left.

fetchrow

The fetchrow() method works by getting the current row from the record set and binding the values from the record set into specific Perl variables. So given this code:

#!/usr/bin/perl
use DBI;
use strict;
use vars qw($dbh $sth $sql);

my $city = 'Seattle';
$dbh = DBI->connect('dbi:Oracle:', 'hr@localhost/XE', 'hr');
$sql = 'SELECT city, state_province, country_id FROM locations
WHERE city = :p_city';
$sth = $dbh->prepare($sql);
$sth->bind_param(':p_city', $city);
$sth->execute();

my ($r_city, $r_state) = $sth->fetchrow();

TL;DR: 1,600 ranting words including F-bombs abound. I am testing out a new theme for this site. Also I am trying to port some content to a new CMS and put up a public test site to get feedback.

Where to begin... how about the latest problem I had. I tried to post a comment saying I fixed a mistake in my last post and it tells me I am not signed in. After logging out and in then trying it a few times with no success I looked to the github issue list. I find the following issues that seem to match my problem #100, #196, and #222. Seeing that this is a known problem I decide to see if I can fix it. I decide to start by setting up a local copy of Movable Type (MT) to experiment with. So beings my tale of woe.

I'll be speaking at the 2014 German Perl Workshop, so I hope to see some of you there.

Ben Tilly posted the following on Facebook a few days ago (I've modified it every so slightly to make the possible answers clearer):

@ar1 = qw(foo bar);
@ar2 = qw(a b c d);
print scalar (@ar1, @ar2);

He argued that even experienced developers will often get this wrong. Looking at the above, we could possibly argue for any of the following to be printed:

• 1
• 2
• 3
• 4
• 6
• d
• foo

Try and guess the answer without consulting the docs or reading any of the responses! I'll give a rationale for each of those after the cut (and for the record, I got the answer wrong the first time), but I won't be giving the answer -- I assume that will show up pretty quickly in the comments. Note that, of course, the following rationales are wrong (badly so in some cases), but this gives you an idea of how difficult context can be in Perl.

Thanks to more new sponsors (think project! and Sophos, among others) we can make GPW2014 even better. Thanks for that!

We are also proud to have Curtis "Ovid" Poe as a guest speaker. Among other things, he is the author of the Beginning Perl and Perl Hacks books and helps expats work in Perl jobs with his company All Around the World.

One more reminder: The early workshop bird catches the Perl worm. So sign up now and save $$, and don't forget to submit your talks!

DE: http://www.perl-community.de/bat/poard/message/171908

Queries That Return No Rows

In part 3 we look as advanced place holders, now we are going to look at non select statements.

Many SQL statements do not return data, and DBI handles those as well. One could code such a statement like this:

$sth = $dbh->prepare("UPDATE locations SET city = 'Ottawa' WHERE location_id = 1800"); $sth->execute();

However, this is a waste of resources or perhaps some typing, since you are creating a statement handle for a query that returns no rows. So the database handle has a built-in shortcut for just such a case. This is the do() method. The code below shows how it is used.

$rows_affected = $dbh->do("UPDATE locations SET city = 'Ottawa'
WHERE location_id = 1800");
print "Rows Affected = $rows_affected\n";

As an added bonus many drivers will return the number of rows affected by the query or –1 if an error occurred.

This shortcut 'do' function is great for doing DLL tasks however it does not take params so its usefulness is somewhat limited.

Placeholders for non Select Queries

If you find yourself spinning your wheels while trying to implement something, it’s usually a red flag that you need to step back and take another look. It might mean you didn’t put enough thought into designing whatever you’re building, or it might mean that you’ve discovered and unforeseen hiccup that may require you to change direction.

Learn to recognize when you’ve lost focus, or are having a harder time than normal completing a task. Use this recognition to re-evaluate the situation. You’ll ultimately produce better code, and you’ll get back to being productive faster. 

[From my blog.]

This post is a brain drain of ideas that I find myself repeating to people when I get asked advice about technology and the surrounding ecosystem. I give no guarantee that any of this information is going to be helpful.

I had the opportunity to sit down with some of the folks at Huffington Post to talk a little bit about Perl and MongoDB and my work as an engineer slash evangelist for the MongoDB community. It was a lot of fun and I enjoyed the experience immensely. You can listen to the podcast below or check out prior episodes at the Floating Points page.

Complex Placeholders

In part 2 we has a look as the '?' or simple placeholder now we will have a look more complex placeholders.

There is nothing wrong with working with just the execute() statement and '?' placeholders, but sooner or later you will be forced to mix in-line SQL and parameter SQL, or have an SQL with so many parameters that the SQL statement will be unreadable and confusing.

bind_param Method

A statement like this comes to mind.

$sql = 'INSERT INTO table_1 values(field_1, field_2, field_3, field_4, month) select field_1, field_2, field_3, field_4, add_months(sysdate, ?) FROM locations WHERE (city = $city_1 or city = ?) AND country_id <> ? AND state_province <> ?';

$sth = $dbh->prepare($sql);
$sth->execute($var_1, $var_2, $var_3, $var_4);

Happily, the statement handle has a the bind_param() method so we can write code that strongly binds one parameter to a placeholder. So, given this code:

$sql = 'SELECT city FROM locations WHERE (city = ? or city = ?) AND country_id = ? AND state_province <> ?';

$sth = $dbh->prepare($sql);

We can use the bind_param() like this:

I discovered (by accident) that in Moose you can be your own exception class:

As part of our eager hunt for new developers I met up a few weeks ago with Ruben from Makers Academy, who told me all about their 12 week intensive web development course. They have a pretty impressive set up: they start courses every 6 weeks and each class is about 20 people, meaning they churn out about 160 new Ruby developers every year!

Given our successful internship program here at Nestoria (which I spoke about at YAPC::EU this year) I've got some experience taking people who know Python, Ruby or PHP and training them up in Perl very quickly. So, when Ruben asked if I would like to come and give a talk to some of their students I knew exactly what I wanted to talk about.

I am using PDL. I become to know PDL can write clean code than I had expected. PDL::Lite, NiceSlice and OO interface is good.

PDL have PDL::Lite module. This module don't import any functions to current name space. and piddle(PDL varialbe) can call many function as method call.

The following are example codes.

In  DBI Part 1 I introduced how to connect and how to DBI  can run hard-coded SQL statements. But to be truly useful it must be able to bind variables to placeholders.

With the following SQL,

SELECT city FROM locations WHERE location_id = 1800

we would like to replace the 1800 with a placeholder. We could just use Perl for this, which would look something like this:

my $loc_id = 1800;
$sql = "SELECT city FROM locations WHERE location_id = $loc_id";

This is called in-line binding. It can work quite well for simple SQL, but what if we get an SQL statement such as this?

SELECT city FROM locations WHERE location_id = 'IT';

Almost identical statements, but we have to add nasty single quotes to make the statement work. So our Perl will look like this:

my $country_id = 'IT';
$sql = "SELECT city FROM locations
WHERE location_id = '$country_id'";

Kavorka is a function signatures module, along the lines of Function::Parameters, Method::Signatures, and MooseX::Method::Signatures.

I really like the increased development pace for Perl5 starting with 5.10. It has led to many nice features in a relatively short time and I´m grateful for that. Occasionally however, the need for speed imho trumps sanity and reason...

Yesterday I tried to port a Moose App to Moo but got stuck when I found out it is using MooseX::Role::Parameterizable, a module not available in Moo. A quick visit in channel #moose on perl.irc.org later and I was told to give Package::Variant a try.

Few lines of conversation/debugging later and here is my parameterizable HTML::FormHandler role. Package::Variant is an extremly promising module to me that is really helpful when refactoring your code.

declare the HFH role

package MyApp::Role::Form::Step::Value;

use Package::Variant
    importing => ['HTML::FormHandler::Moose::Role'],
    subs      => [ qw/ has_field requires has around before after with / ];

sub make_variant {
    my ( $class, $target_package, %arguments ) = @_;

    my $name = exists $arguments{name} ? $arguments{name} : 'value';

    has_field $name => (
        type         => 'Text',
        required     => 0,
        noupdate     => 1,
        not_nullable => 1,
        element_attr => { class => 'detect_whitespace' },
    );

    around '_build_parameter_fields' => sub {
        my ( $orig, $self ) = @_;
        return [ @{ $self->$orig }, $name ];
    };
}

1;

And here is how you would use it.

package MyApp::Form::Step::CondSubTest;
use HTML::FormHandler::Moose;
use MyApp::Role::Form::Step::Value;
extends 'MyApp::Form::Step::Base';
with
  'MyApp::Role::Form::Step::SubTest',
  Value( name => 'value_a' ),
  Value( name => 'value_b' ),
  ;
1;

A little History

Since its inception by Tim Bunce in 1994, DBI (DataBase Interface) has been the de facto standard for interfacing databases with the Perl language. Thanks to the designers' foresight, DBI is database-independent, with methods, variables, and conventions that provide a consistent programming interface, no matter what database management system is being used.

Standard API?

So, in theory, programs written with DBI should work the same between DBs, Reality is a little different as it seems that each RDMS try to outdo each other in the number of non standard SQL function they implement as well there are a number of 'Drivers' that do not even access a RDMS or even a DB for that matter. Fortunately DBI make no attempt to force you to use a standard SQL or even SQL. Think of DBI as the glue that the Perl language uses to link to a specific 'Driver' to a data store giving us a common platform to work with.

Handles

I've been loving my current contract but, sadly, it's coming to an end soon. If you'd be interested in hiring me, drop me a line at info@allaroundtheworld.fr. Obviously I can probably crib together a few lines of Perl for you, but if you specifically need someone to come in and beat your test suite into submission (or create it from thin air), I'm your man.

You might find it ironic that my wife and I do international recruiting and I'm posting here, but we focus on international relocation and I'm not planning on moving. Hence, me being here :)

I've got a couple of leads already, but nothing set in stone.

Perl-Critic-1.121 marks the first production release from the new GitHub repository. We've also moved all the RT tickets to the GitHub issue queue.

Going forward, all development on Perl-Critic will happen on GitHub. For now, other modules like Test::Perl::Critic, Perl::Critic::More, and Perl::Critic::StricterSubs still live in the old Subversion repository but I'll be migrating those as time permits.

I've been wanting to make this move for a long time. The Subversion repository has served us well, but I think GitHub makes it much easier to attract and manage contributions from the community. Huge thanks to Tim Bunce, fREW Schmidt, Andreas Marienborg, Graham Knop, and Andy Lester for making this all happen.

Let the forking begin!

I've been using roles lately and was going to write about them on perltricks.com. However I've come into difficulty in explaining how roles are functionally different from inheritance.

• Avoid complex inheritance hierarchies
• Better encapsulation and code re-use by focusing on "does" over "is"
• Enforce polymorphism through "requires"

The difficulty I'm finding is that all of these features can be provided though inheritance already.

Avoid complex inheritance hierarchies. This is equivalent to only allowing one level of inheritance. Besides, roles can use other roles (at least they can in Roles::Tiny), so this problem is not avoided by using roles. This concept can also be confused with package hierarchies (e.g. File::Text) versus inheritance hierarchies -> clearly subclasses can inherit from modules outside of their package hierarchy, hence the risks of multiple inheritance.