error on running raw sql query in upgradeData.php magento 2

Question

I have created my own module and in UpgradeData.php script I am writing my own, mysql query but it gives me an error when I run bin/magento setup:upgrade to run the setup script files

Recoverable Error: Object of class Magento\Framework\DB\Statement\Pdo\Mysql could not be converted to string in /Users/suman/the-webster/app/code/YX/Migration/Setup/UpgradeData.php on line 51

The mysql query I am trying is:

$sequence_value = $setup->getConnection()->query("SELECT max(sequence_value)+1 FROM sequence_cms_block");

What am I doing wrong

full class code

<?php
namespace YX\Migration\Setup;
/**
* @codeCoverageIgnore
*/
use Magento\Framework\Setup\UpgradeDataInterface;
use Magento\Framework\Setup\ModuleDataSetupInterface;
use Magento\Framework\Setup\ModuleContextInterface;
class UpgradeData implements UpgradeDataInterface
{
/**
 * {@inheritdoc}
 * @SuppressWarnings(PHPMD.ExcessiveMethodLength)
 */
public function 
upgrade(ModuleDataSetupInterface$setup,ModuleContextInterface $context)
{
 $setup->startSetup();
// if (version_compare($context->getVersion(), '0.0.3', '<')) {
 $table_sequence_cms_block = $setup->getTable('sequence_cms_block');
 $table_cms_block = $setup->getTable('cms_block');
 $table_cms_block_store = $setup->getTable('cms_block_store');
 \Magento\Framework\App\ObjectManager::getInstance()
 ->get(\Psr\Log\LoggerInterface::class)->info("i am here before sequence_value query8 ");
 $sequence_value = $setup->getConnection()->query("SELECT max(sequence_value)+1 FROM `sequence_cms_block`");
 \Magento\Framework\App\ObjectManager::getInstance()
 ->get(\Psr\Log\LoggerInterface::class)->info("i am here after sequence_value query8 ".$sequence_value);
 $cms_block_query = "INSERT INTO `cms_block` (`block_id`,`created_in`,`updated_in`,`title`,`identifier`,`content`,`creation_time`,`update_time`,`is_active`) VALUES (" . $sequence_value . ",1,2147483647,'Footer Left Detail','footer_left_detail','<div class=\"col-lg-4\">
 <div class=\"left-parent-header\">
 THE WEBSTER
 </div>
 <div class=\"first-left-content\">
 <ul>
 <li><a href=\"#\">Contact Us</a></li>
 <li><a href=\"#\">About</a></li>
 <li><a href=\"#\">Team</a></li>
 <li><a href=\"#\">Affiliates</a></li>
 <li><a href=\"#\">Press</a></li>
 </ul>
 </div>
 </div>
 <div class=\"col-lg-4\">
 <div class=\"left-parent-header\">
 STORE INFORMATION
 </div>
 <div class=\"third-left-content\">
 <ul>
 <li><a href=\"#\">South Beach</a></li>
 <li><a href=\"#\">Soho</a></li>
 <li><a href=\"#\">Houston</a></li>
 <li><a href=\"#\">Bal Harbour</a></li>
 <li><a href=\"#\">Costa Mesa</a></li>
 </ul>
 </div>
 </div>
 <div class=\"col-lg-4\">
 <div class=\"left-parent-header\">
 SHOPPING ONLINE
 </div>
 <div class=\"second-left-content\">
 <ul>
 <li><a href=\"#\">Payment &amp; Security</a></li>
 <li><a href=\"#\">Shipping</a></li>
 <li><a href=\"#\">Return Policy</a></li>
 <li><a href=\"#\">FAQ</a></li>
 </ul>
 </div>
 </div>',NOW(),NOW(),1);";
 $setup->getConnection()->query($cms_block_query);
 $row_id_query = "select row_id from cms_block where block_id = ".$sequence_value;
 $row_id = $setup->getConnection()->query($row_id_query);
 $cms_block_store_query = "INSERT INTO `cms_block_store` (`row_id`,`store_id`) VALUES (" .$row_id. " ,1);";
 $setup->getConnection()->query($cms_block_store_query);
 // }
 $setup->endSetup();
 } }

• Sohel Rana: Add full class that you have tried
• fmsthird: remove $sequence_value, write like this, $setup->getConnection()->query("SELECT max(sequence_value)+1 FROM sequence_cms_block")
• fmsthird: don't put $setup to variable
• summu: @SohelRana added
• summu: @magefms but i need value of that select query, i have to use that value in insert of another table

Answer 1

Instead of directly query like this, I am suggesting to you use Magento\Cms\Model\BlockFactory oR Magento\Cms\Api\BlockRepositoryInterface to create a static block.

if you will use this, then you no need to update or select the record of sequence_cms_block table.

*Code may like:

class UpgradeData implements UpgradeDataInterface
 /**
 * @var \Magento\Cms\Model\BlockFactory
 */
 protected $blockFactory;
 public function __construct(
 \Magento\Cms\Model\BlockFactory $blockFactory, 
 )
 {
 $this->blockFactory = $blockFactory;
 }
/**
 * {@inheritdoc}
 * @SuppressWarnings(PHPMD.ExcessiveMethodLength)
 */
 public function upgrade(ModuleDataSetupInterface$setup,ModuleContextInterface $context)
 {
 $setup->startSetup();
 $content = '<div class=\"col-lg-4\">
 <div class=\"left-parent-header\">
 THE WEBSTER
 </div>
 <div class=\"first-left-content\">
 <ul>
 <li><a href=\"#\">Contact Us</a></li>
 <li><a href=\"#\">About</a></li>
 <li><a href=\"#\">Team</a></li>
 <li><a href=\"#\">Affiliates</a></li>
 <li><a href=\"#\">Press</a></li>
 </ul>
 </div>
 </div>
 <div class=\"col-lg-4\">
 <div class=\"left-parent-header\">
 STORE INFORMATION
 </div>
 <div class=\"third-left-content\">
 <ul>
 <li><a href=\"#\">South Beach</a></li>
 <li><a href=\"#\">Soho</a></li>
 <li><a href=\"#\">Houston</a></li>
 <li><a href=\"#\">Bal Harbour</a></li>
 <li><a href=\"#\">Costa Mesa</a></li>
 </ul>
 </div>
 </div>
 <div class=\"col-lg-4\">
 <div class=\"left-parent-header\">
 SHOPPING ONLINE
 </div>
 <div class=\"second-left-content\">
 <ul>
 <li><a href=\"#\">Payment &amp; Security</a></li>
 <li><a href=\"#\">Shipping</a></li>
 <li><a href=\"#\">Return Policy</a></li>
 <li><a href=\"#\">FAQ</a></li>
 </ul>
 </div>
 </div>';
 $block = $this->blockFactory->create();
 $block->setIdentifier('footer_left_detail')
 ->setTitle('Footer Left Detail')
 ->setContent($content);
 $block->setStores([\Magento\Store\Model\Store::DEFAULT_STORE_ID]);
 $block->setIsActive(1);
 $block->save(); 
 $setup->endSetup();
 }
}

• summu: thanks for your response, can you provide me some links for the same , how to use BlockFactory or BlockRepositoryInterface to create static block
• summu: Can i do it same for megamenu, as i have lots of queries for megamenu also
• Amit Bera: I don't know how you have managed menu.So cannot tell you
• summu: magento.stackexchange.com/questions/259806/… check this
• summu: I have added megamenu from my magento admin panel, now i have to replicate the same in my production via updateData scripts, I have added which tables gets updated

Answer 2

Your query where the error occurs returns an object. You have to fetch the value and use that in your next statement.

$sequence_value = $setup->getConnection()->query("SELECT max(sequence_value)+1 FROM `sequence_cms_block`")->fetch(Zend_Db::FETCH_NUM);

$sequence_value[0] should contain the value you need.

• summu: It gives me this error PHP Fatal error: Uncaught Error: Class 'YX\Migration\Setup\Zend_Db' not found in /Users/suman/the-webster/app/code/YX/Migration/Setup/UpgradeData.php:190
• HelgeB: you have to put use Zend_Db; in the head of your file