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

I have been running a web page off of my home computer, and had several perl scripts up and running. I used the DBI module to connect to aa MSAccess2002 database to retrieve information and print it to the screen. I have now switched to a web host so that I don't have to change my IP address on my links every few weeks when my ISP updates their servers. I have a CGI bin and Perl capabilities, but does that mean that I automatically can use the DBI to connect to my DB if I put it in the CGI BIN? and, if so, how would I write my scripts now to do this? Here is a sample of my current code. I have an input page, and a page that processes the input.
#!/usr/bin/perl use CGI qw(:standard); print header; print start_html, start_form(-method=>'POST', -action=>'http://www.winterspd.com/cgi-bin +/access.pl'), print "<h1><P ALIGN=CENTER><font color=blue>Winters Police Department <br>Local Warrants Check</font></P></h1><p>", "<h2>To check for warrants on any person please enter a last name only + for a general check, or to narrow your search, enter a first name as well. Searches cannot b +e made by first name alone.<p>", "Enter Last Name \t", textfield(-name=>'Last_Name', -size=>16, -maxlen +gth=>24), "<br>Enter First Name \t", textfield(-name=>'First_Name', -size=>16, - +maxlength=>24); print "<p><p><p>", "If you find that you, or someone you know is wanted, please call +the City of Winters Police Department, or the City Municipal Judge, Gary Moore, and make arra +ngements to take care of your Warrants.<p>It should be understood that although you may find war +rant information on this page, it does not necessarily mean that the warrants are active, and tha +t warrant status may change between updates of the page.<p>Thank you for your interest."; print "<p>", submit(-name=>'Submit'), reset(-name=>'Reset Form'); print end_html; __________________________________________________ #!/usr/bin/perl use CGI qw(:standard); $query = new CGI; # Get CGI form data from accessStart.pl $Lastname = $query->param('Last_Name'); $Firstname = $query->param('First_Name'); #Windows-based Perl/DBI/MS Access example use DBI; #open connection to Access database my $dbh = DBI->connect('dbi:ODBC:LEDR'); #prepare and execute SQL statement if ($Firstname ne ""){ $sth=$dbh->prepare("SELECT [Master Name].[Last Name], [Master Nam +e].[First Name], [Master Name].[Middle Name], [Master Name].[Date of Birth], Locals.[Warrant (Cause) Number], Locals.[Offense Committed], Local +s.[Current Status], Locals.[Date issued] FROM Locals INNER JOIN [Master Name] ON Locals.[Person ID] = [Master Name].[Person ID] WHERE ((([Master Na +me].[Last Name])='$Lastname') AND (([Master Name].[First Name])='$Fir +stname'))");} if ($Firstname eq "") { $sth=$dbh->prepare("SELECT [Master Name].[Last Name], [Master Nam +e].[First Name], [Master Name].[Middle Name], [Master Name].[Date of Birth], Locals.[Warrant (Cause) Number], Locals.[Offense Committed], Local +s.[Current Status], Locals.[Date issued] FROM Locals INNER JOIN [Master Name] ON Locals.[Person ID] = [Master Name].[Person ID] WHERE ((([Master Na +me].[Last Name])='$Lastname'))");} print header(); print start_html; print ("<h1><P ALIGN = CENTER>Winters Police Department<br> Local +Warrant Check</h1></P><p>"); $sth->execute; while (@row=$sth->fetchrow_array()){ $firstname=$row[1]; $midname=$row[2]; $dob=$row[3]; $warrantnum=$row[4]; $offense=$row[5]; $status=$row[6]; $date=$row[7]; print "<h2>$firstname $midname $Lastname<br>DOB: $dob<br><font + color=red> Warrant number:</font> $warrantnum <font color=red><br>Status:</font> $status<br><font color +=red>Offense</font> $offense </h2> <font color=red><H3>Date Issued:</font> $date<br></H +3> __________________________________________________________ +___________________<p>";} if ($Firstname eq ""){print "<h2><font color=Blue>No other match f +ound for Name: $Lastname.</font></h2><br>";} if ($Firstname ne ""){print "<h2><font color=blue>No other match f +ound for Name: $Firstname $Lastname.</font></h2>";} print end_html;
Thanks for the help, Brian

Replies are listed 'Best First'.
Re: DBI on Web Host?
by dws (Chancellor) on Feb 15, 2003 at 06:05 UTC
    ... does that mean that I automatically can use the DBI to connect to my DB if I put it in the CGI BIN?

    If you're asking if you can access your home copy of Access from a CGI running on your (non-home) web host, the answer is a qualified No, with the proviso that my knowledge of Access stop with Access 2000.

    If Access 2002 is a true server (i.e., one you can connect to via socket), then you could arrange to have your home box periodically broadcast an IP address to your web host. Then use this IP address in your DBI connect string. Unless you could set up a selective firewall, this would mean exposing your (Microsoft) database product to the world at large. Recent history has shown this to be a Very Bad Idea.

    An alternative is to delegate work back to your home server at a higher level. This also requires that your home server periodically publish its IP address to your web host, and requires that you construct a server process that listens for requests, translates the requests into SQL, then bundles up the results to transmit back to the web host CGI, which then needs to unpack and display them.

    Another alternative is to see if your web host supports MySQL, then bite the bullet and upgrade your database.

Re: DBI on Web Host?
by steves (Curate) on Feb 15, 2003 at 09:27 UTC

    I listed some of the ways I know to connect to Access from another server in this node.

Re: DBI on Web Host?
by Desdinova (Friar) on Feb 15, 2003 at 10:56 UTC
    And for today's totally offtopic approach...

    You say -- "I have now switched to a web host so that I don't have to change my IP address on my links every few weeks when my ISP updates their servers."

    Another solution for this would be to tyr a service like DynDNS Which lets you give you home PC a set hostname and updates the DNS record everytime your IP changes...
Re: DBI on Web Host?
by Ryszard (Priest) on Feb 15, 2003 at 12:53 UTC
    A more complex solution, set up a daemon process on your local host that will communicate with your Access database and pump the results back over the wire to your cgi stuff.

    A little more complex, however you can do tricky and cool stuff like convert the output to XML.. You also have the added advantage of being able to modify the backend (perhaps using MySQL or Postgres) without having to modify your web code.

    Another advantage is you'll have a common API with which to talk to whatever back you wish! so, you'll be able to scale up your front end, or at least make it a little more extensible.

    So this is kinda OT and a little out of scope, but another viewpoint none the less...

Re: DBI on Web Host?
by Anonymous Monk on Feb 15, 2003 at 15:25 UTC
    Reply to everyone with input: Thanks for all the great suggestions! I will give them a try, see what happens, and may be back again, and again, and again. I'm definitely a perl rookie. Brian
Re: DBI on Web Host?
by Wysardry (Pilgrim) on Feb 15, 2003 at 22:18 UTC

    If you need to find out whether your host has the required DBI/DBD modules installed, the simplest way is to use something like PerlDiver to find out.

    I'm not very familiar with Access, but doesn't it have an option to export databases in other formats?

    If you're going to be uploading the database to the Web server anyway, converting it to another format wouldn't be that much extra effort and may make life much easier if your host doesn't use Windoze and/or doesn't have an Access DBD module installed.

    __________
    "Every program has at least one bug and can be shortened by at least one instruction -- from which, by induction, one can deduce that every program can be reduced to one instruction which doesn't work." -- (Author Unknown)