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

veera

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)

SGaist

Hi,

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

Hope it helps

veera

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

VRonin

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

veera

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

SGaist

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

veera

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

mrjj

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

veera

@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

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

"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

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

veera

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

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

@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

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

Please show the exact code you are using.

veera

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

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

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.