[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 383
  • Last Modified:

Access 2003 CGI with Perl

I'm a newbie working with perl/cgi, and in my attempt to teach myself what's up with it, I've run into a snag that hasn't been solved by exhaustive googling.  I'm attempting to get data from an Access 2003 database using a perl script (with ActivePerl 5.8.1.807) and I keep getting the error:

DBI connect('%name%) failed: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (SQL-IM002))DBD: db_login/SQLConnect err=-1)

I don't know jack about ODBC drivers, and I'm just now learning perl, so I'm assuming that the syntax of my script is no good:

    use strict;
    use DBI;

    #open connection to Access database
    my $DBH = DBI->connect("dbi:ODBC:%name%");

I've seen a bunch of examples of working code that allows you to open an access database, but what confuses me is exactly what's happening (makes it tough to figure out what I'm doing wrong).  %name% should be the filename (without the extension?) of a .dsn file located in Program Files\Common Files\ODBC\Data Sources in XP Pro, right?  I got that .dsn file set up through a wizard in the export option of Access, which gave me an error:

     You cannot use ODBC to import from, export to, or link an external Microsoft Jet or ISAM database table to your database.  

not quite sure what the hell that's supposed to mean, but I'm certainly not trying to do anything tricky - just create a connection to let Perl read from my access database (i.e. learn to crawl before you learn to walk).  I noticed a few results on Google mentioning that there was a problem with the DBI:ODBC module in activeperl, is that the case?  

I just want to be able to create a connection to the Access databse - eventually this will become a CGI script to take form input from a web page and use it to search the database for records matching the input criteria, then output some data from the matching records to the user - not rocket science but tricky enough for a novice.

Any ideas where I might be screwing up?  Any common pitfalls to look out for?  Any obvious mistake in something I mentioned above?
0
Zeek0
Asked:
Zeek0
1 Solution
 
Kim RyanIT ConsultantCommented:
There is nothing wrong with your script, and u can definitley use ODBC to interface to Access database.

I think the problem lies with the setup up of your data source. I would normally use the control panel to create a DSN and specify the correct dirver, in your case MS Access Driver*.mdb. Note that not all drivers may be installed on your operating system by default. You give your data source an name (and make sure you test the connect, such as my_access_dsn. You should also have the option to test the data source. Now use that DSN name in your DB->connect line in Perl.
0
 
kanduraCommented:
From the DBD-ODBC manual:

Connect without DSN The ability to connect without a full DSN is introduced in version 0.21.
    Example (using MS Access):
        my $DSN = 'driver=Microsoft Access Driver (*.mdb);dbq=\\\\cheese\\g$\\perltest.mdb';
        my $dbh = DBI->connect("dbi:ODBC:$DSN", '','')
            or die $DBI::errstr;

Note the full path to the mdb file after dbq.
0
 
Zeek0Author Commented:
Ahh, that seems to do the trick.  

I noticed that there's no ODBC option on the control panel - is that supposed to be standard for Win XP Pro SP2?  I remember having seen it in the past, but there's no tab now.

Of course now I'm getting a different error:

DBD::ODBC::st execute failed [Microsoft][ODBC Microsoft Access Driver] Record(s) cannot be read; no read permissions on %file%

I have pretty restrictive NTFS permissions on this system, so is there some account that should be granted access to read the file?  My account had admin privileges, so the restriction certain isn't on me as the user.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
kanduraCommented:
Is that %file% something you made up to hide the real filename from us, or is that literally what you have in the script?
What account is running your script? It probably needs read and write permission on the database (and possibly on the directory containing the mdb file, since I think Access needs to create lock files)

How are you running your script? From the command line, or through CGI? If CGI, which web server do you use?
0
 
kanduraCommented:
BTW, I think the ODBC Data Source Administrator is in the Administrative Tools section of Control Panel.
If several accounts need access to the ODBC link, make sure to create a system DSN.
0
 
Zeek0Author Commented:
Ah, found the ODBC administrator, thanks.  I thought I looked in admin tools, guess not.  Right now I'm trying to run this as a command-line script, but it will be CGI eventually.  The %file% is just a filename wildcard -- I hand-typed the error into my question, so replacing the filename was just force of habit (I don't think you'll try to hijack my system to read my databases).  

I'm not really sure about the answer to your question regarding the account running my script.  I'm running it from the command-line while logged in with admin privileges, so I would assume that the interpreter is running with my credentials.  I'm not sure how the scripting process would use an account to open the database - I supply my username/pw credentials in the connection line, and my user account has full NTFS permissions over the file and directory.
0
 
sstoukCommented:
Let me give you some headstart.

Here's a snippet that will use your DSN and dump the information from the MS Access Table.
You need to have DBI and DBD::ODBC modules installed.


#################################################################################
my(%Config) = undef;
$main::Config{DSN} = "TrioMotors";
$main::Config{TableName} = "TableName";
main::ConnectDatabase();
my(%DbData) = undef;
my($Sql) = undef;

      if ($main::Config{DbConfig}{Connected} == 1)
      {
            if (defined $main::Config{dbh})
            {
                  $Sql = "SELECT * FROM $main::Config{TableName}";
                  main::Debug("Executing: $Sql");
                  my($hash_ref) = undef;
                  $main::Config{sth} = $main::Config{dbh} -> prepare (qq($Sql));
                  my($CountRecords) = undef;
                  
                  if (defined $main::Config{sth})
                  {
                  $main::Config{sth} -> execute;
                        while ($hash_ref = $main::Config{sth}->fetchrow_hashref)
                        {
                        $CountRecords++;
                        $DbData{$CountRecords} = %{$hash_ref};
                        };
                  $main::Config{sth} -> finish;            
                        
                  } else {
                        main::Debug("ERROR: Could Not Define the Statement Handle...");
                        };
                  

                  foreach my $key (sort keys %DbData)
                  {
                        if (defined $DbData{$key})
                        {
                              foreach my $rkey (sort keys %{$DbData{$key}})
                              {
                                    if (defined $DbData{$key}{$rkey})
                                    {
                                    main::Debug("$key : $rkey = $DbData{$key}{$rkey}");
                                    };
                              };
                        };
                  };

            } else {
                  main::Debug("Database Object Not Defined...");
                  };
      } else {
            main::Debug("Not Connected to DB");
            };


main::DisconnectDatabase();
exit;

# MySQL Database routine Library
######################################################################################################
sub main::ConnectDatabase
######################################################################################################
{
use DBI;
use DBD::ODBC;
main::Debug("... connecting to database: MS Access");
$main::Config{DbConfig}{Connected} = 1;
$main::Config{SecureDbPath} = "$main::Config{DbPath}"."data/";
$main::Config{ConnectionString} = "Driver={Microsoft Access Driver (*.mdb)}; dbq=";
undef $main::Config{dbh};
      if (defined $main::Config{DSN})
      {
      $ConnectionString = "dbi:ODBC:" . "$main::Config{DSN}";
      main::Debug("Database connection string: \"$ConnectionString\"");
      $main::Config{dbh} = DBI -> connect("dbi:ODBC:$main::Config{DSN}","","") || main::DBConnectError();
      } else {
                  my($DbName) = $main::Config{SecureDbPath} . "app.mdb";
                  unless (-e $DbName)
                  {
                  my($DbTemplate) = $main::Config{LibPath} . "dbtemplate.mdb";
                  main::Debug("Default database name: \"$DbName\"");
                        if (-e $DbTemplate)
                        {
                        main::Debug("OK - Database file does not exist. Creating new.");
                        main::CopyFile($DbTemplate,$DbName);
                        $main::Config{DbJustCreated} = 1;
                        } else {
                              main::Debug("ERROR: Database file does not exist. Database Template does not exist either! \"$DbTemplate\"");
                              };
                  };
                  if (-f $DbName)
                  {
                  $ConnectionString = $main::Config{ConnectionString}.$DbName;
                   main::Debug("Database connection string: \"$ConnectionString\"");
                  $main::Config{dbh} = DBI->connect("dbi:ODBC:$ConnectionString","","") or main::DBConnectionError();      
                  } else {
                        main::Debug("ERROR: Cannot connect - No database file! Database folder not writable? \"$main::Config{DbFolder}\"");
                        };
      
            };
      if ($main::Config{DbConfig}{Connected} == 1)
      {
      main::ReadDbInfo();
      return 1;
      } else {
            return 0;      
            };
};
######################################################################################################
sub main::DisconnectDatabase
######################################################################################################
{
      if ($main::Config{DbConfig}{Connected} == 1)
      {
            if (defined $main::Config{sth})
            {
            $main::Config{sth} -> finish;      
            };
      $main::Config{dbh}->disconnect;      
      $main::Config{DbConfig}{Connected} = 0;
      };
};
########################################################################
######################################################################################################
sub main::ReadDbInfo {
######################################################################################################      
my $table_number = 1;
$main::Config{sth} = $main::Config{dbh}->table_info();
# print "Perl Version: ". $] ."<br>";
      while ( my ( $qual, $owner, $table, $type ) = $main::Config{sth}->fetchrow_array())
      {
            unless ($table =~ m!(MSysACEs|MSysObjects|MSysQueries|MSysRelationships)!i)
            {
            $main::Config{db_Database_Info}{$table}{name} = $table;
            $main::Config{db_Database_Info}{$table}{owner} = $owner;
            $main::Config{db_Database_Info}{$table}{type} = $type;
            $main::Config{db_Database_Info}{$table}{qual} = $qual;
            $main::Config{sb_Total_Tables}++;
            main::Debug("\$main::Config{db_Database_Info}{$table}{name} = $main::Config{db_Database_Info}{$table}{name}");
            };
      };      
$main::Config{sth}->finish();      
main::ReadTableInfo();
};
######################################################################################################
sub main::ReadTableInfo {
######################################################################################################      
#print "<br>:: TOTAL TABLES: $main::Config{sb_Total_Tables}<br>";
      foreach my $key (sort keys %{$main::Config{db_Database_Info}})
      {
            if (defined $main::Config{db_Database_Info}{$key}{name})
            {
            my $Sql = "select * from $key";
#            print "<br>$Sql\n";
            $main::Config{sth} = $main::Config{dbh}->prepare($Sql);
            my($names) = undef;
            if (defined $main::Config{sth})
            {
            $main::Config{sth}->execute();
            $names = $main::Config{sth}->{NAME};
            };
            my(@ColumnNames) = undef;
            @ColumnNames = @$names;
            $main::Config{db_Table_Info}{$key}{TotalCols} = scalar @ColumnNames;
            
            # Dump Column Names
            main::Debug("@ColumnNames");
            
#            print "\n<br>TABLE: <b>$main::Config{db_Database_Info}{$key}{name}</b> Columns ($main::Config{db_Table_Info}{$key}{TotalCols}):\"@ColumnNames\"";
#            print "\n<TABLE BORDER=\"1\" CELLPADDING=\"0\" CELLSPACING=\"0\">";
#            print "\n<TR><TD><b>Name:</b></TD><TD>Type</TD><TD>Precision</TD><TD>Scale</TD><TD>Allow Null?</TD><TD>Is Key?</TD></TR>";
#            my($Str) = undef;


                  for ( my $i = 0; $i <= $main::Config{db_Table_Info}{$key}{TotalCols}; $i++ )
                  {
                  my $col_name  = $main::Config{sth}->{'NAME'}->[$i];
                  $main::Config{db_Column_Info}{$key}{$col_name}{name} = $col_name;
                  
                  my $col_type  = $main::Config{sth}->{'TYPE'}->[$i];
                  $main::Config{db_Column_Info}{$key}{$col_name}{type_numeric} = $col_type;
                  
                  $col_type  = &_format_numeric_type_code($col_type);
                  $main::Config{db_Column_Info}{$key}{$col_name}{type} = $col_type;
                  
                  my $col_prec  = $main::Config{sth}->{'PRECISION'}->[$i];
                  $main::Config{db_Column_Info}{$key}{$col_prec}{precision} = $col_prec;
                  
                  my $col_scale = $main::Config{sth}->{'SCALE'}->[$i];
                  $main::Config{db_Column_Info}{$key}{$col_name}{scale} = $col_scale;
                  #my $nullable  = ("Yes", "No", "Unk") [$sth->{'NULLABLE'}->[$i] ];
                  
                  my $nullable  = $main::Config{sth}->{'NULLABLE'}->[$i];
                  $main::Config{db_Column_Info}{$key}{$col_name}{nullable} = $nullable;

                  my $is_key  = undef;
                  if ($main::Config{DbType} =~ m!Access!i)
                  {
                  $is_key  = $main::Config{sth}->{'is_key'}->[$i];
                  $main::Config{db_Column_Info}{$key}{$col_name}{is_key} = $is_key ;
                  } else {
                        $is_key  = $main::Config{sth}->{'mysql_is_key'}->[$i];
                        $main::Config{db_Column_Info}{$key}{$col_name}{is_key} = $is_key ;
                        };

#print "\n<TR><TD><b>$main::Config{db_Column_Info}{$key}{$col_name}{name}</b></TD><TD>$main::Config{db_Column_Info}{$key}{$col_name}{type}</TD><TD>$main::Config{db_Column_Info}{$key}{$col_prec}{precision}</TD><TD>$main::Config{db_Column_Info}{$key}{$col_name}{scale}</TD><TD>$main::Config{db_Column_Info}{$key}{$col_name}{nullable}</TD><TD>$main::Config{db_Column_Info}{$key}{$col_name}{is_key}</TD></TR>";
                  }
#            print "\n</TABLE>";
                  $main::Config{sth}->finish();      
            };
      };
};
######################################################################################################
sub _format_numeric_type_code {
######################################################################################################      
        my $code = shift;
        my $value = 'default';
        my %sql_type =  (
                               1   => 'CHAR',
                               2   => 'NUMERIC',
                               3   => 'DECIMAL',
                               4   => 'INTEGER',
                               5   => 'SMALLINT',
                               6   => 'FLOAT',
                               7   => 'REAL',
                               8   => 'DOUBLE',
                               9   => 'DATE',
                               10  => 'TIME',
                               11  => 'TIMESTAMP',
                               12  => 'VARCHAR',
                               -1  => 'LVARCHAR',
                               -2  => 'BINARY',
                               -3  => 'VARBINARY',
                               -4  => 'LVARBINARY',
                               -5  => 'BIGINT',
                               -6  => 'TINYINT',
                               -7  => 'BIT',
                               -8  => 'WCHAR',
                               -9  => 'WVARCHAR',
                               -10 => 'WLVARCHAR',
                       );
        $value = $sql_type{$code};
       return $value;
};
######################################################################################################
sub main::DBError {
######################################################################################################      
main::Debug("ERROR: Executing Database Statement.");      
};
######################################################################################################
######################################################################################################
sub main::DBConnectError {
######################################################################################################      
main::Debug("ERROR: Connecting to Database.");      
$main::Config{DbConfig}{Connected} = 0;
};
######################################################################################################
# Package Version : 1
##############################################################################
sub main::Debug
##############################################################################
{
my($Line) = (caller(1))[2];
my($ParentSub) = (caller(1))[3];
my($Msg) = $_[0];
print "$Line $ParentSub - $Msg\n";
return 1;
};
##############################################################################
0
 
Zeek0Author Commented:
I appreciate the thoroughness of your response, but in all honesty, it a bit of an overkill.  I'm trying to figure out what's causing me the error connecting to the database.  I went through the ODBC console and set up everything, but I'm still getting the 'no read permissions' error, and I can't figure out why.  I've configured everything I can to use the secured system database attached the Access DB, and I've supplied my username and password.  From what I can see, there's no reason access should be denied.
0
 
Zeek0Author Commented:
Well I think the problem was solved by using Win32::ODBC instead of DBI::ODBC.  I'm not satisfied at being totally confused by why it didn't work before, but I guess I'll just get good with the new mod.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now