How to handle the return values of sql stored procedures in qt application for inserting,delete,update..........
-
Hi,
AFAIK, it's described in the Approaches to binding values in the QSqlQuery documentation.
Hope it helps
-
Did you saw the
Binding values to a stored procedure:
part of the documentation I linked to ? -
-
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? -
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); } }
-
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
ENDif anything wrong ......i want to write for generic way.....
-
@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 ?
-
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?
-
Please show the exact code you are using.
-
Hi SGaist,
this is my stored procedureCREATE 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); } } } }
-
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.