Problem Closing Connection When Using Prepared Statement

Question

I'm converting some code to use prepared statements as is suggested when using mysqli. The problem comes when I try to close the connection ($con). No matter what I try, I get an error if I first close the prepared statement ($sql).

The error is:

Exception has occurred. Warning: AdminDb::test(): Couldn't fetch mysqli_stmt

My guess is that the $sql_>close() is also closing the connection as all of $con's properties turn to null after the statement close. But everywhere I look I see example code closing the statement, then closing the connection. I've tried closing both within the try block, pre-declaring $con and $sql, etc, all to no avail.

If I skip the statement close, the connection close works fine.

Here is a test function demonstrating the problem.

public function test(): bool {

    try {
        $con = new mysqli(self::$serverName, self::$userName, self::$password, self::$dbName, self::$port);
        if ($con->connect_errno) throw new DatabaseException("Database connection failed: ($con->connect_errno) $con->connect_error");
        $sql = $con->prepare("
            SELECT `Id`
            FROM   `provider`
            WHERE  `HeaderText` = ?");
        $sql->bind_param('s', $headerText);
        $sql->execute();
        $sql->store_result();
        $sql->free_result();
        return TRUE;
    } catch (\Throwable $e) {
        throw $e;
    } finally {
        if (isset($sql)) $sql->close();
        if (isset($con)) $con->close();
    }
}

@YourCommonSense I think you're being a bit overzealous in closing this question as a duplicate; the question you say this is answered in isn't the same. The other is asking about practice and I'm highlighting an error. — SteveCinq, Jun 10 '20 at 16:19
Fair. I changed the dupe and adding the link with a better practice into comments instead https://stackoverflow.com/questions/61323391/closing-prepared-statements — Your Common Sense, Jun 10 '20 at 16:21
@YourCommonSense Nope; still not the same issue. Refer my edited answer below. — SteveCinq, Jun 10 '20 at 16:30
The error message and the conditions are the same, so I assume it's a duplicated question. You may add your answer to the other question, it will draw more attention there. this is how Stack Overflow is intended to work. — Your Common Sense, Jun 10 '20 at 16:42

SteveCinq · Answer 1 · 2020-06-10T16:26:24.643

0

Well, I think this is actually a problem somewhere with Visual Studio Code, PHP Debug, PHP Server and/or XDebug because if I remove all function breakpoints, all is well.

I ended up twigging because I was seeing errors on innocuous statements such as return TRUE;.

Bizarre, but something to watch out for.

UPDATE

Visual Studio Code Debug come with a default Breakpoints setting of "Everything" (bottom left of the Debug pane). This seems to throw all errors and warnings rather than letting your code handle them.

Untick this and reload and you should be ok. I lost a few hours on this.

edited Jun 10 '20 at 16:26

answered Jun 10 '20 at 04:35

SteveCinq

1,920
1
17
22

what is *really* bizarre is that you are connecting every time a query is executed, instead of having **one single connection** for all queries executed in your code. Which as a side effect would make closing a connection simply pointless at all. – Your Common Sense Jun 10 '20 at 04:38
closing a statement is also pointless as well, as it will be closed automatically the moment the function execution ends. which makes the whole business with try/catch/finally also pointless. You can really make your code two times less WET and more meaningful – Your Common Sense Jun 10 '20 at 04:49
@YourCommonSense Thanks for your opinion but open late/close early is actually a "thing" with DB connections. Not that that has anything to do with the question as the example code is merely to demonstrate the problem. – SteveCinq Jun 10 '20 at 16:21

Problem Closing Connection When Using Prepared Statement

1 Answers1