Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

SQL etc

Posted Oct 19, 1998

Introduction to Databases for the Web: Pt. 3 - Page 4

By Selena Sol

Running CGI Applications on a Single Station Local Area Network

You still with me? Okay the next thing we need to do is try running some CGI applications with your new server. To do so, simply dump a simple CGI script in the cgi-bin directory under the sambar directory. Here is one I use:

Click here for code example 1.

Setting up a Sample Database

Next, you need to make sure that you have set up a sample database to test on. In the previous parts we have introduced MSAccess which is a good database to practice with since it is fairly lightweight and user-friendly. Of course, as we have said before, what database you use will not really matter. I am just using MSAccess to demonstrate.

Go ahead and setup a database with two tables: Employees and Customers with some sample data in each. In fact, here are the tables I am using for your reference:

[Access Tables]
(Click image for full size)

When you are done with data entry, you'll need to register your database with your operating system. On Windows, you simply need to use the 32 bit ODBC Control panel.

[ODBC Control Panel]

The "Add Wizard" will walk you right through assigning a system ODBC name to the Access database you setup. Notice that I made four of them: "Access", "db1", "Extropia", and "MyCompany".

[ODBC Setup]

Putting it all together with a DBI-AwareCGI Script

I have prepared a little DBI script for us to walk through. You can copy this over to your system, change the setup variables and it should run on your system as well. The code of this should all be straight forward and you should have all read the Perl tutorials at Web Ware, so I won't spend much time documenting the code.

     # First we identify the location of 
     # the Perl interpreter. You will need 
     # to change this line to reflect the
     # location of Perl on your system.

#!c:\Perl\Perl5.00402\bin\perl.exe

     # Next we will tell Perl that we are
     # going to USE the DBI and CGI 
     # modules.
     #
     # We will use the CGI module mainly
     # to handle incoming form data.
     #
     # The CGI::CARP module has a nice
     # feature called "fatalsToBrowser"
     # that sends error messages to the
     # web browser window so that the
     # user does not get a meaningless 
     # 500 Server Error message.
    
use DBI;
use CGI;
use CGI::Carp qw(fatalsToBrowser);

     # Setup some implementation 
     # specific variables. $dbname
     # is going to be the Data 
     # Source name that you 
     # assigned to your database in
     # the 32Bit ODBC Control 
     # Panel plus the DBI:ODBC
     # pre-tag.  I did not setup
     # any security on my database
     # since it is only for local
     # testing, so we can leave 
     # those values blank.

$dbName     = "DBI:ODBC:MyCompany";
$dbUserName = "";
$dbPassword = "";

     # We will create a new CGI 
     # object and use it to send
     # out the HTTP header and
     # to parse out the incoming 
     # form variables "requestType"
     # and "sql".  You will see 
     # where those form variables 
     # come from in just a bit.

$dataIn       = new CGI;
$dataIn->header();
$requestType  = $dataIn->param('requestType');
$sql  = $dataIn->param('sql');

     # Next we will check to see 
     # if there is a value coming
     # in from a form for the
     # variable "sql".  If there
     # is no value, then we know
     # that the user has not yet
     # seen the submission form.
     # In that case, we will send
     # them the HTML form so they
     # can actually submit a value
     # for "sql". The following 
     # screen shot shows you what
     # will be returned.

[SQL Entry Form]

if ($sql eq "")
    {
    print qq!
    <HTML>
    <HEAD>
    <TITLE>Enter 
                 SQL</TITLE>
    </HEAD>
    <BODY BGCOLOR = "#FFFFFF" 
             TEXT = "#000000">
    <FORM METHOD = "POST" 
             ACTION = "dbi_demo.cgi">
    <TABLE BORDER = "1">
    <TR>
    <TH>Enter SQL 
              Query</TH>
    <TD><INPUT TYPE = "TEXT" 
                        SIZE = "40" 
                        NAME = "sql">
    </TD>
    <TD><INPUT TYPE = "SUBMIT" 
                        NAME = "requestType" 
                        VALUE = "Submit SQL">
    </TD>
    </TR>  
    </TABLE>
    </FORM>
    </BGODY>
    </HTML>!;
    exit;
    }

     # If there was a value for $sql, we know
     # that the user has already seen the
     # HTML form and has submitted some
     # SQL for us to process.  In that case,
     # we will open a connection to the 
     # database, execute the SQL, gather
     # the result set and display it
     # to the user.  In the case of a
     # simple SELECT, we will display
     # the results in an HTML table.
     # If, on the other hand, the SQL
     # was a DELETE, MODIFY or INSERT,
     # we will let them know that the
     # operation was successful.
     #
     # Notice that you need to do a lot
     # of dereferencing with the returned
     # rows :)

[SQL Entry Form]

else
    {
    $dbh = DBI->connect($dbName, 
                        $dbUserName, 
                        $dbPassword);
    $dataObject = $dbh->prepare($sql);
    $dataObject->execute();
    @dbRows = $dataObject->
              fetchall_arrayref();
    if ($sql =~ /^SELECT/i)
        {
        print qq!
        <HTML>
        <HEAD>
        <TITLE>SQL Statement 
                     Results</TITLE>
        </HEAD>
        <BODY BGCOLOR = "#FFFFFF" 
                 TEXT = "#000000">
        <CENTER>
        <TABLE BORDER = "1">!;
        foreach $rowReference (@dbRows)
            {
            foreach $columnReference 
                    (@$rowReference)
                {
                print qq!<TR>!;
                foreach $column 
                        (@$columnReference)
                    {
                    print qq!<TD>
                             $column
                             </TD>\n!;
                    }
                print qq!</TR>!;
                }
            }
        print qq!
        </TABLE>	
        </CENTER>
        </BODY>
        </HTML>!;
        exit;
        }
    else
        {
        print qq~Your SQL Query has been 
               processed, please hit the 
               back button and submit a 
               SELECT to see the changes!~;
        }
    }

Well, however simple that application is, it should be enough to get you started. You will have to design your own application logic for a more useful database tool, but all the components for a full featured application are demonstrated here.

As it so happens, I wrote a more complex application that you can use if you'd like. This application handles inserts, selects, updates and deletes in a much more user-friendly way. If you want to see the code, click here. Otherwise check out the screen shot of the interface.

[Demo Application]



SQL etc Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
SQL etc Forum
Topic By Replies Updated
MySQL rollback UAL225 0 August 21st, 09:56 PM
Complex Search Query Galway 0 May 20th, 10:04 PM
change collation at once supercain 2 May 15th, 06:18 AM
SQL Features, tools and utilities question Neomite 1 April 10th, 09:13 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date