Beefy Boxes and Bandwidth Generously Provided by pair Networks
Just another Perl shrine

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

by tomdbs98 (Beadle)
on May 04, 2011 at 18:15 UTC ( #902959=perlquestion: print w/replies, xml ) Need Help??

tomdbs98 has asked for the wisdom of the Perl Monks concerning the following question:

Greetings Monks,

This question may be very obvious to many of you, but I humbly ask for you to illuminate my tangled knowledge.

My task is to implement a database (made using mySQL) that can be accessed (and, eventually, modified) through a wiki like web portal (TWiki) that has been written in perl. I believe I need to create a perl module as a 'middle man' between the wiki user and the database.

From what I understand, I need to use perl CGI to send/recieve information from a browser (which uses HTTP), as well as to create the UI. I must then use perl DBI to send/recieve that information to/from the database using SQL.

Even if the above information is correct, the path through which this can be done is lost upon me and guidance would be highly appreciated.

Thank you for your time,


  • Comment on CGI, DBI, HTTP, SQL - How does it all fit together?

Replies are listed 'Best First'.
Re: CGI, DBI, HTTP, SQL - How does it all fit together?
by davido (Cardinal) on May 04, 2011 at 18:53 UTC

    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

    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.


      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

        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 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. ;)


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

Re: CGI, DBI, HTTP, SQL - How does it all fit together?
by CountZero (Bishop) on May 04, 2011 at 22:12 UTC
    Have a look at Wiki::Toolkit as well. It can use MySQL as a storage backend for the WiKi-pages (if that is what you were looking for).


    A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James

Re: CGI, DBI, HTTP, SQL - How does it all fit together?
by ww (Archbishop) on May 04, 2011 at 20:29 UTC

    TWiki <-> database: but note also (from

    TWiki has database features built into the engine. A TWiki Form7 is attached to a page as meta data. This represents a database record. A set of pages that share the same type of form build a database table. A formatted search19 with a SQL-like query20 can be embedded into a page to construct dynamic presentation of data from multiple pages. This allows for building wiki applications and constitutes the TWiki's notion of a structured wiki.
Re: CGI, DBI, HTTP, SQL - How does it all fit together?
by igelkott (Priest) on May 04, 2011 at 19:39 UTC

    TWiki seems to be a fairly "high-level" so there may be little need (or opportunity) to write the usual CGI scripts.

    There is an API for plugins but it's quite possible that all of the parts you need are already available (eg, DatabasePlugin).

Re: CGI, DBI, HTTP, SQL - How does it all fit together?
by sundialsvc4 (Abbot) on May 06, 2011 at 18:12 UTC

    It is useful to consider that PHP is specifically designed to be used “for web sites,” whereas Perl is a more general-purpose tool that relies upon packages such as CGI.   PHP takes somewhat of a “kitchen sink” approach ... a monolithic approach ... that is different (neither “better” nor “worse,” merely “different”) than the one taken by Perl.

    Nevertheless:   the business purpose that you are now setting out to accomplish is a very routine one, and a very good strategy would be to try to figure out how much code you won’t have to write at all.   Work within the TWiki framework and explore thoroughly what is possible with it through the assembly of of already-existing modules versus the creation of new ones.   A little bit of “glue coding” is to be expected, but if it starts to sound like a bigger undertaking, press the Pause button and reconsider.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://902959]
Approved by Corion
Front-paged by Corion
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others surveying the Monastery: (4)
As of 2021-05-13 09:15 GMT
Find Nodes?
    Voting Booth?
    Perl 7 will be out ...

    Results (134 votes). Check out past polls.