Due date

11:00 am, Monday, March 25

Students may work together on this assignment, however each student must complete Part I of this assignment on their own laptop and using their own AWS virtual machine.

The code in Part II can be written as a team but must be submitted to GitHub by everyone on the team. Everyone must have their team’s server running on their AWS Linux instance.

Part I – Setting up the Infrastructure

Creating a MySQL Database in AWS

Follow steps 1-3 in the AWS tutorial found here. I’ve completed the tutorial myself and found a few differences between what was written in the tutorial and what I found in in the AWS console. You can read my observations here.

Create a Connection Profile in MySQL Workbench

Let’s create a connection profile so that we can connect to the database quickly.

In MySQL Workbench, disconnect from your database by closing the window.
Then choose Database > Manage Connections in the menu bar.
Select New in the bottom left-hand corner.
Enter ContactsDB in the Connection name field.
Enter the database’s host URL in the Hostname field, the Master username in the Username field, and the Master password in the Password field.

Press Test Connection. If MySQL Workbench cannot connect to your database, check your entries, otherwise press Close.

Creating a Contacts Table in the MySQL Database

In MySQL Workbench select Database > Connect to Database in the menu bar. Select the Stored Connection that you just created and press OK.

Select the Schemas tab and double-click on the name of the database schema you created in the AWS RDS console. At the bottom on the left side of the window you should see Schema followed by the name of the schema you selected.
Enter the CREATE TABLE command shown in the screenshot below. Then press the lightning bolt icon to execute the command.
If successful, you should see 0 row(s) affected in the Response column at the bottom of the window.

In the left side bar, expand the schema to reveal Tables. Right-click Tables and select Refresh All. You should see the column names of the new table.

Install the MySQL C API Libraries

To install the MySQL C development libraries run the following command on your AWS server.

$sudo yum install mysql-devel

Choose y when asked if this is ok.

Part II – Connecting Your Server to your MySQL Server

Code Submission

Please create a folder named mysql_insert_server in your repository. Include a Makefile that compiles your program using $make. Also include a file named README file that includes the names of your teammates and the public IP address of your AWS server.

Include in the folder a client and a server. The client code should be in a file named contacts.c and the server should be in a file named mysql_insert_server.c.

The client can be the same client that you wrote in the previous assignment with one small change. The client should only connect to the server if the user chooses menu option 1 and the client should disconnect from the server before before reprinting the menu.

The server should process INSERT requests from the client and insert the contacts into the MySQL database. Each time a client connects to the server, the server should process only one request and after the result code is sent to the client, the server should close the connection.

MySQL C API Documentation

Documentation in the MySQL 8.0 Reference manual on how to use the MySQL C libraries in client code can be found here. I’ve also found a nice tutorial on the basics here.

Connecting to a MySQL Server

A c program that uses the MySQL C libraries to create a new table in the database is shown below. Your server needs to connect to your MySQL server, but does not need to programmatically create the table. You can create the table via MySQL Workbench.

#include <mysql.h>
#include <stdlib.h>
#include <stdio.h>

int main(int argc, char **argv)
{
  MYSQL *con = mysql_init(NULL);
  char *hostname = "your-host-url";
  char *username = "your-master-username";
  char *password = "your-master-password";
  char *schema = "your-database-schema";

  if (con == NULL){
      fprintf(stderr, "%s\n", mysql_error(con));
      exit(1);
  }

  if (mysql_real_connect(con, hostname, username, password, schema, 0, NULL, 0) == NULL){
      fprintf(stderr, "%s\n", mysql_error(con));
      mysql_close(con);
      exit(1);
  }

  char *query = "CREATE TABLE Contacts_T ( "
                "first_name VARCHAR(24), "
                "last_name VARCHAR(48), "
                "email VARCHAR(36), "
                "phone BIGINT, "
                "PRIMARY KEY(first_name, last_name))";

  if (mysql_query(con, query)){
      fprintf(stderr, "%s\n", mysql_error(con));
      mysql_close(con);
      exit(1);
  }

  mysql_close(con);
  exit(0);
}

Compiling Your Server With the MySQL Libraries

Assuming the code is in a file named mysql_insert_server.c, the command to compile the program with the MySQL library is shown below.

$gcc -O mysql_insert_server.c `mysql_config --cflags --libs` -o server

Note that that the command uses back ticks (below the tilde ~ on your keyboard) and not single quotes.

Inserting Values in a MySQL Table

We can use mysql_query for inserting data into a table as well. Below is code that creates an INSERT statement using sprintf and calls mysql_query to perform the INSERT command.

  char *first_name = "Joe";
  char *last_name = "Black";
  char *email = "joe@gmail.com";
  int phone = 3155551212;

  char query[200];
  sprintf(query, "INSERT INTO Contacts_T VALUES('%s','%s','%s',%d)", first_name,
    last_name, email, phone);

  if (mysql_query(con, query)){
      fprintf(stderr, "%s\n", mysql_error(con));
      mysql_close(con);
      exit(1);
  }

Retrieving Data From the Database

Retrieving data from a MySQL database is not as easy as inserting data, but it’s not difficult. Below we retrieve all of the contacts (including all of their data) in the Contacts_T table and print their values to the console.

  if (mysql_query(con, "SELECT * FROM Contacts_T")){
      fprintf(stderr, "%s\n", mysql_error(con));
      mysql_close(con);
      exit(1);
  }

  MYSQL_RES *result = mysql_store_result(con);

  if (result == NULL) {
      fprintf(stderr, "%s\n", mysql_error(con));
      mysql_close(con);
      exit(1);
  }

  int num_fields = mysql_num_fields(result);

  MYSQL_ROW row;

  while ((row = mysql_fetch_row(result))) {
      for(int i = 0; i < num_fields; i++) {
          printf("%s ", row[i] ? row[i] : "NULL");
      }
      printf("\n");
  }