0

Performing upgrades and having trouble getting this statement to execute. I know it is failing on the execute() but unfortunately the logs aren't much help in describing exactly what the issue is.

The Stored Procedure: (sidenote: this stored procedure has been used for a long time and works, the issue is it used to be called from a Flask framework, not PHP)

CREATE DEFINER=`physics_user`@`localhost` PROCEDURE `sp_add_lab`(
  IN p_type VARCHAR(10),
  IN p_name VARCHAR(60),
  IN p_topic VARCHAR(30),
  IN p_concept VARCHAR(30),
  IN p_subconcept VARCHAR(30),
  IN p_lab_id INT(36)
)
BEGIN
  IF (p_lab_id is NULL) THEN
  REPLACE into lab_demo (
      type,
      name,
      topic,
      concept,
      subconcept
    ) values (
      p_type,
      p_name,
      p_topic,
      p_concept,
      p_subconcept
    );
    SELECT LAST_INSERT_ID();
  ELSE

    DELETE from lab_demo where lab_id=p_lab_id;
    REPLACE into lab_demo (
      lab_id,
      type,
      name,
      topic,
      concept,
      subconcept
    ) values (
      p_lab_id,
      p_type,
      p_name,
      p_topic,
      p_concept,
      p_subconcept
    );
    SELECT p_lab_id;
    END IF;
  commit;

END

The following are the relevant snippets from the PHP file:

$dbh = ConnectDB(); //standard connection function used elsewhere and known to work
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$name = $_POST['lab_name'];
$topic = $_POST['lab_topic'];
$concept = $_POST['lab_concept'];
$subconcept = None;
$lab_id = None;
$input_type = 'LAB';
$stmt = $dbh->prepare("CALL sp_add_lab(?,?,?,?,?,?)");
$stmt->bindParam(1, $input_type, PDO::PARAM_STR);
$stmt->bindParam(2, $name, PDO::PARAM_STR);
$stmt->bindParam(3, $topic, PDO::PARAM_STR);
$stmt->bindParam(4, $concept, PDO::PARAM_STR);
$stmt->bindParam(5, $subconcept, PDO::PARAM_STR);
$stmt->bindParam(6, $lab_id, PDO::PARAM_INT);
$stmt->execute();
...

If anyone can offer any insight it would be greatly appreciated.

chris
  • 25
  • 1
  • 5
  • 1
    first of all, [loop over all result sets](https://phpdelusions.net/pdo#call) – Your Common Sense Sep 18 '20 at 09:19
  • @YourCommonSense I appreciate the link and it will come in handy in the future. But this doesn't have anything to do with what is causing the error, right? I have confirmed that nothing past the execute() statement is running as an echo prints nothing. – chris Sep 18 '20 at 15:02
  • 1
    then you have to access a php error – Your Common Sense Sep 18 '20 at 15:04
  • @YourCommonSense Thanks a lot. I should have realized this was a PHP error not a MySQL error. Once I enabled viewing errors I was able to see what the issue was and fix it. (I was unknowingly calling the wrong database...) – chris Sep 18 '20 at 15:55

0 Answers0