MySQL with C API Tutorial with Dev C++

MySQL with C API Tutorial

Hello, [username]

If you love C, like me and you want to explore C more then this tutorial will take you another step ahead.

First of all know Before you proceed further you need to have some prerequisites ,and they are:-

You know what is programming and how to program.
You know How to C? atleast the basics of C programming Language
You have a bit of concept regarding databases and MySQL.
In all the above cases your answer is yes.

If you don't C but you know any other programming language like Java, python, VB etc, then I would suggest you to go HERE or HERE and learn the basics of C programming.

If you don't know what is MySQL then I will recommend you to go HERE and learn the Basics of MySQL.You can also refer to the MySQL Documentation HERE or Download The Whole Documentation HERE

So, lets get Started, but First you will be needing some tools if they are not pre-installed, you can get them from the links below:-
1. Dev C++ IDE

2. MySQL Community Server

Also you need to download the MySQl Devpak for Dev C++, and for further reading an eBook on MySQL with C, so for convenience I have compiled them together and you can download them from the mediafire link below

3. MySQL Devpak for Dev C++ and eBook

Now we are good enough to start our main purpose, So lets get started.

See How to Install MySQL
See How to Install Dev C++

By now you must have installed Dev C++ and MySQL Server on your PC.Therefore now I will guide you how to use MySQL with C in Dev C++.

Follow the steps below:-

Step 1:Installation of MySQL Devpack
You have to install the MySQL Devpack that I have provided for download.Now here's how you will have to install it.

(a.) Now go to location where you have downloaded the mysql devpack and then double click it

(b.) Now a popup window opens asking you to install the dev package.Go on clicking next and install.The following images will make it clear.

(c.) Now when you click Finish , another window will appear which shows the packages that has been installed.We are not concerned with that, just close the window.

(d.) Now from Start menu of your task bar start Dev C++ IDE.

(e.) Now on the main menu Strip of the IDE Goto Tools->Compiler Options

(f.) Another window pops up , there check the checkbox as shown in the image below and then write -lmysql on the text area just below it and click Ok.See the image below.

Now Since we have installed an external package so the Dev C++ compiler is not configured for that and hence we will not be able to work with mysql until and unless the compiler is configured properly and hence the command -lmysql is a linker command that creates a link between the Dev C++ compiler and the mysql Library.

(g.) Now lets get our hands on writing some codes.

Code:
#include <stdlib.h>
#include <stdio.h>
#include <conio.h>
#include <windows.h>
#include <mysql/mysql.h>

static char *opt_host_name = "localhost"; /* server host (default=localhost) */
static char *opt_user_name = "root"; /* username (default=login name) */
static char *opt_password = ""; /* password (default=none) */
static unsigned int opt_port_num = 3306; /* port number (use built-in value) */
static char *opt_socket_name = NULL; /* socket name (use built-in value) */
static char *opt_db_name = "mysql"; /* database name (default=none) */
static unsigned int opt_flags = 0; /* connection flags (none) */

int main (int argc, char *argv[])
{
    MYSQL *conn; /* pointer to connection handler */
    MYSQL_RES *res; /* holds the result set */
    MYSQL_ROW row;
    
    /* initialize connection handler */
    conn = mysql_init (NULL);
    
    /* connect to server */
    mysql_real_connect (conn, opt_host_name, opt_user_name, opt_password,
    opt_db_name, opt_port_num, opt_socket_name, opt_flags);
    
    /* show tables in the database (test for errors also) */
    if(mysql_query(conn, "show tables"))
    {
        fprintf(stderr, "%s \n", mysql_error(conn));
        printf("Press any key to continue. . . ");
        getch();
        exit(1);
    }    
    
    res = mysql_use_result(conn); /* grab the result */
    
    printf("Tables in database\n");
    while((row = mysql_fetch_row(res)) != NULL)
        printf("%s \n", row[0]);
    
    /* disconnect from server */
    mysql_close (conn);
    
    printf("Press any key to continue . . . ");
    getch();
    return 0;
} /* end main function */

Copy and Paste the above code in your IDE , after you have created a new source file from File->New->Source File

(h.)Now this is an important step.
Note:You have to set the password of the mysql server (that you have set during installation) in the above source code

Quote:static char *opt_password = "Insert your MySQL Server Password Here";

In my case my password is "root", So my source has this statement:

Quote:static char *opt_password = "root";

See the image below:-

(i.) Now we are ready to Compile and Run the Code file.To compile and run the source file press Ctrl+F9
If you have done everything right in the above steps then you'll get the following command line output.

[Image: 3e55a038-5a4f-4f04-a248-f0dc7be41dbb.jpg]

Step 2:What We are trying to do?

Now what we are trying to do is very simple.We are connecting to the database mysql with the default user root@localhost and see all the tables that are present in this database.

Step 3:Functions Used in the Code

1.) MYSQL:-
This structure represents a handle to one database connection. It is used for almost all MySQL functions. Do not try to make a copy of a MYSQL structure. There is no guarantee that such a copy will be usable.

2.) MYSQL_RES
This structure represents the result of a query that returns rows (SELECT, SHOW, DESCRIBE, EXPLAIN). The information returned from a query is called the result set in the remainder of this section.

3.) MYSQL_ROW
This is a type-safe representation of one row of data. It is currently implemented as an array of counted byte strings. (You cannot treat these as null-terminated strings if field values may contain binary data, because such values may contain null bytes internally.)

4.) mysql_init()

Syntax

Quote:MYSQL *mysql_init(MYSQL *mysql)

Description

Allocates or initializes a MYSQL object suitable for mysql_real_connect(). If mysql is a NULL pointer, the function allocates, initializes, and returns a new object. Otherwise, the object is initialized and the address of the object is returned. If mysql_init() allocates a new object, it is freed when mysql_close() is called to close the connection.

5.) mysql_real_connect()

Syntax

Quote:MYSQL *mysql_real_connect(MYSQL *mysql, const char *host, const char *user, const char *passwd, const char *db, unsigned int port, const char *unix_socket, unsigned long client_flag)

Description

mysql_real_connect() attempts to establish a connection to a MySQL database engine running on host. mysql_real_connect() must complete successfully before you can execute any other API functions that require a valid MYSQL connection handle structure.

Parameters:-

a.) For the first parameter, specify the address of an existing MYSQL structure. Before calling mysql_real_connect(), call mysql_init() to initialize the MYSQL structure.

b.) The value of host may be either a host name or an IP address.

c.) The user parameter contains the user's MySQL login ID.

d.) The passwd parameter contains the password for user.

e.) The user and passwd parameters use whatever character set has been configured for the MYSQL object. By default, this is latin1,

f.) db is the database name.

g.) If port is not 0, the value is used as the port number for the TCP/IP connection. Note that the host parameter determines the type of the connection.

h.) If unix_socket is not NULL, the string specifies the socket or named pipe to use. Note that the host parameter determines the type of the connection.

i.) The value of client_flag is usually 0, but can be set to a combination of the following flags to enable certain features.

6.) mysql_query()

Syntax

Quote:int mysql_query(MYSQL *mysql, const char *stmt_str)

Description

Executes the SQL statement pointed to by the null-terminated string stmt_str. Normally, the string must consist of a single SQL statement without a terminating semicolon (“;”) or \g. If multiple-statement execution has been enabled, the string can contain several statements separated by semicolons.

7.) mysql_use_result()

Syntax

Quote:MYSQL_RES *mysql_use_result(MYSQL *mysql)

Description

After invoking mysql_query() or mysql_real_query(), you must call mysql_store_result() or mysql_use_result() for every statement that successfully produces a result set (SELECT, SHOW, DESCRIBE, EXPLAIN, CHECK TABLE, and so forth). You must also call mysql_free_result() after you are done with the result set.

8.) mysql_error()

Syntax

Quote:const char *mysql_error(MYSQL *mysql)

Description

For the connection specified by mysql, mysql_error() returns a null-terminated string containing the error message for the most recently invoked API function that failed.

9.) mysql_fetch_row()

Systax

Quote:MYSQL_ROW mysql_fetch_row(MYSQL_RES *result)

Description

Retrieves the next row of a result set.

10.) mysql_close()

Syntax

Quote:void mysql_close(MYSQL *mysql)

Description

Closes a previously opened connection. mysql_close() also deallocates the connection handle pointed to by mysql if the handle was allocated automatically by mysql_init() or mysql_connect().

Quote:Note: The above details about the various functions has been taken from the mysql C API documentation.For More Details Visit Here

Step 4:How Does the Code functions work ?

Quote:#include <mysql/mysql.h>

Including the above header enables us to use to mysql functions and other objects.

Quote:static char *opt_host_name = "localhost";
static char *opt_user_name = "root";
static char *opt_password = "";
static unsigned int opt_port_num = 3306;
static char *opt_socket_name = NULL;
static char *opt_db_name = "mysql";
static unsigned int opt_flags = 0;

In the above line we store the host name , username and password and they have been defined as static so that their value cannot be changed throughout the length of the code.We are also taking the port through which we want to connect to mysql. The default port being 3306.The we are taking the database name to which we wish to connect.

Quote:MYSQL *conn;
MYSQL_RES *res;
MYSQL_ROW row;

Here conn is the connection object and res is the resultset.

Quote:conn = mysql_init (NULL);
mysql_real_connect (conn, opt_host_name, opt_user_name, opt_password,opt_db_name, opt_port_num, opt_socket_name, opt_flags);

With the above statements we initialize the connection handler and using mysql_real_connect() function we connect to the mysql database where various values are passed as parameters.

Quote:if(mysql_query(conn, "show tables"))
{
fprintf(stderr, "%s \n", mysql_error(conn));
printf("Press any key to continue. . . ");
getch();
exit(1);
}

Here, we are executing a mysql query which shows all tables in the database to which you have connected.If for some reason the queries fails to execute then the mysql error will be fetched using the function mysql_error().

Quote:res = mysql_use_result(conn); /* grab the result */

printf("Tables in database\n");
while((row = mysql_fetch_row(res)) != NULL)
printf("%s \n", row[0]);

Here what happens is the resultset cursor grabs result and we print all the tables that are present simply by iterating.

Quote:mysql_close ();

When we are done with our purpose of db then we shall close the mysql connection using the above function.It is really important for us to close the connection.

Quote:Please Note:-
The Source code has been taken from the eBook that I have provided for download above.

http://www.freewebs.com/dragonstein/msql_c.html
In the above link I saw a demonstration of MySQL with Dev C++.But I thought that its is not enough and it lacks perfection and might be troublesome for newbies so I made this tutorial,The credits goes to me for the tutorial, design and presentation of the fact.
Please leave a reply if you liked my Post.Thank you.