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

• simonthesorcerer: 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

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

• Duke: Still same result
• Duke: I am not sure whats blocking me from committing.
• Neklo.com: Please add exception logging ...}catch (Exception $e){ $connection->rollBack(); Mage::logException($e); }
• Neklo.com: 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); }

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.