Try updating/reinstalling windows ODBC software and possibly DBD::ODBC.
Seen this error quite a few times, but never a solution, other than
recommended reinstalation.
Ilya Sterin
-----Original Message-----
From: Trevor Webster
To: [EMAIL PROTECTED]
Cc: dbi-users
Sent: 05/11/2001 9:16 AM
Subject: Re: Problem with DBI and Access newbie
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?
