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]

  * 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]

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

Ok, I stand corrected - I have not used or tested DBD::SQLite :-( I shouldn't have advised against using it, since I don't actually have any experience with it, and especially since my assumption about *what* it does was wrong. I'm sorry if I confused anyone with my mis-information.

But I do stand behind my statements about using SQL in your Perl/DBI/DBD:: applications. I also agree with your view that the 5% can be a big problem. There is date/time and sequence handling, just to name a few in that 5%, and those differences are enough to give anyone headaches.

I also have never used HDB, so I will put my tail between my legs and stop commenting before I say something wrong about that. :-)

Graciliano,

I believe you are right about the SQL syntax problem. For example, in the CREATE statement, Oracle uses VARCHAR2 instead of VARCHAR. So, I'm already thinking that the CREATE statement would need to be a parameter that comes with the selected database.

So far, it appears that the same SELECT, INSERT and UPDATE statements can be used on at least the popular databases.

I see SQLite as a DBI driver. I see HDB as another abstraction layer. Am I wrong? I am not sure of this since I am not able to find any information about HDB. Could you provide a link?

Thanks for your help.

Richard

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

Wow, sorry - now, reading what your intent is I may stand corrected. Maybe SQLite does make sense.

That is quite a project you are undertaking! I'm sure you realize that there are enough differences between the databases to make your job a hard one. For example all(?) the databases handle these things differently:


  * Date/Time handling
  * Sequences (unique identifiers)
[download]

Those are the 2 glaring ones off the top off my head, and I'm sure there are more. I try to stick with standard SQL, and try to stay away from things that each database does which are specific only to that database. But some things(like Date/Time, Sequences, etc.) you just have to deal with in each database. I haven't worked with triggers and constraints(and other stuff), so if you use those you may encounter issues there. Just thinking about how you would handle the differences in date/time handling makes my head hurt ;-)

Good luck with your project!

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