Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1987
  • Last Modified:

mysql C++ Connector multiple inserts using a transaction.

Hello:

I have the following schema called doctor in the code snippet below.
I want to do an insert across multiple related tables using transaction.
A sample transaction when inputted through the mysql prompt is shown in the code snippet below.
 I also have implemented the following code(see snippet below) using Connector/C++ which tries to emulate the  aforementioned transaction using the Connector C++ API.

The problem is that when I run the C++ Code (see snippet below) only the Patient table gets populated. The rest of the tables are not.
Note that each of the classes and their attributes used in the code has a direct mapping to the tables.
For example The Patient Class is defined as shown in the Patient.h file. You can see that its attributes are mapped from the Patient schema. Further wherever there is a one-to-many relation, composition is used and hence you see the vector storing the "many side".
I would appreciate if you could inspect the C++ code below and let me know what I am doing wrong?

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;

CREATE TABLE DRUG_WHEN (
pk_drug_when INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
when_to_take ENUM('BeforeBreakfast','AfterBreakFast','BeforeLunch','AfterLunch','BeforeDinner','AfterDinner'),
fk_drug INT NOT NULL,
FOREIGN KEY (fk_drug) REFERENCES DRUG(pk_drug)
)ENGINE=innodb;

Open in new window

void DataInterface::createNewPatient(Patient &in)
{
	try
	{
		sql::Driver* mDriver;
		sql::Connection *con;
		sql::Statement *stmt;
		sql::ResultSet *res;
		sql::PreparedStatement *pstmt;

		mDriver = get_driver_instance();
		con = mDriver->connect("tcp://127.0.0.1:3306", "root", "root");
  
		con->setSchema("doctor");

		stmt = con->createStatement();
		stmt->execute("START TRANSACTION");
		
		pstmt = con->prepareStatement("INSERT INTO PATIENT (pk_patient,first_name,last_name,mobile_number,e_mail,gender) VALUES (NULL,?,?,?,?,?)");
		pstmt->setString(1,in.fname->c_str());
		pstmt->setString(2,in.lname->c_str());
		pstmt->setString(3,in.mob_number->c_str());
		pstmt->setString(4,in.e_mail->c_str());
		pstmt->setString(5,in.gender->c_str());
		pstmt->executeUpdate();		
		std::vector <Session> mSessionVector = in.getSessions();
		for(int i =0 ; i < mSessionVector.size() ; i++)
		{
			Session mSession = mSessionVector[i];
			sql::PreparedStatement *qstmt;
			qstmt = con->prepareStatement("INSERT INTO SESSION (pk_session,fk_patient,session_data,diagnosis,session_type,session_date) VALUES (NULL,LAST_INSERT_ID(),?,?,?,CURRENT_DATE())");
			qstmt->setString(1,mSession.session_data->c_str());
			qstmt->setString(2,mSession.diagnosis->c_str());
			qstmt->setString(3,mSession.session_type->c_str());
			qstmt->executeUpdate();

			Prescription* mPres = mSession.getPrescription();
			pstmt = con->prepareStatement("INSERT INTO PRESCRIPTION (pk_prescription,duration_valid_for,fk_session) VALUES (NULL,?,LAST_INSERT_ID())");
			pstmt->setInt(1,mPres->duration);
			stmt->execute("SET @myval = LAST_INSERT_ID()");
			std::vector<Drug> mDrugVector = mPres->mDrugs;
			for (int j = 0 ; j < mDrugVector.size() ; j++)
			{
				Drug mDrug = mDrugVector[j];
				pstmt = con->prepareStatement("INSERT INTO DRUG VALUES (NULL,@myval,?,?)");
				pstmt->setString(1,mDrug.drug_name->c_str());
				pstmt->setDouble(2,mDrug.dosage);
				stmt->execute("SET @val = LAST_INSERT_ID();");
				std::vector<Drug_When> mDrugWhenVector = mDrug.mDrugWhen;
				for (int k = 0 ; k < mDrugWhenVector.size() ; k++)
				{
					Drug_When mDrugWhen = mDrugWhenVector[k];
					pstmt = con->prepareStatement("INSERT INTO DRUG_WHEN VALUES (NULL,?,@val)");
					pstmt->setString(1,mDrugWhen.when->c_str());
				}
			}
		}
			
			con->commit();
	}
	catch (sql::SQLException &e)
	{
		std::cout << "error!  " << e.what() << std::endl;
		
	}
};

Open in new window

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

	int pk_patient;
	std::string *fname;
	std::string *lname;
	std::string *mob_number;
	std::string *e_mail;
	std::string *gender;
	std::vector<Session> mSessions;

	void addSession(Session &in);
	std::vector<Session> getSessions();

	
};

# endif

Open in new window

/* sample transaction that works when supplied to the mysql prompt
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
arjoshi77
Asked:
arjoshi77
1 Solution
 
johanntagleCommented:
I haven't done C++ or C for quite a while, but are you certain that mSessionVector and mDrugVector gets populated?  Maybe you can print the sizes of each as part of your debug code.
0
 
arjoshi77Author Commented:
johantangle:

Thanki yuu for pointing me to that direction. I have also forgotten to add the executeUPdate() method after every insert. Solution solved
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now