top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

C Programming with MySQL

0 votes
421 views 
How do I write a C program to connect MySQL database server?

That is the very prominent question about C Data base connection here we will discuss it step by step

MySQL database does support C program API just like PHP or perl.
The C API code is distributed with MySQL. It is included in the mysqlclient library and allows C programs to access a database.
Many of the clients in the MySQL source distribution are written in C. If you are looking for examples that demonstrate how to use the C API, take a look at these clients. You can find these in the clients directory in the MySQL source distribution.

Requirements

Make sure you have development environment installed such as gcc, mysql development package etc. Following is the list summarize the list of packages to compile program:

mysql: MySQL client programs and shared library
mysqlclient: Backlevel MySQL shared libraries (old libs)
mysql-devel: Files for development of MySQL applications (a must have)
mysql-server: Mysql server itself
gcc, make and other development libs: GNU C compiler

Following instructions should work on any Linux distro or UNIX computer. Here is the small program that connects to mysql server and list tables from mysql database.(download link):

/* Simple C program that connects to MySQL Database server*/
#include <mysql.h>
#include <stdio.h>
main() {
   MYSQL *conn;
   MYSQL_RES *res;
   MYSQL_ROW row;
   char *server = "localhost";
   char *user = "root";
   char *password = "PASSWORD"; /* set me first */
   char *database = "mysql";
   conn = mysql_init(NULL);
   /* Connect to database */
   if (!mysql_real_connect(conn, server,
         user, password, database, 0, NULL, 0)) {
      fprintf(stderr, "%s\n", mysql_error(conn));
      exit(1);
   }
   /* send SQL query */
   if (mysql_query(conn, "show tables")) {
      fprintf(stderr, "%s\n", mysql_error(conn));
      exit(1);
   }
   res = mysql_use_result(conn);
   /* output table name */
   printf("MySQL Tables in mysql database:\n");
   while ((row = mysql_fetch_row(res)) != NULL)
      printf("%s \n", row[0]);
   /* close connection */
   mysql_free_result(res);
   mysql_close(conn);
}

How do I compile and link program against MySQL libs?

MySQL comes with a special script called mysql_config. It provides you with useful information for compiling your MySQL client and connecting it to MySQL database server. You need to use following two options.

  1. Pass --libs option - Libraries and options required to link with the MySQL client library.

    $ mysql_config --libs

Output:

-L/usr/lib64/mysql -lmysqlclient -lz -lcrypt -lnsl -lm -L/usr/lib64 -lssl -lcrypto

  1. Pass --cflags option - Compiler flags to find include files and critical compiler flags and defines used when compiling the libmysqlclient library.

    $ mysql_config --cflags

Output:

  -I/usr/include/mysql -g -pipe -m64 -D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE -fno-strict-aliasing

You need to pass above option to GNU C compiler i.e. gcc. So to compile above program, enter:

$ gcc -o output-file $(mysql_config --cflags) mysql-c-api.c $(mysql_config --libs)

Now execute program:

$ ./output-file

Output: MySQL Tables in mysql database:

columns_priv
db
func
help_category
help_keyword
help_relation
help_topic
host
tables_priv
time_zone
time_zone_leap_second
time_zone_name
time_zone_transition
time_zone_transition_type
user
posted Aug 4, 2014 by Amit Kumar Pandey

  Promote This Article
Facebook Share Button Twitter Share Button LinkedIn Share Button


Related Articles

C Programming: Pointers Basic

A pointer is a variable whose value is the address of another variable or in simple words a variable which points to another variable.

int *myvar;

Here, myvar is a pointer type and can point to a integer variable. The asterisk * you used to declare a pointer is the same asterisk that you use for multiplication.

Few sample declaration

int    *ip;    /* pointer to an integer */
double *dp;    /* pointer to a double */
float  *fp;    /* pointer to a float */
char   *ch     /* pointer to a character */

Sample Program

#include<stdio.h>

int main()
{
  int x = 5;
  int *ptr,**pptr;
  ptr = &x;
  pptr = &ptr;
  return(0);
}

Here x is an integer and ptr is a pointer pointing to the x. pptr is another pointer which is pointing to the ptr. x is having a value as 5 and support the address of x is 100 then value of ptr would be 100. Now assume value of ptr is 500 then pptr would have value as 500.

Null Pointers in C

It is a good practice to assign a NULL value to a pointer variable in case you do not have exact address to be assigned. Pointer that is assigned NULL is called a null pointer.

main ()
{
   int  *ptr = NULL; // ptr is a null pointer here
   printf("The value of ptr is : %x\n", ptr  );
}

Use Case To check for a null pointer one can use if statement as follows:

if(ptr)     /* succeeds if p is not null */
if(!ptr)    /* succeeds if p is null */

Address Operator (&) and pointers

It is denoted by & and used as a prefix of variable and returns the address of variable. It can be used only with variables not with literals (&5 is a invalid statement. Check the following program for the use case - 

#include <stdio.h>

int main ()
{
   int  5 = 5;   /* actual variable declaration */
   int  *ip;        /* pointer variable declaration */

   ip = &i;  /* store address of i in pointer variable*/

   printf("Address of variable i: %x\n", &i);
   printf("Address stored in variable ip: %x\n", ip);
   printf("Value of *ip variable: %d\n", *ip);

   return 0;
}
READ MORE
...