RE: Problem with DBI and Access newbie

[Sterin, Ilya]

>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  /; 
 
   $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.
 
        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>>