Programming in C++ with SQL [35 points] Background: As we discussed in class, we can write a program just like the mysql command-line program that we used in the previous lab. In this lab, we will be making a light-weight interactive program to interface with a MySQL RDMS. The program will need to do the following:

1. Take an argument from the command-line to specify interactive mode (-I), load (-L) a file into the database, write (-W) the results of a query/queries to a file, drop (-D) a table, create (-C) a table based on some file. [1 point]

a. Interactive mode will operate like the mysql program where it reads from standard input to get queries from a user.

b. Loading a file takes an additional argument of the file (so it will be: -L filename) to parse and load into the database.

c. Reading and writing a file takes two additional arguments of the input and output file (so it will be: -W inputFile outputFile)

d. Dropping a table takes an additional argument of the table name. e. Creating a table takes an additional argument of the file specifying the table that the program will create.

2. Program operation: [1 point]

a. The program will need to use the argument to determine its execution path.

b. The program will need to connect to the database. The database information is given below.

3. Interactive mode (-I): [7 points]

a. Continue to run unless the user enters "quit" or reaches logical end-of-file

b. Take a user input and make it a query

c. Execute the query

d. Access and format the results

e. Display the results to the user

4. Load a file (-L): [7 points]

a. Parse the supplied file.

Each line in this input file has the following form: Product,id:1,pname:'ProductX',price:12,category:'2',manufacturer:'a'

Your program should parse this and create a query that inserts the table Product. The table name will always be the first on a line. The values for the insert sql command will be in the form of "attribute:value".

Thus, the input line above will produce the sql command: Insert into Product (id, pname, price, category, manufacturer) Values( 1, 'ProductX', 12, '2', 'a' );

Note: it can be safely assumed any file give will be structured the same, but values can be different.

b. Generate a query to insert the file's contents into the database

c. Execute the query

d. After each command, write out a statement like this: Data Line 1 Loaded

e. The file load_input.txt gives sample input for this option.

5. Create a table (-C): [7 points]

a. Parse the supplied file

Note: it can be safely assumed any file give will be structured the same, but values can be different.

The first column of the input file is the name of the table; then columns formatted as attribute:value for some arbitrary number of columns.

b. Generate a query to insert the file's contents into the database

c. Execute the query.

d. Note: It should print "Table tableName Created" after execution and where tableName is the name of the new table.

6. Drop a table (-D): [4 points]

a. Generate a query to drop a table specified based on the command-line argument after -D (e.g., ./hw4 -D myTable)

b. Execute the query (Note: It should print "Table myTable Dropped" after execution.

7. Read and write data to file (-W): [8 points]

a. Read each line from the supplied file (list of queries)

Note: this file will include several queries and should execute all queries

b. Each line is a string that is a complete SQL statement.

c. Execute the query

d. Write the results of the query to an output file

Do NOT write empty results

e. The file read_write_input.txt provides sample input for this option.

8. Exception handling: [1 point]

a. Use a similar approach as seen in the lab sample code to appropriate capture the parse exception

#define MYSQLPP_MYSQL_HEADERS_BURIED

#include

#include

#include

#include

#include

#include

#include

void interactive() {

// Connect to database with: database, server, userID, password

mysqlpp::Connection myDB("cse278", "localhost","cse278","S3rul3z");

// Variable to build query string

std::string qString;

// Get user input for query

while(/* reading in lines */ && /* some conditon */) {

// Create a query

try{

// Check query is correct

// Execute query

// Results is a 2D vector of mysqlpp::String objects.

// Print the results.

std::cout << "-----Query Result-----" << std::endl;

for (size_t row = 0; (row < /* result variable */.size()); row++) {

/*

* Some logic to display the results

*/

}

std::cout << "------End Result------" << std::endl;

} catch(mysqlpp::BadQuery e) {

std::cerr << "Query: " << qString <

std::cerr << "Query is not correct SQL syntax" <

}

}

}

std::string generateLoadQuery(std::string& line){

// Create base insert query string

// Split file on commas

// Start building query from split files (table name)

// Strings to hold attributes and values

// Build attribute and value strings

// Form full query string

}

void loadData(std::string& path){

// Open file stream

// Connect to database with: database, server, userID, password

// Some necessary variables for the file IO

// Read file line-by-line

while (/* reading lines */) {

// Create query string from current line

// Create mysql++ query

try{

// Check query is correct

//Execute Query

std::cout << /* feedback it worked - format on assignment */;

} catch(mysqlpp::BadQuery e) {

std::cerr << "Query: " << queryStr<

std::cerr << "Query is not correct SQL syntax" <

}

}

}

int main(int argc, char *argv[]) {

// Ensure files are specified

if(/* some condition */){

std::cerr << /* some message */;

return 1;

}

std::string option = argv[1];

if(/* some condition */) {

/* do something */

}

else if (/* some condition */ && /* some other check */) {

/* do something else */

}

else {

std::cerr << "Invalid input" << std::endl;

return 1;

}

// All done

return 0;

}

• create_input.txt: This file corresponds to the "-C" option
• load_input.txt:This file corresponds to the "-L" option (same as in lab)
• read_write_input.txt: This file corresponds to the "-W" option

create_input.txt

table:myTable1,id:int:not_null:key,pname:varchar(15):not_null,price:float:not_null,region:char(4)

table:myTable2,id:int:not_null:key,pname:char(15):not_null,price:float:not_null,region:char(4):not_null

table:myTable3,id:int:not_null:key,pname:char(15):not_null,price:int,region:char(4):not_null

table:myTable4,id:int,pname:char(15):not_null,price:int,region:char(4):not_null

table:myTable5,id:int:not_null,pname:char(15):not_null,price:int,region:char(4):not_null

table:myTable5,id:int:not_null,pname:char(15):not_null:key,price:int:not_null,region:char(4):not_null

load_input.txt

Product,id:101,pname:'OldDrive',price:7712,category:'Soft',manufacturer:'Google'

Product,id:101,pname:'NewExcel',price:12,category:'Tech',manufacturer:'Microsoft'

Product,id:777,pname:'NewDrive',price:1299,category:'Tech',manufacturer:'Google'

read_write_input.txt

SELECT * FROM Product;

SELECT cname FROM Company;

SELECT pname FROM Product WHERE price > 100;

SELECT id from Product;

CREATE TABLE Test (id int not null, pname char(5) not null);

INSERT INTO Test (id, pname) VALUES (1, 'John');

INSERT INTO Test (id, pname) VALUES (2, 'Mario');

INSERT INTO Test (id, pname) VALUES (3, 'Ben');

INSERT INTO Test (id, pname) VALUES (4, 'Chris');

SELECT pname,id FROM Test ;

SELECT pname,id FROM TestWHERE id > 2 ;

DROP TABLE Test ;