Make sure your DSN is not *read only*.
Ilya Sterin
-----Original Message-----
From: Trevor Webster [mailto:[EMAIL PROTECTED]]
Sent: Thursday, May 10, 2001 7:00 PM
To: Sterin, Ilya; dbi-users
Subject: Re: Problem with DBI and Access newbie
Yeah sorry, but I had started preparing the message while I was making
adjustments on the fly, so was manually changing the script in the messge
rather than recopying . Bad error on my part sorry!. My actual script is
correct and has $sqlstatement. So it is not an empty string issue. Below
is the actual code in the script for the last and all important area.
$sqlstatement = qq/INSERT INTO Respondent (england, relativity, lotr)
VALUES ('$england', '$relativity', '$lotr')/;
#SQL statement syntax verification
print qq/$sqlstatement /;
my $sth = $dbh->prepare($sqlstatement);
$sth-> execute||
die "Could not execute SQL statement";
It still gives the execute error previously mentioned as per screen output
below.
CGI Error
The specified CGI application misbehaved by not returning a complete set of
HTTP headers. The headers it did return are:
main::readPostInputs() called too early to check prototype at
c:\inetpub\wwwroot\cgi-bin\survey.pl line 4.
main::UpdateDatabase() called too early to check prototype at
c:\inetpub\wwwroot\cgi-bin\survey.pl line 6.
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
69.
Could not execute SQL statement at c:\inetpub\wwwroot\cgi-bin\survey.pl line
69.
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 attached a fresh copy of the trace file for your perusal.
cheers
Trevor
> >From this...
>
> $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;
>
> Looks like you are preparing an empty string $sqlstatement?
>
> Ilya Sterin
>
> -----Original Message-----
> From: Trevor Webster
> To: dbi-users
> Cc: Neil Lunn; Sterin, Ilya
> Sent: 05/10/2001 4:36 AM
> 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');
>
> #Error trapping for invalid connection
> # if (! $myDb){
> if (! $dbh){
>
> print "Failed to Connect to $DSN\n";
> #end program as processing aborted
> 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 /;
>
> $sqlstatement = 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.
>
> dbs.Execute "insert into Respondent (england, relativity, lotr)"
> _
> & "values ('" & vengland & "','" & vrelativity &
> "','" & vlotr & "')"
>
> 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 have since added DBI->trace as suggested and I have attached the
> resultant trace file.
>
> 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?
>
> Thanks again
> Trevor
>
>
>
> <<dbitrace.log>>
>
