Posting in the Magento forums has been disabled pending the implementation of a new and improved forum solution which should better serve the community.

For new questions please post at magento.stackexchange.com, the community-run support site for the Magento community. We will be providing updates on the new forum solution soon. For questions or concerns please email community@magento.com.

Magento Forum

Using SQL scripts in Model to update a database table fails
 
Prad
Member
 
Total Posts:  72
Joined:  2008-11-12
 

For my custom module, I have to update a table other that I made for the module from model. I have raw scripts and can get the data from that table but cannot update the data in the table. I am wondering I missed something, any help would be appreciated.

FILEMage_MyModule_Model_File
$conn 
Mage::getSingleton('core/resource')->getConnection('core_read');
$sql="UPDATE table SET field2='update' WHERE field1='value'";
$result $conn->query($sql);
 
Magento Community Magento Community
Magento Community
Magento Community
 
Prad
Member
 
Total Posts:  72
Joined:  2008-11-12
 

After digging down deep and doing some search, I finally found out the error. It had nothing to do where I place the custom sql query in my custom module but it was something in database that was stopping the whole process. I tried the code with insert statement and found that auto increment id was reserved but the record never got entered in the database.
Finally this link helped me.

I had never came across using “commit;” in mysql, will have to read through about it. But for now it worked.

FILEMage_MyModule_Model_File
$conn 
Mage::getSingleton('core/resource')->getConnection('core_read');
$sql="UPDATE table SET field2='update' WHERE field1='value'; commit;";
$result $conn->query($sql);
 
Magento Community Magento Community
Magento Community
Magento Community
 
MagePsycho
Moderator
 
Avatar
Total Posts:  1702
Joined:  2009-06-23
 

Try to edit the following line of code:

$conn Mage::getSingleton('core/resource')->getConnection('core_read');
to
$conn Mage::getSingleton('core/resource')->getConnection('core_write');
 
Magento Community Magento Community
Magento Community
Magento Community
 
Prad
Member
 
Total Posts:  72
Joined:  2008-11-12
 

1. Sorry about the mistake in the code I used in the post. I have been trying with “core_write\” not “core_read”.
2. I have even checked the database attributes and connection configurations. At first, I thought it had to do something with connection configuration and checked for it.

I found no luck with both, as I said I could only fix it with changes in the sql statement. As soon as I get some time, I will dig deep and post if I find anything.

FILEMage_MyModule_Model_File
$conn 
Mage::getSingleton('core/resource')->getConnection('core_write');
$sql="UPDATE table SET field2='update' WHERE field1='value'; commit;";
$result $conn->query($sql);
 
Magento Community Magento Community
Magento Community
Magento Community
 
Ryan Sun _Kuafu
Guru
 
Total Posts:  317
Joined:  2008-11-14
FL
 

1, you might had exception after the query and rolled back updates.
2, its bad practice to write raw sql in model.

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top