{"id":735,"date":"2010-02-17T10:22:23","date_gmt":"2010-02-17T10:22:23","guid":{"rendered":"http:\/\/www.codingfriends.com\/?p=735"},"modified":"2010-02-17T10:22:23","modified_gmt":"2010-02-17T10:22:23","slug":"mysql-connection-example","status":"publish","type":"post","link":"https:\/\/www.codingfriends.com\/index.php\/2010\/02\/17\/mysql-connection-example\/","title":{"rendered":"mysql – connection example"},"content":{"rendered":"

Mysql<\/a> is a database, and to gain access to the data within C++ you will need to be able to “talk” to the database via queries (just like on the mysql command line interface e.g. select * from tablename), the connection process is very similar to the command line interface you will need to supply connection details as in hostname (localhost normally), username, password, database to use and also there are other details that you can pass e.g port number more information can be gained from the MYSQL API pages<\/a><\/p>\n

To start with I created a struct that will hold the username, host etc details.<\/p>\n

\r\nstruct connection_details\r\n{\r\n    char *server;\r\n    char *user;\r\n    char *password;\r\n    char *database;\r\n};\r\n<\/pre>\n
In essence to connect to a database I have created a function that will connect and return a MYSQL pointer to the new connection using the structure above connection_details.<\/p>\n
\r\nMYSQL* mysql_connection_setup(struct connection_details mysql_details)\r\n{\r\n     \/\/ first of all create a mysql instance and initialize the variables within\r\n    MYSQL *connection = mysql_init(NULL);\r\n    \r\n    \/\/ connect to the database with the details attached.\r\n    if (!mysql_real_connect(connection,mysql_details.server, mysql_details.user, mysql_details.password, mysql_details.database, 0, NULL, 0)) {\r\n      printf(\"Conection error : %s\\n\", mysql_error(connection));\r\n      exit(1);\r\n    }\r\n    return connection;\r\n}\r\n<\/pre>\n
That is it, you are connected now, now you can perform some sql queries, once again I have created a function to accomplish this and it returns a MYSQL_RES (mysql result pointer)<\/p>\n
\r\nMYSQL_RES* mysql_perform_query(MYSQL *connection, char *sql_query)\r\n{\r\n   \/\/ send the query to the database\r\n   if (mysql_query(connection, sql_query))\r\n   {\r\n      printf(\"MySQL query error : %s\\n\", mysql_error(connection));\r\n      exit(1);\r\n   }\r\n   \r\n   return mysql_use_result(connection);\r\n}<\/pre>\n
since you are now connected and also preformed a query, lets say the query was<\/p>\n
\r\nshow tables;\r\n<\/pre>\n
You can traverse the results with a while loop in C++ and use the mysql_fetch_row function from within the mysql library set where a row is a type of MYSQL_ROW.<\/p>\n
\r\nMYSQL_ROW row;\r\n  while ((row = mysql_fetch_row(res)) !=NULL)\r\n      printf(\"%s\\n\", row[0]);\r\n<\/pre>\n
Here is the full code that will display all of the tables within the mysql database database, you will need to alter the username and password for the mysql access details.<\/p>\n
\r\n#include \r\n#include \r\n#include \r\n\r\n\/\/ just going to input the general details and not the port numbers\r\nstruct connection_details\r\n{\r\n    char *server;\r\n    char *user;\r\n    char *password;\r\n    char *database;\r\n};\r\n\r\nMYSQL* mysql_connection_setup(struct connection_details mysql_details)\r\n{\r\n     \/\/ first of all create a mysql instance and initialize the variables within\r\n    MYSQL *connection = mysql_init(NULL);\r\n    \r\n    \/\/ connect to the database with the details attached.\r\n    if (!mysql_real_connect(connection,mysql_details.server, mysql_details.user, mysql_details.password, mysql_details.database, 0, NULL, 0)) {\r\n      printf(\"Conection error : %s\\n\", mysql_error(connection));\r\n      exit(1);\r\n    }\r\n    return connection;\r\n}\r\n\r\nMYSQL_RES* mysql_perform_query(MYSQL *connection, char *sql_query)\r\n{\r\n   \/\/ send the query to the database\r\n   if (mysql_query(connection, sql_query))\r\n   {\r\n      printf(\"MySQL query error : %s\\n\", mysql_error(connection));\r\n      exit(1);\r\n   }\r\n   \r\n   return mysql_use_result(connection);\r\n}\r\n\r\nint main()\r\n{\r\n  MYSQL *conn;\t\t\/\/ the connection\r\n  MYSQL_RES *res;\t\/\/ the results\r\n  MYSQL_ROW row;\t\/\/ the results row (line by line)\r\n\r\n  struct connection_details mysqlD;\r\n  mysqlD.server = \"localhost\";  \/\/ where the mysql database is\r\n  mysqlD.user = \"mysqlusername\";\t\t\/\/ the root user of mysql\t\r\n  mysqlD.password = \"mysqlpassword\"; \/\/ the password of the root user in mysql\r\n  mysqlD.database = \"mysql\";\t\/\/ the databse to pick\r\n\r\n  \/\/ connect to the mysql database\r\n  conn = mysql_connection_setup(mysqlD);\r\n\r\n  \/\/ assign the results return to the MYSQL_RES pointer\r\n  res = mysql_perform_query(conn, \"show tables\");\r\n  \r\n  printf(\"MySQL Tables in mysql database:\\n\");\r\n  while ((row = mysql_fetch_row(res)) !=NULL)\r\n      printf(\"%s\\n\", row[0]);\r\n  \r\n  \/* clean up the database result set *\/\r\n  mysql_free_result(res);\r\n  \/* clean up the database link *\/\r\n  mysql_close(conn);\r\n  \r\n  return 0;\r\n}<\/pre>\n
To compile up this program you will need to link to the mysql libraries and headers that are used within the program, e.g. mysql.h at the top of the program.  To gain access to these, there is a nice mysql_config (you may need to install it via your package manager system if you do not have it already).<\/p>\n
Here are my outputs of what is required on the command line for the g++ compiler<\/p>\n
\r\nmysql_config --cflags\r\n-I\/usr\/include\/mysql  -DBIG_JOINS=1  -fno-strict-aliasing   -DUNIV_LINUX\r\nmysql_config --libs\r\n-Wl,-Bsymbolic-functions -rdynamic -L\/usr\/lib\/mysql -lmysqlclient\r\n<\/pre>\n
These are the g++\/c++ switches that tell the compiler where the libraries (-L) are and the headers (-I) and to insert into the g++\/c++ compiler line within linux you can do something like<\/p>\n
\r\ng++ -o mysqlconnect $(mysql_config --cflags) mysqlconnect.cpp $(mysql_config --libs)\r\n<\/pre>\n
Where the mysqlconnect.cpp is the c++ file of the above program and the output would be <\/p>\n
\r\n.\/mysqlconnect\r\nMySQL Tables in mysql database:\r\ncolumns_priv\r\ndb\r\nevent\r\nfunc\r\ngeneral_log\r\nhelp_category\r\nhelp_keyword\r\nhelp_relation\r\nhelp_topic\r\nhost\r\nndb_binlog_index\r\nplugin\r\nproc\r\nprocs_priv\r\nservers\r\nslow_log\r\ntables_priv\r\ntime_zone\r\ntime_zone_leap_second\r\ntime_zone_name\r\ntime_zone_transition\r\ntime_zone_transition_type\r\nuser\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
Mysql is a database, and to gain access to the data within C++ you will need to be able to “talk” to the database via queries (just like on the mysql command line interface e.g. select * from tablename), the connection process is very similar to the command line interface you will need to supply … Continue reading mysql – connection example<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7],"tags":[100,22],"class_list":["post-735","post","type-post","status-publish","format-standard","hentry","category-c_and_cpp","tag-connection","tag-mysql"],"_links":{"self":[{"href":"https:\/\/www.codingfriends.com\/index.php\/wp-json\/wp\/v2\/posts\/735","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.codingfriends.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.codingfriends.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.codingfriends.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.codingfriends.com\/index.php\/wp-json\/wp\/v2\/comments?post=735"}],"version-history":[{"count":1,"href":"https:\/\/www.codingfriends.com\/index.php\/wp-json\/wp\/v2\/posts\/735\/revisions"}],"predecessor-version":[{"id":736,"href":"https:\/\/www.codingfriends.com\/index.php\/wp-json\/wp\/v2\/posts\/735\/revisions\/736"}],"wp:attachment":[{"href":"https:\/\/www.codingfriends.com\/index.php\/wp-json\/wp\/v2\/media?parent=735"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.codingfriends.com\/index.php\/wp-json\/wp\/v2\/categories?post=735"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.codingfriends.com\/index.php\/wp-json\/wp\/v2\/tags?post=735"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}