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

Two words: it's declarative.

Longer answer: Puppet lets you specify what configuration you want without worrying about how to get it. In that way, it is similar to SQL (where you specify what data to retrieve) or Nagios (where you specify what you want to monitor on the network). This is a higher level of abstraction than a procedural language like Chef -- kind of like saying, "Climb that mountain" without concerning yourself with the details of what approach to take, what gear to bring, etc. (Matter of fact, Chef might make a dandy implementation language for a Puppet clone.)

I've had a somewhat productive week releasing updates to some of the modules I maintain.

SOAP::Lite had 2 releases this week:

1.08 November 8, 2013
! #15703 add no_index for examples to prevent CPAN pollution [githumb.com/sergeyromanov]

1.07 November 7, 2013
! #89948 Remove warning when Content-Id is not present

WebService::MES 0.01 was released which is my first attempt at a simple interface into the Merchant e-Solutions API.

I was just granted COMAINT to Archive::Zip, and 1.32 is enroute to CPAN.

1.32 Fri 8 Nov 2013
- Unlink temp files generated by tempFile. [PHRED]

1.31_04 Fri 14 Oct 2011 - Alan Haggai Alavi
- Updated Perl dependency to 5.006 to reflect implicit dependencies
in the code exposed by Perl::MinimumVersion xt test (ADAMK)
- Fixes: #68446: Set compressed size and uncompressed size of an entry to 0
if either of them is 0 (HAGGAI)
- Added $VERSION to crc32 (ADAMK)

PDL become to be able to output png format in Windows Active Perl ppm.

ppm install http://www.sisyphusion.tk/ppm_alt/PDL-Graphics-PLplot_alt.ppd --force

Note that you must set PLPLOT_LIB and PLPLOT_DRV_DIR environment variable.

I enjoy to output png files.

The following links are Japanese site links, but you maybe read source codes and graphs.

Simple graph

Various graphes

Stripplot

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

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

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:

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.

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'";

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.

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

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;