Magento – Direct sql query in magento not working

databasemagento-1.9

I am facing strange problem, when I use direct SQL query method in Magento controller its not working

    $resource     = Mage::getSingleton('core/resource');
    $connection   = $resource->getConnection('core_write');
    $table        = $resource->getTableName('toynav_referral');

    $query        = "INSERT INTO {$table} (`referral_id`,`email`,`coupon`) VALUES ('1', '2', '3')";
    $connection->query($query);

But when I use this in controller its working fine and one thing the fetch method works everywhere as expected.
When I debug in the system.log file i got the below error

User Error: Some transactions have not been committed or rolled back in /var/www/vhosts/************/httpdocs/lib/Varien/Db/Adapter/Pdo/Mysql.php on line 3937

Please advise me

Best Answer

Try to begin transaction and then commit it.

$resource     = Mage::getSingleton('core/resource');
$connection   = $resource->getConnection('core_write');
$table        = $resource->getTableName('toynav_referral');

try
{
    $connection->beginTransaction();

    $query        = "INSERT INTO {$table} (`referral_id`,`email`,`coupon`) VALUES ('1', '2', '3')";
    $connection->query($query);

    $connection->commit();
}
catch (Exception $e)
{
    $connection->rollBack();
}
Related Topic