How to fix CR_NO_PREPARE_STMT (2030): Statement not prepared in MySQL

#include <mysql.h>
#include <stdio.h>

MYSQL_STMT *stmt = mysql_stmt_init(mysql);
if (!stmt) {
    fprintf(stderr, "mysql_stmt_init() failed\n");
    return 1;
}

const char *query = "SELECT * FROM users WHERE id = ?";
if (mysql_stmt_prepare(stmt, query, strlen(query))) {
    // Prepare failed - do NOT proceed with execute/fetch
    fprintf(stderr, "mysql_stmt_prepare() failed: %s\n", 
            mysql_stmt_error(stmt));
    mysql_stmt_close(stmt);
    return 1;
}

// Only proceed if prepare succeeded
// Bind parameters, execute, fetch results...

// These commands will FAIL with prepared statements:
const char *unsupported[] = {
    "SHOW CREATE TABLE users",
    "LOCK TABLES users WRITE",
    "START TRANSACTION",
    "COMMIT",
    "ROLLBACK",
    "SET @variable = 1",
    "USE database_name"
};

// Instead, use regular mysql_query() for these:
if (mysql_query(mysql, "SHOW CREATE TABLE users")) {
    fprintf(stderr, "Query failed: %s\n", mysql_error(mysql));
}

// Use prepared statements ONLY for DML operations:
const char *supported[] = {
    "SELECT * FROM users WHERE id = ?",
    "INSERT INTO users (name, email) VALUES (?, ?)",
    "UPDATE users SET name = ? WHERE id = ?",
    "DELETE FROM users WHERE id = ?"
};

// WRONG: Reusing closed statement
MYSQL_STMT *stmt = mysql_stmt_init(mysql);
mysql_stmt_prepare(stmt, query1, strlen(query1));
mysql_stmt_execute(stmt);
mysql_stmt_close(stmt);  // Statement is now invalid

mysql_stmt_prepare(stmt, query2, strlen(query2));  // ERROR: stmt is closed!

// CORRECT: Create new statement handler
MYSQL_STMT *stmt1 = mysql_stmt_init(mysql);
mysql_stmt_prepare(stmt1, query1, strlen(query1));
mysql_stmt_execute(stmt1);
mysql_stmt_close(stmt1);

MYSQL_STMT *stmt2 = mysql_stmt_init(mysql);  // New handler
mysql_stmt_prepare(stmt2, query2, strlen(query2));
mysql_stmt_execute(stmt2);
mysql_stmt_close(stmt2);

// Alternative: Reuse by reinitializing
stmt = mysql_stmt_init(mysql);
mysql_stmt_prepare(stmt, query3, strlen(query3));
mysql_stmt_execute(stmt);
mysql_stmt_close(stmt);

// WRONG: Not fetching all results
MYSQL_STMT *stmt1 = mysql_stmt_init(mysql);
mysql_stmt_prepare(stmt1, "SELECT * FROM large_table", 27);
mysql_stmt_execute(stmt1);
// Fetch only first row, ignore the rest
mysql_stmt_fetch(stmt1);

// Now try to use another statement - may cause issues
MYSQL_STMT *stmt2 = mysql_stmt_init(mysql);
mysql_stmt_prepare(stmt2, "SELECT * FROM other_table", 26);  // May error

// CORRECT: Fetch all results or free them
MYSQL_STMT *stmt1 = mysql_stmt_init(mysql);
mysql_stmt_prepare(stmt1, "SELECT * FROM large_table", 27);
mysql_stmt_execute(stmt1);

// Option 1: Fetch all rows
while (mysql_stmt_fetch(stmt1) == 0) {
    // Process each row
}

// Option 2: Free result set without fetching
mysql_stmt_free_result(stmt1);

mysql_stmt_close(stmt1);

// Now safe to use another statement
MYSQL_STMT *stmt2 = mysql_stmt_init(mysql);
mysql_stmt_prepare(stmt2, "SELECT * FROM other_table", 26);

// WRONG: Overlapping statements on same connection
MYSQL *mysql = mysql_init(NULL);
mysql_real_connect(mysql, host, user, pass, db, 0, NULL, 0);

MYSQL_STMT *stmt1 = mysql_stmt_init(mysql);
MYSQL_STMT *stmt2 = mysql_stmt_init(mysql);  // Same connection!

mysql_stmt_prepare(stmt1, query1, strlen(query1));
mysql_stmt_execute(stmt1);

mysql_stmt_prepare(stmt2, query2, strlen(query2));  // May conflict

// CORRECT: Use separate connections or finish stmt1 first
// Option 1: Separate connections
MYSQL *mysql1 = mysql_init(NULL);
MYSQL *mysql2 = mysql_init(NULL);
mysql_real_connect(mysql1, host, user, pass, db, 0, NULL, 0);
mysql_real_connect(mysql2, host, user, pass, db, 0, NULL, 0);

MYSQL_STMT *stmt1 = mysql_stmt_init(mysql1);
MYSQL_STMT *stmt2 = mysql_stmt_init(mysql2);

// Now safe to use concurrently
mysql_stmt_prepare(stmt1, query1, strlen(query1));
mysql_stmt_prepare(stmt2, query2, strlen(query2));

// Option 2: Complete stmt1 lifecycle before starting stmt2
mysql_stmt_prepare(stmt1, query1, strlen(query1));
mysql_stmt_execute(stmt1);
mysql_stmt_free_result(stmt1);
mysql_stmt_close(stmt1);

// Now safe to use stmt2 on same connection
mysql_stmt_prepare(stmt2, query2, strlen(query2));

typedef struct {
    MYSQL_STMT *stmt;
    int is_prepared;
    int is_executed;
} SafeStatement;

SafeStatement* safe_stmt_create(MYSQL *mysql) {
    SafeStatement *safe = malloc(sizeof(SafeStatement));
    safe->stmt = mysql_stmt_init(mysql);
    safe->is_prepared = 0;
    safe->is_executed = 0;
    return safe;
}

int safe_stmt_prepare(SafeStatement *safe, const char *query, size_t length) {
    if (mysql_stmt_prepare(safe->stmt, query, length)) {
        fprintf(stderr, "Prepare failed: %s\n", mysql_stmt_error(safe->stmt));
        return 1;
    }
    safe->is_prepared = 1;
    return 0;
}

int safe_stmt_execute(SafeStatement *safe) {
    if (!safe->is_prepared) {
        fprintf(stderr, "Cannot execute unprepared statement\n");
        return 1;
    }
    if (mysql_stmt_execute(safe->stmt)) {
        fprintf(stderr, "Execute failed: %s\n", mysql_stmt_error(safe->stmt));
        return 1;
    }
    safe->is_executed = 1;
    return 0;
}

void safe_stmt_close(SafeStatement *safe) {
    if (safe->stmt) {
        mysql_stmt_close(safe->stmt);
        safe->stmt = NULL;
    }
    free(safe);
}

// Usage:
SafeStatement *stmt = safe_stmt_create(mysql);
if (safe_stmt_prepare(stmt, query, strlen(query)) == 0) {
    if (safe_stmt_execute(stmt) == 0) {
        // Process results
    }
}
safe_stmt_close(stmt);