CGI/Perl Guide | Learning Center | Forums | Advertise | Login
Site Search: in

  Main Index MAIN
INDEX
Search Posts SEARCH
POSTS
Who's Online WHO'S
ONLINE
Log in LOG
IN

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

 



PapaGeek
User

Aug 23, 2013, 12:22 PM

Post #1 of 8 (1310 views)
My first Perl Database! Can't Post

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!


FishMonger
Veteran / Moderator

Aug 23, 2013, 1:45 PM

Post #2 of 8 (1307 views)
Re: [PapaGeek] My first Perl Database! [In reply to] Can't Post

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)


Laurent_R
Veteran / Moderator

Aug 23, 2013, 1:53 PM

Post #3 of 8 (1304 views)
Re: [PapaGeek] My first Perl Database! [In reply to] Can't Post

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)


PapaGeek
User

Aug 23, 2013, 2:22 PM

Post #4 of 8 (1299 views)
Re: [Laurent_R] My first Perl Database! [In reply to] Can't Post

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.


FishMonger
Veteran / Moderator

Aug 23, 2013, 3:04 PM

Post #5 of 8 (1298 views)
Re: [PapaGeek] My first Perl Database! [In reply to] Can't Post

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.


PapaGeek
User

Aug 23, 2013, 5:22 PM

Post #6 of 8 (1291 views)
Re: [FishMonger] My first Perl Database! [In reply to] Can't Post

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?


FishMonger
Veteran / Moderator

Aug 23, 2013, 5:59 PM

Post #7 of 8 (1290 views)
Re: [PapaGeek] My first Perl Database! [In reply to] Can't Post

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".


Zhris
Enthusiast

Aug 24, 2013, 11:00 PM

Post #8 of 8 (1265 views)
Re: [PapaGeek] My first Perl Database! [In reply to] Can't Post

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.


Code
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!

Chris


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

 
 


Search for (options) Powered by Gossamer Forum v.1.2.0

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives