As I am cozening up to Moose these days I wanted to play with Roles as one is suppose to be able to scurry around the Diamond problem with them, and having some background with Lisp and Smalltalk I knew of them.

Unfortunately like many many many other tutorial on OO we stayed firmly in the barnyard (well in moose's case a /users/byterock/2014/01/index.html

The DBI Issues List is not one that see allot of traffic but there are few of us out there that keep an eye on and a lurk around waiting to pounce on a good one.

Most are simple newbie errors or an obscure install problem but the odd one is a real bugger and they usually start or end with a line like this;

There usually follows the obligatory;

I was happily enjoying my usual playing about when I made a slight change to my nice new Moose class and suddenly my thingy no longer worked as it could no longer couple with my gonkulator to produce a splork.

Fortunately I am an old school perl programmer and spotted the problem right away. It seems Moose's 'extends' works the same way 'use base', or if you want to go real old school push(@Some::Class,"Some::Other::Class") , does. In other words, the order in which you get something out, in this case an inherited sub, depends on the order it goes in.

So I was wondering how…

in principio creavit

Life was easy on the web so many years ago, urls where simple. You only bothered had one.

www.mysillylife.com/main.cgi

You simply tacked on all sort of extra little bits on end to know, what to do, what to show, where and who are you, and such things as how long you have been here. Something akin to;

www.mysillylife.com/main.cgi?user=blogins&lastvisit=1928282&mode=update&sessionkey=24823498....

anyway you get the picture. We only had get and post a…

Some time ago I was on a team that was rewriting a very large legacy system. We wanted to use a more maintainable OO style (rather then just a huge global hash-ref ) but unfortunately the team was restricted by using only perl 5.6.0. By the time I joined the project work was well underway and I was still stuck doing accessors by hand. So I came up with this little beastly Orignal to solve that problem.

It fixed things up nicely as we could have nice 5.14 style accessors with some silly other little bits th…

Well looks like old fiend smartypants was up to her old tricks again.

You know the type, spends the day at work in some IRC, never forgets the minutia of page 67 of the help file of a sys-admin command, has memorized and and has a better version every regex ever written, knows she is always right and in the bosses eyes can never make a poor choice when designing code.

Anyway let's get on with the code example. Sometimes the ugly way to do
things is the best. In this case pro…

So I though it would be just a regular day until with me just doing a little coding, drinking my coffee* and generally enjoying life, But I was wrong.

A Little Vulnerable

So I have been babysitting and ever so slowly migrating a 15+ year old application over to a more manageable and of course we have to keep doing improvements to the code so on a very old part I found something like this. (SQL changed to protect the innocent)

my $usr_ids = join(",",@user_sel);

my $sth = $h->prepare("Select * from a_table where id in ($usr_ids)")…

Changed the handle passed to Callbacks from being an 'inner' handle to being an 'outer' handle.

I though ma…

In part 17 we had a look at DBD:Pg's ability to do Asynchronous Queries, now I think it is time I got a little back in line with the spec so we are going to look at DBI's ability to do callbacks.

How it happened that Mastro Cherry,

Callbacks are common attribute to all three of the the DBI handles and with them you can be come if you want the great and all powerful puppet master of DBI contorting everything from A to Z. Myself I have really only u…

I had to do a little playing around today as I have to validate if a number is an integer? Well there are as many ways to do this as there are perl programmers.

At first I though that is easy just look for the '.' in the string so I gave this a try

if (index($a,'.') == -1 )

but wait what if $a was not a valid number say '0.1.1.1'. All I am really checking for is the '.' so I would have to check if it is a number first. We do have a nifty little util called 'looks_like_number' so we could do this

use Scalar::Uti…

In part 16 we has a quick look at the NAME and other attributes that can help you create portable code. Well this time lets break with good portable code like I did in part 15 and bend the spec a bit. We will have a look at Asynchronous Queries

Wrapped Around Your Fingers
So we are not talking about the…

In part 15 we had a look at a look at some extra XML goodies that DBD::Oracle give you. Now we are going to see how the statement handle can save you arse.

LOGID, LogID, LOgid

Egad those really are three table names I once encountered in a Oracle DB and I am not even going to show you all the fields in some of these three that where upper, lower and even mixed case.

Well how can DBI statement handle help?? Well you can at least get everything out of a table in the same case. So lets take this SQL for example

Select "First_Name", "last…

In part 14 we had a look at a look at blob, lob and clobs and even memo data sometimes DBD developers add in a few goodies that are outside the spec despite the ever watchful eyes of the DBI gods. Here is one exmple.

XML, That will never catch on we have SML

On neat little trick, besides a bunch of others, that DBD::Oracle can do is suck up large XML documents into its native XMLType.

It is actually quite easy as all that is really necessary is to import the correct data type and then bind it to the appropriate column.

So to start

/users/byterock/2014/01/index.html

In part 13 we had a look at a look at bind_col now we will take a quick look at blob, lob and clobs and even memo data.

Some Good! Some Bad!

The one thing about memo, blob, lob, clobs and other large DB fields is that not all DBDs use em, or even care about them, but when you need them you have to use them.

So for fun we will just use an example from DBD::Oracle but in general most of the others that do (DBD::DB2, DBD::Sybase) follow the spec fairly closely,…

In part 12 we had a look at a look at the array_execute and now we are going to look at bind_param's Evil Twin bind_col.

Bind what?

DBI's master chef designed it from the outset for 'SPEED', to get quickly to the point any extra under the hood calls to DBI will slow things down, bind_col eliminates some of the under the hood call …

In part 11 we had a look at a look at getting values back from SPs (stored procedures) now we are going to look a another variation on binding the array_execute.

One of Those Days

You know what its like. You have just been handed a requirement to store 200 individual selections from a web page. Well as a diligent programer with you now epic DBI skills you come up with this

my $sth = $dbh->prepare("INSERT INTO picks (user_id, pick_id) VALUES(…

In part 10 we had a look at a look at the $sth 'bind_param_inout' method to get something extra from an DBI query. This time we will stick with this but look at another common use at was hinted at in the last post.

Store This!!

Unlike many perl programmers I think SPs are a great. The encapsulate biz-rules really well, in the old days they ran faster, are easier to maintain and they can protect you when the Boss tells you we want to move over to ="http://elixir-lang.org/?ut…

Look Ma no Hands

There are many times when playing with RDBS that we would like to get just a little more back form the DB when we do something. What comes to mind first of is those nasty primary_key values after we do an insert. Most if no…

Ouch I didn't Mean to do That

In part 8 we used fetchall_arrayref to get just the fields we want and not the entire row now we will looks at when we make mistakes.

DBI Transactions

In a perfect world, we would never need our old friends commit and rollback. But hard disks die, the power goes out, and fingers click the wrong buttons; so DBI supports the age-old ACID Module for transactions. As lon…

Your all excited you spent 3 months of your own special free time working on your first CPAN package.

You spent so many hours on an IRC discussing how it should work your spouse suspects your getting a bit on the side.

You included a test suite of over 500 tests,

You put blood, sweat and tears into the Makefile.PL so it will load on any perl on any platform.

If you see one more comment on prepan from that son of a jerk off byterock you're going to punch in in the throat next time y…

Get Just What You Want

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.

So if where given this this SQL

SELECT Employee_Id, First_Name, Last_Name, Email, Phone_Number, Hire_Date, Job_Id, Salary, Commission_Pct, Manager_Id, Department_Id FROM employees

and …

More Data Fetching

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 especia…

More Data Fetching

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

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 i…

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 pe…

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 stat…

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

This leaves us vulnerable to potential bugs. For example, if country_id happened to contain a single quote, you would have to write a whole procedure in Perl to account for it. Of course you could write all of your SQL in-line as long as you are careful with ' and other characters that SQL does not like. DBI even has a method on the database handle called quote(), to fix most of these problems for you. So you may never encounter a problem.

A couple caveats when using in-line binding.

The first pertains to how many RDBMS optimizes an SQL statement, most analyze and then save an execution plan for the query and then reuse the plan when the same query is run again. With in-line coded SQL most optimizers will not reuse execution plans, so in the long run your DB will work faster and more efficient when you use placeholders.

The second warning, when you use in-line binding, you will opening your system to SQL injection attacks.

You really do not want some nasty out there to run this on your DB

SELECT * FROM users WHERE name = 'a';DROP TABLE users; SELECT * FROM userinfo WHERE 't' = 't';

or worse still have a situation like this

Thought there is little change to find such a malevolent mother out there (unless your name is Howard Wolowitz) but it does leads one to the question of data sanitation.  DBI has a number of  functions that can clean data up for you, such as 'neat', 'looks_like_number' and 'sql_type_cast' fortunately most RDBMS today handle this for you, though the sql_type_cast is one that is helpful to any scientision  out there dealing with very small or large number. 

There is always some poor sap out there that has to do allot of UTF8 localization work and  this is when some of the built in functions such as  'data_diff','data_string_diff' and 'data_string_desc' come in handy, but that is for another post.

Using Simple Placeholders

In DBI, the most simple placeholder is the '?' character. So, if we make the following change to our first SQL like this:

$sql = 'SELECT city FROM locations WHERE location_id = ?';

the '?' will act as our placeholder, and all you have to do to make it work is use the database handle’s prepare() method to get a statement handle.

$sth = $dbh->prepare('SELECT city FROM locations
WHERE location_id = ?');

Alternatively:

$sql = 'SELECT city FROM locations WHERE location_id = ?';
$sth = $dbh->prepare($sql);

You can then can pass the parameter to the statement handle with its execute() method.

$sth->execute(1800);

So our code would look like this:

#!/usr/bin/perl

use DBI;
use strict;
use vars qw($dbh $sth $sql);

my $loc_id = 'IT';
$dbh = DBI->connect('dbi:Oracle:', 'hr@localhost/XE', 'hr');
$sql = 'SELECT city FROM locations WHERE location_id = ?';
$sth = $dbh->prepare($sql);
$sth->execute($loc_id);

while (my ($r_city) = $sth->fetchrow()){

print " $r_city \n";

}

$sth->finish();
$dbh->disconnect();

If the $loc_id variable contained a ' character, as in IN'T, DBI would automatically fix this for us when you execute the SQL. So no worries about single quotes - DBI takes care of them, and you know the same execution plan will be reused no matter what value you enter for country_id.

Complex SQLs

What about the case where you have SQL like this?

SELECT city FROM locations
WHERE
(city = 'Rome' or city = 'New York')
AND country_id <> 'IT'
AND state_province <> 'New York'

This could have the parameters like the following.

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

With a usage like this, DBI allows you to use the execute() statement as:

$sth->execute('Rome', 'New York', 'IT', 'New York');

DBI will substitute the parameters one after another in the same order as it finds '?' characters. So the execute() above would return a set of records.

Given the same SQL as above, and this execute statement:

$sth->execute('Rome', 'New York', 'New York', 'IT');

a different set of records would be returned, as this code corresponds to this SQL:

SELECT city FROM locations
WHERE
(city = 'Rome' or city = 'New York')
AND country_id <> 'New York'
AND state_province <> 'IT'

Next we will deeper into parametrized queries.

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…