Re: CGI, DBI, HTTP, SQL - How does it all fit together?

[davido]

What is your background? Are you new to Perl, or just to CGI and Database interaction? Knowing that would help in tailoring a response to the appropriate level of detail.

A CGI program reads the GET or POST data passed to it by the webserver, which it received from the browsing client. The CGI program will then do whatever work it needs to do, and then output a HTTP header followed by the HTML needed to render the resulting web page. The CGI program is, essentially, creating HTML dynamically.

Pretend for a moment that your CGI program ONLY needs to output some predefined HTML. To do so, it just prints it, tags and all. Reading the incoming parameters (GET/POST) is made easier and safer through the use of CGI.pm.

A phrase you'll often hear is that CGI is stateless, which means you don't have a binding between the application and the client. Requests can come in from multiple clients in any order. That sounds complicated, but it's really not too bad. A common way to deal with it is to send a cookie to the client, and read that cookie when the client responds to your web pages. The cookie can be used to maintain a sort of state-ID for a given client. Some people pass a lot of session data to the client too, while others maintain session data server-side, and use the cookie to identify which session belongs to which client.

Your interactions with the database are generally handled through DBI or Class::DBI, though there are many other modules, and many of them are more specific to a web environment. Pretend for a moment that you're NOT creating a CGI script, but instead just using Perl to access a database through DBI. To do so, you open a DBI connection handle, prepare an SQL statement, execute the statement, and then fetch the results.

If you can compartmentalize your thought process it will really help. You take input through CGI's param() function. You send output via print (which should be marked up with HTML). You handle database transactions through DBI.

Later you'll probably want to look at a templating system (such as HTML::Template, a cookie manager such as CGI::Cookie, and session management such as CGI::Session.

I know it's really dated, but you could get yourself a copy of CGI Programming with Perl, 2nd edition (O'Reilly), and a copy of Programming the Perl DBI (O'Reilly). The former is a two to four day read, and the latter is probably two or three days. But having spent a week reading you will save yourself weeks of trial-and-error down the road.

People can feel free to follow-up to my post with newer, more relevant resources. Those are the ones I started with though.

As I browse CPAN I see there's already a blogging module there: App::Plog (Tag-lined "The one and a half minute blog.") That might be helpful to your cause.

[tomdbs98]

Excellent response, thank you.

To answer your question, I have used perl in the past to analyze large quantities of data from txt files. I have also done DB interaction in the past using postgreSQL & PHP, although the database system was already in place. I have never used perl CGI.

I will be picking up a copy of those books if I can find them at a local library.

So, from what I gather, a theoretical script would:

(a) Recieve a signal from a webserver using CGI param() and render the webpage using print() with HTML markup to create things like text fields for user data input.

(b) Use CGI to read in user input from those text fields (once again through param())

(c) Use DBI to generate an SQL query from the user input, connect to the database, send the query and fetch results

(d) Update webpage with CGI, display something like a table with the requested data retrieved using DBI.

Is the way in which the TWiki webpage communicates with my perl script dependent on the host (?terminology? -- referring to TWiki), or is there a resource that can help me understand that process as well?

Thanks once again,

Somewhat less confused.. but still confused

[davido]

Often there will be an index.html initially that presents a form (or a log-in page), which submits its data via POST or GET for CGI.pm to read through the param() method. But it doesn't have to be that way. The client could be led to invoke the CGI program right off the bat (without there being an index.html leading into it.) Your script should detect whether this is an initial call or a later stage in the session. If there's no form data than it can be assumed you've got an initial request, and output your front page. Also, if there is no cookie, you could assume the same. If there is a cookie, it should correspond with a session that you're keeping track of server-side. That session data should keep track of where you are in the site (and program)navigation. Some of this framework is handled by CGI::Session, and CGI::Cookie.

Another topic to consider is trying to separate your HTML from your code. This is where templating systems come in handy (and that's why I referred earlier to HTML::Template). You will also need to deal with access permission and authentication. CGI::Session helps with this. You can also read up on .htaccess.

CGI programming is a topic that can go as deep as you can dig. And that's without even getting into webserver handlers/API, mod_perl, etc. Fun (and potentially frustrating) stuff. ;)

[actualize]

If you want to make an entire application, you might want to look into a web framework. If you have a solid schema, and some time to kill, take a look at catalyst. I once created an inventory database with no front end and was able to build a Catalyst front end on top of it using the tutorial as a reference(Catalyst::Manual::Tutorial).