I have a problem when calling execute on a prepared statement using PDO.
Here is my code:
$db = Connection::getConnection();
$query = 'INSERT INTO stories (st_title, st_authors, st_content, st_cover) VALUES (?,?,?,?)';
$params = ['title', 'Authors', 'content', 'cover'];
$stmt = $db->prepare($query);
echo "before <br>";
$stmt->execute($params);
echo "after <br>";
If I comment out the line $stmt->execute($params);, both "before" and "after" show up. However, if I leave it uncommented, neither of them show up.
I have also tried using bindParam() instead of executing with an array, but I got the same result. It's also worth taking note that inserting into the database, with the exact same parameters, from sqlPlus works flawlessly.
I am running an oracle 11g database server locally.
Any help would be highly appreciated, I've been stuck on this for a while now.
Edit: I mentioned it in the title, but forgot to mention it here too. When this code runs, the server hangs indefinetely and I am forced to restart apache.
Edit2: The connection is ok, and I figured out the source of the problem. On the stories table, i have an unique constraint on st_title. The problem occurs when i try to insert a story with the same title as a previous one AFTER that previous one has been DELETED. If I remove the unique constraint, the problem goes away.
However I would like to keep that constraint. Any ideas as to what could cause this?
Edit3: The triggers I have on the stories table:
--auto-increment on st_id--
DROP SEQUENCE stories_id_seq;
CREATE SEQUENCE stories_id_seq;
CREATE OR REPLACE TRIGGER stories_id_auto_inc
BEFORE INSERT ON stories
FOR EACH ROW
BEGIN
SELECT stories_id_seq.NEXTVAL
INTO :new.st_id
FROM dual;
END;
/
--auto-completion for st_date_added--
CREATE OR REPLACE TRIGGER stories_date_added
BEFORE INSERT ON stories
FOR EACH ROW
BEGIN
SELECT sysdate
INTO :new.st_date_added
FROM dual;
END;
/
CREATE OR REPLACE TRIGGER stories_delete
BEFORE DELETE
ON stories
FOR EACH ROW
DECLARE
BEGIN
DELETE FROM st_cat
WHERE st_id = :old.st_id;
-- DELETE FROM characters
-- WHERE st_id = :old.st_id;
DELETE FROM favourites
WHERE st_id = :old.st_id;
DELETE FROM bookmarks
WHERE st_id = :old.st_id;
DELETE FROM comments
WHERE st_id = :old.st_id;
DELETE FROM ratings
WHERE st_id = :old.st_id;
END;
/
The last one is the only relevant one, I think, but i posted them all, just in case.
It's worth noting that the problem only occurs with PDO. If i do the same operations from sqlplus, it works fine.
SOLVED: As it turns out, it wasn't a problem with my code, it was simply the fact that I didn't do a commit; in sqlplus after deleting the rows, and PDO dis weird things with them. (It didn't just show me the normal "title is already taken" message, which is what fooled me).
