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

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 follow-up.

Fortunately (for now anyway), the scripts needs very little from a database, so I won't initially encounter the many incompatibilities that I'm sure exist. Hopefully, I won't design myself into a corner that will cause a lot of rewriting later!

Richard