You must reclaim memory used by a prepared statement named prep. Which two achieve this?

Question

Options

• ASET @a = ''; EXECUTE prep USING @a;
• BDEALLOCATE PREPARE prep?
• CDROP PROCEDURE prep;
• DSET @prep = NULL;
• EDROP PREPARE prep;
• FPREPARE prep FROM '';

• B
  5% (1)
• C
  76% (16)
• E
  5% (1)
• F
  14% (3)

Explanation

Heads up: the stated correct answers (C, D) appear to be wrong. This looks like a MySQL prepared statement question, and the actual correct answers should be B and E.

In MySQL, a prepared statement is deallocated - and its memory reclaimed - using either DEALLOCATE PREPARE prep; or its synonym DROP PREPARE prep;. These are the only two commands that actually release the server-side resources tied to a named prepared statement.

Why the other options are wrong:

• A (EXECUTE prep USING @a) - executes the statement, consuming resources rather than freeing them.
• C (DROP PROCEDURE prep) - drops a stored procedure, a completely different object type. prep is a prepared statement, not a procedure.
• D (SET @prep = NULL) - nullifies a user variable called @prep, but the prepared statement object prep is unaffected and remains in memory.
• F (PREPARE prep FROM '') - attempts to re-prepare with an empty string, which causes a syntax error; it does not free memory.

Note on B: It's written with a trailing ? which is invalid syntax. Assuming that's a typo in the question, DEALLOCATE PREPARE prep; is correct.

Memory tip: Think "DEALLOCATE = free." MySQL gives you two ways to say the same thing: DEALLOCATE PREPARE and DROP PREPARE. Neither DROP PROCEDURE nor setting variables touches prepared statement memory.

No community discussion yet for this question.