MySQL Query error - no data supplied for parameters in prepared statement

Question

I'm relatively new to MySQL, and I'm trying to combine my insert and update statements like I saw elsewhere. For the following code,

$query = "this is not the problem";
$ora_stmt = oci_parse($conn, $query);
$result = oci_execute($ora_stmt);
$stmt1 = $mysqli1->prepare("INSERT INTO na_dslam_card (n_alias, shelf_pt_num, card_pt_num, card_pt_description)
                            VALUES (?, ?, ?, ?)
                            ON DUPLICATE KEY UPDATE n_alias=?, shelf_pt_num=?, card_pt_num=?, card_pt_description=?");

while($row = oci_fetch_array($ora_stmt, OCI_ASSOC+OCI_RETURN_NULLS)) {
    print  $row['DSLAM'] . "," . $row['MODEL']  . "," . $row['VENDOR'] . "," . $row['BROADBAND_CIRCUIT_ID'] . "," . $row['SVC_N'] . "," . $row['PORT_ADDR_STATUS']. "," . $row['BROADBAND_CIRCUIT_ID'] . "," . $row['RACK'] . "," . $row['SHELF'] . "," .  $row['SLOT'] . "," . $row['PORT'] . "," . $row['SHELF_PT_NUMBER'] . "," .  $row['CARD_PT_NUMBER'] . "," . $row['CARD_PT_DESCRIPTION'] . "\n";
    $stmt1->bind_param("ssss", $row['DSLAM'],$row['SHELF_PT_NUMBER'],$row['CARD_PT_NUMBER'],$row['CARD_PT_DESCRIPTION']); 
    if(!$stmt1->execute())
    { 
      $tempErr = "Error setting card info: " . $stmt1->error;
      printf($tempErr . "\n");  //show mysql execute error if exists  
      $err->logThis($tempErr);
    } 
} //while

I get this error message:

Error setting card info: No data supplied for parameters in prepared statement

I used bind_parameters, so it shouldn't say I didn't supply the parameter data. I know rack can be null sometimes, but wouldn't it just set it to null in that case? I know the oracle query works because the print statement shows this:

CNVLDAT00,TA12,Ad,196,641,In Service,196,,2,,7,117,115,11L5

So why are the parameters not supplied correctly? It looks ok to me.

I looked at insert values and I think I'm doing that ok.

My table looks like this:

na_dslam_card: index n_alias shelf_pt_num card_pt_num card_pt_description

Thanks for any help...

spencer7593 · Accepted Answer · 2018-05-25T16:01:48.770

There are eight bind placeholders in the SQL text. We would need to supply eight values.

The bind_param is only supplying four values.

But rather than supply eight values, we can modify the SQL text to replace the placeholders in the UPDATE clause with the VALUES() function, to reference the value that would have been inserted into the column.

INSERT INTO na_dslam_card
( n_alias
, shelf_pt_num
, card_pt_num
, card_pt_description
)  
VALUES (?, ?, ?, ?)
ON DUPLICATE KEY
UPDATE n_alias             = VALUES(alias)
     , shelf_pt_num        = VALUES(shelf_pt_num)
     , card_pt_num         = VALUES(card_pt_num)
     , card_pt_description = VALUES(card_pt_description)

Now there are only four placeholders that need values.

Excerpt from MySQL Reference Manual:

In assignment value expressions in the ON DUPLICATE KEY UPDATE clause, you can use the VALUES(col_name) function to refer to column values from the INSERT portion of the INSERT ... ON DUPLICATE KEY UPDATE statement. In other words, VALUES(col_name) in the ON DUPLICATE KEY UPDATE clause refers to the value of col_name that would be inserted, had no duplicate-key conflict occurred.

References:

https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html

https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_values

So it looks funny for me that the UPDATE n_alias = VALUES(alias) , shelf_pt_num = VALUES(shelf_pt_num) , card_pt_num = VALUES(card_pt_num) , card_pt_description = VALUES(card_pt_description) has the same variable on both sides of the equals sign. Is that right? The excerpt does say col_name, so I guess it's right. — Michele, May 25 '18 at 17:08
That's not a variable inside the `VALUES()` function... it's a reference to a column. It references the value that would have been inserted into the column. If any of these columns are part of the primary key or unique key, we probably don't need to update that. For example, if n_alias is the PRIMARY KEY on the table, we could omit the assignment to n_alias in the UPDATE clause. — spencer7593, May 25 '18 at 20:12

MySQL Query error - no data supplied for parameters in prepared statement

1 Answers1