Home Accessibility Courses Twitter The Mouth Facebook Resources Site Map About Us Contact
For 2023 (and 2024 ...) - we are now fully retired from IT training.
We have made many, many friends over 25 years of teaching about Python, Tcl, Perl, PHP, Lua, Java, C and C++ - and MySQL, Linux and Solaris/SunOS too. Our training notes are now very much out of date, but due to upward compatability most of our examples remain operational and even relevant ad you are welcome to make us if them "as seen" and at your own risk.

Lisa and I (Graham) now live in what was our training centre in Melksham - happy to meet with former delegates here - but do check ahead before coming round. We are far from inactive - rather, enjoying the times that we are retired but still healthy enough in mind and body to be active!

I am also active in many other area and still look after a lot of web sites - you can find an index ((here))
Perl - database access - DBD, DBI and DBIx modules

Perl's DBI module provides an excellent link from SQL databases to Perl, and the assorted DBD modules that fit underneath it allow it to operate well for a whole lot of different databases. But they are only excellent as far as they go - for the SQL statements have to be generated in your application code, rather than keeping the database instructions clear of your code. Enter the DBIx module, which provides an interface from Perl language variables to database tables. Using DBIx (which calls DBI then DBD internally), you can extract the SQL from the application and generate it, encapsulated, within the DBIx classes.

The diagram shows - in the top line - how you need to code both your application and your SQL to application translation if you do not use DBIx. In contrast, the bottom line shows how you only need to code your application if you use DBIx.

I've started with a "Control" example - showing direct access to an SQLite database through DBI and DBD - source code [here] (and there's an older version using MySQL [here]). You'll note if you look at this code that it's quite short, but contains all sorts of SQL statements.

My second example uses DBIx. Look at this:
  my $schema = D2::Main->connect('dbi:SQLite:d2_test.sqlite');
  my @teamadditions = ( ["Tom", 5], ["Jerry", 6]);
  $schema->populate('People', [ [qw/name pid/], @teamadditions ]);

Those three lines of application code ... which contain no SQL ... add two rows to an SQL table using DBIx. The source code (with two use statements to complete it!) may be found [here].

The "magic" of DBIx is in one of those uses - that pulls in separate datatype definitions:
  package D2::Main;
  use base qw/DBIx::Class::Schema/;

Which in turn pull in the table definition:
  package D2::Main::Result::People;
  use base qw/DBIx::Class::Core/;
  __PACKAGE__->add_columns(qw/ pid name /);

(You can download these two files [here] and [here])

The final file for this application - which sets up the database and some initial test data - is [here].

You'll find sample outputs from the programs, and details of how to run it, in the first file - and it's a straightforward and complete DBIx example; I'll go so far as to say it's the simplest I've seen around as the examples in the official documentation show off a lot more facilities - but set the hurdle far higher for you when you first want to try DBIx.

I have allowed myself to add further complexity (generating selects with joined tables, sorting my output, etc) in my third example. Once you've tried the example above, have a look at this set of files:

SQL to set up the tables - [here].
The main application, showing two examples of data extraction - [here]. INCLUDES SAMPLE RUN and DOCUMENTATION
The model loader - [here].
Defining a table of computers (and how it relates to a people table) - [here].
Defining a table of people (and how it relates to a computers table) - [here].

Once you get the hand of DBIx, it's a nice way to help keep the model layer in its own compartment, though you still need to know SQL subjects such as database normalization to come up with good designs. We're covering Perl to Database interfaces on this week's Perl for Larger Projects course, and we also cover it briefly on the more introductory Perl Programming Course. The DBD and DBI modules are usable by people who are quite new to Perl; some of the DBIx data structures, though, are rather ambitious for the first week - easy to use once you know them, but perhaps not too easy to learn.
(written 2010-12-22, updated 2010-12-24)

Associated topics are indexed as below, or enter http://melksh.am/nnnn for individual articles
S200 - MySQL - SQLite
  [2561] The future of MySQL - (2010-01-03)
  [2744] PyQt (Python and Qt) and wxPython - GUI comparison - (2010-04-28)
  [2745] Connecting Python to sqlite and MySQL databases - (2010-04-28)
  [2746] Model - View - Controller demo, Sqlite - Python 3 - Qt4 - (2010-04-29)
  [2786] Factory methods and SqLite in use in a Python teaching example - (2010-05-29)
  [3139] Steering our Python courses towards wxPython, SQLite and Django - (2011-01-19)
  [3269] Files or Databases? MySQL, SQLite, or Oracle? - (2011-04-28)
  [4006] Ruby / SQLite3 example program, showing JOIN v LEFT JOIN - (2013-02-16)
  [4007] Which database should I use? MySQL v SQLite - (2013-02-16)
  [4024] SQL databases from Python - an SQLite example - (2013-03-02)

S156 - Interfacing Applications to MySQL Databases
  [104] mysql_connect or mysql_pconnect in PHP? - (2004-10-30)
  [644] Using a MySQL database from Perl - (2006-03-13)
  [663] Python to MySQL - (2006-03-31)
  [723] Viewing images held in a MySQL database via PHP - (2006-05-17)
  [1381] Using a MySQL database to control mod_rewrite via PHP - (2007-10-06)
  [1450] Easy selection of multiple SQL conditions from PHP - (2007-11-30)
  [1518] Downloading data for use in Excel (from PHP / MySQL) - (2008-01-25)
  [1561] Uploading to a MySQL database through PHP - examples and common questions - (2008-03-02)
  [1885] Hiding a MySQL database behind a web page - (2008-11-15)
  [2263] Mysqldump fails as a cron job - a work around - (2009-06-30)
  [2381] Checking the database connection manually - (2009-08-28)
  [2790] Joining a MySQL table from within a Python program - (2010-06-02)
  [3035] How to display information from a database within a web page - (2010-11-07)
  [3447] Needle in a haystack - finding the web server overload - (2011-09-18)
  [3455] MySQL, MySQLi, PDO or something else - how best to talk to databases from PHP - (2011-09-24)
  [4436] Accessing a MySQL database from Python with mysql.connector - (2015-02-21)

P308 - Using SQL Databases from Perl
  [515] MySQL - an FAQ - (2005-12-03)
  [975] Answering ALL the delegate's Perl questions - (2006-12-09)
  [1224] Object Relation Mapping (ORM) - (2007-06-09)
  [1904] Ruby, Perl, Linux, MySQL - some training notes - (2008-11-23)

Back to
Learning Object Orientation in Perl through bananas and perhaps Moose
Previous and next
Horse's mouth home
Forward to
Looking ahead and behind in Regular Expressions - double matching
Some other Articles
Thank you - and Happy Christmas
AND and OR operators - what is the difference between logical and bitwise varieties?
The week before Christmas
Looking ahead and behind in Regular Expressions - double matching
Perl - database access - DBD, DBI and DBIx modules
Learning Object Orientation in Perl through bananas and perhaps Moose
Making Perl class definitions more conventional and shorter
Contrast in pictures
The Christmas Season has arrived
Setting your user_agent in PHP - telling back servers who you are
4759 posts, page by page
Link to page ... 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96 at 50 posts per page

This is a page archived from The Horse's Mouth at http://www.wellho.net/horse/ - the diary and writings of Graham Ellis. Every attempt was made to provide current information at the time the page was written, but things do move forward in our business - new software releases, price changes, new techniques. Please check back via our main site for current courses, prices, versions, etc - any mention of a price in "The Horse's Mouth" cannot be taken as an offer to supply at that price.

Link to Ezine home page (for reading).
Link to Blogging home page (to add comments).

You can Add a comment or ranking to this page

© WELL HOUSE CONSULTANTS LTD., 2024: 48 Spa Road • Melksham, Wiltshire • United Kingdom • SN12 7NY
PH: 01144 1225 708225 • EMAIL: info@wellho.net • WEB: http://www.wellho.net • SKYPE: wellho

PAGE: http://www.wellho.net/mouth/3099_Per ... dules.html • PAGE BUILT: Sun Oct 11 16:07:41 2020 • BUILD SYSTEM: JelliaJamb