Joomla 4组件迁移：数据库存储过程代码改写求助

错误原因 #

Joomla 4 默认使用预编译语句（prepared statements）执行SQL，但MySQL的预编译协议不支持CREATE PROCEDURE、DROP PROCEDURE这类DDL命令，因此触发了This command is not supported in the prepared statement protocol yet错误。解决方案是临时关闭预编译语句来执行存储过程相关操作。

改写后的适配代码 #

// 获取数据库连接（Joomla 4 推荐使用命名空间方式）
$db = \Joomla\CMS\Factory::getDbo();

// 保存当前预编译状态，执行完后恢复
$oldPrepareState = $db->getOption(\PDO::ATTR_EMULATE_PREPARES);
// 临时关闭预编译语句，适配存储过程操作
$db->setOption(\PDO::ATTR_EMULATE_PREPARES, false);

try {
    // 删除已存在的存储过程
    $dropQuery = "DROP PROCEDURE IF EXISTS " . $db->qn('redSHOP_Column_Update');
    $db->setQuery($dropQuery)->execute();

    // 提取重复变量，简化SQL构建
    $dbPrefix = \Joomla\CMS\Factory::getConfig()->get('dbprefix');
    $procComment = $db->quote('Procedure for use in redSHOP to update / add column to table avoid unexpected errors.');
    $hashPlaceholder = $db->quote('#__');
    $prefixedHash = $db->quote($dbPrefix);

    // 构建创建存储过程的SQL
    $createQuery = "CREATE PROCEDURE " . $db->qn("redSHOP_Column_Update") . "(
        IN " . $db->qn('tableName') . " VARCHAR(50),
        IN " . $db->qn('columnName') . " VARCHAR(50),
        IN " . $db->qn('newColumnName') . " VARCHAR(50),
        IN " . $db->qn('columnDetail') . " VARCHAR(255)
        )
        LANGUAGE SQL
        NOT DETERMINISTIC
        CONTAINS SQL
        COMMENT {$procComment}
        BEGIN
            SET tableName = REPLACE(tableName, {$hashPlaceholder}, {$prefixedHash});
            SET columnDetail = REPLACE(columnDetail, {$hashPlaceholder}, {$prefixedHash});
            
            SET @ColOldExist = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE COLUMN_NAME=columnName AND TABLE_NAME=tableName AND table_schema = DATABASE());
            SET @ColNewExist = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE COLUMN_NAME=newColumnName AND TABLE_NAME=tableName AND table_schema = DATABASE());
            
            IF (@ColOldExist = 0 AND @ColNewExist = 0) THEN
                /* 新旧列都不存在，直接添加新列 */
                SET @StatementToExecute = CONCAT('ALTER TABLE `', DATABASE(), '`.`', tableName, '` ADD COLUMN `', newColumnName, '` ', columnDetail);
                PREPARE DynamicStatement FROM @StatementToExecute;
                EXECUTE DynamicStatement;
                DEALLOCATE PREPARE DynamicStatement;
            ELSEIF (@ColOldExist = 1 AND @ColNewExist = 0) THEN
                /* 旧列存在，新列不存在，修改列名与结构 */
                SET @StatementToExecute = CONCAT('ALTER TABLE `', DATABASE(), '`.`', tableName, '` CHANGE `', columnName, '` `', newColumnName, '` ', columnDetail);
                PREPARE DynamicStatement FROM @StatementToExecute;
                EXECUTE DynamicStatement;
                DEALLOCATE PREPARE DynamicStatement;
            ELSEIF (@ColOldExist = 0 AND @ColNewExist = 1) THEN
                /* 旧列不存在，新列已存在，更新列结构 */
                SET @StatementToExecute = CONCAT('ALTER TABLE `', DATABASE(), '`.`', tableName, '` CHANGE `', newColumnName, '` `', newColumnName, '` ', columnDetail);
                PREPARE DynamicStatement FROM @StatementToExecute;
                EXECUTE DynamicStatement;
                DEALLOCATE PREPARE DynamicStatement;
            ELSE
                /* 新旧列都存在 */
                IF (columnName <> newColumnName) THEN
                    /* 列名不同，删除旧列 */
                    SET @StatementToExecute = CONCAT('ALTER TABLE `', DATABASE(), '`.`', tableName, '` DROP COLUMN `', columnName, '`');
                    PREPARE DynamicStatement FROM @StatementToExecute;
                    EXECUTE DynamicStatement;
                    DEALLOCATE PREPARE DynamicStatement;
                END IF;
                
                /* 更新新列结构 */
                SET @StatementToExecute = CONCAT('ALTER TABLE `', DATABASE(), '`.`', tableName, '` CHANGE `', newColumnName, '` `', newColumnName, '` ', columnDetail);
                PREPARE DynamicStatement FROM @StatementToExecute;
                EXECUTE DynamicStatement;
                DEALLOCATE PREPARE DynamicStatement;
            END IF;
        END";

    $db->setQuery($createQuery)->execute();
} catch (\Exception $e) {
    // 异常捕获，便于调试排查
    throw new \RuntimeException('存储过程操作失败: ' . $e->getMessage(), $e->getCode(), $e);
} finally {
    // 恢复原有预编译状态，避免影响其他数据库操作
    $db->setOption(\PDO::ATTR_EMULATE_PREPARES, $oldPrepareState);
}

关键修改说明 #

• 预编译状态控制：临时关闭预编译语句执行存储过程操作，执行完成后恢复原状态，不影响其他数据库逻辑
• 命名空间兼容：使用\Joomla\CMS\Factory替代全局JFactory，符合Joomla 4 的编码规范
• 代码优化：提取重复变量（如数据库前缀、占位符引号值），提升代码可读性与可维护性
• 异常处理：添加异常捕获逻辑，便于快速定位错误

内容的提问来源于stack exchange，提问作者Ronni