Re: Cannot display mysqli_error($con) ???

[Arthur Chan]

Peter, I took the embedded if-else out and my Java app displays the error message in the PHP code correctly, but not the error message from the sproc.
The induced error is an fk-constraint violation, I put in an $aA which is not in TableA.

The new PHP if-else:

if ($numA==1 && $numB==1){
    $newC= "call sp_intoTableC ('$aA', '$aB','$c3po')";
    $rNewC = mysqli_query($con,$newC);
}
if ($rNewC) {
        printf("\"".$numA."\"". " and ". "\"".$numB."\"". " have been posted.");
} else { die('Insert failed '. mysqli_error($con)); }  // INDUCE ERROR: THIS NOW DISPLAYs CORRECTLY

If you run the following mini-script in MySQL-Workbench, then call the sproc, you will see it works in Workbench.

DROP TABLE IF EXISTS TableTest;
DROP TABLE IF EXISTS TableA;
CREATE TABLE TableA( A_ID INT UNSIGNED NOT NULL
	, CONSTRAINT pkTableA PRIMARY KEY(A_ID)
)    ENGINE=INNODB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
INSERT INTO TableA(A_ID) VALUES (1),(2),(3);
;
CREATE TABLE TableTest( A_ID INT UNSIGNED NOT NULL
	, B_ID INT UNSIGNED NOT NULL
	, T_Name VARCHAR(50)
	, CONSTRAINT pkTest PRIMARY KEY(A_ID, B_ID)
	, CONSTRAINT fkTableA FOREIGN KEY(A_ID) REFERENCES TableA(A_ID)
)    ENGINE=INNODB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
;
DELIMITER //
DROP PROCEDURE IF EXISTS sp_tableTest;
CREATE PROCEDURE `sp_tableTest`(in aID int unsigned, in bID int unsigned, in tName varchar(50))
BEGIN
DECLARE c INT;
START TRANSACTION;
SELECT COUNT(A_ID) INTO c FROM TableA WHERE A_ID=aID;
-- Check if A_ID exists
IF(c != 1) THEN SIGNAL SQLSTATE '45000'
	SET MESSAGE_TEXT = 'A_ID Not Found in TableA!';
END IF;
	INSERT INTO TableTest(A_ID,B_ID,T_Name) VALUES (aID,bID,tName); 
COMMIT;
END //
delimiter ;

-- These calls work correctly
call sp_tableTest(1,1,'Hey Diddle Diddle');
call sp_tableTest(1,2,'The Cat and the Fiddle');

select * from TableTest;

--INDUCED ERROR b/c 999 DOES NOT EXIST and Workbench console correctly reports 'A_ID Not Found in TableA!'
call sp_tableTest(999,3,'The Cow Jumped over the Moon'); 


QUESTION: How do I get the error message from the sproc to my Java app.

REASON: If I can use the error handling mechanism in the sproc, I can drop the error handling in PHP, saving me 2 trips from PHP to the mySQL-server