Writing to MS Access using DBI and DBD::ODBC

Jim Whitcomb Thu, 16 Oct 2003 16:30:19 -0700

I can read my MS Access DB using DBI but I cannot write, delete, or update it. I have seen a couple relevant discussions posted but none (I've seen) offered a solution.

The web site is using:
 Windows 2000 Server
 Perl v 5.8.0 build 805
 DBI v 1.37 from ActiveState
 DBD-ODBC v 1.06 from ActiveState

Code fragment:

my ($DSN, $dbh, $sth);
$DSN = 'driver=Microsoft Access Driver (*.mdb); dbq=SID.mdb';
$dbh = DBI->connect("dbi:ODBC:$DSN", '','') or
 print "Cannot connect to $DSN $DBI::errstr\n ";

### I can then read the file contents successfully
$sth = $dbh->do( "Delete from SID" ) or
print "Cannot do delete: $DBI::errstr\n ";

### I get error message 1 below
$sth = $dbh->do( "insert into SID (SID, LOA, Expire) values ('anything', 'N', 'ts')" ) or
print "Cannot do insert: $DBI::errstr\n ";

### I get error message 2 below

I have replaced the DO with the Prepare/Execute equivalent with no change (the execute gives the error).

Error Message 1 (from the attempt to delete all records):

Cannot do delete:
[Microsoft][ODBC Microsoft Access Driver] Could not delete from specified tables. (SQL-HY000)(DBD: Execute immediate failed err=-1)

Error Message 2 (from the attempt to insert a record):

Cannot do insert:
[Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable query. (SQL-HY000)(DBD: Execute immediate failed err=-1)

Perhaps a related issue. If I attempt to provide a URL to the database or an absolute path I get an error on the attempt to connect:

Cannot connect to driver=Microsoft Access Driver (*.mdb); dbq=/{path}/SID.mdb
[Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key 'Temporary (volatile) Jet DSN for process 0x470 Thread 0xcc0 DBC 0x1a41c54 Jet'. (SQL-HY000)
[Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key 'Temporary (volatile) Jet DSN for process 0x470 Thread 0xcc0 DBC 0x1a41c54 Jet'. (SQL-HY000)
[Microsoft][ODBC Microsoft Access Driver] The Microsoft Jet database engine cannot open the file '(unknown)'. It is already opened exclusively by another user, or you need permission to view its data. (SQL-HY000)(DBD: db_login/SQLConnect err=-1)

This, together with a hint from one posting, suggests that the "Jet" directory, which I understand is the WINNT directory, be made world writable. Can anyone confirm that? Is that related to my inability to write the DB? Is it reasonable to ask the web site host to make that directory world writable? (Seems risky to me!)

Finally, related to a direct path, I understand that if using ASP (ugh, rather than perl?) one can convert a path using a Server.MapPath() call. What is the perl equivalent?

The people hosting the web site are willing to do what is necessary, but neither of us knows what the solution is.

Note: The SID table (the only table in SID.mdb) has just the 3 text fields named in the Insert command. The errors are the same if there is no primary key or if the SID field is the primary key. The SQL commands have been confirmed to work correctly on the DB not using Perl/DBI. Permissions have been checked and appear to be correctly set.

#!/usr/bin/perl -w

##### All the header stuff
use strict;
use CGI;
use DBI;
my($q) = CGI::new();
my ($sid, $level, $expire);
my ($DSN, $dbh, $sth);


# include the necessary headers
print $q->header( "text/html" );

##### # 1 -- Connect to the DB
$DSN = 'driver=Microsoft Access Driver (*.mdb); 
dbq=Version6/Members/Data/qMhNxLQsEd/SID.mdb';
$dbh = DBI->connect("dbi:ODBC:$DSN", '','') or 
        print "Cannot connect to $DSN<br>$DBI::errstr\n<br>";

##### # 2 -- dump contents
print "Dump the contents<br>\n";
Dump();

##### # 3 -- delete contents
print "<br><br>Try to delete the entire contents<br>\n";
$sth = $dbh->do( "Delete from SID" ) or
        print "Cannot do delete:<br>$DBI::errstr\n<br>";

##### # 4 -- Insert new record
print "<br><br>Try to Insert a new record<br>\n";
$sth = $dbh->do( "insert into SID (SID, LOA, Expire) values ('anything', 'N', 'ts')" ) 
or
        print "Cannot do insert:<br>$DBI::errstr\n<br>";

##### # 5 -- Redump contents
print "<br><br>Dump the contents again to confirm the delete and insert<br>\n";
Dump();


##### Subroutine to dump the contents of the DB
sub Dump {
        $sth = $dbh->prepare( "SELECT SID, LOA, Expire FROM SID" ) or 
                print "Cannot prepare:<br>$DBI::errstr\n<br>";
        $sth->execute() or 
                print "Cannot execute:<br>$DBI::errstr\n<br>";
        print "<font face='Courier New'>";
        while ( ($sid, $level, $expire) = $sth->fetchrow_array ) {
                print "--> $sid . $level . $expire\n<br>";
        }
        print "</font>";
}

Dump the contents
--> mrWVDEqrFzhYRq1Y . N . 1065907337
--> ncfdLM5nkljWjmsw . N . 1065908180


Try to delete the entire contents
Cannot do delete:
[Microsoft][ODBC Microsoft Access Driver] Could not delete from specified tables. 
(SQL-HY000)(DBD: Execute immediate failed err=-1)


Try to Insert a new record
Cannot do insert:
[Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable query. 
(SQL-HY000)(DBD: Execute immediate failed err=-1)


Dump the contents again to confirm the delete and insert
--> mrWVDEqrFzhYRq1Y . N . 1065907337
--> ncfdLM5nkljWjmsw . N . 1065908180

Writing to MS Access using DBI and DBD::ODBC

Reply via email to