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
Initialization Examples
Data Definition Examples
Data Manipulation Examples
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
$gcc mysql_app.c -o mysql_app -I/usr/local/include -L/usr/local/lib/mysql -lmysqlclient
$./mysql_app
-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.