0

I am migrating a componenet from J3 > J4. I am stuck with some db code (not my strongest).

Maybe there is a kind soul that could re-write this code so it works with J4 and i could onwords :-)

        $db = JFactory::getDbo();

    $query = "DROP PROCEDURE IF EXISTS " . $db->qn('redSHOP_Column_Update');

    $db->setQuery($query)->execute();

    $query = "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 " . $db->quote(
            'Procedure for use in redSHOP to update / add column to table avoid unexpected errors.'
        ) . "
        BEGIN
            SET tableName = REPLACE(tableName, " . $db->quote('#__') . ", " . $db->quote(
            JFactory::getConfig()->get('dbprefix')
        ) . ") ;
            SET columnDetail = REPLACE(columnDetail, " . $db->quote('#__') . ", " . $db->quote(
            JFactory::getConfig()->get('dbprefix')
        ) . ") ;
            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
                /* Both column doesn't exist. Just add column */

                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
                /* Old column exist. New column not exist. Change column */

                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
                    /* Old column not exist. New column exist. Update column */

                    set @StatementToExecute = concat('ALTER TABLE `',DATABASE(),'`.`',tableName,'` CHANGE `',newColumnName,'` ','`',newColumnName,'` ',columnDetail);
                    prepare DynamicStatement from @StatementToExecute ;
                    execute DynamicStatement ;
                    deallocate prepare DynamicStatement ;
            ELSE
                /* Old column exist. New column exist. */

                IF (columnName <> newColumnName) THEN
                    /* Old column is different with new column and both exist => The old column had to be remove */

                    set @StatementToExecute = concat('ALTER TABLE `',DATABASE(),'`.`',tableName,'` DROP COLUMN `',columnName,'`');
                    prepare DynamicStatement from @StatementToExecute ;
                    execute DynamicStatement ;
                    deallocate prepare DynamicStatement ;
                END IF ;
                
                /* Update structure of new column column */

                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($query)->execute();
1
  • Don't think this is going to work on MySQLi using prepared statements. You can try not using prepared statements or using PDO. See similar case joomla.stackexchange.com/a/33234/12652.
    – Sharky
    Commented Sep 13, 2023 at 8:06

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.