Open Source

To create tables with records in them

In this introduction to creating C programs to interact with SQL databases, Alexander Prohorenko walks you through the creation, compilation, and execution of a program that will connect to a MySQL database, read the database, and print the results.


"The main goals of MySQL are speed, robustness and ease of use..."

—The MySQL Server Developers


In this Daily Drill Down, I’m going to describe how you can code and develop your own C programs that will interact with MySQL databases. It goes without saying that this will not be a full guide to all functions and possibilities of MySQL and its C API. Instead, this is just a manual for creating basic programs with the help of a search engine like MySQL. My target system will be one of the most powerful releases of UNIX-clone systems—FreeBSD. However, all source code mentioned here should not be fixed or patched to be ported to other platforms. Before I start, I’d like to suggest that you familiarize yourself with the C programming language and with simple (basic) SQL commands. This will help you to better understand this article.

What is MySQL?
MySQL is a true multiuser, multithreaded, SQL database server. Structured Query Language (SQL) is the most popular and standardized database language in the world. MySQL is a client/server implementation that consists of a server daemon (mysqld) and many different client programs and libraries, such as mysqlshow (a front end for accessing MySQL-handled databases with read-only permissions) and mysqladmin (a client program for managing administrative tasks).

As you may know, SQL is a standardized language that makes it easy to store, update, and access information. For example, you can use SQL to retrieve product information and store customer data for a Web site. MySQL is also fast and flexible enough to allow you to store logs and even pictures! (Actually, I should say, it is flexible enough to store any type of data.)

MySQL was originally developed as a SQL server that could handle very large databases faster than any database a vendor could then offer (using inexpensive hardware).

MySQL has been used since 1996 in various environments. For example, MySQL creators and developers are now using the MySQL server in an environment with more than 40 databases containing 10,000 tables, 500 of which have more than 7 million rows. This is about 100 GB of mission-critical data.

The base upon which MySQL is built is a set of routines that have been used in a highly demanding production environment for many years. Although MySQL is still under development, it already offers a rich and very useful function set.

The most important features
So what are the MySQL features that make it so popular? Let’s list them:
  • It’s fully multithreaded—it can easily use multiple CPUs if available.
  • Use your favorite language (C, C++, Eiffel, Java, Perl, PHP, Python, and Tcl APIs) to interact with MySQL-driven databases without any problems of porting.
  • It works on many different platforms.
  • It has many column types—signed/unsigned integers 1, 2, 3, 4, and 8 bytes long, FLOAT, DOUBLE, CHAR, VARCHAR, TEXT, BLOB, DATE, TIME, DATETIME, TIMESTAMP, YEAR, SET, and ENUM types.
  • It offers very fast joins using an optimized one-sweep multijoin.
  • It has full operator and function support in the SELECT and WHERE parts of queries. For example:
    mysql> SELECT CONCAT(first_name, " ", last_name) FROM table_name
    WHERE income/dependents > 10000 AND age > 30;
  • SQL functions are implemented through a highly optimized class library and have been optimized to be as fast as they can possibly be. Under normal use, there won’t be any memory allocation after query initialization.
  • It offers full support for SQL GROUP BY and ORDER BY clauses, and support for group functions, such as COUNT(), COUNT(DISTINCT), AVG(), STD(), SUM(), MAX(), and MIN().
  • It also offers support for LEFT OUTER JOIN with ANSI SQL and ODBC syntax.
  • MySQL gives you the ability to mix tables from different databases within the same query.
  • MySQL offers a privilege and password system that’s very flexible and secure and that allows host-based verification. Passwords are secure because all password traffic, when connecting to a server, is encrypted.
  • It offers ODBC (Open Database Connectivity) support for Windows 95 (with source), all ODBC 2.5 functions, and many others. For example, you can use Access to connect to your MySQL server.
  • MySQL features very fast B-tree disk tables with index compression.
  • Sixteen indexes per table are allowed. Each index may consist of from one to 16 columns or parts of columns. The maximum index length is 256 bytes (this may be changed when compiling MySQL). An index may use a prefix of a CHAR or VARCHAR field.
  • It offers fixed-length and variable-length records.
  • It also features in-memory hash tables that are used as temporary tables.
  • All columns have default values. You can use INSERT to insert a subset of a table's columns; those columns not explicitly given values are set to their default values.
  • It uses GNU automake, autoconf, and libtool for portability.
  • It was written in C and C++ and tested with a broad range of compilers.
  • MySQL offers a very fast thread-based memory allocation system.
  • It has no memory leaks; it was tested with a commercial memory-leakage detector (purify).
  • It includes myisamchk, a very fast utility for table checking, optimization, and repair.
  • It has full support for several different character sets.
  • Aliases on tables and columns are allowed as in the SQL92 standard.
  • DELETE, INSERT, REPLACE, and UPDATE return how many rows were changed (affected). It’s possible to return the number of rows matched instead by setting a flag when connecting to the server.
  • Function names do not clash with table or column names.
  • Clients may connect to the MySQL server using TCP/IP connections, UNIX sockets, or named pipes under NT.
  • The MySQL-specific SHOW command can be used to retrieve information about databases, tables, and indexes. The EXPLAIN command can be used to determine how the optimizer resolves a query.

For UNIX and OS/2 platforms, MySQL is basically free; for Microsoft platforms, you must get a MySQL license after a trial time of 30 days.

First stage: Creating the database and defining variables
Let’s start from the very beginning in creating our C program. Before writing any piece of code, you need an existing database to work with. Since you do not have a working database, I’ll show you how to create one.
You need to have root privileges on the server with MySQL in order to create it.green@pacific:~/# mysql mysql

This command will run the MySQL monitor and open a database with the name mysql, which contains all the information needed to configure our server. Now, you’ll enter SQL requests at the monitor prompt to create the new database and add a new user. Let’s call our database qwerty: Click here to view the SQL requests.
mysql> CREATE DATABASE qwerty;
Query OK, 1 row affected (1.92 sec)

mysql> INSERT INTO mysql.user VALUES ('localhost', 'zzz', password('zzzUser'),
'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y');
Query OK, 1 row affected (0.42 sec)

This request will add new user zzz@localhost with password zzzUser into the user table of the mysql database. Now, the MySQL server will use host-based authentication for the zzz user, and this user’s accesses will be limited to localhost only. The purpose of the user table is to manage users’ accounting information. Click here to view the command.
mysql> INSERT INTO mysql.db VALUES ('localhost', 'qwerty', 'zzz',
'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y');
Query OK, 1 row affected (2.42 sec)

The above command will allow user zzz@localhost to work with database qwerty with full rights.
All actions and codes mentioned in this article are used only for demonstration purposes and, in most cases, won’t be the same on the running system. For example, providing full access rights for a dummy user is poor practice from a security point of view. You should limit your MySQL users to only the databases and services they really need.
Quit the currently running connection with
mysql> quit

Next, you have to reload the mysql databases. You could do so with this command:
green@pacific:~/# mysqladmin reload

Now that you have an empty database, you should create a few tables with records in them. Click here to view the commands.
green@pacific:~/# mysql qwerty
mysql> DROP TABLE IF EXISTS qwerty.realtest;
mysql> CREATE TABLE qwerty.realtest (one INT(8), two INT(8), three INT(8));
mysql> INSERT INTO qwerty.realtest VALUES (100, 200, 300);
mysql> INSERT INTO qwerty.realtest VALUES (400, 500, 600);
mysql> INSERT INTO qwerty.realtest VALUES (700, 800, 900);
mysql> quit

And so goes the initial creation. You can create your own tables, fill them with your own junk data, or whatever—it doesn't matter.

Now, try to create your first C program that will interact with the MySQL database.
#include <mysql.h>

You need only that header file from MySQL.

Next, you’ll define the common variables like this:
#define CONNECT_HOST   "localhost"
#define CONNECT_USER   "zzz"
#define CONNECT_PSW    "zzzUser"
#define CONNECT_DB     "qwerty"
#define CONNECT_TABLE  "realtest"


These variable names are fairly obvious: CONNECT_HOST is a hostname (FQDN is okay) of the MySQL server, CONNECT_USER and CONNECT_PSW are the user’s login name and password to connect to the server, CONNECT_DB is the name of database I’ll work with, and CONNECT_TABLE is the name of the table in my database I’m going to address.

Then, I’ll define the global variables.
MYSQL *con;
MYSQL_ROW row;
MYSQL_RES *res;


Now, it’s time to define variables that will indicate any errors during the process.
#define E_NOMEM     4
#define E_NOCONN    5
#define E_NOPERM    6
#define E_QUERYFAIL 7
#define E_STOREFAIL 8
#define E_OK        0
#define E_OPENDB   -1
#define E_SENDSQL  -2


Second stage: Programming functions
Now, what functions must you write in order to work with MySQL server?
int    open_db_connection (void);
void   close_db_connection (void);
int    send_sql_request (const char *reqstr);


The whole process of interaction with MySQL database follows these steps:
  1. Connect to the MySQL database.
  2. If successful, go to step 3; otherwise, report an error and quit.
  3. Send a SQL request.
  4. If successful, go to step 5; otherwise, report an error, close the connection with the database, and quit.
  5. Parse the results and perform the necessary operations.
  6. Close the connection with the MySQL database.

Here’s a piece of code from the open_db_connection() function:
int
open_db_connection (void)
{
             int rv;
         MYSQL *init;

         init = mysql_init (NULL);
if (init == NULL) return E_NOMEM;
con = mysql_real_connect (init, CONNECT_HOST, CONNECT_USER,
 CONNECT_PSW, CONNECT_DB, 0, NULL, 0);
if (con == NULL) return E_NOCONN;
rv = mysql_select_db (con, CONNECT_DB);
if (rv != 0) return E_NOPERM;
             return E_OK;
}


This function will return the following:
  • E_NOMEM—There wasn't enough memory to allocate a new object.
  • E_NOCONN—The connection was unsuccessful.
  • E_NOPERM—The user doesn't have permission to use the database.
  • E_OK—Everything was passed successfully.

The following function simply closes the MySQL server connection:
void
close_db_connection (void)
{
         mysql_close (con);
}


Third stage: SQL requests
Now, I will show you how SQL requests should be sent.
int
send_sql_request (const char *reqstr)
{
         int rv;

rv = mysql_query (con, reqstr);
if (rv != 0) return E_QUERYFAIL;
res = mysql_store_result (con);
if (res == NULL) return E_STOREFAIL;
return E_OK;
}


A few words about returned values:
  • E_QUERYFAIL will be returned if the query failed.
  • E_STOREFAIL will be returned if an error occurred while storing data.
  • E_OK will be returned if everything was successful.

Finally, you’ll get to the main part of program, which will work with the MySQL database. (I’ll comment on this pseudo-code as necessary.)
int
main (void)
{
         int err, i = 0;
      char request_string[BUFSIZ];

         for (i = 0; i != BUFSIZ; i++) request_string[i] = '\0';


Here I’m using the request_string variable to handle my SQL request. Later, I’ll send the variable to the MySQL server. But before using it, you should make sure it’s empty:
if ((err = open_db_connection ()) != E_OK)
{
         printf ("open_db_connection () fails with %d!\n", err);
         return E_OPENDB;
}


Now, I’ll set up a connection to the MySQL server. If I get an error, I’ll break an execution with the return code of E_OPENDB, which means an error occurred during the setting up of a connection. Otherwise, I’ll proceed with no notification.
sprintf (request_string,
"SELECT one, two, three FROM %s.%s ORDER BY three, two, one;",
 CONNECT_DB, CONNECT_TABLE);


Now, I’m going to prepare my request_string variable for sending to the SQL server; in other words, I’m putting an SQL request there. The SQL request looks like this:
SELECT one, two, three FROM qwerty.realtest
ORDER BY three, two, one;


Here’s a sample request string:
if ((err = send_sql_request (request_string)) != E_OK)
{
         printf ("send_sql_request () fails with %d!\n", err);
         return E_SENDSQL;
}


Now, I’ll try to send my SQL string (request) to the MySQL server. If I get any error, I’ll break the execution with the return code of E_SENDSQL, which means an error sending the SQL request to the server; otherwise, I’ll proceed with no notification.
while ((row = mysql_fetch_row (res)) != 0)
         printf ("      %s %s %s\n", row[0], row[1], row[2]);
mysql_free_result (res);


At last, it’s time to fetch the SQL response from the server, which should have the data I requested earlier. I’m using a mysql_fetch_row() system call from the mysql library, which fetches an answer from the SQL server and puts it into the row variable array.
        close_db_connection ();
         return E_OK;
}


That's all!

Compiling and executing
A few words about compiling and executing are in order. To compile this sample program properly, you must link your object file with the libmysql library. I used the GNU C (GCC) compiler to build these sources, and I assume that libmysql will be known to the linker. If not, you may have to manually pass libmysql.a with full path to the linker. The string I used to compile it on FreeBSD looks like this:
gcc -o mysqldemo mysqldemo.c -lmysql

and on Solaris, it looks like this:
gcc -o mysqldemo mysqldemo.c -lmysql -lnsl -lsocket

where mysqldemo.c is my program. The Solaris operating system queries two extra libraries to be linked with (-lnsl and -lsocket) so your program can work with networking functions and sockets. In the program itself, this isn’t necessary, but libmysql uses numerous socket operations.

As for the C compiler, you may use any one you like without altering your sources (though the compilation process may vary a bit).

Upon execution of this code, the results should look like this:
300 200 100
600 500 400
900 800 700


Conclusion
In this Daily Drill Down, I described just the basics of programming using the MySQL C API. MySQL holds a stable position among SQL servers for UNIX and Windows platforms because of its numerous features and abilities, and it will surely land a leading position for commercial applications. In my opinion, it’s well worth your time to get very familiar with this API.
The authors and editors have taken care in preparation of the content contained herein but make no expressed or implied warranty of any kind and assume no responsibility for errors or omissions. No liability is assumed for any damages. Always have a verified backup before making any changes.

Editor's Picks

Free Newsletters, In your Inbox