2

I want to achieve following functionality:

Whenever user tries to insert new data, i want to remove all old data from my custom table and insert new data.

For this, i want to use truncateTable() method. i am using code from below link:

Truncate table using resource model in Magento 2

But i am getting below error when i run code:

Something went wrong while saving this configuration: User Error: DDL statements are not allowed in transaction

Is there anyway we can truncate table using code?

Note: It works with delete but reason to use truncate is to reset my custom table's auto-increment key.

  • Hello Khushbu. you can try with database level programming. – samumaretiya Jun 21 at 10:02
  • You can create a database trigger for insert event for a particular table and try to delete the data and fill with a new value. There is also before and after the event too – samumaretiya Jun 21 at 10:03
  • Add me in skype if you want to explore it further skype: samumaretiya – samumaretiya Jun 21 at 10:04
  • ok i got your point. I will try this – Khushbu Jun 21 at 10:12
  • 1
    I post the answer, magento provide the way to remove all the data from table using collection. you can use that one. – Dhiren Vasoya Jun 21 at 11:10
1

To remove old data from database, you can use magento code as well.

<?php
use Vendor\Extension\Model\ResourceModel\CollectionFactory;

class Yourclass
{
    protected $_collectionFactory;

    public function __construct(.....
    CollectionFactory $collectionFactory
    .....)
    {
        $this->_collectionFactory = $collectionFactory;
    }

    public function execute()
    {
       // To remove all old data.

       $mycollection = $this->_collectionFactory->create()
                     ->getCollection();
       foreach ($mycollection as $myitem) {
            $myitem->delete();
        }

        // Now you can do code here to add new data
    }

}
  • I used delete code only and it's working right now. I will post that here too. but i was looking to use truncate because deleting won't reset my auto increment key. I hope you understand – Khushbu Jun 21 at 11:29
  • Truncate means you need to remove all the data from table. And above code do the same things. – Dhiren Vasoya Jun 21 at 11:49
  • No truncate is different in many ways from delete. you can read this: c-sharpcorner.com/UploadFile/65fc13/… – Khushbu Jun 21 at 11:54
0

Right now i ended up with delete instead of truncate. Actually this is not the best solution because i want to reset my auto increment key in addition to deleting all data from my table. Still someone can use this code so i am sharing here:

Solution with delete instead of truncate is as below:

<?php
class custom
{
    protected $connection;
    protected $dbresource;

    public function __construct(\Magento\Framework\App\ResourceConnection $dbresource)
    {
        $this->connection = $dbresource->getConnection();
        $this->dbresource = $dbresource; 
    }

    public function mycustomfunction()
    {
        $table = $this->dbresource->getTableName("my_custom_table");
        $this->connection->delete(
                $table,
                ['1 = 1']
        );
    }
}
?>

I am still looking for a way to use truncate!!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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