MySQL C API by Example

Download Kayoty, the spyware detector
Kayoty now available from

Database Programming in C

Client Server Programming in C

MySQL C API by Example

by Jahan jahan@geocities.com  http://www.geocities.com/jahan.geo

Version: 1.2.1

Preface

Pre-requisite

Basic Structure of C/C++ Programs that uses MySQL C/C++ API

Compiling and Running

```
Compiling and Running in UNIX
```
```
Compiling and Running in Visual C++
```

Initialization Examples

```
Initializing MySQL API
```
```
Checking Client Version
```
```
Checking Server Version
```
```
Logging into MySQL Server
```
```
Changing user
```
```
Selecting a database
```

Data Definition Examples

```
Creating Database
```
```
Deleting Database
```
```
Creating Tables
```
```
Editing Tables
```
```
Deleting Tables
```
```
Creating Index
```
```
Deleting Index
```

Data Manipulation Examples

```
Adding records to table
```
```
Find records from table
```
```
Editing existing records from table
```
```
Deleting exisitng records from table
```

API Examples

my_ulonglong mysql_affected_rows(MYSQL *mysql)

mysql_close(MYSQL *mysql) - almost all of the examples calls mysql_close()

mysql_create_db(MYSQL *mysql, const char *db)

mysql_change_user(MYSQL *mysql, const char *user, const char *password, const char *db)

mysql_drop_db(MYSQL *mysql, const char *db)

mysql_get_client_info(void)

mysql_get_server_info(MYSQL *mysql)

mysql_init(MYSQL *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 int client_flag)

mysql_real_query(MYSQL *mysql, const char *query, unsigned long length)

mysql_select_db(MYSQL *mysql, const char *db)

mysql_options(MYSQL *mysql, enum mysql_option option, const char *arg)

strmov(char *dstst,char * src)

CGI Examples 

JPEG output from JPEG field

Utility Functions Examples

strmov(char *dstst,char * src)
Using my_ulonglong in printf

Preface

I see that lots of c/c++ authors are looking for MySQL API examples in c/c++. So I thought to make MySQL C API
examples in this web site. And in near future I will publish this as book with a full web system 
as bonus.

Since my return to my country Bangladesh, I've got so much time to myself and thought to contribute
to the GNU applications that I use most ( ie, MySQL,FreeBSD,CGICC,GCC), while my country and government
catches up with me. (phhhhoooooooo)

Prior to this I have contributed the mysql_last_value(), released http://www.DhakaStockExchangeGame.com
http://www.NYSEGame.com and now going to release this.

Aftab Jahan Subedar
Subedar Technologies
Subedar Baag
Bibir Bagicha #1
North Jatrabari
Dhaka 1204
Bangladesh
http://www.DhakaStockExchangeGame.com/
http://www.NYSEGame.com/
http://www.CEOBangladesh.com/
http://www.geocities.com/jahan.geo/ - mysql_last_value() available here.
Phone://+88027519050
jahan@bol-online.com

Pre-requisite

You should know how to use C/C++, and what is MySQL.

Basic Structure of C/C++ Programs that uses MySQL C/C++ API

All programs must include <mysql/mysql.h> as the last include.

Define MYSQL type variable. NOTE: THERE CAN BE ONLY ONE MYSQL VARIABLE. (Sounds like highlander.)

Initialize MYSQL type variable with mysql_init()

Load any options, if required, by using mysql_options(). If you don't need don't call.
You can call this fuction multiple times if you require. If you call this, call this before
mysql_real_connect() and after mysql_init().

```
Connect by calling mysql_real_connect()
```
```
Call the business logic and MySQL API's
```
```
Close the MYSQL type variable.
```

An infra structure

#include <mysql/mysql.h>

return_type function_name(parameters)

   MYSQL mysql;

   mysql_init(&mysql);

   mysql_options(&mysql,MYSQL_OPT_COMPRESS,0);/*call only if required otherwise omit*/

   mysql_options(&mysql,MYSQL_READ_DEFAULT_GROUP,"jahans_Dhaka_Stock_Exchange_Game");/*call only if required otherwise omit*/

   mysql_real_connect(....);

   /* now call other API's*/

   mysql_close(&mysql);

Compiling and Running

```
Compiling and Running in UNIX
```

   $gcc mysql_app.c -o mysql_app -I/usr/local/include -L/usr/local/lib/mysql -lmysqlclient

   $./mysql_app

```
Compiling and Running in Visual C++
```

   -not yet added, will be added later.

Initialization Examples

Initializing MySQL API

/*------InitMySQLv1.c--------*/

/*Variation #1*/

/*
Calls:
MYSQL *mysql_init(MYSQL *mysql)
char *mysql_get_server_info(MYSQL *mysql)
void mysql_close(MYSQL *mysql)
*/

#ifdef WIN32
  #include <windows.h>
  #include <winsock.h>
  #pragma warning (disable: 4514 4786)
  #pragma warning( push, 3 )
#endif

#include <stdio.h>
#include "mysql/mysql.h"
#ifndef WIN32
  #include <unistd.h>
#endif

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

  MYSQL mysql;/* variation #1*/

  printf("\n\n\tCopyright Aftab Jahan Subedar\n\t\thttp://www.geocities.com/jahan.geo");
  printf("\n\t\tjahan@geocities.com \n\t\tPhone:+88027519050\n");

  if(mysql_init(&mysql)==NULL)
	{
		printf("\nFailed to initate MySQL connection");
		exit(1);
	}

  /*now you can call any MySQL API function you like*/

   mysql_close(&mysql);

/*------InitMySQLv2.c--------*/

/*Variation #2*/

/*
Calls:
MYSQL *mysql_init(MYSQL *mysql)
char *mysql_get_server_info(MYSQL *mysql)
void mysql_close(MYSQL *mysql)

*/

#ifdef WIN32
  #include <windows.h>
  #include <winsock.h>
  #pragma warning (disable: 4514 4786)
  #pragma warning( push, 3 )
#endif

#include <stdio.h>
#include "mysql/mysql.h"
#ifndef WIN32
  #include <unistd.h>
#endif

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

  MYSQL *mysql=NULL;/* variation #2*/

  printf("\n\n\tCopyright Aftab Jahan Subedar\n\t\thttp://www.geocities.com/jahan.geo");
  printf("\n\t\tjahan@geocities.com \n\t\tPhone:+88027519050\n");

  if((mysql=mysql_init(mysql))==NULL)/* variation #2*/
	{
		printf("\nFailed to initate MySQL connection");
		exit(1);
	}

  /*now you can call any MySQL API function you like*/

   mysql_close(mysql);

Checking Client Library Version

/*------ClientVersion.c--------*/

/**/

/*
Calls:
MYSQL *mysql_init(MYSQL *mysql)
char *mysql_get_client_info(void)
void mysql_close(MYSQL *mysql)
*/

#ifdef WIN32
  #include <windows.h>
  #include <winsock.h>
  #pragma warning (disable: 4514 4786)
  #pragma warning( push, 3 )
#endif

#include <stdio.h>
#include "mysql/mysql.h"
#ifndef WIN32
  #include <unistd.h>
#endif

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

   /*Notice: it does not require MYSQL initialization*/

   printf("\n\n\tCopyright Aftab Jahan Subedar\n\t\thttp://www.geocities.com/jahan.geo");
   printf("\n\t\tjahan@geocities.com \n\t\tPhone:+88027519050\n");
   printf("MySQL Client Version is %s\n",mysql_get_client_info());

Checking Server Version

/*------ServerVersion.c--------*/

Calls:
MYSQL *mysql_init(MYSQL *mysql)
char *mysql_get_server_info(void)
void mysql_close(MYSQL *mysql)

#ifdef WIN32
  #include <windows.h>
  #include <winsock.h>
  #pragma warning (disable: 4514 4786)
  #pragma warning( push, 3 )
#endif

#include <stdio.h>
#include "mysql/mysql.h"
#ifndef WIN32
  #include <unistd.h>
#endif

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

  MYSQL mysql;

  mysql_init(&mysql);
  mysql_real_connect(&mysql,"subedartech.sytes.net","jahan","shoja_passwd","DBDSE",0,NULL,0)) 
  printf("\n\n\tCopyright Aftab Jahan Subedar\n\t\thttp://www.geocities.com/jahan.geo");
  printf("\n\t\tjahan@geocities.com \n\t\tPhone:+88027519050\n");
  printf("MySQL Server Version is %s\n",mysql_get_server_info(&mysql));

Logging into MySQL Server

/*------DBLogonv1.c--------*/

/*Variation #1*/

/*
MYSQL *mysql_init(MYSQL *mysql)
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 int client_flag)
char *mysql_error(MYSQL *mysql)
void mysql_close(MYSQL *mysql)

*/

#ifdef WIN32
  #include <windows.h>
  #include <winsock.h>
  #pragma warning (disable: 4514 4786)
  #pragma warning( push, 3 )
#endif

#include <stdio.h>
#include "mysql.h"
#ifndef WIN32
  #include <unistd.h>
#endif

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

  MYSQL mysql;

  printf("\n\n\tCopyright Aftab Jahan Subedar\n\t\thttp://www.geocities.com/jahan.geo");
  printf("\n\t\tjahan@geocities.com \n\t\tPhone:+88027519050\n");

  if(mysql_init(&mysql)==NULL)
	{
		printf("\nFailed to initate MySQL connection");
		exit(1);
	}

  /*now you can call any MySQL API function you like*/

if (!mysql_real_connect(&mysql,"subedartech.sytes.net","jahan","shoja_passwd","DBDSE",0,NULL,0)) 
/*variation #1*/

     printf( "Failed to connect to MySQL: Error: %s\n", mysql_error(&mysql));

     exit(1);

printf("Logged on to database sucessfully");

mysql_close(&mysql);

/*------DBLogonv2.c--------*/

/*Variation #2 without database parameter*/

/*
MYSQL *mysql_init(MYSQL *mysql)
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 int client_flag)
char *mysql_error(MYSQL *mysql)
int mysql_select_db(MYSQL *mysql, const char *db)

*/

#ifdef WIN32
  #include <windows.h>
  #include <winsock.h>
  #pragma warning (disable: 4514 4786)
  #pragma warning( push, 3 )
#endif

#include <stdio.h>
#include "mysql.h"
#ifndef WIN32
  #include <unistd.h>
#endif

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

  MYSQL mysql;

  printf("\n\n\tCopyright Aftab Jahan Subedar\n\t\thttp://www.geocities.com/jahan.geo");
  printf("\n\t\tjahan@geocities.com \n\t\tPhone:+88027519050\n");

  if(mysql_init(&mysql)==NULL)
	{
		printf("\nFailed to initate MySQL connection");
		exit(1);
	}

  /*now you can call any MySQL API function you like*/

if (!mysql_real_connect(&mysql,"subedartech.sytes.net","jahan","shoja_passwd",NULL/*variation #2*/,0,NULL,0)) 
{
    printf( "Failed to connect to MySQL: Error: %s\n", mysql_error(&mysql));
    exit(1);
}

if(mysql_select_db(&mysql,"DhakaStockExchangeGame" /*const char *db*/)==0)/*success*/
    printf( "Database Selected\n");
else
    printf( "Failed to connect to Database: Error: %s\n", mysql_error(&mysql));

mysql_close(&mysql);

Changing Logged user

/*------ChangeUser.c--------*/

/*
MYSQL *mysql_init(MYSQL *mysql)
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 int client_flag)
char *mysql_error(MYSQL *mysql)
void mysql_close(MYSQL *mysql)
my_bool mysql_change_user(MYSQL *mysql, const char *user, const char *password, const char *db)

*/

#ifdef WIN32
  #include <windows.h>
  #include <winsock.h>
  #pragma warning (disable: 4514 4786)
  #pragma warning( push, 3 )
#endif

#include <stdio.h>
#include "mysql.h"
#ifndef WIN32
  #include <unistd.h>
#endif

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

  MYSQL mysql;

  printf("\n\n\tCopyright Aftab Jahan Subedar\n\t\thttp://www.geocities.com/jahan.geo");
  printf("\n\t\tjahan@geocities.com \n\t\tPhone:+88027519050\n");

  if(mysql_init(&mysql)==NULL)
	{
		printf("\nFailed to initate MySQL connection");
		exit(1);
	}

  /*now you can call any MySQL API function you like*/

if (!mysql_real_connect(&mysql,"subedartech.sytes.net","jahan","shoja_passwd","DBDSE",0,NULL,0))

     printf( "Failed to connect to MySQL: Error: %s\n", mysql_error(&mysql));

     exit(1);

printf("Logged on to database sucessfully as jahan.\n going to change login to web_user.");

if( mysql_change_user(&mysql, "web_user", "nopassword", "DBDSE")==0)/*warning it initiates rollback*/
    printf("User changed\n);
else
    printf("Error occuered:%s",mysql_error(&mysql));

mysql_close(&mysql);

Selecting a Database

/*-------SelectDB.c---------*/

/*
MYSQL *mysql_init(MYSQL *mysql)
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 int client_flag)
char *mysql_error(MYSQL *mysql)
int mysql_select_db(MYSQL *mysql, const char *db)

*/

#ifdef WIN32
  #include <windows.h>
  #include <winsock.h>
  #pragma warning (disable: 4514 4786)
  #pragma warning( push, 3 )
#endif

#include <stdio.h>
#include "mysql.h"
#ifndef WIN32
  #include <unistd.h>
#endif

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

  MYSQL mysql;

  printf("\n\n\tCopyright Aftab Jahan Subedar\n\t\thttp://www.geocities.com/jahan.geo");
  printf("\n\t\tjahan@geocities.com \n\t\tPhone:+88027519050\n");

  if(mysql_init(&mysql)==NULL)
	{
		printf("\nFailed to initate MySQL connection");
		exit(1);
	}

  /*now you can call any MySQL API function you like*/

if (!mysql_real_connect(&mysql,"subedartech.sytes.net","jahan","shoja_passwd",NULL/*variation #2*/,0,NULL,0)) 
{
    printf( "Failed to connect to MySQL: Error: %s\n", mysql_error(&mysql));
    exit(1);
}

if(mysql_select_db(&mysql,"DhakaStockExchangeGame" /*const char *db*/)==0)/*success*/
    printf( "Database Selected\n");
else
    printf( "Failed to connect to Database: Error: %s\n", mysql_error(&mysql));

mysql_close(&mysql);

Data Definition Examples

Creating a Database

/*-------CreateDB.c---------*/

/*
MYSQL *mysql_init(MYSQL *mysql)
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 int client_flag)
char *mysql_error(MYSQL *mysql)
int mysql_create_db(MYSQL *mysql, const char *db)

*/

#ifdef WIN32
  #include <windows.h>
  #include <winsock.h>
  #pragma warning (disable: 4514 4786)
  #pragma warning( push, 3 )
#endif

#include <stdio.h>
#include "mysql.h"
#ifndef WIN32
  #include <unistd.h>
#endif

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

  MYSQL mysql;

  printf("\n\n\tCopyright Aftab Jahan Subedar\n\t\thttp://www.geocities.com/jahan.geo");
  printf("\n\t\tjahan@geocities.com \n\t\tPhone:+88027519050\n");

  if(mysql_init(&mysql)==NULL)
	{
		printf("\nFailed to initate MySQL connection");
		exit(1);
	}

  /*now you can call any MySQL API function you like*/

if (!mysql_real_connect(&mysql,"subedartech.sytes.net","jahan","shoja_passwd",NULL,0,NULL,0)) 
{
    printf( "Failed to connect to MySQL: Error: %s\n", mysql_error(&mysql));
    exit(1);
}

if(mysql_create_db(&mysql, "DhakaStockExchangeGame" )==0)/*success*/
    printf( "Database Created\n");
else
    printf( "Failed to create new database.  Error: %s\n", mysql_error(&mysql));

mysql_close(&mysql);

Deleting Database

/*-------DeleteDB.c---------*/

/*
MYSQL *mysql_init(MYSQL *mysql)
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 int client_flag)
char *mysql_error(MYSQL *mysql)
int mysql_drop_db(MYSQL *mysql, const char *db)

*/

#ifdef WIN32
  #include <windows.h>
  #include <winsock.h>
  #pragma warning (disable: 4514 4786)
  #pragma warning( push, 3 )
#endif

#include <stdio.h>
#include "mysql.h"
#ifndef WIN32
  #include <unistd.h>
#endif

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

  MYSQL mysql;

  printf("\n\n\tCopyright Aftab Jahan Subedar\n\t\thttp://www.geocities.com/jahan.geo");
  printf("\n\t\tjahan@geocities.com \n\t\tPhone:+88027519050\n");

  if(mysql_init(&mysql)==NULL)
	{
		printf("\nFailed to initate MySQL connection");
		exit(1);
	}

  /*now you can call any MySQL API function you like*/

if (!mysql_real_connect(&mysql,"subedartech.sytes.net","jahan","shoja_passwd",NULL,0,NULL,0)) 
{
    printf( "Failed to connect to MySQL: Error: %s\n", mysql_error(&mysql));
    exit(1);
}

if(mysql_drop_db(&mysql, "DhakaStockExchangeGame" )==0)/*success*/
    printf( "Database Deleted\n");
else
    printf( "Failed to delete database.  Error: %s\n", mysql_error(&mysql));

mysql_close(&mysql);

Creating Tables

/*-------CreateTable.c---------*/

/*
MYSQL *mysql_init(MYSQL *mysql)
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 int client_flag)
char *mysql_error(MYSQL *mysql)
int mysql_select_db(MYSQL *mysql, const char *db)
int mysql_real_query(MYSQL *mysql, const char *query, unsigned long length)
char *strmov(register char *dst, register const char *src)

*/

#ifdef WIN32
  #include <windows.h>
  #include <winsock.h>
  #pragma warning (disable: 4514 4786)
  #pragma warning( push, 3 )
#endif

#include <stdio.h>
#include <string.h>
#include "mysql.h"
#ifndef WIN32
  #include <unistd.h>
#endif

/*helper fuction */

int mysql_exec_sql(MYSQL *mysql,const char *create_definition)
{
   return mysql_real_query(mysql,create_definition,strlen(create_definition));
}

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

  MYSQL mysql;

char create_definition[1000];

printf("\n\n\tCopyright Aftab Jahan Subedar\n\t\thttp://www.geocities.com/jahan.geo");
printf("\n\t\tjahan@geocities.com \n\t\tPhone:+88027519050\n\t\tsupport:jahan@bol-online.com\n");

if(mysql_init(&mysql)==NULL)
	{
		printf("\nFailed to initate MySQL connection");
		exit(1);
	}

/*now you can call any MySQL API function you like*/

if (!mysql_real_connect(&mysql,"subedartech.sytes.net","jahan","shoja_passwd",NULL,0,NULL,0)) 
{
    printf( "Failed to connect to MySQL: Error: %s\n", mysql_error(&mysql));
    exit(1);
}

if(mysql_select_db(&mysql,"DhakaStockExchangeGame" /*const char *db*/)==0)/*success*/
    printf( "Database Selected\n");
else
    printf( "Failed to connect to Database: Error: %s\n", mysql_error(&mysql));

strmov(create_definition,"CREATE TABLE Users(");
strmov(create_definition,"UserID BIGINT UNSIGNED NOT NULL");
strmov(create_definition,",");
strmov(create_definition,"UserName CHAR [50] NOT NULL");
strmov(create_definition,")");

if(mysql_exec_sql(&mysql,create_definition )==0)/*success*/
    printf( "Table Created\n");
else
    printf( "Failed to create table: Error: %s\n", mysql_error(&mysql));

mysql_close(&mysql);

Editing Tables

/*-------EditTable.c---------*/

/*
MYSQL *mysql_init(MYSQL *mysql)
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 int client_flag)
char *mysql_error(MYSQL *mysql)
int mysql_select_db(MYSQL *mysql, const char *db)
int mysql_real_query(MYSQL *mysql, const char *query, unsigned long length)
char *strmov(register char *dst, register const char *src)

*/

#ifdef WIN32
  #include <windows.h>
  #include <winsock.h>
  #pragma warning (disable: 4514 4786)
  #pragma warning( push, 3 )
#endif

#include <stdio.h>
#include <string.h>
#include "mysql.h"
#ifndef WIN32
  #include <unistd.h>
#endif

/*helper fuction */

int mysql_exec_sql(MYSQL *mysql,const char *create_definition)
{
   return mysql_real_query(mysql,create_definition,strlen(create_definition));
}

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

  MYSQL mysql;

char edit_definition[1000];

printf("\n\n\tCopyright Aftab Jahan Subedar\n\t\thttp://www.geocities.com/jahan.geo");
printf("\n\t\tjahan@geocities.com \n\t\tPhone:+88027519050\n\t\tsupport:jahan@bol-online.com\n");

if(mysql_init(&mysql)==NULL)
	{
		printf("\nFailed to initate MySQL connection");
		exit(1);
	}

/*now you can call any MySQL API function you like*/

if (!mysql_real_connect(&mysql,"subedartech.sytes.net","jahan","shoja_passwd",NULL,0,NULL,0)) 
{
    printf( "Failed to connect to MySQL: Error: %s\n", mysql_error(&mysql));
    exit(1);
}

if(mysql_select_db(&mysql,"DhakaStockExchangeGame" /*const char *db*/)==0)/*success*/
    printf( "Database Selected\n");
else
    printf( "Failed to connect to Database: Error: %s\n", mysql_error(&mysql));

strmov(edit_definition,"Alter TABLE Users ");
strmov(edit_definition,"MODIFY UserID BIGINT UNSIGNED NOT NULL AUTO INCREMENT");

if(mysql_exec_sql(&mysql,edit_definition)==0)/*success*/
    printf( "Table Created\n");
else
    printf( "Failed to connect to edit table: Error: %s\n", mysql_error(&mysql));

mysql_close(&mysql);

Deleting Tables

/*-------DeleteTable.c---------*/

/*
MYSQL *mysql_init(MYSQL *mysql)
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 int client_flag)
char *mysql_error(MYSQL *mysql)
int mysql_select_db(MYSQL *mysql, const char *db)
int mysql_real_query(MYSQL *mysql, const char *query, unsigned long length)
char *strmov(register char *dst, register const char *src)

*/

#ifdef WIN32
  #include <windows.h>
  #include <winsock.h>
  #pragma warning (disable: 4514 4786)
  #pragma warning( push, 3 )
#endif

#include <stdio.h>
#include <string.h>
#include "mysql.h"
#ifndef WIN32
  #include <unistd.h>
#endif

/*helper fuction */

int mysql_exec_sql(MYSQL *mysql,const char *create_definition)
{
   return mysql_real_query(mysql,create_definition,strlen(create_definition));
}

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

  MYSQL mysql;

char delete_definition[1000];

printf("\n\n\tCopyright Aftab Jahan Subedar\n\t\thttp://www.geocities.com/jahan.geo");
printf("\n\t\tjahan@geocities.com \n\t\tPhone:+88027519050\n\t\tsupport:jahan@bol-online.com\n");

if(mysql_init(&mysql)==NULL)
	{
		printf("\nFailed to initate MySQL connection");
		exit(1);
	}

/*now you can call any MySQL API function you like*/

if (!mysql_real_connect(&mysql,"subedartech.sytes.net","jahan","shoja_passwd",NULL,0,NULL,0)) 
{
    printf( "Failed to connect to MySQL: Error: %s\n", mysql_error(&mysql));
    exit(1);
}

if(mysql_select_db(&mysql,"DhakaStockExchangeGame" /*const char *db*/)==0)/*success*/
    printf( "Database Selected\n");
else
    printf( "Failed to connect to Database: Error: %s\n", mysql_error(&mysql));

strmov(delete_definition,"DROP TABLE Users ");

if(mysql_exec_sql(&mysql,delete_definition)==0)/*success*/
    printf( "Table Deleted\n");
else
    printf( "Failed to delete table: Error: %s\n", mysql_error(&mysql));

mysql_close(&mysql);

Creating Index

/*-------CreateIndex.c---------*/

/*
MYSQL *mysql_init(MYSQL *mysql)
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 int client_flag)
char *mysql_error(MYSQL *mysql)
int mysql_select_db(MYSQL *mysql, const char *db)
int mysql_real_query(MYSQL *mysql, const char *query, unsigned long length)
char *strmov(register char *dst, register const char *src)

*/

#ifdef WIN32
  #include <windows.h>
  #include <winsock.h>
  #pragma warning (disable: 4514 4786)
  #pragma warning( push, 3 )
#endif

#include <stdio.h>
#include <string.h>
#include "mysql.h"
#ifndef WIN32
  #include <unistd.h>
#endif

/*helper fuction */

int mysql_exec_sql(MYSQL *mysql,const char *create_definition)
{
   return mysql_real_query(mysql,create_definition,strlen(create_definition));
}

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

  MYSQL mysql;

char create_definition[1000];

printf("\n\n\tCopyright Aftab Jahan Subedar\n\t\thttp://www.geocities.com/jahan.geo");
printf("\n\t\tjahan@geocities.com \n\t\tPhone:+88027519050\n\t\tsupport:jahan@bol-online.com\n");

if(mysql_init(&mysql)==NULL)
	{
		printf("\nFailed to initate MySQL connection");
		exit(1);
	}

/*now you can call any MySQL API function you like*/

if (!mysql_real_connect(&mysql,"subedartech.sytes.net","jahan","shoja_passwd",NULL,0,NULL,0)) 
{
    printf( "Failed to connect to MySQL: Error: %s\n", mysql_error(&mysql));
    exit(1);
}

if(mysql_select_db(&mysql,"DhakaStockExchangeGame" /*const char *db*/)==0)/*success*/
    printf( "Database Selected\n");
else
    printf( "Failed to connect to Database: Error: %s\n", mysql_error(&mysql));

strmov(create_definition,"CREATE INDEX UserNames ON Users( UserName )");

if(mysql_exec_sql(&mysql,create_definition )==0)/*success*/
    printf( "Index created\n");
else
    printf( "Failed to create index: Error: %s\n", mysql_error(&mysql));

mysql_close(&mysql);

Deleting Index

/*-------DeleteIndex.c---------*/

/*
MYSQL *mysql_init(MYSQL *mysql)
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 int client_flag)
char *mysql_error(MYSQL *mysql)
int mysql_select_db(MYSQL *mysql, const char *db)
int mysql_real_query(MYSQL *mysql, const char *query, unsigned long length)
char *strmov(register char *dst, register const char *src)

*/

#ifdef WIN32
  #include <windows.h>
  #include <winsock.h>
  #pragma warning (disable: 4514 4786)
  #pragma warning( push, 3 )
#endif

#include <stdio.h>
#include <string.h>
#include "mysql.h"
#ifndef WIN32
  #include <unistd.h>
#endif

/*helper fuction */

int mysql_exec_sql(MYSQL *mysql,const char *create_definition)
{
   return mysql_real_query(mysql,create_definition,strlen(create_definition));
}

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

  MYSQL mysql;

char delete_definition[1000];

printf("\n\n\tCopyright Aftab Jahan Subedar\n\t\thttp://www.geocities.com/jahan.geo");
printf("\n\t\tjahan@geocities.com \n\t\tPhone:+88027519050\n\t\tsupport:jahan@bol-online.com\n");

if(mysql_init(&mysql)==NULL)
	{
		printf("\nFailed to initate MySQL connection");
		exit(1);
	}

/*now you can call any MySQL API function you like*/

if (!mysql_real_connect(&mysql,"subedartech.sytes.net","jahan","shoja_passwd",NULL,0,NULL,0)) 
{
    printf( "Failed to connect to MySQL: Error: %s\n", mysql_error(&mysql));
    exit(1);
}

if(mysql_select_db(&mysql,"DhakaStockExchangeGame" /*const char *db*/)==0)/*success*/
    printf( "Database Selected\n");
else
    printf( "Failed to connect to Database: Error: %s\n", mysql_error(&mysql));

strmov(delete_definition,"DROP INDEX UserNames ON Users");

if(mysql_exec_sql(&mysql,delete_definition)==0)/*success*/
    printf( "Index deleted\n");
else
    printf( "Failed to delete index: Error: %s\n", mysql_error(&mysql));

mysql_close(&mysql);

Data Manipulation Examples

Adding records to table

/*-------AddRecords.c---------*/

/*
MYSQL *mysql_init(MYSQL *mysql)
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 int client_flag)
char *mysql_error(MYSQL *mysql)
int mysql_select_db(MYSQL *mysql, const char *db)
int mysql_real_query(MYSQL *mysql, const char *query, unsigned long length)
char *strmov(register char *dst, register const char *src)

*/

#ifdef WIN32
  #include <windows.h>
  #include <winsock.h>
  #pragma warning (disable: 4514 4786)
  #pragma warning( push, 3 )
#endif

#include <stdio.h>
#include <string.h>
#include "mysql.h"
#ifndef WIN32
  #include <unistd.h>
#endif

/*helper fuction */

int mysql_exec_sql(MYSQL *mysql,const char *create_definition)
{
   return mysql_real_query(mysql,create_definition,strlen(create_definition));
}

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

  MYSQL mysql;

char record[1000];

printf("\n\n\tCopyright Aftab Jahan Subedar\n\t\thttp://www.geocities.com/jahan.geo");
printf("\n\t\tjahan@geocities.com \n\t\tPhone:+88027519050\n\t\tsupport:jahan@bol-online.com\n");

if(mysql_init(&mysql)==NULL)
	{
		printf("\nFailed to initate MySQL connection");
		exit(1);
	}

/*now you can call any MySQL API function you like*/

if (!mysql_real_connect(&mysql,"mysql.DhakaStockExchangeGame.com","jahan","shoja_passwd",NULL,0,NULL,0)) 
{
    printf( "Failed to connect to MySQL: Error: %s\n", mysql_error(&mysql));
    exit(1);
}

if(mysql_select_db(&mysql,"DhakaStockExchangeGame" )==0)/*success*/
    printf( "Database Selected\n");
else
    printf( "Failed to connect to Database: Error: %s\n", mysql_error(&mysql));

strmov(record,"INSERT INTO Users VALUES(8,'Jahan')");

if(mysql_exec_sql(&mysql,record)==0)/*success*/
    printf( "Record Added\n");
else
    printf( "Failed to add records: Error: %s\n", mysql_error(&mysql));

mysql_close(&mysql);

finding records from table

/*-------FindRecordsStatic.c---------*/

/*
MYSQL *mysql_init(MYSQL *mysql)
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 int client_flag)
char *mysql_error(MYSQL *mysql)
int mysql_select_db(MYSQL *mysql, const char *db)
int mysql_real_query(MYSQL *mysql, const char *query, unsigned long length)
char *strmov(register char *dst, register const char *src)

*/

#ifdef WIN32
  #include <windows.h>
  #include <winsock.h>
  #pragma warning (disable: 4514 4786)
  #pragma warning( push, 3 )
#endif

#include <stdio.h>
#include <string.h>
#include "mysql.h"
#ifndef WIN32
  #include <unistd.h>
#endif

/*helper fuction */

int mysql_exec_sql(MYSQL *mysql,const char *create_definition)
{
   return mysql_real_query(mysql,create_definition,strlen(create_definition));
}

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

MYSQL mysql;
MYSQL_RES *result;
MYSQL_ROW row;

unsigned int num_fields;
unsigned int i;
char query_def[1000];

printf("\n\n\tCopyright Aftab Jahan Subedar\n\t\thttp://www.geocities.com/jahan.geo");
printf("\n\t\tjahan@geocities.com \n\t\tPhone:+88027519050\n\t\tsupport:jahan@bol-online.com\n");

if(mysql_init(&mysql)==NULL)
{
  printf("\nFailed to initate MySQL connection");
  exit(1);
}

/*now you can call any MySQL API function you like*/

if (!mysql_real_connect(&mysql,"mysql.DhakaStockExchangeGame.com","jahan","shoja_passwd",NULL,0,NULL,0)) 
{
    printf( "Failed to connect to DhakaStockExchangeGame.com: Error: %s\n", mysql_error(&mysql));
    exit(1);
}

if(mysql_select_db(&mysql,"DhakaStockExchangeGame")==0)/*success*/
    printf( "Database DhakaStockExchangeGame Selected\n");
else
    printf( "Failed to connect to Database DhakaStockExchangeGame: Error: %s\n", mysql_error(&mysql));

strmov(query_def,"SELECT Name,EMail FROM Registration WHERE MembershipType='TasteMODE'");

if(mysql_exec_sql(&mysql,record)==0)/*success*/
{
    printf( "%ld Record Found\n",(long) mysql_affected_rows(&mysql));
    result = mysql_store_result(&mysql);
    if (result)  // there are rows
    {
        num_fields = mysql_num_fields(result);
        while ((row = mysql_fetch_row(result))) 
        { 
           for(i = 0; i < num_fields; i++) 
           { 
             printf("[%.*s] ", (int) lengths[i], row[i] ? row[i] : "NULL"); 
            } 
           printf("\n"); 
          }
        mysql_free_result(result)
     }
     else  // mysql_store_result() returned nothing
     {
        if(mysql_field_count(&mysql) > 0)
        // mysql_store_result() should have returned data
        {
            printf( "Error getting records: %s\n", mysql_error(&mysql));
        }
    }
}
else
{
    printf( "Failed to find any records and caused an error: %s\n", mysql_error(&mysql));
}

mysql_close(&mysql);

CGI Examples

JPEG OUTPUT FROM JPEG FIELD

/*********************************************************************
 g++ -o /usr/local/www/cgi-bin/jpeg_mysql.cgi  jpeg_mysql_cgi_test.c -I/usr/local/include -L/usr/local/lib/mysql -lc -lmysqlclient
 *********************************************************************
 */
#include <stdio.h>
/*#include <sys/types.h>
#include <sys/stat.h> fstat    
#include <sys/uio.h> 
#include <string.h>*/ 
#include <unistd.h> 
#include <fcntl.h>          
#include <stdlib.h>/*get env*/
#include <mysql/mysql.h>
#include <syslog.h>/*syslog*/
#include <stdarg.h>    

#define HTTP_STR "HTTP/1.1 200 OK\n"
#define HTTP_SERVER  "Server: Subedar Technologies\n"
#define HTTP_CONTENT "Content-type: image/jpeg\n\n"

int main(int argc, char *argv[])
{
	char *query="SELECT JPEG FROM Snaps ORDER BY ID DESC LIMIT 1";

    MYSQL mysql;
	mysql_init(&mysql);

	if(!mysql_real_connect (&mysql,"","your_login_name","your_mysql_password","your_database",0,NULL,0))
	{
			 syslog(LOG_CONS,"%s->%s","MySQL Connect Error:",mysql_error(&mysql));
			 mysql_close(&mysql);
			 exit(0);
	}

	if(mysql_query(&mysql,query))
	{
			 syslog(LOG_CONS,"%s->%s","MySQL Query Eorror:",mysql_error(&mysql));
			 mysql_close(&mysql);
			 exit(0);
	}
	MYSQL_RES *result;
	result = mysql_use_result(&mysql);

	if (result)  // there are rows
	{
		/* retrieve rows, then call mysql_free_result(result)*/
		MYSQL_ROW row;
		if((row = mysql_fetch_row(result)))
		{
			unsigned long *lengths = mysql_fetch_lengths(result);
			if(lengths[0] > 0)
			{
				write(STDOUT_FILENO, HTTP_CONTENT, strlen(HTTP_CONTENT));
				write(STDOUT_FILENO,row[0],lengths[0]);

			}
		}
		mysql_free_result(result);
	}

	mysql_close(&mysql);
	exit(0);
}

Utility Functions Examples

Using my_ulonglong in printf

mysql_query(&mysql,"UPDATE Registration SET ValidUntil='11-20-2003' WHERE MembershipType='TasteMODE'");
printf("Number Users Promoted:%ld ",(long) mysql_affected_rows(&mysql));

Changes

1.2 Added CGI Topic

1.2.1 Corrected internal links Sept 25 2004

Contrubutions & comments

Would you like to contribute MySQL C API Examples ? Feel free to send it to me.