Home: Perl Programming Help: Beginner:
My first Perl Database!


Aug 23, 2013, 12:22 PM

Views: 3396
My first Perl Database!

I’m working on a home project where I need to create a database on my PC which can be updated by and accessed by a Perl program. I’ve created MS-Access databases on my home system in the past, and I’ve worked with Oracle at my 9 to 5.

What is the best way forward for a home project? Is there an easy “free” download of Oracle single user, and if so what is the best way to access it using Perl, what packages do I need to load?

Same question for working with MS-Access? I have Access 2013 on my Window’s 7 PC now as part of Office 2013. If I use Access, is the best method through ODBC, or is there a better way?

What does everyone recommend? Links would be helpful also!

Veteran / Moderator

Aug 23, 2013, 1:45 PM

Views: 3393
Re: [PapaGeek] My first Perl Database!

As far as I know, there isn't any free versions of Oracle.

I haven't used Access for more than 10 years and didn't care much for it. If you want to use Access, you would use ODBC (specifically the DBD::ODBC module).

Most people, including myself, use Mysql which is free. Perl's driver module for it is DBD::mysql.

DBI - Database independent interface for Perl ~ http://search.cpan.org/~timb/DBI-1.628/DBI.pm

DBD::ODBC - ODBC Driver for DBI ~ http://search.cpan.org/~mjevans/DBD-ODBC-1.43/ODBC.pm

DBD::mysql - MySQL driver for the Perl5 Database Interface (DBI) ~ http://search.cpan.org/~capttofu/DBD-mysql-4.023/lib/DBD/mysql.pm

Mysql would be the best choice because it's platform independent and an overall better database than Access and has a much larger support base.

Mysql Download ~ http://dev.mysql.com/download

Keep in mind that most ISP's that provide database usage use mysql, not Access. So, if you're planning on developing a web site, you'll want to use mysql.

(This post was edited by FishMonger on Aug 23, 2013, 1:50 PM)

Veteran / Moderator

Aug 23, 2013, 1:53 PM

Views: 3390
Re: [PapaGeek] My first Perl Database!

What do you want to have in your database? One table, several, many? How many columns? How many records?

If it is very simple, I might go for one of the Perl DBM modules. Straight Perl hash syntax, nothing special to do.

A simple alternative with wider possibilities is SQL Lite.

If it gets more complicated, mySQL may be an option.

There are also some free NoSQL packages worth considering.

I would consider commercial software packages such as SQL Server, Access or Oracle only if the above alternatives have serious limitations for what you want to do.

But that is only a personal opinion (or personal prejudices).

(This post was edited by Laurent_R on Aug 23, 2013, 1:54 PM)


Aug 23, 2013, 2:22 PM

Views: 3385
Re: [Laurent_R] My first Perl Database!

The database and table relationships will be fairly complex. This is for an investment strategy process. I will be tracking a list of stock symbols. I will have tables that track the annual and monthly income statements for each company and determine if they are investment quality.

If they are I will track historical prices and the calculated indicators like moving average, MACD, Stochastic, etc. to determine when is a good time to invest. I will also have tables that will track the clse to the current market price of various call options to expand the investment into covered calls.

So, in short, the tables can become rather large with a lot of dependencies. I have used MS – access for this type of system in the past, but looking to convert the world from Visual Studio to Perl.

Veteran / Moderator

Aug 23, 2013, 3:04 PM

Views: 3384
Re: [PapaGeek] My first Perl Database!

I would definitely recommend using mysql over Access for your needs.

Make sure that you use InnoDB for the engine with creating the tables. It supports row/record locking when adding/updating records. The other engine type use table locks instead of record locks. I believe Access also uses full table locks instead of row/record locks.

More importantly, InnoDB is ACID compliant and hence fully transactional with ROLLBACK and COMMIT and support for Foreign Keys to aide in maintaining referential integrity.


Aug 23, 2013, 5:22 PM

Views: 3377
Re: [FishMonger] My first Perl Database!

Thanks fishmonger.

As a relative newbee to DB, is there a step by step guide anywhere on how to download, install, and set up mySQL the way you are suggesting?

Veteran / Moderator

Aug 23, 2013, 5:59 PM

Views: 3376
Re: [PapaGeek] My first Perl Database!

My first post has the mysql download link. It's a simple download and install process.

You could use the mysql cli for all of your database design and maintenance. Or, you could download/install Mysql Workbench; it's a free gui tool (similar to phpmyadmin, but more powerfull). I use Workbench for the initial design process and schema changes and the cli for most everything else.

Workbench download ~ http://dev.mysql.com/downloads/tools/workbench/

http://dev.mysql.com is a great resource and is where I go to get answers to most of my mysql questions.

Since you are new to DB, you'll want to do some research on "database design" and more specifically "Database normalization".


Aug 24, 2013, 11:00 PM

Views: 3351
Re: [PapaGeek] My first Perl Database!

Just to throw in another link ( to look into once you are familiar with DBI ):

DBIx::Class - Extensible and flexible object <-> relational mapper ~ http://search.cpan.org/~ribasushi/DBIx-Class-0.08250/lib/DBIx/Class.pm

Personally, I have created a wrapper around DBI, simplifying executions, i.e.

my $data = $databaseObj->read 
query => q#select ;columns from ;table where ;where#,
clause_placeholders => { columns => q#id, value#, table => q#test#, where => q#id = :1# },
value_placeholders => { 1 => q#2# },
fetch_handler => [ FETCH_HANDLERS->{rows_hash_of_list}, q#id# ],
error_handler => ERROR_HANDLERS->{fatal},
cache => 0

But thats another story!


(This post was edited by Zhris on Aug 24, 2013, 11:10 PM)