How to handle the return values of sql stored procedures in qt application for inserting,delete,update..........



  • Hi all,
    I am writing stored procedures for inserting,update,delete operations,how to handle the return values in qt application (Ubuntu 16.04 LTS)


  • Lifetime Qt Champion

    Hi,

    AFAIK, it's described in the Approaches to binding values in the QSqlQuery documentation.

    Hope it helps



  • Hi ,
    Thanks for your valuable reply ....in that link they have mentioned only queries ,,,,,i want to handle return vales of MSSQL stored procedures ......



  • @veera If you scoll down 10 lines there's a paragraph titled "Binding values to a stored procedure"



  • Hi,
    binding the values to a stored procedure done successfully,but i did not understand the how to collect the return values of stored procedures in qt application....


  • Lifetime Qt Champion

    Did you saw the Binding values to a stored procedure: part of the documentation I linked to ?



  • yes i go through the documents but i am successfully binding the values ...i did not understand how to handle the stored procedures return values in qt application ....in that document they did not provide the return values of stored procedure,,,,,


  • Qt Champions 2016

    @veera

    Hi

    Sample from docs:

    This code calls a stored procedure called AsciiToInt(), passing it a character through its in parameter, and taking its result in the out parameter.

    QSqlQuery query;
    query.prepare("CALL AsciiToInt(?, ?)");
    query.bindValue(0, "A");
    query.bindValue(1, 0, QSql::Out); <<<<<<<<<<<< the out paramter
    

    The results are put in that parameter.



  • @mrjj said in How to handle the return values of sql stored procedures in qt application for inserting,delete,update..........:

    AsciiToInt

    How to handle AsciiToInt procedure in qt application......i e
    int myreturn = AsciiToInt(?,?);
    how should i get the return values of AsciiToInt?


  • Lifetime Qt Champion

    And again: it's all in the example in the documentation:

    QSqlQuery query;
    query.prepare("CALL AsciiToInt(?, ?)");
    query.bindValue(0, "A");
    query.bindValue(1, 0, QSql::Out); <<<<<<<< the out parameter
    query.exec();
    int i = query.boundValue(1).toInt(); // i is 65 <<<<<< the content of the out parameter
    


  • "return values" of stored procedures are accessed as normal queries:

    QSqlQuery query;
    query.prepare("CALL MyStoredProcedure()");
    if(query.exec()){
    while(query.next()){
    const auto currRecord = query.record();
    for(int i=0;i<currRecord.count();++i)
    qDebug() << currRecord.field(i) << ": " << currRecord.value(i);
    }
    }
    


  • Okay thanks for the Usefull information ....i will try now



  • how to pass the parameters for updating the record using stored procedure call in qt application....please find the code below

    // QString strSql1 = "{CALL myupdate1(:id,:name,:lastname,:contact,:emailid)}";
    QString strSql1 = "{CALL myupdate1(?,?,?,?,?)}";
    query.prepare(strSql1);
    if(query.exec())
    {
    //ui->pushButton->setText("Update");

                QMessageBox::information(this,tr("Update"),tr("Record has been updated"));
                on_clear_pushButton_clicked();
            }


  • Hi ,
    I am writing the stored procedure like this ,

    ALTER PROC myupdate1
    (
    @id INT,
    @name VARCHAR(100),
    @contact INT,
    @lastname VARCHAR(100),
    @emailId VARCHAR(100)
    )
    AS
    BEGIN
    ----INSERT INTO Info (id ,name,lastname,contact,emailid)VALUES(@id,@Name,@lastname,@contact,@emailid);
    --UPDATE Info set name='"+name+"',contact='"+contact+"',lastname='"+lastname+"',emailId='"+emailid+"' where id='"+id+"'
    UPDATE Info SET name = @name,contact = @contact,lastname = @lastname,emailId = @emailId WHERE id = @id
    END

    if anything wrong ......i want to write for generic way.....





  • okay i have gone through the documents ...I did not understand the updating the record of the stored procedure ...i am trying to call the stored procedure name in the qt application it is build successfully but not updating record please help me what are the parameters need to pass?


  • Lifetime Qt Champion

    Please show the exact code you are using.



  • Hi SGaist,
    this is my stored procedure

    CREATE PROCEDURE myupdate1
    (
     @id INT,
     @name NVARCHAR(100),
     @contact INT,
     @lastname NVARCHAR(100),
     @emailId NVARCHAR(100)
    )
    AS
    --DECLARE @name NVARCHAR(100),@contact INT,@lastname NVARCHAR(100),@emailId NVARCHAR(100)
    BEGIN
          SET NOCOUNT ON;
    	--INSERT INTO Info (id ,name,lastname,contact,emailid)VALUES(@id,@Name,@lastname,@contact,@emailid);
    	 --UPDATE Info SET name=??,contact=?,lastname=?,emailId=? where id=?
    	  --update Info set name='"+name+"',contact='"+contact+"',lastname='"+lastname+"',emailId='"+emailid+"' where id='"+id+"'
         -- UPDATE Info SET name=ISNULL(@name,name) ,contact=ISNULL (@contact,contact),lastname =ISNULL (@lastname,lastname),emailId =ISNULL(@emailId,emailId) WHERE id = @id
         UPDATE Info SET name=ISNULL(@name,'"+name+"') ,contact=ISNULL (@contact,'"+contact+"'),lastname =ISNULL (@lastname,'"+lastname+"'),emailId =ISNULL(@emailId,'"+emailId+"') WHERE id = @id
    END
    


  • I am calling this stored procedure in my qt application ,the code is

    // myupdate.........
    else
    {
    
           //ui->on_->setText("Update");
           setStyleSheet("QPushButton:focus { outline: 0 }");
           ui->add_pushbutton->setText("Update");
           QString id ,name,contact,lastname,emailid;
           name = ui->lineEdit_2->text();
           lastname = ui->lineEdit_3->text();
           contact = ui->lineEdit_4->text();
           emailid = ui->lineEdit_5->text();
           ui->lineEdit->setFocus();
           QSqlQueryModel *model = new QSqlQueryModel;
           QSqlQuery query;
    
       //   MainWindow ::findChildren();
       // Setting the read only ............
          QList <QLineEdit*> l_lineEdits = ui->lineEdit->findChildren<QLineEdit*>();
          foreach(QLineEdit* id,l_lineEdits){
              id->setReadOnly(true);
          }
          ui->lineEdit->setReadOnly(true);
      /*
          QPalette *palette = new QPalette();
          palette->setColor(QPalette::Base,Qt::white);
          palette->setColor(QPalette::Text,Qt::white);
          ui->lineEdit_5->setPalette(*palette);*/
    
    
    
          //ID while updating
         id = ui->lineEdit->text();
          if ((name == NULL && contact == NULL && lastname == NULL && emailid == NULL) && (name == "" && contact == "" && lastname == "" && emailid == "")  )
          {
    
                  QMessageBox::critical(this,tr("Error"),tr("You Cant't able to Update the Table"));
                  ui->lineEdit->setFocus();
                  ui->lineEdit_2->setFocus();
                  ui->lineEdit_3->setFocus();
                  ui->lineEdit_4->setFocus();
                  ui->lineEdit_5->setFocus();
          }
    else
    {
          // QLineEdit *accessLineEdit;
          // accessLineEdit->setReadOnly(true);
          QRegExp re8( "^[A-Za-z]*$" );
    
          //Name while updating
          name = ui->lineEdit_2->text();
    
         if(name == "" || lastname == "" || contact == "" || emailid == "")
          {
            if (name == "")
             {
                QMessageBox::critical(this,tr("Validation of Name:"),tr("Please Enter the Valid Name"));
                ui->lineEdit_2->setFocus();
    
             }
             if(lastname == "" )
             {
                QMessageBox::critical(this,tr("Validation of Lastname:"),tr("Please Enter the Valid Lastname"));
                ui->lineEdit_3->setFocus();
             }
              if(contact == "")
              {
                QMessageBox::critical(this,tr("Validation of Contact:"),tr("Please Enter the Valid Mobile Number:"));
                ui->lineEdit_4->setFocus();
              }
                if(emailid == "")
               {
                QMessageBox::critical(this,tr("Validation of EmailId:"),tr("Please Enter the Valid EmailId:"));
                ui->lineEdit_5->setFocus();
               }
    
          }
    else
      {
              QString text88 = name;
              bool ok1;
              int value = text88.toInt(&ok1);
    
         if(!ok1)
         {
    
             name = ui->lineEdit_2->text();
    
          }
    
    else
    
        {
           ui->lineEdit_2->setFocus();
           QMessageBox::critical(this,tr("Validation of Name:"),tr("Please Update with Valid Name"));
    
           if (text88 == "0123456789" || text88 == NULL || text88 == 0 )
            {
              QMessageBox msgBox;
              msgBox.setIcon(QMessageBox::Warning);
              msgBox.setWindowTitle("Wrong Name" );
              msgBox.setText("Please Enter the Valid Name");
              ui->lineEdit_2->setFocus();
              QMessageBox::critical(this,tr("Validation of Name:"),tr("Please Enter the Valid Name"));
    
            }
         }
    
         //Lastname while updating
         QRegExp re8( "^[A-Za-z]*$" );
         lastname = ui->lineEdit_3->text();
         //QRegExpValidator *validator8 = new QRegExpValidator( re8, 0 );
         //ui->lineEdit_4->setValidator(validator8);
       //  lastname = ui->lineEdit_4->text();
         QString text77 = lastname;
         bool ok77 = true;
         int value7 = text77.toInt(&ok77);
         if(!ok77)
         {
            //lastname = text7;
            lastname = ui->lineEdit_3->text();
    
           // query.prepare("update Info set name='"+name+"',contact='"+contact+"',lastname='"+lastname+"',emailId='"+emailid+"' where id='"+id+"' ");
            //query.exec();
           }
        else
        {
          ui->lineEdit_3->setFocus();
          QMessageBox::critical(this,tr("Validation of LastName:"),tr("Please Update with the Valid LastName"));
    
          if (text77 == "0123456789" || text77 == NULL || text77 == 0 )
           {
              QMessageBox msgBox;
              msgBox.setIcon(QMessageBox::Warning);
              msgBox.setWindowTitle("Wrong Name" );
              msgBox.setText("Please Enter the Valid Name");
              ui->lineEdit_3->setFocus();
              QMessageBox::critical(this,tr("Validation of LastName:"),tr("Please Update with the Valid LastName"));
           }
         }
    
    
            //Contact while updating
             contact = ui->lineEdit_4->text();
            // QRegExpValidator *validator9 = new QRegExpValidator( re8, 0 );
            // ui->lineEdit_3->setValidator(validator9);
           //  contact = ui->lineEdit_4->text();
             qint64 mobile=contact.toInt();
             if(((const char*)mobile) == "" || ((const char*)mobile) == NULL || ((const char*)mobile) == 0 )
              {
                 QMessageBox msgBox;
                 msgBox.setIcon(QMessageBox::Warning);
                 msgBox.setWindowTitle("Wrong ID" );
                 msgBox.setText("Please Update with the Valid Mobile Number: ");
                 ui->lineEdit_4->setFocus();
                 QMessageBox::critical(this,tr("Validation of Mobile Number:"),tr("Please Update with the Valid Mobile Number:"));
              }
             else
             {
                 //query.prepare("update Info set name='"+name+"',contact='"+contact+"',lastname='"+lastname+"',emailId='"+emailid+"' where id='"+id+"' ");
                 contact = ui->lineEdit_4->text();
    
             }
    
              //Email ID validating and updating
    
              emailid = ui->lineEdit_5->text();
              bool retorno = true;
              qDebug() << emailid;
              QRegularExpression regex("^[0-9a-zA-Z]+([0-9a-zA-Z]*[-._+])*[0-9a-zA-Z]+@[0-9a-zA-Z]+([-.][0-9a-zA-Z]+)*([0-9a-zA-Z]*[.])[a-zA-Z]{2,6}$");
    
              if(!regex.match(emailid).hasMatch())
    
               {
                   retorno = false;
                   ui->lineEdit_5->setFocus();
                   QMessageBox::critical(this,tr("Validation of EmailID:"),tr("Please Update with the Valid EmailID:"));
               }
    
               if(regex.match(emailid).hasMatch())
               {
                    retorno = true;
    
               }
    
    
               if((!ok1) && (!ok77) && (regex.match(emailid).hasMatch()) && (((const char*)mobile) != "" || ((const char*)mobile) != NULL || ((const char*)mobile) != 0))
               {
                   QSqlQuery query;
                 //  query.prepare("update Info set name='"+name+"',contact='"+contact+"',lastname='"+lastname+"',emailId='"+emailid+"' where id='"+id+"' ");
    
                  ui->lineEdit->setReadOnly(true);
                  id = ui->lineEdit->text().toInt();
                    //id = ui->lineEdit->text();
                   //name = ui->lineEdit_2->text();
                    //lastname = ui->lineEdit_3->text();
                    //contact = ui->lineEdit_4->text().toInt();
                    //emailid = ui->lineEdit_5->text();
    
    // Calling stored procedure name ......
    
                  // QString strSql1 = "{CALL myupdate1(17,'veera',9999999,'raj','raja@gmail.com')}";
                   QString strSql1 = "{CALL myupdate1(:id,:name,:contact,:lastname,:emailId)}";
                   //  QString strSql1 = "{CALL myupdate1(?,?,?,?,?)}";
                     query.prepare(strSql1);
                  if(query.exec())
                  {
                //ui->pushButton->setText("Update");
    
                    QMessageBox::information(this,tr("Update"),tr("Record has been updated"));
                    on_clear_pushButton_clicked();
                }
    
                int up = query.boundValue(1).toInt();
                qDebug() << up ;
               }
    
              else
    
                   QMessageBox::critical(this,tr("Error:"),tr("Record updation Error:"));
                   model->setQuery("SELECT *FROM  Info");
                   ui->tableView->setModel(model);
              // changing the text box
             // ui->add_pushbutton->setText("Add");
    
             /* // clearing the Textbox
              foreach (QLineEdit* clr, this->findChildren<QLineEdit*>()) {
                  clr->clear();
              emit clearFocus();
              }*/
              // ui->lineEdit->setFocus();
    
             //   MainWindow ::findChildren();
             // Setting the read only ............
                QList <QLineEdit*> ll_lineEdits  = ui->lineEdit->findChildren<QLineEdit*>();
                foreach(QLineEdit* id,ll_lineEdits){
                    id->setReadOnly(false);
                }
                ui->lineEdit->setReadOnly(false);
          }
        }
      }
    
    }
    

  • Lifetime Qt Champion

    Please read again the example in the documentation of QSqlQuery::prepare. More specifically the calls to bindValue and addBindValue.

    You're just preparing the query but you don't assign anything for its parameters. How do you expect the query to run successfully when non of its parameters have values ?

    Also you should print the error you get when the query is failing rather than just a message when it succeeds.



  • Hi SGaist,
    I am writing stored procedure in DBeaver-General GUI using MS SQL for inserting records into the tableview in qt application,i am sharing the my stored procedure as

    ALTER PROCEDURE dbo.insert_data2
    (
     @ID INT,
     @Name VARCHAR(100),
     @Lastname VARCHAR(100),
     @contact INT,
     @EmailId VARCHAR(100),
     @ret INTEGER=NULL OUTPUT
    )
    
    AS
    BEGIN
    	If Exists (select ID from Info where ID = @ID)
    	
    	BEGIN
    	   SELECT @ret = -2
    		RETURN @ret;
    	 -- SELECT  -2 as StatusID  
    	  --ID already exists. So returning -2. Means, not successful
    	END 
    	ELSE
    	BEGIN
    		INSERT INTO Info (ID,Name,Lastname,contact,EmailId) VALUES(@ID,@Name,@Lastname,@contact,@EmailId);
    		--SELECT *FROM Info
    		--SELECT 2 as StatusID
    		 SELECT @ret = 115;
    		 RETURN @ret;
    	END 	
    END 
    

    after successful of insertion it needs to return value is "115" but returning 1 what I am doing wrong ,I did not understand please guide me right direction...



  • I am checking the stored procedure's return value in my qt application.....


Log in to reply
 

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.