SQL- for starters

[Anonymous Monk]

I'm not new to Perl or CGI but I haven't used a "real" database before. I'm accustomed to DB_File actually.

I had a client come up to me today and asked if I could rewrite a very small PHP site into CGI because their PHP programmer "vanished" and someone referred them to me for my work in CGI.

The site is really small, it's just a sign in form, sign up form, and a few mini things like this. The only thing I can't do right now is the SQL part.

How difficult would it be for me to find out how to store and retrieve specific fields from an SQL database? There are only a few things such as: username, password, name, etc. The database is already setup with their current user information, all I need to do is come up with a CGI version of everything.

Would it be difficult to just find out what I need to know in order to use the premade SQL database on this project? Do you think it would be similar to the code the PHP script is already using?

[davido]

The standard way of handling relational databases in Perl is through the DBI module. DBI acts as the portal into which you pass SQL queries, and out of which you pull the results. DBI then handles the details of interfacing with a database driver, and gives you a fairly simple interface to your database. I recommend reading the DBI documentation thoroughly, paying particular attention to the examples given. Keep an eye open for the portion of the POD that discusses placeholders; they're important. Also, when you think of error handling, think along the same lines as what you already practice with flat files. That is, always check return values to ensure that operations have succeeded.

And then there's SQL. There are as many different flavors of SQL as there are databases, but they all have a lot in common. You can begin to learn the common, basic portion of SQL by visiting this site: http://www.w3schools.com/sql/default.asp. It's a decent online tutorial geared at providing newcomers to SQL with enough of the basics that they can get some work done, while still leaving plenty of room for growth.

Read the DBI documentation, and the SQL tutorial two times, and the light bulb should flick on.

Minor nit to pick. You mentioned converting a PHP website to CGI. CGI isn't Perl. CGI is the "Common Gateway Interface". CGI server-side programs can be written in C, Perl, PHP, and a plethora of other languages. I assume what you mean is converting a PHP based site to a Perl based one.

[pg]

"CGI isn't Perl. CGI is the "Common Gateway Interface".

It is actually quite often to see people don't really understand (or understand, but too used to link CGI with Perl) the difference between CGI - the protocol, and the Perl scripts that use it ;-) However this does show how Perl is a major player in delivering dynamic web content, although it gets more and more challenges nowadays than before in this area.

[larryp]

You will probably be able to use the SQL statements directly out of the PHP code. This will save you the trouble of having to come up with the SQL on your own. However, if you're dead set on learning SQL, there are plenty of tutorial sites on the web. SQL in a Nutshell from O'Reilly is good, but it's more of a reference than a tutorial.

Another issue is the database you're using. If it's MySQL or PostgreSQL, there are direct DBI/DBD modules. However, if you're using a Microsoft Database such as SQL Server or Access, you'll have to setup and use ODBC. While that's not necessarily more difficult, you will need to set up an ODBC connection to the database on the hosting machine.

Using all the DBI/DBD modules is fairly strightforward and only requires that you download and install the appropriate drivers/interfaces. Check out

• http://www.perlmonks.org/?node=Tutorials#datafiles
• http://www.perlmonks.org/?node_id=1831

here in the monestary for some good information, tips, and tutorials.

HTH,

/Larry

[davidj]

First, regarding SQL, the internet is replete with introductory SQL tutorials. It should be very easy to find one that you can understand. Second, if you are going to database program in Perl, I would suggest you get "Programming the Perl DBI" by O'Reilly. It will prove to be very helpful. Third, if the site is currently working in PHP, then the code logic is probably sound. Converting it to Perl should not be that difficult if you understand PHP. Just follow the logic.

davidj

[johnnywang]

There are many solutions, most are pretty straight forward. I'd suggest using Class::DBI. To make it even easier, you can use Class::DBI::Loader, which figures out the columns from the database schema directly, assuming you're dealing with some of the standard databases like Mysql or postgres. Of course, if you don't want any machinary, you can always use DBI

[pg]

I am against Class::DBI for learning purpose. This has nothing to do with whether I like Class::DBI or not, but for a beginner to start with Class::DBI, too many things are hidden from his eyes. It is just not the way to learn, at least not my learning style. It is better to start from where you have to write your own SQL statements.

[tachyon]

Dominus wrote a A Short Guide to DBI It's a great reference for beginners. Short and sweet.

[jZed]

If your data is already in tables created with DB_File, you can use DBD::DBM to access and modify them with SQL, it provides a SQL/DBI frontend to DBM files including the BerkeleyDB files created by DB_File. It's in the latest DBI distribution. You could also use it in conjunction with another DBD driver to dump from your existing files into PostgreSQL, MySQL, SQLite, etc.