From my mailbag:
How do I write a C program to connect MySQL database server?
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
Sample C Program
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.
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
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
References:
- MySQL C API - A must read - official MySQL C API documentation
Featured Articles:
- 20 Linux System Monitoring Tools Every SysAdmin Should Know
- 20 Linux Server Hardening Security Tips
- 10 Greatest Open Source Software Of 2009
- My 10 UNIX Command Line Mistakes
- Top 5 Email Client For Linux, Mac OS X, and Windows Users
- Top 20 OpenSSH Server Best Security Practices
- Top 10 Open Source Web-Based Project Management Software
- Top 5 Linux Video Editor Software
- Email this to a friend
- Download PDF version
- Printable version
- Comment RSS feed
- Last Updated: Oct/8/2007
{ 41 comments… read them below or add one }
One thing to be aware of though with the mysql library – there is licensing involved. It was complicated enough that our company backed off from MySQL and went with SQLite.
Jonathan,
You have raised an excellent point.
MySQL dual Licensing policy is bit dangerous; if you are giving out GPL code it can be done with GPL; otherwise it force commercial vendor under its own terms and condition.
PGSQL or SQLite seems to good alternative.
Appreciate your post!
I’m no C guru, but I think there’s a typo. shouldn’t you run:
./output-file
or compile with:
-o output.file
Or, am I missing something?
David,
Thanks for the heads up!
i want to connect mysql from c program in linux mandriva 2007.i am not being able to do it as mysql.h is not there in mandriva 2007
Just install mysql-devel package
How do you specify the socket file?
Use mysql_real_connect() and set host to NULL socket to NULL or 0
See API doc for more help!
how i do a makefile that compile me the .c?
it has to find the result of mysql_config –libs and –cflags and put it in the gcc -o line
thx in advance
Hi Edwind,
I’m not big on c-fu, but you should “vi Makefile” in the directory where you have your .c program and in the Makefile, you should type something along the lines of:
MYSQLCFLAGS=`mysql_config –cflags`
MYSQLLIBS=`mysql_config –libs`
datafoxclient:
$(CC) -I/usr/include/mysql -o datafoxclient $(MYSQLCFLAGS) datafoxclient.c $(MYSQLLIBS)
clean:
rm -f datafoxclient
some of the c-fu gurus would probably tell you that my Makefile is broken and not that good! But, hey, it works for me :)
hope that helps :)
i want c code for FTP server and client.
any scaffolding for C available ? so i don’t have to
code add/edit/delete bla bla bla etc ?
thx
./output-file output not created while compile mysql-c-api.c in gcc as specified in procedure
plz anyone help me
Combining your script there with memcached C API (http://danga.com/memcached/apis.bml) will also boost the performance, instead of directly querying to mysqld.
Rgds,
semarsuper
i m facing a error message when wanna run output file-
“error while loading shared libraries: libmysqlclient.so.15: cannot open shared object file: No such file or directory”
would anyone help me to over come this prob.
thanks
Saiful
Excelent Bro! thanks for the info. It helped me a lot.
i hav followed same procedure which is given above for executing n i m getting an error message when wanna run output file-
“Access denied for user ‘root’@'localhost’ (using password: YES)” Can anyone tell me how to fix this prob. Pleeeeeeeeeeeeease
shiva, if you have problems of this level, you shouldn’t be programming.
I encountered with problems about it, but when I see your site and apply what you said, I could finally succeed it.
Thanks a lot..
a have 2 table or more, how to make connect n operation in c.
hi,thanks for the code which you gave but i am getting some errors.i tried running the code on ubuntu8.04,when i execute the 1st statement –\”$ mysql_config –libs\” i get the output as -Wl,-Bsymbolic-functions -L/usr/lib/mysql -lmysqlclient
and when i execut the 2nd statement i get the output as -I/usr/include/mysql -DBIG_JOINS=1 -fPIC
i am not able to get the output as mentioned in the post.Can anyone please help me??
Also can anyone tell me what is the output file in the 3rd statement.
thankyou :)
“Access denied for user ‘root’@’localhost’ (using password: YES)”
and when i change the password to NULL i get this
“Access denied for user ‘root’@’localhost’ (using password: NO)”
not able to find out what is the real problem
i also tried using “sudo ./output-file” but it did not work
shiva/sher:
Those errors are because you haven’t specified the correct password for the mysql server. If you don’t remember the password go here:
http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html
Thank you for a great tutorial, I did it with the server of my website, but I had to give permission to my IP to access the server.
i am using TC …from where i will get the the mysql.h file for turbo c(windows)
I don’t think so TC supports mysql API. Get better compiler set or move to GNU/Linux.
Hey great tutorial, but I’m having problems inserting double values & when I try to retrieve the values using the C application, all I see is the primary key column. Here is my insert code:
if (mysql_query(conn, “INSERT INTO Meta_Data (alpha, beta, delta, gamma) VALUES (1.01, 2.34, 3.9, 4.3)”)) {
This will put the values into DB OK, but if I try to insert variables, all that gets inserted is 0, for example
if (mysql_query(conn, “INSERT INTO Meta_Data (alpha, beta, delta, gamma) VALUES (alpha, beta, delta, gamma)”)) {
alpha, beta, delta, gamma are declared as doubles & I’ve also tried making them constants but same results. I’m not very strong with C so any advice would be great.
Would anyone have any advice to get around this?
Thanks
John
john_l the c compiler will see your variables as strings, not as variables.
have a look at the sprintf function to append variables to strings.
man sprintf
If you see an error regarding ‘exit’ implicit function, remember to add an include of stdlib.h or process.h. Otherwise, good starting program for MySQL programming.
Hi walter ,
I Wrote makefile as u suggested for one of the member ,i have problem while doing make,it cannot find
gcc: mysql_config -cflags: No such file or directory
gcc: mysql_config -libs: No such file or directory
where i should find it ,
PLZ help me out
Thank u
Bhanu
Hi, I have mysql4 and mysql5 running in my linux. I set the path lib and the path include in compilation to paths mysql5. The program compile, but occurs a erro when program is execute.
(dlopen(): /usr/lib/imspector/mysqlloggingplugin.so: undefined symbol: compress). Please help me
Below, is the line compilation to program.
g++ mysqlloggingplugin.o libimspector.so -ldl -fPIC -shared -Wl,-soname,mysqlloggingplugin.so -o mysqlloggingplugin.so -L/opt/ef/mysql5/lib/mysql -I/opt/ef/mysql5/include -lmysqlclient -lm -lnsl
to compile the litte script with gcc = 4.3 i had to inlcude “#include ” and had to change those four lines from:
char *server = “localhost”;
char *user = “root”;
char *password = “password”; /* set me first */
char *database = “mysql”;
to:
const char *server = “localhost”;
const char *user = “root”;
const char *password = “password”; /* set me first */
const char *database = “mysql”;
and the compile command needed this additional flag: “-lstdc++”
gcc -lstdc++ -o mysql_test $(mysql_config –cflags) mysql_test.cpp $(mysql_config –libs)
otherwise i got the following errors:
mysql_test.cpp: In function ‘int main()’:
mysql_test.cpp:10: warning: deprecated conversion from string constant to ‘char*’
mysql_test.cpp:11: warning: deprecated conversion from string constant to ‘char*’
mysql_test.cpp:12: warning: deprecated conversion from string constant to ‘char*’
mysql_test.cpp:13: warning: deprecated conversion from string constant to ‘char*’
mysql_test.cpp:21: error: ‘exit’ was not declared in this scope
mysql_test.cpp:27: error: ‘exit’ was not declared in this scope
/tmp/ccwZLvOo.o:(.data.DW.ref.__gxx_personality_v0[DW.ref.__gxx_personality_v0]+0×0): undefined reference to `__gxx_personality_v0′
Hello,
I have tried the above c code.
But while compiling i get an error message saying:
mysql_c.c:1:19: error: mysql.h: No such file or directory
mysql_c.c: In function ‘main’:
mysql_c.c:5: error: ‘MYSQL’ undeclared (first use in this function)
mysql_c.c:5: error: (Each undeclared identifier is reported only once
mysql_c.c:5: error: for each function it appears in.)
mysql_c.c:5: error: ‘conn’ undeclared (first use in this function)
mysql_c.c:6: error: ‘MYSQL_RES’ undeclared (first use in this function)
mysql_c.c:6: error: ‘res’ undeclared (first use in this function)
mysql_c.c:7: error: ‘MYSQL_ROW’ undeclared (first use in this function)
mysql_c.c:7: error: expected ‘;’ before ‘row’
mysql_c.c:20: warning: incompatible implicit declaration of built-in function ‘exit’
mysql_c.c:26: warning: incompatible implicit declaration of built-in function ‘exit’
mysql_c.c:33: error: ‘row’ undeclared (first use in this function)
Which certainly means that i dont have mysql.h file.
I am using Fedora 11.
Can anyone solve this problem for me. Please.
Thanks in Advance.
-Hemant
This works in ubuntu 9.04
sudo apt-get install mysql-server mysql-client
mysql -uroot -p
sudo apt-get install libmysqlclient16-dev
gcc -o mysql-c-api-test `mysql_config --cflags` mysql-c-api-test.c `mysql_config --libs`
./mysql-c-api-test
Hey I have problem similar to John_L. I want to push variable declared in C into the database.
CODE EXPLANATION:
I have declared some variables
//declared variables
unsigned char a,b,c,d;
and want to push these variables in MYSQL database using MYSQL C API. I used sprintf and pushed all variables in a string variable.
//declared the string.
char buffer[50];
//used sprintf function to format variables into string.
sprintf(buffer,”%02X:%02X:%02X:%02X”,a,b,c,d);
now pushed “buffer” into database using mysql c api
// Insert into MySQL
if (mysql_query(conn, “INSERT INTO events (srcAddress) VALUES (‘buffer’)”))
{
fprintf(stderr, “%s\n”, mysql_error(conn));
exit(1);
}
But instead of writing values of the variable buffer, “buffer” is as it is getting written in the database.
Can anyone plz help me to get through this problem.
Thanks for the code and the updates here above :-)
Please help me fix this problem. I try to run the above exmple but I get the error like this
./mysql-c-api-test
Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’ (2)
I’m getting this.
/tmp/ccCldxTF.o: In function `main’:
mysql-c-api.c:(.text+0×31): undefined reference to `mysql_init’
mysql-c-api.c:(.text+0×79): undefined reference to `mysql_real_connect’
mysql-c-api.c:(.text+0×89): undefined reference to `mysql_error’
mysql-c-api.c:(.text+0xc4): undefined reference to `mysql_query’
mysql-c-api.c:(.text+0xd4): undefined reference to `mysql_error’
mysql-c-api.c:(.text+0×107): undefined reference to `mysql_use_result’
mysql-c-api.c:(.text+0×133): undefined reference to `mysql_fetch_row’
mysql-c-api.c:(.text+0×14a): undefined reference to `mysql_free_result’
mysql-c-api.c:(.text+0×156): undefined reference to `mysql_close’
collect2: ld returned 1 exit status
Please help me
My question is
“Create a C/C++ console application which connects to MySQL database and inserts a new column of type varchar into a given table. Application should ask the user for table and new column name.”
Please let me know what are the modifications/changes that I have to make for the code given here so that I the get desired result.
For Centos and other rpm based packages
mysql-devel packages is must
yum install mysql-devel
Huge thanks for this. Exactly what I needed. Other than difficulty finding the MySQL-Devel package for SUSE 11, it was a breeze to get started with C and MySQL.