0

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

1
  • where do you want to use this? in a save operation maybe? if you use this while a mysql-transaction is happening, this may be a problem Sep 22, 2017 at 21:11

5 Answers 5

0

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();
}
4
  • Still same result
    – Duke
    Jul 5, 2016 at 4:47
  • I am not sure whats blocking me from committing.
    – Duke
    Jul 5, 2016 at 5:58
  • Please add exception logging ...}catch (Exception $e){ $connection->rollBack(); Mage::logException($e); }
    – Neklo.com
    Jul 5, 2016 at 11:18
  • Or try to use the Zend_Db 'insert' statement try { $connection->beginTransaction(); $data = array( 'referral_id' => $id, 'email' => $email, 'coupon' => $couponCode, ); $connection->insert($table, $data); }catch (Exception $e){ $connection->rollBack(); Mage::logException($e); }
    – Neklo.com
    Jul 5, 2016 at 11:35
0

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');
0

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!

0

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.

0

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.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.