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

veera · wrote on 21 Jun 2017, 11:41

@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?

SGaist · wrote on 21 Jun 2017, 11:48

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

VRonin · wrote on 21 Jun 2017, 11:55

"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);
}
}

veera · wrote on 21 Jun 2017, 12:24

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

veera · wrote on 22 Jun 2017, 12:19

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

veera · wrote on 22 Jun 2017, 12:22

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.....

VRonin · S SGaist 17 Jun 2017, 23:00

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

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

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

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

veera · wrote on 29 Jun 2017, 12:08

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?

SGaist · wrote on 29 Jun 2017, 22:06

Please show the exact code you are using.

veera · 7 May 2017, 07:01

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

veera · 7 May 2017, 07:02

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

}

SGaist · wrote on 30 Jun 2017, 11:42

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.

veera · 7 May 2017, 07:02

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...

veera · wrote on 5 Jul 2017, 06:58

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