Direct sql query in magento not working

Question

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

Answer 1

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();
}

Answer 2

Try to Make an log like below...

Mage::log(var_export($query, TRUE), null,'query.log');
Mage::log(var_export($connection,TRUE),null,'connection.log');

Answer 3

There is no issue with your block of code.

I believe this error is happening because this perticular block is being run in the midst of a higher level transaction.

The solution to you problem is putting this block of code either before the beginning of the transaction or after the transaction is being commited.

Cheers!

Answer 4

Please check with this

$resource     = Mage::getSingleton('core/resource');
$connection   = $resource->getConnection('core_read');
$table        = $resource->getTableName('toynav_referral');
$query        = "INSERT INTO ". $table ."(referral_id,email,coupon) VALUES ('1','2','3')";
$connection->query($query);

I hope it will help you. Thank you.

Answer 5

Try to use the following flow to debug the transaction, maybe it will be helpful.

$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();
    Mage::logException($e);
}

You can trace it in the log if any error in the commit.