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

Hiya All! I need help -- I recently changed to a different webhost who requires me to label my Access databases (i.e. DSN name). The thing is that I had the script below on my previous host and it worked fine and now I have to convert it to work with a DSN name ("ml"). Please help :-) 
----
sub viewall {
my $dbh;
$dbh = CreateObject OLE "ADODB.Connection" or die "Can't create connec
+tion to DataBase: $!" unless $dbh;
$dbh->Open("Driver={Microsoft Access Driver (*.mdb)};DBQ==../../db/ml.
+mdb");

my $rs = CreateObject OLE "ADODB.Recordset" or die "Can't adminmail da
+tabase problemgfg"; 
my $sqlstatement = "SELECT firstname,lastname,company,title,email FROM
+ users";

$rs = $dbh->Execute( $sqlstatement ) or die print ( "Can't adminmail d
+atabase problemdsds Perl says $!, DBI says ",$dbh::errstr,""); 
print <<ENDOF;
<div align="center">
  <center>
  <table border="1" cellpadding="4" cellspacing="0" bordercolor="#FFFF
+FF" bordercolordark="#000080" bordercolorlight="#FFFFFF">
    <tr>
      <td align="center"><font face="Verdana" size="2"><b>First Name</
+b></font></td>
      <td align="center"><font face="Verdana" size="2"><b>Last Name</b
+></font></td>
      <td align="center"><font face="Verdana" size="2"><b>Company Name
+</b></font></td>
      <td align="center"><font face="Verdana" size="2"><b>Title</b></f
+ont></td>
      <td align="center"><font face="Verdana" size="2"><b>Email Addres
+s</b></font></td>
    </tr>
ENDOF
while (!$rs->EOF())
{
        my $checkemail = $rs->Fields('email')->Value;
        my $fname = $rs->Fields('firstname')->Value;
        my $lname = $rs->Fields('lastname')->Value;
        my $cname = $rs->Fields('company')->Value;
        my $title = $rs->Fields('title')->Value;
        
print <<END_OF_TEXT;
<tr>
<td><font face="Verdana" size="2">$fname</font></td>
<td><font face="Verdana" size="2">$lname</font></td>
<td><font face="Verdana" size="2">$cname</font></td>
<td><font face="Verdana" size="2">$title</font></td>
<td><font face="Verdana" size="2">$checkemail</font></td>
</tr>
END_OF_TEXT
        
        $rs->MoveNext;
    }
print <<ENDOFB;
  </table>
  </center>
</div>
ENDOFB
$dbh->Close();
admin();
}

[download]
--------- Thanks in advance! Surya

Replies are listed 'Best First'.
Re: Need help with MS Access with Perl.......
by holygrail (Scribe) on Jun 28, 2001 at 11:20 UTC
    While I don't have the solution to your problem, I take the opportunity to comment on some strange things you use in your code: 
    $dbh = CreateObject OLE "ADODB.Connection" or die "Can't create connec
+tion to DataBase: $!" unless $dbh;

    [download]
    In this statement you create the OLE object, or if that fails, you die with a message UNLESS it did not fail...Read that again... You want to remove the unless $dbh

    Another thing is this: 
    $rs = $dbh->Execute( $sqlstatement ) or die print ( "Can't adminmail d
+atabase problemdsds Perl says $!, DBI says ",$dbh::errstr,"");

    [download]
    Why do you do a die print? All other die statements you use have just the text as a parameter, and you now you're printing it explicitly. Besides that I don't know if that would work (it may, it's perl after all), you could just remove the print and the parens (), and it should all work.

    Last question is why you are not using DBI? If you change to another ISP that uses MS SQL Server, or MySQL, you would have to change your code again. With DBI (and more specifically, using the ODBC or ADO DBD-driver) you would have the ability to write portable code. It would prevent you from rewriting your code over and over again.

    Just my $0.02

    --HolyGrail
[reply]
[d/l]
[select]
      In this statement you create the OLE object, or if that fails, you die with a message UNLESS it did not fail...Read that again... You want to remove the unless $dbh

      No! He creates a database connection (or dies), unless he has one already. Thats a fully valid caching technique.
[reply]
        Still kind of strange when you created $dbh in the statement before this one with my $dbh;...

        --HolyGrail
[reply]
[d/l]
Re: Need help with MS Access with Perl.......
by t0mas (Priest) on Jun 28, 2001 at 10:55 UTC
    If your new provider have created a System DSN for you, you'll just change the $dbh->Open call to: 
    $dbh->Open("DSN=ml");

    [download]
    and you should be running again.

    The DSN should be changed to FILEDSN and point to the .dsn file, if they created a File DSN for you.

    Good luck

    /brother t0mas
[reply]
[d/l]
      Thanks a tonf or your reply. It worked for me!!!!!!11 Really appreciate it. take care, surya
[reply]

Back to Seekers of Perl Wisdom