0

When I do an INSERT directly into clinical_descriptions.description from the PHP, it works with more than 673 characters. Example-

    $sql = "INSERT INTO clinical_descriptions (description) VALUES ('Encididunt ut labore et dolore magna aliqua. Ut enim ad minim veniamquisno strudexercitation ullamco laboris Ampor incididunt ut labore et dolore magna aliqua... ')";

When I try to insert the text for the description by calling a stored procedure, it works fine until the text exceeds 673 characters, then it won't insert anything. The description column is blank. I don't get an error message. All the other sql statements in the stored procedure work.

Here is the stored procedure-

    CREATE PROCEDURE setPageAddObjectives(
           IN classID INT,
           IN objective_text,
           IN page CHAR(20),
           IN pbin INT,
           IN descript TEXT
    )

    BEGIN
    DECLARE pagetest CHAR(25);
    DECLARE pageName CHAR(25);
    DECLARE pageID INT;
    DECLARE lastObID INT;
    DECLARE descriptID INT;
    DECLARE q CHAR(255);
    DECLARE qt CHAR(255);
    DECLARE qd CHAR(255);
    DECLARE qdi CHAR(255);
    DECLARE first_letter CHAR(5);
    SET first_letter = LEFT(page, 1);
    SET pagetest = page; 

    If pbin = 0 THEN
      SET pageID = (SELECT page_id FROM pages WHERE page_name LIKE CONCAT("%", page, "%"));
      
      INSERT INTO page_info (class_id, page_id) VALUES (classID, pageID);

      SET qd = CONCAT("INSERT INTO ", page, "_descriptions (description) VALUES ('", descript, "')");
      PREPARE stmt1 FROM qd;
      EXECUTE stmt1;
      DEALLOCATE PREPARE stmt1;

      SET descriptID = LAST_INSERT_ID();

      SET qdi = CONCAT("INSERT INTO ", page, "_description_info (", first_letter, "d_id, class_id) VALUES (", descriptID, ", ", classID, ")" );
      PREPARE stmt2 FROM qdi;
      EXECUTE stmt2;
      DEALLOCATE PREPARE stmt2;
    END IF;

    SET q = CONCAT("INSERT INTO sfclasses.", page, "_objectives (objective) VALUES ('", objective_text, "')");
    PREPARE stmt FROM q;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    SET lastObID = LAST_INSERT_ID();

    SET qt = CONCAT("INSERT INTO ", page, "_objective_info (class_id, ", first_letter, "o_id)         VALUES(", classID, ",", lastObID, ")");
    PREPARE stmtt FROM qt;
    EXECUTE stmtt;
    DEALLOCATE PREPARE stmtt;
    END

Here is the PHP that calls the stored procedure-

    $sql = "CALL setPageAddObjectives(:id, :objective, :page, :pbin, :description)";
    $stmt = $pdo->prepare($sql);
    $stmt->bindValue(":id", $classID, PDO::PARAM_STR);
    $stmt->bindValue(":objective", $objective, PDO::PARAM_STR);
    $stmt->bindValue(":page", $page, PDO::PARAM_STR);
    $stmt->bindValue(":pbin", $bin, PDO::PARAM_INT); 
    $stmt->bindValue(":description", $description, PDO::PARAM_STR);
    $stmt->execute();
    
Mark
  • 1
  • 1
  • 1
    You declared `qd CHAR(255);` and then set a text with 673 characters to it. You should actually get an error message for this, if you don't, check your [sql mode](https://stackoverflow.com/q/18459184) and check if you just didn't [check for errors in php](https://stackoverflow.com/q/22662488). Also be aware that if your description contains a `'`, it will break your query (actually without error this time). – Solarflare Jul 18 '20 at 07:00
  • That did it. Thanks so much! It seems so obvious now. As for the single quote, I can't get it to work without them wrapped around the text variables. – Mark Jul 18 '20 at 12:09
  • 1
    Haha, yes, forest and trees. But what I meant with the `'` is: if you want to add e.g. a description like `That's a nice book!`, you may use `call setPageAddObjectives(..., 10, 'That''s a nice book!');`. This will fail. Without throwing an error. [Be aware of that](https://stackoverflow.com/q/332365). And [parametrize your code](https://stackoverflow.com/q/5633195) (which would actually also fix your original problem). – Solarflare Jul 18 '20 at 12:38

0 Answers0