Hello Everyone,
I am new to DBI programming and the list. I originally wrote a script that
uses DBI-ODBC on an NT server running Website to accees a Dbase database of
about 2000 records and it worked fine for over a year. Recently however, I
was forced to port it over to a Linux server running Apache. Now I don't
know a lot about Linux but after some researching found out that it is
difficult to get an ODBC driver/manager for Linux. I have therefore opted to
use the DBD:XBase driver.
My main problem, is that it is slower than when I was using ODBC on NT at
returning the results of a simple query and seems to timeout (I receive a
read script header timed out message in Linux error log) with more complex
queries.
So my questions are:
Am I correct in my understanding that all I needed to do to get it to work
was change the driver from ODBC to XBase?
Are there any setting on the Linux server than need to be performed?
Shouldn't the response actually be faster using the native XBase driver over
the ODBC?
Should I attempt to go back to ODBC and does anyone know where to find a
diver/manager?
My second problem, I believe is actually related to Linux but am not sure.
Some of the database data contains french characters which are not displayed
properly since the move to Linux/Apache/Xbase. The database originally uses
cp 850 on Windows. I have found mention of a utility called recode but was
wondering whether or not there was a better way?
I have included a copy of the script below in case it helps. Any help much
appreciated.
Thanks,
Rick.
#!/usr/local/bin/perl
# This script returns Listee info based on city entered.
# To run this script without the form type the following:
# url_to_script/crhsppdb.pl?city=ottawa.
use DBI;
use CGI;
use CGI::Carp qw(fatalsToBrowser);
$dbName = "DBI:XBase:/www/crhspp";
$dbUserName = "";
$dbPassword = "";
### Get input and format it.###
$dataIn = new CGI;
print $dataIn->header();
$requestType = $dataIn->param('requestType');
### Get language used.###
$language = $dataIn->param('language');
### Get search type.###
$search_type = $dataIn->param('searchtype');
if ($search_type eq "ByLast")
{
### Get expertise.###
$lastname = $dataIn->param('lastname');
### Connect to Database.###
$dbh = &connectToDB()
or die $DBI::errstr;
### Set display length of Memo field.###
$dbh->{LongReadLen}= 600;
### Add Wildcard. ###
$lastname = $lastname . '%';
### Compensate for last names with quotes in name.###
$lastname = $dbh->quote($lastname);
$sqlStatement= qq!SELECT
W_FULLNAME,W_UNIV,W_LANG,W_ADD,W_CITY,W_PHONE,W_FAX,W_PHONE2,W_FAX2,W_EMAIL,
W_O2_ADD,W_O2_CITY,W_O2_PH,W_O2_FAX,W_O2_PH2,W_O2_FAX2,W_O2_EML,
W_OP1_ADD,W_OP1_CITY,W_OP1_PH,W_OP1_FAX,W_OP1_PH2,W_OP1_FAX2,W_OP1_EML,
W_OP2_ADD,W_OP2_CITY,W_OP2_PH,W_OP2_FAX,W_OP2_PH2,W_OP2_FAX2,W_OP2_EML,
W_OP3_ADD,W_OP3_CITY,W_OP3_PH,W_OP3_FAX,W_OP3_PH2,W_OP3_FAX2,W_OP3_EML,
W_TO,W_AEXP,W_AGE FROM CRWEB WHERE W_LASTNAME LIKE $lastname;!;
}
elsif ($search_type eq "ByCity")
{
### Get city name entered.###
$city = $dataIn->param('city');
if ($city eq "")
{
printHeader();
print qq! Found 0 results<p>!;
print qq! <b>Error in input.</b><p>No city entered. Please try your search
again.!;
exit;
}
$city = $dataIn->param('city');
### Strip all periods.###
$city =~ s/\.//g;
### Replace all dashes with spaces.###
$city =~ s/-/ /g;
### If Montréal is entered, replace with Montreal.###
if ($city eq "Montréal")
{
$city = "Montreal"
}
### Get expertise.###
$expertise = $dataIn->param('expertise');
### Connect to Database.###
$dbh = &connectToDB()
or die $DBI::errstr;
### Set display length of Memo field.###
$dbh->{LongReadLen}= 600;
### Compensate for cities with quotes in name.###
$cityname = $city;
$city = $dbh->quote($city);
if ($expertise eq "All")
{
$sqlStatement= qq!SELECT
W_FULLNAME,W_UNIV,W_LANG,W_ADD,W_CITY,W_PHONE,W_FAX,W_PHONE2,W_FAX2,W_EMAIL,
W_O2_ADD,W_O2_CITY,W_O2_PH,W_O2_FAX,W_O2_PH2,W_O2_FAX2,W_O2_EML,
W_OP1_ADD,W_OP1_CITY,W_OP1_PH,W_OP1_FAX,W_OP1_PH2,W_OP1_FAX2,W_OP1_EML,
W_OP2_ADD,W_OP2_CITY,W_OP2_PH,W_OP2_FAX,W_OP2_PH2,W_OP2_FAX2,W_OP2_EML,
W_OP3_ADD,W_OP3_CITY,W_OP3_PH,W_OP3_FAX,W_OP3_PH2,W_OP3_FAX2,W_OP3_EML,
W_TO,W_AEXP,W_AGE FROM CRWEB WHERE W_CITY = $city OR W_REGION = $city OR
W_O2_CITY = $city OR W_O2_REGN = $city OR
W_OP1_CITY = $city OR W_OP1_REGN = $city OR W_OP2_CITY = $city
OR W_OP2_REGN = $city OR W_OP3_CITY = $city OR W_OP3_REGN = $city;!;
}
else
{
$sqlStatement= qq!SELECT
W_FULLNAME,W_UNIV,W_LANG,W_ADD,W_CITY,W_PHONE,W_FAX,W_PHONE2,W_FAX2,W_EMAIL,
W_O2_ADD,W_O2_CITY,W_O2_PH,W_O2_FAX,W_O2_PH2,W_O2_FAX2,W_O2_EML,
W_OP1_ADD,W_OP1_CITY,W_OP1_PH,W_OP1_FAX,W_OP1_PH2,W_OP1_FAX2,W_OP1_EML,
W_OP2_ADD,W_OP2_CITY,W_OP2_PH,W_OP2_FAX,W_OP2_PH2,W_OP2_FAX2,W_OP2_EML,
W_OP3_ADD,W_OP3_CITY,W_OP3_PH,W_OP3_FAX,W_OP3_PH2,W_OP3_FAX2,W_OP3_EML,
W_TO,W_AEXP,W_AGE FROM CRWEB WHERE (W_CITY = $city OR W_REGION = $city
OR W_O2_CITY = $city OR W_O2_REGN = $city OR
W_OP1_CITY = $city OR W_OP1_REGN = $city OR W_OP2_CITY = $city
OR W_OP2_REGN = $city OR W_OP3_CITY = $city OR W_OP3_REGN = $city)
AND $expertise;!;
}
}
else
{
print "Unknown search type";
exit;
}
### Execute SQL Statement.###
$dataObject = executeSQLStatement($sqlStatement);
@dbRows = &getDBRows($dataObject);
### Display Header. ###
printHeader();
### Process returned results. ###
foreach $rowReference (@dbRows)
{
$number_of_rows = @$rowReference;
if ($language eq "English")
{
print qq! Found $number_of_rows result(s)<p>!;
}
else
{
print qq! $number_of_rows résultat(s) trouvé<p>!;
}
if ($number_of_rows ne 0)
{
foreach $columnReference (@$rowReference)
{
print qq!
<font size="-1">
<b>@$columnReference[0]</b>
@$columnReference[1] @$columnReference[2].<br>!;
### If search is by last display a basic listing. ###
if ($search_type eq "ByLast")
print qq! @$columnReference[3]<br>!;
if (@$columnReference[5] ne "")
{
print qq! @$columnReference[5]!;
}
if (@$columnReference[6] ne "")
{
print qq! FAX: @$columnReference[6]!;
}
if (@$columnReference[7] ne "")
{
print qq! @$columnReference[7]!;
}
if (@$columnReference[8] ne "")
{
print qq! FAX: @$columnReference[8]!;
}
if (@$columnReference[9] ne "")
{
print qq! <br>E-MAIL: @$columnReference[9]!;
}
if (@$columnReference[10] ne "")
{
print qq! <br>@$columnReference[10]!;
}
if (@$columnReference[12] ne "")
{
print qq! <br>@$columnReference[12]!;
}
if (@$columnReference[13] ne "")
{
print qq! FAX: @$columnReference[13]!;
}
if (@$columnReference[14] ne "")
{
print qq! @$columnReference[14]!;
}
if (@$columnReference[15] ne "")
{
print qq! FAX: @$columnReference[15]!;
}
if (@$columnReference[16] ne "")
{
print qq! <br>E-MAIL: @$columnReference[16]!;
}
}
else
{
### Check for match on first outside province City ###
### If match display that address. ###
if (@$columnReference[18] eq $cityname)
{
print qq! @$columnReference[17]<br>!;
if (@$columnReference[19] ne "")
{
print qq! @$columnReference[19]!;
}
if (@$columnReference[20] ne "")
{
print qq! FAX: @$columnReference[20]!;
}
if (@$columnReference[21] ne "")
{
print qq! @$columnReference[21]!;
}
if (@$columnReference[22] ne "")
{
print qq! FAX: @$columnReference[22]!;
}
if (@$columnReference[23] ne "")
{
print qq! <br>E-MAIL: @$columnReference[23]!;
}
}
### Check for match on second outside province City. ###
### If match display that address. ###
elsif (@$columnReference[25] eq $cityname)
{
print qq! @$columnReference[24]<br>!;
if (@$columnReference[26] ne "")
{
print qq! @$columnReference[26]!;
}
if (@$columnReference[27] ne "")
{
print qq! FAX: @$columnReference[27]!;
}
if (@$columnReference[28] ne "")
{
print qq! @$columnReference[28]!;
}
if (@$columnReference[29] ne "")
{
print qq! FAX: @$columnReference[29]!;
}
if (@$columnReference[30] ne "")
{
print qq! <br>E-MAIL: @$columnReference[30]!;
}
}
### Check for match on third outside province City. ###
### If match display that address. ###
elsif (@$columnReference[32] eq $cityname)
{
print qq! @$columnReference[31]<br>!;
if (@$columnReference[33] ne "")
{
print qq! @$columnReference[33]!;
}
if (@$columnReference[34] ne "")
{
print qq! FAX: @$columnReference[34]!;
}
if (@$columnReference[35] ne "")
{
print qq! @$columnReference[35]!;
}
if (@$columnReference[36] ne "")
{
print qq! FAX: @$columnReference[36]!;
}
if (@$columnReference[37] ne "")
{
print qq! <br>E-MAIL: @$columnReference[37]!;
}
}
### Display the basic listing. ###
else
{
print qq! @$columnReference[3]<br>!;
if (@$columnReference[5] ne "")
{
print qq! @$columnReference[5]!;
}
if (@$columnReference[6] ne "")
{
print qq! FAX: @$columnReference[6]!;
}
if (@$columnReference[7] ne "")
{
print qq! @$columnReference[7]!;
}
if (@$columnReference[8] ne "")
{
print qq! FAX: @$columnReference[8]!;
}
if (@$columnReference[9] ne "")
{
print qq! <br>E-MAIL: @$columnReference[9]!;
}
if (@$columnReference[10] ne "")
{
print qq! <br>@$columnReference[10]!;
}
if (@$columnReference[12] ne "")
{
print qq! <br>@$columnReference[12]!;
}
if (@$columnReference[13] ne "")
{
print qq! FAX: @$columnReference[13]!;
}
if (@$columnReference[14] ne "")
{
print qq! @$columnReference[14]!;
}
if (@$columnReference[15] ne "")
{
print qq! FAX: @$columnReference[15]!;
}
if (@$columnReference[16] ne "")
{
print qq! <br>E-MAIL: @$columnReference[16]!;
}
}
}
if ($language eq "English")
{
print qq! <br>Theoretical Orientation:!;
}
else
{
print qq! <br>orientation théorique:!;
}
if (@$columnReference[38] ne "")
{
print qq! @$columnReference[38]!;
}
if ($language eq "English")
{
print qq! <br>Areas of Expertise:!;
}
else
{
print qq! <br>expertise:!;
}
if (@$columnReference[39] ne "")
{
print qq! @$columnReference[39]</a>!;
}
print qq! <br>Age:!;
if (@$columnReference[40] ne "")
print qq! @$columnReference[40]!;
}
print qq!. <a
href="http://www.crhspp.ca/cgi-bin/crhsppdb.pl#top";>[Go top]</a><p></font>!;
}
}
else
{
if ($language eq "English")
print qq! Your search provided zero results. To improve
your search results, please check our <a
href="http://www.crhspp.ca/infotips.htm";>Search Information and Tips</a>
page<p>!;
}
else
{
print qq! Votre recherche n'a pas trouvé de resultats. S'il vous plaît,
pour améliorer vos resultats, visitez notre page d'<a
href="http://www.crhspp.ca/infotips.htm";>Information et conseils pour la
recherche</a><p>!;
}
}
}
if ($language eq "English")
{
print qq!
<p>
<img border="0" width="475" height="3"
src="http://www.crhspp.ca/Graphics/goldline.gif";><br>
<font face="Arial,Helvetica" size="-2" COLOR="#636363">© 1998, 1999
Canadian Register of Health Service Providers in Psychology.</font>
</body>
</html>!;
}
else
{
print qq!
<p>
<img border="0" width="475" height="3"
src="http://www.crhspp.ca/Graphics/goldline.gif";><br>
<font face="Arial,Helvetica" size="-2" COLOR="#636363">© 1998, 1999
Répertoire Canadien des Psychologues Offrant des Services de Santé.</font>
</body>
</html>!;
}
exit;
###########################################
### connectToDB() ###
###########################################
sub connectToDB
{
return (DBI->connect($dbName, $dbUserName, $dbPassword));
}
###########################################
### executeSQLStatement() ###
###########################################
sub executeSQLStatement
{
my ($sql) = shift;
$dataObject = $dbh->prepare($sql);
$dataObject->execute();
return $dataObject;
}
###########################################
### getDBRows() ###
###########################################
sub getDBRows
{
my ($dataObject) = shift;
return $dataObject->fetchall_arrayref();
}
###########################################
### printHeader() ###
###########################################
sub printHeader
{
### Check language ###
if ($language eq "English")
{
print qq!
<html>
<head>
<title>CRHSPP Search Results</title>
</head>
<BODY BGCOLOR ="#FFFFFF" link="#0000FF" vlink="#000080" alink"0000FF">
<a name="top"></a>
<table border="0" cellspacing="0" cellpadding="0">
<tr valign=top>
<td rowspan=2><img src="http://www.crhspp.ca/Graphics/srchlogo.gif";
align=top width="145" height="108" border=0 alt="CRHSPP"></td>
<td><a href="http://www.crhspp.ca/aboutus.htm";><img
src="http://www.crhspp.ca/Graphics/srchabt.gif"; align=top width="114"
height="62" border=0 alt="About the Canadian Register"></a><a
href="http://www.crhspp.ca/signup.htm";><img
src="http://www.crhspp.ca/Graphics/srchmem.gif"; align=top width="53"
height="62" border=0 alt="Listing with Us"></a><a
href="http://www.crhspp.ca/pubindx.htm";><img
src="http://www.crhspp.ca/Graphics/srchpub.gif"; align=top width="74"
height="62" border=0 alt="Our Publications"></a><a
href="http://www.crhspp.ca/contact.htm";><img
src="http://www.crhspp.ca/Graphics/srchfdb.gif"; align=top width="60"
height="62" border=0 alt="Feedback"></a><a
href="http://www.crhspp.ca/links.htm";><img
src="http://www.crhspp.ca/Graphics/srchlink.gif"; align=top width="51"
height="62" border=0 alt="Related Links"></a><img
src="http://www.crhspp.ca/Graphics/srchblnk.gif"; align=top width="88"
height="62" border=0 alt="Blank"><a
href="http://www.crhspp.ca/findlist.htm";><img
src="http://www.crhspp.ca/Graphics/srchfind.gif"; align=top width="51"
height="62" border=0 alt="New Search"></a></td>
</tr>
<tr>
<td><font size=+2>Canadian Register On-line Directory</font></td>
</tr>
</table>!;
}
else
{
print qq!
<html>
<head>
<title>Résultat des recherches RCPOSS</title>
</head>
<BODY BGCOLOR ="#FFFFFF" link="#0000FF" vlink="#000080" alink"0000FF">
<a name="top"></a>
<table border="0" cellspacing="0" cellpadding="0">
<tr valign=top>
<td rowspan=2><img src="http://www.crhspp.ca/Graphics/srchlogo.gif";
align=top width="145" height="108" border=0 alt="RCPOSS"></td>
<td><a href="http://www.crhspp.ca/apropos.htm";><img
src="http://www.crhspp.ca/Graphics/chaprop.gif"; align=top width="98"
height="62" border=0 alt="À propos du Rép. Canadien"></a><a
href="http://www.crhspp.ca/inscrire.htm";><img
src="http://www.crhspp.ca/Graphics/chinscr.gif"; align=top width="88"
height="62" border=0 alt="Vous souhaitez vous inscrit"></a><a
href="http://www.crhspp.ca/npubindx.htm";><img
src="http://www.crhspp.ca/Graphics/chpubl.gif"; align=top width="74"
height="62" border=0 alt="Nos publications"></a><a
href="http://www.crhspp.ca/comment.htm";><img
src="http://www.crhspp.ca/Graphics/chcom.gif"; align=top width="80"
height="62" border=0 alt="Vos commentaires"></a><a
href="http://www.crhspp.ca/liens.htm";><img
src="http://www.crhspp.ca/Graphics/chliens.gif"; align=top width="47"
height="62" border=0 alt="Autres liens"></a><img
src="http://www.crhspp.ca/Graphics/chblnk.gif"; align=top width="43"
height="62" border=0 alt="Blank"><a
href="http://www.crhspp.ca/chpsy.htm";><img
src="http://www.crhspp.ca/Graphics/chrech.gif"; align=top width="63"
height="62" border=0 alt="Nouvelle recherche"></a></td>
</tr>
<tr>
<td><font size=+2>Répertoire en-ligne du Répertoire Canadien</font></td>
</tr>
</table>!;
}
}
![http://www.crhspp.ca/Graphics/goldline.gif"](http://www.crhspp.ca/Graphics/goldline.gif"){kind=link}
![http://www.crhspp.ca/Graphics/srchlogo.gif"](http://www.crhspp.ca/Graphics/srchlogo.gif"){kind=link}
![http://www.crhspp.ca/Graphics/srchabt.gif"](http://www.crhspp.ca/Graphics/srchabt.gif"){kind=link}
![http://www.crhspp.ca/Graphics/srchmem.gif"](http://www.crhspp.ca/Graphics/srchmem.gif"){kind=link}
![http://www.crhspp.ca/Graphics/srchpub.gif"](http://www.crhspp.ca/Graphics/srchpub.gif"){kind=link}
![http://www.crhspp.ca/Graphics/srchfdb.gif"](http://www.crhspp.ca/Graphics/srchfdb.gif"){kind=link}
![http://www.crhspp.ca/Graphics/srchlink.gif"](http://www.crhspp.ca/Graphics/srchlink.gif"){kind=link}
![http://www.crhspp.ca/Graphics/srchblnk.gif"](http://www.crhspp.ca/Graphics/srchblnk.gif"){kind=link}
![http://www.crhspp.ca/Graphics/srchfind.gif"](http://www.crhspp.ca/Graphics/srchfind.gif"){kind=link}
![http://www.crhspp.ca/Graphics/chaprop.gif"](http://www.crhspp.ca/Graphics/chaprop.gif"){kind=link}
![http://www.crhspp.ca/Graphics/chinscr.gif"](http://www.crhspp.ca/Graphics/chinscr.gif"){kind=link}
![http://www.crhspp.ca/Graphics/chpubl.gif"](http://www.crhspp.ca/Graphics/chpubl.gif"){kind=link}
![http://www.crhspp.ca/Graphics/chcom.gif"](http://www.crhspp.ca/Graphics/chcom.gif"){kind=link}
![http://www.crhspp.ca/Graphics/chliens.gif"](http://www.crhspp.ca/Graphics/chliens.gif"){kind=link}
![http://www.crhspp.ca/Graphics/chblnk.gif"](http://www.crhspp.ca/Graphics/chblnk.gif"){kind=link}
![http://www.crhspp.ca/Graphics/chrech.gif"](http://www.crhspp.ca/Graphics/chrech.gif"){kind=link}