In part 7 we used fetchall_arrayref to get an entire resultset into an array ref. One nice thing that DBI adds with this method it the ability to get just the fields we want and not the entire row.
and from this were are required only want the first, third, second, and the fifth column, we can invoke fetchall_arrayref() to cut down on the amount of space our array ref may take up.
So what we want is simply invoke fetchall_arrayref() like this:
my $employees = $sth->fetchall_arrayref([0,2,1,5]);
Have you ever whipped up a quick shell script that later you wished was a real program? We did an experimental workshop at MadMongers on Tuesday where we, as a group, roughed out a Perl program from a Windows batch file. The whole process worked out rather well and we plan to try it again with another topic, perhaps something like web scraping or doing something with a web service. Here are the brief notes (also available in MadMongers files):
Premise We have a windows script that we want to convert to a real programming language for the following reasons:
* Cross platform availability * Code readability * Flexibility in parsing, templating, and storage of data * To learn a real programming language
This is going to be a short post. I'm the new maintainer of
Redis.pm, the most used Redis Perl client.
Pedro Melo was the previous maintainer, but due to Real Life, he is unable to
continue. I'd like to thank him for all his efforts so far in maintaining and
improving this module. I hope I'll be able to achieve the same level of
quality. Pedro will actually stay around for a while, watching over my
shoulder and giving his opinions about stuff, to allow for a smooth transition.
We've used this maintainership change to improve the tools we use around this
project. So we've moved the code to the github's
PerlRedis organization (notice the cool logo),
and I've performed quite a few code cleanups and housekeeping.
But the big thing is the creation of a mailing list, that aims at gathering
forces around Redis support in Perl. It's
located here, and
hosted by the good folks at ShadowCat Systems Limited
(thank you guys).
It is not limited to the Redis.pm module: any Perl related Redis topic is
welcome, including other Perl clients. So if you have any interest in Perl and
Redis, feel free to subscribe !
In part 5 and part 6 we looked at fetching a single record from queries into either a array, array ref or even a hash refs now we will look at fetching more than one record at a time.
selectall_arrayref
Many times when working with data, it is desirable to have all of the rows returned at once. The is especially so in the Perl world, which has so many nifty little tools to manipulate arrays in all sorts of different ways. DBI has a both a method on the statement handle and one on the database handle that can utilize batch fetching.
The database handle method, selectall_arrayref(), is most useful when you want to get the data from static SQL more that once in a program.
That's right, Padre, the Perl IDE has just hit the 1.0 milestone.
Sadly there's not a lot of fanfare over this release. For the most part the changes from 0.98 to 1.0 have been patches applied by Kevin Dawson (bowtie) and improvements to the Debugger - Kevin's project in Padre.
- Applied Patch in #1488 comment:7 itcharlie++ (BOWTIE)
- Apply patch for #1504 dod++ (BOWTIE)
- Apply patch2 from #1459 bojinlund++ (BOWTIE)
- Update Makefile.PL with new versions (BOWTIE)
- Add Patch for cut n paste adam++ #1312 (BOWTIE)
- Fix Debug ip hanging, use 127.0.0.1 instead of localhost (BOWTIE)
- fix some unwanted background noise from debug raw (BOWTIE)
- Apply patch from #1508 itcharlie++ (BOWTIE)
- Add refresh_breakpoint_panel to Wx::Main (BOWTIE)
- Add correct comment for PerlXS (BOWTIE)
As you can see Kevin is keeping the wheels turning and people are submitting patches.
While development isn't as active as it once was, Padre is still seeing improvements, but releases are a little further apart.
If you are using Padre and you want to see something fixed or added feel free to drop into #padre on irc.perl.org say hi, ask questions, change the world! :)
In part 5 we look at fetching records for queries into arrays and array refs now we will look at fetching into a hash ref.
fetchrow_hashref
Not a big step here from the ftechrow_arrayref one is simple fetching the name values pairs of the row into a hash ref. So our query could look like this
#!/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 $result = $sth->fetchrow_hashref();
print "My city is ". $result->{city}.". \n";
print "My state is ". $result->{state}.".\n";
$sth->finish();
So as one can see our selected row was converted into a simple hash ref, where the field name is the key and it value is the value returned from the DB.
In order to transpile JavaScript to (any language but first) perl(5), I wanted to have a generic methodology, independent of the target language. The proof of concept being the default transpiling of JavaScript to JavaScript.
Marpa::R2
, came again to the rescue: Marpa allows the programmer to have
introspection
on the grammar. This mean that one can:
use an AST that would contain the rule Ids which, together with the previously mentionned generated package, allows to transpile JavaScript to any target language.
Let's be concrete: If, in the Marpa grammar, you specify an action in the userspace like that:
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.)
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();
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)
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.
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.
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.
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.
