?
Solved

QSqlQuery problem.

Posted on 2011-10-18
9
Medium Priority
?
542 Views
Last Modified: 2012-05-12
Hello :

I have succesfully installed the mysql plugin for QT. As I am learning to use the SQL Functionality of QT I have writtent the following test code. ( see snippet 1). It connects to mysql and attempts to do an insert in the person table (which is defined in code snippet 2). The exmaple compiles, links and runs fine (along with showing a successfull connection to the database) but I do not see the inserted data in the mysql table. For example when I type the select statement (see code snippet 3), I get an empty set as a result. What am I doing wrong?


#include <QApplication>
#include <QtSql/qsqldatabase>
#include <QtSql/qsqlquery>
# include <QMessageBox>
# include <QString>
# include <QVariant>

class Person
{
public:
	QString *fname;
	QString *lname;
	QString *mobile;
	Person()
	{
		this->fname = new QString();
		this->lname = new QString();
		this->mobile = new QString();
	};
};




int main(int argc, char *argv[])
{
  QApplication a(argc, argv);
  
  QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
  db.setHostName("localhost");
  db.setDatabaseName("demo");
  db.setUserName("root");
  db.setPassword("root");
  if(!db.open())
  {
	  QMessageBox *m = new QMessageBox();
	  m->setText("Failure Connecting to Database");
	  m->show();
  }
  else
  {
	  QMessageBox *m = new QMessageBox();
	  m->setText("Successful connection");
	  m->show();
  }

  Person *p = new Person();
  p->fname->append("John");
  p->lname->append("Smith");
  p->mobile->append("9980324083");

  QSqlQuery mQuery(db);
  mQuery.prepare("INSERT INTO PERSON (id,first_name,last_name,phone) VALUES (?,?,?,?)");
  mQuery.addBindValue(QVariant("NULL"));
  mQuery.addBindValue(QVariant(*(p->fname))) ; 
  mQuery.addBindValue(QVariant(*(p->lname)));
  mQuery.addBindValue(QVariant(*(p->mobile)));
  mQuery.exec();

  return a.exec();
}

Open in new window

CREATE DATABASE DEMO
       CREATE TABLE PERSON
      ( id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
      first_name VARCHAR(255),
      last_name VARCHAR(255),
      mobile_phone VARCHAR(255) );

Open in new window

use demo;
      select * from person;

Open in new window

0
Comment
Question by:arjoshi77
  • 5
  • 3
9 Comments
 
LVL 4

Expert Comment

by:qasim_md
ID: 36985301
You are passing value NULL mQuery.addBindValue(QVariant("NULL")) to the ID column which you have defined as ( id int NOT NULL PRIMARY KEY AUTO_INCREMENT.

so it is failing internally.

Pass some value or modify the code like this:

  QSqlQuery mQuery(db);
  mQuery.prepare("INSERT INTO PERSON (first_name,last_name,phone) VALUES (?,?,?)");
  mQuery.addBindValue(QVariant(*(p->fname))) ;
  mQuery.addBindValue(QVariant(*(p->lname)));
  mQuery.addBindValue(QVariant(*(p->mobile)));
0
 

Author Comment

by:arjoshi77
ID: 36985462
that worked well. I just have one more question. WHat if I have a query such as :

INSERT INTO TABLE SOMETABLE VALUES ("LAST_INSERT_ID()).

In such a case what argument should I supply to addBindValue function?

Soemthing like :

mQuery.addBindValue(QVariant(*("LAST_INSERT_ID()")) ; ?????





0
 

Author Comment

by:arjoshi77
ID: 36985504
Hate to bother you again, I have the following transaction that is to be implemented using QT Database functionality. I dont expect you to write code for me but if you could point me to references / links to where I can be able to implement this functionality I would appreciate it very much. This is the first time I  am working with the database aspects of QT. Your help woul.d be much appreciated.

START TRANSACTION;

USE DOCTOR;

INSERT INTO PATIENT VALUES (
NULL,"john","smith","9907834929","jsmith@gmail.com","Male"
);

INSERT INTO SESSION VALUES (
NULL,LAST_INSERT_ID(),"this is data","spinal injury","Physical",CURRENT_DATE()
);

INSERT INTO PRESCRIPTION VALUES (
NULL,10,LAST_INSERT_ID()
);

SET @myval = LAST_INSERT_ID();

INSERT INTO DRUG VALUES (NULL,@myval,"combiflam",200);
SET @val = LAST_INSERT_ID();
INSERT INTO DRUG_WHEN VALUES (NULL,"AfterDinner",@val);

INSERT INTO DRUG VALUES (NULL,@myval,"crocin",200);
SET @val = LAST_INSERT_ID();
INSERT INTO DRUG_WHEN VALUES (NULL,"AfterDinner",@val);
INSERT INTO DRUG_WHEN VALUES (NULL,"BeforeBreakFast",@val);

COMMIT;

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 35

Expert Comment

by:sarabande
ID: 36985571
if you get your values from a function i would assign the value to a local variable and use that for addBindValue.

i don't see any in the script which could not be done with the technique you already posted beside of the transaction. here class QSqlDriver has function beginTransaction, commitTransaction and more.

Sara
0
 

Author Comment

by:arjoshi77
ID: 36985616
sara: could you elaborate and place into context what you mean by :

"if you get your values from a function i would assign the value to a local variable and use that for addBindValue."

Sorry for being dumb, but I did not understand this statement.  could you toss in some example code  along with an explanation so I can understand the statement. ??




0
 
LVL 35

Expert Comment

by:sarabande
ID: 36985741
i meant that with addbindInsert the prepare would use the address of a variable to get the value for insert. that's why i thought you simply need replace p->fname by a local QString variable and then do the call:

QString * last_id = new QString();
*last_id = LAST_INSERT_ID();  // here call your function
addBindValue(QVariant(*(last_id)));

Open in new window


 
but looking more thoroughly to the addBindValue call, i see that they pass the QString value and not QString pointer. so you may shorten things by

addBindValue(QVariant(LAST_INSERT_ID()));

Open in new window


 
what has the additional advantage that you can pass an integer value directly in case the LAST_INSERT_ID() returns an integer and not a QString.

Sara
0
 

Author Comment

by:arjoshi77
ID: 36986372
sara:

Thanks very much for your help. I have implemented code ( see snippet 1) which interacts with schema
shown in code snippet 2.

The code I have written does do the work of populating data for all the tables except the last table i.e. the drug_when table. I have put in debugging code ( See inclusion of message box) to ensure that the drug_when field contains data. And it does. So something is wrong with my query. I have highlighted the location of the query in question in both the code snippets with comments in BOLD.

Just to facilitate your understanding:

Note that the attributes of each class is mapped directly from the schema. So for example the patient class is shown in (snippet 3) has attributes mapped directly from the Patient schema in snippet 2.
Wherever there is a one-to-many relationship, the many side object has a compositional relationship in the form of a vector in the parent object. So patient object contains a vector of session objects and so on so forth.

I would appreciate very much if inspect the code and let me know why the highlighted code is not working?

/*********CODE SNIPPET 1**************************/

# include "DataInterface.h"
# include "Patient.h"
 #include <QtSql/qsqldatabase>
#include <QtSql/qsqlquery>

# include <qdebug>
# include <QStringList>
# include <QMessageBox>
# include <QString>
# include <QVariant>

DataInterface::DataInterface()
{
};

void DataInterface::createNewPatient(Patient &in)
{
		QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
		db.setHostName("localhost");
		db.setDatabaseName("doctor");
		db.setUserName("root");
		db.setPassword("root");
		if(!db.open())
			{
				QMessageBox *m = new QMessageBox();
				m->setText("Failure Connecting to Database");
				m->show();
			}
		else
			{
				QMessageBox *m = new QMessageBox();
				m->setText("Successful connection");
				m->show();
			}
		QSqlQuery mQuery(db);
		mQuery.exec("START TRANSACTION");
		mQuery.prepare("INSERT INTO PATIENT (first_name,last_name,mobile_number,e_mail,gender) VALUES (?,?,?,?,?)");
		mQuery.addBindValue(QVariant(*(in.fname)));
		mQuery.addBindValue(QVariant(*(in.lname)));
		mQuery.addBindValue(QVariant(*(in.mob_number)));
		mQuery.addBindValue(QVariant(*(in.e_mail)));
		mQuery.addBindValue(QVariant(*(in.gender)));
		mQuery.exec();

		QVector<Session> mSessionVector = in.getSessions();
		for(int i = 0 ; i < mSessionVector.size() ; i++)
		{
			Session mSession = mSessionVector[i];
			mQuery.prepare("INSERT INTO SESSION (fk_patient,session_data,diagnosis,session_type,session_date) VALUES (LAST_INSERT_ID(),?,?,?,CURRENT_DATE())");
			mQuery.addBindValue(QVariant(*(mSession.session_data)));
			mQuery.addBindValue(QVariant(*(mSession.diagnosis)));
			mQuery.addBindValue(QVariant(*(mSession.session_type)));
			mQuery.exec();

			Prescription *mPres = mSession.getPrescription();
			mQuery.prepare("INSERT INTO PRESCRIPTION (duration_valid_for,fk_session) VALUES (?,LAST_INSERT_ID())");
			mQuery.addBindValue(QVariant(mPres->duration));
			mQuery.exec();

			mQuery.exec("SET @myval = LAST_INSERT_ID()");

			QVector<Drug> mDrugVector = mPres->getDrugs();
			for(int j = 0 ; j < mDrugVector.size() ; j++)
			{
				Drug mDrug = mDrugVector[j];
				mQuery.prepare("INSERT INTO DRUG (fk_prescription,drug_name,drug_dosage) VALUES (@myval,?,?)");
				mQuery.addBindValue(QVariant(*(mDrug.drug_name)));
				mQuery.addBindValue(QVariant(mDrug.dosage));
				mQuery.exec();

				mQuery.exec("SET @val = LAST_INSERT_ID()");
				QVector<Drug_When> mDrugWhenVector = mDrug.getDrugWhen();
				for (int k = 0 ; k < mDrugWhenVector.size() ; k++)
				{
					Drug_When mDrugWhen = mDrugWhenVector[k];
					//********THIS IS THE QUERY THAT IS NOT WORKING**********					
					mQuery.prepare("INSERT INTO DRUG_WHEN (when_to_take) VALUES (?,@val)");
					//********THIS IS THE QUERY THAT IS NOT WORKING**********					
					QMessageBox* mBox = new QMessageBox();
					mBox->setText(*(mDrugWhen.when));
					mBox->show();
					mQuery.addBindValue(QVariant(*(mDrugWhen.when)));
					mQuery.exec();
				}
			}
		}
		mQuery.exec("COMMIT");


};

Open in new window

/*********CODE SNIPPET 2**************************/
CREATE DATABASE DOCTOR;
USE DOCTOR;

CREATE TABLE PATIENT (
pk_patient INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(255) NOT NULL DEFAULT "Not Specified",
last_name VARCHAR(255) NOT NULL DEFAULT "Not Specified",
mobile_number VARCHAR(100) NOT NULL DEFAULT 0,
e_mail VARCHAR(255) NOT NULL DEFAULT "Not Specified",
gender ENUM ('Male','Female')
)ENGINE=innodb;

CREATE TABLE SESSION (
pk_session INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
fk_patient INT NOT NULL,
session_data TEXT NOT NULL, 
diagnosis TEXT NOT NULL,
session_type ENUM ('Telephonic','Physical'),
session_date DATE,
FOREIGN KEY (fk_patient) REFERENCES PATIENT (pk_patient)
)ENGINE=innodb;

CREATE TABLE PRESCRIPTION (
pk_prescription INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
duration_valid_for INT NOT NULL DEFAULT 0,
fk_session INT NOT NULL,
FOREIGN KEY (fk_session) REFERENCES SESSION(pk_session)
)ENGINE=innodb;

CREATE TABLE DRUG (
pk_drug INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
fk_prescription INT NOT NULL,
drug_name VARCHAR(255) DEFAULT "Not Specified",
drug_dosage FLOAT NOT NULL DEFAULT 0,
FOREIGN KEY (fk_prescription) REFERENCES PRESCRIPTION(pk_prescription)
)ENGINE=innodb;

/*******THIS IS THE TABLE IN QUESTION WHERE THE QUERY IS NOT WORKING**************

CREATE TABLE DRUG_WHEN (
pk_drug_when INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
when_to_take VARCHAR(255),
fk_drug INT NOT NULL,
FOREIGN KEY (fk_drug) REFERENCES DRUG(pk_drug)
)ENGINE=innodb;
/*******THIS IS THE TABLE IN QUESTION WHERE THE QUERY IS NOT WORKING**************

Open in new window

/***********CODE SNIPPET 3************************/

#ifndef __PATIENT_HH__
# define __PATIENT_HH__
# include "session.h"
# include <QVector>
# include <QString>
 
class Patient
{
public:
	Patient();

	int pk_patient;
	QString *fname;
	QString *lname;
	QString *mob_number;
	QString *e_mail;
	QString *gender;
	QVector<Session> mSessions;

	void addSession(Session &in);
	QVector<Session> getSessions();
	//void dump();

	
};

# endif

Open in new window

0
 
LVL 35

Accepted Solution

by:
sarabande earned 2000 total points
ID: 36986784
an insert statement must have the same number of fields as number of values. you have only one field but two values.

note, there are three possibilities (as far as i understand the sample script) to set the last id (isn't it ithe next id?) .

number one is to execute a set statement as done with the DRUG table. here the following insert statement gets its value from database value 'myval' via @myval.

number two is to specify the database function LAST_INSERT_ID() in the values clause as done with SESSION table.

number three would be to fetch LAST_INSERT_ID() from database (table) and assign it to a local variable. then do addBindValue using that variable as argument.

for number three we have no sample code. so you probably shouldn't use it. i would go for number two which is quite straight forward and would spare you executing a separate set statement before insert.

Sara
0
 

Author Closing Comment

by:arjoshi77
ID: 36992342
sara:

thank you very much for your help. I have the code working now thanks to your inputs.
0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Container Orchestration platforms empower organizations to scale their apps at an exceptional rate. This is the reason numerous innovation-driven companies are moving apps to an appropriated datacenter wide platform that empowers them to scale at a …
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
The viewer will learn how to use the return statement in functions in C++. The video will also teach the user how to pass data to a function and have the function return data back for further processing.
The viewer will learn how to user default arguments when defining functions. This method of defining functions will be contrasted with the non-default-argument of defining functions.
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question