PHP PDO 'SQLSTATE[HY093]: Invalid parameter number: parameter was not defined

2
votes

I've been fighting this one for a couple of days now ...

Just after connection to db - 

    $this->sqladdimport = $this->db->prepare('INSERT INTO `importqueue`
                    (`user_id`, `service_id`, `format`, `comment`, `data`, `status`)
                VALUES (:user, :service, :format, ":commment", :file, :status)',
                 array(PDO::ATTR_EMULATE_PREPARES=>true));

Called by -

function addImportQueue($filename, $user, $format, $service_id, $comment, $status = IMPORT_NEW ){
    $fh = fopen($filename, 'r');
    if (!$fh) {
        return false;
    }

    $params = array(    ':user' => $user['user_id'],
                        ':format' => $format,
                        ':service' => $service_id,
                        ':comment' => $comment,
                        ':status' => $status) ;
    $this->sqladdimport->bindParam(':file', $fh, PDO::PARAM_LOB);
    foreach ($params as $key => &$val){
        $this->sqladdimport->bindParam($key,$val);
    }

    $this->sqladdimport->execute();
    $id = $this->db->lastInsertId();

    return $id;
}

But I just get

PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY093]: Invalid parameter number: parameter was not defined'

on the execute line.

In case it's relevant, the table is defined thus:

CREATE TABLE `importqueue` (
`import_id` INT(11) NOT NULL AUTO_INCREMENT,
`service_id` INT(11) NOT NULL DEFAULT '0',
`format` INT(11) NULL DEFAULT '0',
`user_id` INT(11) NULL DEFAULT '0',
`comment` TEXT NULL,
`date` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
`data` MEDIUMBLOB NULL,
`status` INT(11) NULL DEFAULT NULL,
INDEX `Index 1` (`import_id`)
)
COMMENT='Import queue/archive'
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
;

I've fairly new to PDO, and especially BLOBs, so I'm probably doing something really obviously stupid, but it's currently escaping me!

Thanks!

Edit: I note the references to the quotes around :comment in the prepare - this was an artefact of my debugging, and I have removed those again with no change.

Have also simplified the function to 

    $fh = fopen($filename, 'r');
    $this->sqladdimport->bindValue(':file', $fh, PDO::PARAM_LOB);
    $this->sqladdimport->bindValue(':user', $user['user_id']);
    $this->sqladdimport->bindValue(':format', $format);
    $this->sqladdimport->bindValue(':service', $service_id);
    $this->sqladdimport->bindValue(':comment', $comment);
    $this->sqladdimport->bindValue(':status', $status);
    $this->sqladdimport->execute();

and I still get the same error (using either bindParam or bindValue)

3
phppdo
Possible duplicate of SQLSTATE[HY093]: Invalid parameter number: parameter was not defined - aynber
Don't wrap your placeholders in quotes, PDO/mysqli will think they're just strings instead of placeholders. - aynber
aynber ah yes, that was just one of my debug attempts.. it doesn't work without the quotes either. - user2158583
Why you define an array of bind parameters and after this you bin them in a loop? Why you do not bind them directly? - Jens
Just wonder why do you have ":commment" only in double quotes. I believe it is the culprit - Rinsad Ahmed

3 Answers

1
votes

see Keywords and Reserved Words ...

you may want to rename columns comment, date, format and status.

the Invalid parameter number comes from ":comment" not being substituted, therefore the number of columns does not match the number of parameters, less one. in order to provide the prepared statement with the correct number of parameters, it should be :comment instead.

0
votes

You have only the ":commment" placeholder in double quotes. Hence it will be treated as a string

Also if you are trying to store a binary file use

$fh = fopen($filename, 'rb');

instead of

$fh = fopen($filename, 'r');

Also, try to define the datatype for all the columns because bindValue method has a 3rd column which defaults to PDO::PARAM_STR. But you have numerical columns too

$fh = fopen($filename, 'rb');
$this->sqladdimport->bindValue(':file', $fh, PDO::PARAM_LOB);
$this->sqladdimport->bindValue(':user', $user['user_id'],PDO::PARAM_INT);
$this->sqladdimport->bindValue(':format', $format,PDO::PARAM_INT);
$this->sqladdimport->bindValue(':service', $service_id,PDO::PARAM_INT);
$this->sqladdimport->bindValue(':comment', $comment,PDO::PARAM_STR);
$this->sqladdimport->bindValue(':status', $status,PDO::PARAM_INT);
$this->sqladdimport->execute();
0
votes

Found it ....

I copied the code into a test script that I could more easily modify. Started with just the one field (which worked) and gradually added the rest one by one.

The problem turned out to be a simple typo in the prepare command... That referred to :commment. The bind was referring to :comment. I never noticed the extra 'm' ..

Thanks everybody for all the suggestions. It's been a learning experience!