MySQL C – API programming
MySQL is a leading open source database management system. It is a multi user, multithreaded database management system. MySQL is especially popular on the web.
| Basic Structure of C Programs that uses MySQL C API 1. All programs must include <mysql/mysql.h> as the last include.2. Define MYSQL type variable. NOTE: THERE CAN BE ONLY ONE MYSQL VARIABLE.3. Initialize MYSQL type variable with mysql_init()4. 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().5. Connect by calling mysql_real_connect()
6. Call the business logic and MySQL API’s 7. Close the MYSQL type variable. |
 |
Template of program
#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,”Pushpraj”);
/*call only if required otherwise omit*/
mysql_real_connect(….);
/* now call other API’s*/
mysql_close(&mysql);
}
Compiling and Running in UNIX :-
To Compile :- $gcc mysql_app.c -o mysql_app -I/usr/local/include -L/usr/local/lib/mysql –lmysqlclient
To Run :- $./mysql_app
Examples:-
1. Test the connection.
#include </usr/include/mysql/my_global.h>
#include </usr/include/mysql/mysql.h>
int main(int argc, char **argv)
{
printf(“MySQL client version: %s\n”, mysql_get_client_info());
exit(0);
}
Description:- “mysql_get_client_info()”, this mysql function collects the version information of mysql.
2. Create table and insert data into table.
#include <my_global.h>
#include <mysql.h>
void finish_with_error(MYSQL *con){
fprintf(stderr, “%s\n”, mysql_error(con));
mysql_close(con);
exit(1);
}
int main(int argc, char **argv)
{
MYSQL *con = mysql_init(NULL);
if (con == NULL) {
fprintf(stderr, “%s\n”, mysql_error(con));
exit(1);
}
if (mysql_real_connect(con, “192.168.2.50”, “username”, “password”,”db-Name”, port-No, NULL, 0) == NULL)
{
finish_with_error(con);
}
if (mysql_query(con, “DROP TABLE IF EXISTS Dell”)) {
finish_with_error(con);
}
if (mysql_query(con, “CREATE TABLE Dell(Id INT, Name TEXT, Price INT)”)) {
finish_with_error(con);
}
if (mysql_query(con, “INSERT INTO Dell VALUES(1,’vostro14′,26042)”)) {
finish_with_error(con);
}
if (mysql_query(con, “INSERT INTO Dell VALUES(2,’inspiron15′,37000)”)) {
finish_with_error(con);
}
if (mysql_query(con, “INSERT INTO Dell VALUES(3,’inspiron16′,40000)”)) {
finish_with_error(con);
}
if (mysql_query(con, “INSERT INTO Dell VALUES(4,’ vostro15′,29000)”)) {
finish_with_error(con);
}
if (mysql_query(con, “INSERT INTO Dell VALUES(5,’express’,69000)”)) {
finish_with_error(con);
}
mysql_close(con);
exit(0);
}
Description:- mysql_init, allocates/intialises a Mysql object suitable for mysql_real_connect()establishes a connection to the database.We provide connection handler, host name, username ,password ,database name, port number , unix socket and client flag.
3. Create connection with Database & fetch the records.
#include <my_global.h>
#include <mysql.h>
void finish_with_error(MYSQL *con)
{
fprintf(stderr, “%s\n”, mysql_error(con));
mysql_close(con);
exit(1);
}
int main(int argc, char **argv)
{
MYSQL *con = mysql_init(NULL);
if (con == NULL)
{
fprintf(stderr, “mysql_init() failed\n”);
exit(1);
}
if (mysql_real_connect(con, “192.168.2.50”, “username”, “password”,”db-Name”, port-No, NULL, 0) == NULL)
{ finish_with_error(con); }
if (mysql_query(con, “SELECT * FROM Dell”))
{ finish_with_error(con); }
MYSQL_RES *result = mysql_store_result(con);
if (result == NULL)
{ finish_with_error(con); }
int num_fields = mysql_num_fields(result);
MYSQL_ROW row;
while ((row = mysql_fetch_row(result)))
{
for(int i = 0; i < num_fields; i++)
{
printf(“%s “, row[i] ? row[i] : “NULL”);
}
printf(“\n”);
}
mysql_free_result(result);
mysql_close(con);
exit(0);
}
Description:-
- Create a connection:: mysql_real_connect(con, “192.168.2.50”, “username”, “password”,”db-Name”, port-No, NULL, 0)
- Execute query:: mysql_query(con, “SELECT * FROM Dell”)
- Get the result set:: MYSQL_RES *result = mysql_store_result(con)
- Fetch all available rows one by one :: row = mysql_fetch_row(result)
- Free the result set:: mysql_free_result(result)
- Close the connection:: mysql_close(con);
Thanks
Pushpraj Kumar
Best Open Source Business Intelligence Software Helical Insight is Here
