Thanks Phil for your help in this. Having implemented the suggested changes I was
still getting the same error response:
DBD::ODBC::st execute failed: [Microsoft][ODBC Microsoft Access Driver] Operation must
use an updateable query. (SQL-S1000)(DBD: st_execute/SQLExecute err=-1) at
c:\inetpub\wwwroot\cgi-bin\survey.pl line 70.
Could not execute SQL statement at c:\inetpub\wwwroot\cgi-bin\survey.pl line 70.
$sqlstatement = "INSERT INTO Respondent (england, relativity, lotr) VALUES (?,?,?)";
#SQL statement syntax verification
print qq/$sqlstatement/;
INSERT INTO Respondent (england, relativity, lotr) VALUES (?,?,?)
Just to see whether it is something that I am doing wrong I have created a second
table which contains a single record with "London", "Einstein" and "Tolkein" as the
field values. I used this table in a Select statement and then compared them with the
responses received from the web page and I get a correct result see below.
this is the value of england 'Birmingham'
this is the value of relativity 'Einstein'
this is the value of lotr 'Tolkien'
The correct answers were:
The Capital of England is London
Einstein invented the Theory of Relativity and
Tolkien wrote The Lord of The Rings
you got 2 answers right
So it would appear that the problem is purely with the INSERT INTO statement. But I
cannot for the life of me work out what it is.
If you or anybody else can figure it out I would be most grateful.
One point I should make, I have checked the DSN, the directory and even the database
properties and none of them are Read only.
I will reiterate that I am running this script on Microsofts Personal Web Server under
Windows 2000, using Perl 626. I am not sure what version of DBI I am using, I
downloaded it last week if that's any help.
Thanks again Phil, Neil, Ilya and Bodo for all your comments and assistance.
PS
Just on the off chance that there was a problem with the directory I moved the
database out of the website directory into a standard data directory on another
partition and hey presto problem solved.
Thanks to one and all for your helpful hints.
Cheers
Trevor
----- Original Message -----
From: Philip DiFalco
To: [EMAIL PROTECTED]
Sent: Friday, May 11, 2001 12:39 AM
Subject: RE: Problem with DBI and Access newbie
The code below is a sample of INSERTing and SELECTing from some Access Db.
This works for me on my NT4 machine
good luck
- phil
# LIBRARIES: #################################################################
use strict;
use DBI;
# MAIN: ######################################################################
{
my $DSN = 'driver=Microsoft Access Driver (*.mdb);
dbq=E:\\mdb\\someAccessDb.mdb';
my $dbh = DBI->connect("dbi:ODBC:$DSN") or die "$DBI::errstr\n";
# CONSTRUCT VARIUOS SQL STATEMENTS...
my $selectSQL = "SELECT ID, user FROM usage_log";
my $insertSQL = "INSERT INTO usage_log( user ) VALUES( ? )";
# PREPARE SQL STATEMENTS...
my $selectSTH = $dbh->prepare($selectSQL) or die "CAN'T PREPARE STATEMENT:
$dbh->errstr\n";
my $insertSTH = $dbh->prepare($insertSQL) or die "CAN'T PREPARE STATEMENT:
$dbh->errstr\n";
# EXECUTE STATEMENTS AT DATABASE LEVEL...
$insertSTH->execute( "Ed Kranepool" );
$selectSTH->execute;
# REVIEW SELECT RESULTS...
my( $x, $result );
while ( my @data = $selectSTH->fetchrow_array ) {
printf("%03d. id/user = '%s/%s\n", ++$x, $data[0], $data[1]);
}
# CLEAN UP BEFORE EXITING
$selectSTH->finish;
$insertSTH->finish;
$dbh->disconnect;
}
-----Original Message-----
From: Trevor Webster [mailto:[EMAIL PROTECTED]]
Sent: Thursday, May 10, 2001 3:37 AM
Cc: Neil Lunn; Sterin, Ilya
Subject: Re: Problem with DBI and Access newbie
Thank you for the help.
I think the problem was the DSN name.
Having switched off the response form in the script it was coming back with an
error in the dbi->connect line.
I shortened the DSN name to a single word.
Having adjusted a couple of syntax errors the script runs to the execute stage and
falls over.
use DBI;
$DSN = "Survey";
# $myDb = DBI->connect('dbi:ODBC:Survey');
my $dbh = DBI->connect('dbi:ODBC:Survey');
if (! $dbh){
print "Failed to Connect to $DSN\n";
die;
}
#Setup Variables for loading table data
$england = $PostInputs{ 'england'};
$relativity = $PostInputs{ 'relativity'};
$lotr = $PostInputs{ 'lotr'};
print qq/this is the value of 'england '$england', \n /;
print qq/this is the value of relativity '$relativity' , \n /;
print qq/this is the value of lotr '$lotr', \n /;
$SQL = qq/INSERT INTO Respondent (england, relativity, lotr) VALUES ('$england',
'$relativity', '$lotr')/;
# $SQL = qq|INSERT INTO Respondent (england, relativity, lotr) VALUES (?, ?,
?)|;
print qq/$SQL /;
# $sth = $dbh->prepare($SQL); my $sth = $dbh->prepare($sqlstatement);
$sth->execute || die "Could not execute SQL statement";
$myDb->disconnect;
As you can see I have inserted the print statements to test the data variables and
also the $SQL format the results I get are as follows:
this is the value of england 'London'
this is the value of relativity 'Einstein'
this is the value of lotr 'Tolkien'
insert into Respondent (england, relativity, lotr) VALUES ('London', 'Einstein',
'Tolkien')
I have run the 'same' statement in Access itself and it worked without any
problems.
However, the result I get back from the website is:
DBD::ODBC::st execute failed: [Microsoft][ODBC Microsoft Access Driver] Operation
must use an updateable query. (SQL-S1000)(DBD: st_execute/SQLExecute err=-1) at
c:\inetpub\wwwroot\cgi-bin\survey.pl line 67.
Could not execute SQL statement at c:\inetpub\wwwroot\cgi-bin\survey.pl line 67.
I don't know whether you guys can help on this or whether this is the appropriate
forum.
Either way can you please give me some pointers?
