[RESOLVED] help with perl/sql syntax

[yssirhc]

I keep getting different error messages after I try to fix this. It's driving me mad! Does anyone know the proper way to write this? When I try it this way, it seems to give messages about quotes being wrong.

$sql = "INSERT into Bugs VALUES (
. NULL
. $dbhandle->quote($formHash{"problem"})
. $dbhandle->quote($formHash{"product"})
. $dbhandle->quote($formHash{"version"})
. $dbhandle->quote($formHash{"type"})
. $dbhandle->quote($formHash{"severity"})
. $dbhandle->quote($formHash{"description"})
. $dbhandle->quote($formHash{"user"})
. $dbhandle->quote($formHash{"submittedDate"})
. $dbhandle->quote($formHash{"status"})
. NULL
. )";

This code:

$sql = "INSERT into Bugs VALUES ("
. $dbhandle->quote($formHash{"problem"})
. $dbhandle->quote($formHash{"product"})
. $dbhandle->quote($formHash{"version"})
. $dbhandle->quote($formHash{"type"})
. $dbhandle->quote($formHash{"severity"})
. $dbhandle->quote($formHash{"description"})
. $dbhandle->quote($formHash{"user"})
. $dbhandle->quote($formHash{"submittedDate"})
. $dbhandle->quote($formHash{"status"})
. ")";

seemed to work, except for the fact that it didn't have enough parameters for the table that it's putting the info in. which is why I'm adding the null values.

[winracer]

I could be wrong but I thought it was something like this. I has been awhile


$sql = "INSERT into Bugs VALUES ('NULL',
'$dbhandle->quote($formHash{"problem"})',
'$dbhandle->quote($formHash{"product"})',
'NULL')";

[Nedals]

As winracer points out, you have missed the commas seperating your data.. I'm supprised you second example seemed to work

In addition, it is ALWAYS better to define the columns you are inserting/updating.
That way, if ever you ALTER your table structure, you will not need to fix your sql statements.
AND it is ALWAYS better to use placeholders to avoid SQL injection


(assumed column names)
$sql = "INSERT INTO Bugs (problem,product,version,type,severity,description,user,submittedDate,status) VALUES (?,?,?,?,?,?,?,?,?)";
$dbh->do($sql, {}, $formHash{problem},$formHash{product},$formHash{version},$formHash{type},$formHash{severity},$formHa sh{description},$formHash{user},$formHash{submittedDate},$formHash{status});

[yssirhc]

Thanks. I'm now getting the error "Data type mismatch in criteria expression." when I run this. I tried adding quotes around each of the $formHash{} and adding quotes just within the {}, neither worked.

$sql = "INSERT into Bugs (ID, Problem, Product, Version, Type, Severity, Description, User, SubmittedDate, Status, FixedDate) VALUES (?,?,?,?,?,?,?,?,?,?,?)";
$query = $dbhandle->prepare($sql) or die "Couldn't prepare statement: " . $dbhandle->errstr;
$query->execute('', $formHash{problem}, $formHash{product}, $formHash{version}, $formHash{type}, $formHash{severity}, $formHash{description}, $formHash{user}, $formHash{submittedDate}, $formHash{status}, 'NULL') or die "Couldn't execute statement: " . $query->errstr;

I'm using an Access database, and the ID is the primary key and also an autonumber that Access is supposed to supply.

oh, and when I fixed the old way I was doing this, I was getting an error about ODBC not supporting multiple binding parameters.

but, could this be a database problem rather than a code problem? do I need to change something with how the database is set up?

[Nedals]

Just a guess. You are trying to set ID = '' but you say you use 'auto increment'
Try...

$sql = "INSERT into Bugs (Problem, ....);
$query = $dbhandle->prepare($sql)...;
$query->execute($formHash{problem},....)

ie: leave out the ID. It will be automatically set

ALSO
Add error checking to your database connect statement.
my $dbhandle = DBI->connect("DBI:mysql:$conn",$userid,$pwd,{RaiseError=>1,PrintError=>0});
That way you don't have to include the error checking in each statement
:)

[yssirhc]

yes, that was the problem - the ID! :D

thanks! :)