A dbm is an on-disk hash. It is a great way to store data that fits nicely into a hash, but that's all it is. It does not have anything to do with SQL or relational databases.

Thank you for your help.

I was looking at the FAQ page at http://dbi.perl.org/doc/faq.html, which was last updated in 2000 and then noticed that for the list of DBI drivers, the FAQ page references

http://www.symbolstone.org/technology/perl/DBI

which in turn references http://dbi.perl.org. So, I guess I got the impression the Web site was getting stale.

Anyway, I'm reading the CPAN entry at

http://search.cpan.org/author/TIMB/DBI-1.32/DBI.pm

and getting the DBI book from the library.

Thanks again.

Richard

Its easy to install, and with it, your web apps become that much more extensible. For example you now have access to any legacy data that you may need to, you can store all kinds of statistics that are accessable from within and out side of perl.

Your sites become much more scalable (using a bunch of webservers behind a load balancer model), you now dont need to have storage on localhost.

All of a sudden your web based data can be incredibly safe, now being maintained by a myriad of standard database backup/restore proceedures...

There are prolly four main choices you'll entertain:

1. Oracle
2. Postgres
3. My SQL
4. SQL server

Oracle is expensive, but damn, its a great RDBMS. Postgres uses transactions and has support for subselects but isnt as popular as MySQL. MySQL seems to be the most popular among the web, but doesnt support subselects. Both Postgres and MySQL are free and both have DBI drivers (DBD). All are SQL92 compliant (an important factor in deciding which RDDMS you choose IMHO.

Once you've decided you want to use a RDBMS lay our your requirements for what you want it to do, i'm thinking fault tolerance, support, cost, active development, availability, any specific features you're after, future requirements etc etc.

There is also (shamless plug) an abstraction to the DBI available.

Update: This was my 300th post!

Like perrin said, DBI is definately the way to go. With DBI you will also be able to handle the situation where your client may not have a database on the system. The DBD::CSV driver for DBI implements a rudimentory SQL database using CSV (Comma Separated Values) files. This is by no means fast, but it works the same as any other database. You can create tables, and select data using regular SQL syntax. Any real database would be preferred, but you could always fall back to this if necessary.

also it might be worthwhile to look at DBD::SQLite

The official support channel for people like yourself is the mailing list "dbi-users", see http://lists.perl.org for the archives and to subscribe, in case you have a question. Do not send support questions directly to Tim Bunce. If he had to answer every question he gets, he'd do nothing else all day. He'll just forward it to the above mailing list, any way. :-)

For the plain perl installation, people need to install DBI as well as the desired DBD::* driver for the database — which driver depends on the type of database. Use DBD::ODBC for MS-Access on Windows.

As for PerlApp, it will include the modules and any required DLL's or equivalent, inside the executable. So for this type of distribution, you needn't worry (much).

About your problem for distribute your application, why not send it with the DBI & DBD modules in the binarys, since you are publishing binarys with PerlApp.

You can wait the module HDB to be published, that works with DBI. With HDB you don't need to use SQL, you just use the commands of the module and perl syntax (all the query syntax is based on Perl), and HDB convert to the SQL syntax for each database. In other words, all your commands in HDB will work with any database, and if you want to change the DB you don't need to change the commands of your script. For example, I develope my site with HDB, and I test it in my Win2k desktop, using HDB::SQLite, but when I send to the server it works with HDB::Mysql, the only thing that I change is in the HDB->new(type=>'sqlite'...) to HDB->new(type=>'mysql'...). HDB make all the convertions of types of columns, and resources that one DB have and others not. And the module is very easy to use, I have peoples using it a lot but doesn't know anything about SQL syntax!

DBD::SQLite
SQLite Web Site

Graciliano M. P.
"The creativity is the expression of the liberty".

I have to play the devil's advocate here :-)

If you're going to use DBI/DBD::??? to have perl interact with a database(whose "native" language is SQL), IMHO it's good(not bad) to actually know and use the SQL that your database uses. Again, IMHO, abstraction can be a good thing, but there is a limit - here's why I think using SQL in your code is good:

   1. SQL is *NOT* that tough - if I can learn it and use 
      it, then anyone can.  And while some SQL(joins and
      there are more advanced topics that involve a 
      learning curve) can be more challenging to learn for
      newbies, the basics (SELECT, INSERT, DELETE) are very
      straightforward.
   2. Using Perl/DBI/DBD::?? to interact with a database 
      sort of assumes that you already have a working
      database, which kind of assumes that you already
      know how to interact with the database on its
      own(without Perl/DBI/DBD::??).  To do that, you
      should already have some familiarity with SQL, or
      you should learn SQL, at least enought to interact
      with your database through its "built in" client 
      interface.
[download]

My my 1st database project, I didn't know SQL, nor did I know the MySQL database that I used for that project. But here's what I did:



  * downloaded and installed MySQL
  * bought a book about MySQL(by Paul Dubois - excellent
    book) and read the online MySQL documentation, which is
    very good - I think it's at www.mysql.com(it's been a
    while - not sure about the address of that site)
  * learned enough SQL to be able to interact with MySQL
    using MySQL's client command line interface
  * installed DBI and DBD::mysql
  * read the excellent DBI perldocs by doing

       perldoc DBI

    at a command prompt

  * read the excellent DBD::mysql perldocs by doing

       perldoc DBD::mysql

    at a command prompt

  * bought the book "Programming the Perl DBI" by Tim Bunce
    and read it

  * wrote my 1st basic Perl/DBI/DBD::mysql application
[download]

It's been quite a while since I've read the DBI book, but I believe I remember that all the examples used straight SQL - there were no abstraction layers used like DBD::SQLite. Using these simple methods, I learned SQL and created my Perl/DBI/DBD::mysql using SQL, and you can too - it's really pretty easy.

So, my advice(just my humble opinion) is to just use SQL in your DBI applications - don't use an SQL abstraction like DBD::SQLite. Learning SQL will enable you to use any SQL database, since the SQL syntax is 95% the same between different SQL databases. Using DBD::SQLite means that you'll have to learn the syntax of DBD::SQLite, and then you'll be dependent on using that module for any database app you write. HTH.

Did you tested DBD::SQLite? It's not a SQL abstraction! It's a database to be embeded inside applications, to have a RDBMS in your application without the need of 3rd part applications! It has a client like MySQL, binary database files, SQL syntax and is very fast. Is very different than DBD:CSV, much more powerful!

The syntax of SQLite is not different than MySQL or any other SQL DB.

Don't confuse HDB with SQLite. SQLite is only one of the DB types that HDB can use, like MySQL and Oracle.

About the "SQL syntax is 95% the same between different SQL databases" I disagree. This "5%" can be a big problem! For example, create a table is different in most DB. The type of columns differ a lot. The SQL syntax for select in some DB have REGEXP, in others not. The NULL value is another big problem, for some is null, for others is a valid value. The INTEGER is other thing that can differ a lot, for MySQL we can chose 4 types for integer values, for SQLite 1! And this is only the begining.

But I respect your opinion, know SQL is very important, specially if you need to make complex things. But HDB still accept SQL querys: $HDB->cmd("select * from foo"); And you still can use the dbh (From DBI) interface inside it: my $sth = $HDB->dbh->prepare(...) ;

Graciliano M. P.
"The creativity is the expression of the liberty".

Thank you for your advice. It's making me think hard about this.

As it turns out, I am actually quite familiar with SQL. However, my familiarity with SQL is only partly relevant.

What I'm implementing is a script that can be configured by people who use my software to connect to whatever database they have on their Web server. What kind of configuration options do they see in my software? I am pretty sure none of my users know SQL, so I will try to avoid having them type in any SQL statements. Instead, I will likely have them choose from a list of databases, and have them type in the name, and other distinguishing information for the database. If their database is not in the list, I plan to provide some way for them to create a custom configuration.

At the outset, I am planning to have SQL statements inside my script for creating tables, and retrieving, inserting and editing records - should be very simple stuff. If, for example, it turns out that there isn't one SQL statement that will create a table in whatever database the user selects, then I will need to make the table-creating SQL statement part of the parameters that come with the selected database. I'm already betting that I will need to do this. It may turn out that all SQL statements will be parameters that come with the selected database. This is a paranoid approach that says I don't know everything about all databases.

This means that if a user doesn't see her kind of database in the list, she will create a new set of parameters and type in the SQL statement for creating a table and any other SQL statement that I cannot make work for all databases. I can of course help her on the telephone. If having another abstraction layer avoids my users from having to type SQL statements for custom databases it is worth investigating.

Also, I'm considering SQLite only as an alternative like any of the other databases (Oracle, SQL Server, MS Access, mySQL, etc.), where a user can choose to use a SQLite database to store the information for the script. SQLite would be just another database as far as the script is concerned. CSV might also be that way, but since CSV is already supported by the script, I haven't decided if the CSV implementation will be replaced.

Thank you again,

Richard

Graciliano,

Thank you for your help.

For the PerlApp versions, I am going to try to build a Windows executable and a Linux executable that have the DBI binary and possibly some DBD binaries as well. Including DBD binaries in the executables would be a convenience for Windows and Linux webmasters.

However, for the generic Unix version of the script, I think I will need to distribute the DBI source or leave downloading and compiling to the webmaster.

HDB sounds interesting as well. It's been a while since I have worked with databases such as Oracle and SQL Server, etc. I vaguely remember that the steps to create a database table are different. That is, I may not be able to have a SQL statement create a database table in SQL Server and have that same SQL statement create a database table in an Oracle database. If the DBD modules don't handle this, HDB may be attractive.

I now see why SQLite may be interesting as well: a database server doesn't need to be around for it to work. I'll read more about SQLite. Maybe this is an option for webmasters who are already familiar with SQLite and have tools that can get at the tables inside the database.

Thanks again,

Richard

I will investigate the CSV option. Currently, the script reads and writes CSV files and does it pretty well. This has been an excellent option since (as it was pointed out) it requires nothing from the Web server. However, people now want to be able to use their databases.

Of course it would be cleaner to have one way of doing things rather than a CSV way and a DBI way. Unfortunately, performance will dictate whether or not I replace the current machinery for reading and writing CSV files.

Thank you also for recommending some databases. However, other than CSV, I am not planning to implement any particular database but instead a framework where people can configure my script to make use of whatever database they have installed on their Web server. I think DBI will be very good at this.

I'm proceeding with DBI and will sort out the installation issues for DBI and the various database drivers. I will be finding out if it's better to include lots of drivers with my program or have webmasters download and install drivers themselves, avoiding any possible licensing problems.

Thanks again,

Richard

There is DBIpp that is a Pure Perl DBI, which is almost as fast as the C/XS version of DBI. You can check the dbiusers archives for an actual quote of testing numbers. There is also a Pure Perl DBD::mysql driver. These Pure Perl versions would be easy to ship, 'C' compiling. Not that this would be used for your product, there is also the dbish which gives a Oracle sqlplus type interface to a db, it will work with any driver(DBD::?) installed. Check out dbish on search.cpan.org. There is one being developed outside of DBI, which will eventually be part of the DBI dist, that has more features. The DBIpp and DBD::SQLite could be shipped with the product, although I don't remember if DBD::SQLite is pure perl , you can also check out DBD::AnyData.

STH

Thanks for the heads up.

I've been reading the CPAN article about DBI and just now realize that DBI is not written completely in Perl. This may pose a problem for my generic Perl script for all Unix platforms. It appears that the list of supported drivers for the pure perl version is much smaller. I'll have to look into this more.

Thanks again,

Richard