A little mySQL problem

[kennymoens]

I'm running a mySQL database, this is accessed through Perl.

Now I'm writing a module to update some of the records of the mySQL database.

# First open connection to database (variables are definied) #
$dbh  = DBI->connect($DSN,$user,$pw);

# SQL Commando #
my $query = "SELECT * FROM roster";
my $sth = $dbh->prepare($query);
$sth->execute;

#-> Here is the code placed for reading the table #

# Close connection #
$sth->finish;
my $finish=$dbh->disconnect;

Until here it works perfectly, now the script submits data through a form, I'll read it out and update the tables:

# Open mySQL connection #
$dbh  = DBI->connect($DSN,$user,$pw);

# First SQL -> This doesn't update the database #
my $queryroster = "UPDATE roster SET pilotnr='$inputpilotnr', name='$inputname', hours='$inputhours', rank='$inputrank', award='$inputaward', comments='$inputcomments' WHERE pilotnr='$inputpilotnr'";
my $sth = $dbh->prepare($queryroster);
$sth->execute;
$sth->finish;

# Second SQL -> This does update the table #
my $queryuserinfo = "UPDATE userinfo SET pilotnr='$inputpilotnr', name='$inputname', email='$inputemail', icq='$inputicq', country='$inputcountry', fs='$inputfs', ivao='$inputivao', vatsim='$inputvatsim' WHERE pilotnr='$inputpilotnr'";
my $sth = $dbh->prepare($queryuserinfo);
$sth->execute;
$sth->finish;

# Third SQL -> This also updates the table #
my $queryassignments = "UPDATE assignments SET pilotnr='$inputpilotnr', name='$inputname', icao='$assicao', des='$assdes', flightnr='$assfnr', comments='$inputcomments', lastflight='$date' WHERE pilotnr='$inputpilotnr'";
my $sth = $dbh->prepare($queryassignments);
$sth->execute;
$sth->finish;

# Close the SQL connection #
my $finish=$dbh->disconnect;

So the first table isn't updated with the data, all the other tables are. I can't find the error, do you find it?

 

[atlas]

You really need to check for errors. Look at the return value of the $sth->execute

$sth = $dbh->prepare($statement); 
$rv = $sth->execute; 
print "ERRROR: $DBI::errstr" if(!$rv);

Add that and see what you get.

-mk

 

[kennymoens]

I checked the error logs of my server they say the following:

DBD::mysql::st execute failed: You have an error in your SQL
syntax near 'firstofflr', award='noaward', comments='none' 
WHERE pilotnr='SAB137'' at line 1 at {script location and path} 
line 324.

note that line 324 is this line:

$sth->execute;

Can you tell me what's wrong with the SQL syntax?? This one generates the error:

my $queryroster = "UPDATE roster SET pilotnr='$inputpilotnr', 
name='$inputname', hours='$inputhours', rank='$inputrank', 
award='$inputaward', comments='$inputcomments' WHERE 
pilotnr='$inputpilotnr'";

BTW: I tried the above stuff, changed the name of the variables
to the correct name, but I always get 500 errors then, runs some
checks with my Perl debugger and nothing seems to be wrong
with my code.

Thanks.

 

[atlas]

Could you print out the select statement before it gets executed?

-mk

 

[kennymoens]

Sorry for not posting it, but i solved the problem this weekend, only I don't know anymore how i did it