Mryntu

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

You do not need to use Mage API to get the table name.

Try this please:

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

Let me know if this worked!

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

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

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!