2

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)

user2158583
  • 41
  • 1
  • 7
  • 1
    Possible duplicate of [SQLSTATE\[HY093\]: Invalid parameter number: parameter was not defined](https://stackoverflow.com/questions/10966251/sqlstatehy093-invalid-parameter-number-parameter-was-not-defined) – aynber Sep 13 '18 at 17:55
  • 1
    Don't wrap your placeholders in quotes, PDO/mysqli will think they're just strings instead of placeholders. – aynber Sep 13 '18 at 17:55
  • aynber ah yes, that was just one of my debug attempts.. it doesn't work without the quotes either. – user2158583 Sep 13 '18 at 18:00
  • 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 Sep 13 '18 at 18:06
  • Just wonder why do you have ":commment" only in double quotes. I believe it is the culprit – Rinsad Ahmed Sep 13 '18 at 18:06
  • Jens - A consequence of copying other examples. I did originally have everything as an array() within the execute, but had the same errors. read that I needed the PDO::PARAM_LOB on the file param, so split it out. – user2158583 Sep 13 '18 at 18:09
  • The likely problem is the lack of uniformity between :commment in the SQL, and :comment in the bind. The former has 3 'm's, whereas the later has 2. – ReverseEMF Nov 30 '22 at 00:03

3 Answers3

2

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.

Martin Zeitler
  • 1
  • 19
  • 155
  • 216
  • They're keywords, but not reserved. The OP has them in backticks, so the usage is fine. – aynber Sep 13 '18 at 18:12
  • it is still bad practice, because one must wrap them in backticks - and it might only cause confusion, when having to differ in between column name and a keyword. – Martin Zeitler Sep 13 '18 at 18:21
  • Thanks, I can adjust the field names in production (it's not too late to do this) but am trying to get functionality working first.. – user2158583 Sep 13 '18 at 18:22
0

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();
Rinsad Ahmed
  • 1,877
  • 1
  • 10
  • 28
0

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!

user2158583
  • 41
  • 1
  • 7