{"id":1119,"date":"2013-09-13T15:16:55","date_gmt":"2013-09-13T15:16:55","guid":{"rendered":"https:\/\/raspberry-projects.com\/pi\/?p=1119"},"modified":"2015-06-10T19:37:43","modified_gmt":"2015-06-10T19:37:43","slug":"accessing-the-database","status":"publish","type":"post","link":"https:\/\/raspberry-projects.com\/pi\/programming-in-c\/databases-programming-in-c\/mysql\/accessing-the-database","title":{"rendered":"Accessing The Database"},"content":{"rendered":"

\nConnecting
\n<\/h4>\n

\nGlobal Things
\n<\/h5>\n
\r\n\r\n#define DATABASE_NAME\t\t"YOUR_DATABASE_NAME"\r\n#define DATABASE_USERNAME\t"YOUR_DATABASE_USERNAME"\r\n#define DATABASE_PASSWORD\t"YOUR_DATABASE_PASSWORD"\r\nMYSQL *mysql1;\r\n<\/code><\/pre>\n
\n \n<\/p>\n
\nConnect and Disconnect
\n<\/h5>\n
\r\n\r\n#include <mysql\/mysql.h>\r\n\r\n\/\/*****************************************\r\n\/\/*****************************************\r\n\/\/********** CONNECT TO DATABASE **********\r\n\/\/*****************************************\r\n\/\/*****************************************\r\nvoid mysql_connect (void)\r\n{\r\n    \/\/initialize MYSQL object for connections\r\n\tmysql1 = mysql_init(NULL);\r\n\r\n    if(mysql1 == NULL)\r\n    {\r\n        fprintf(stderr, "%s\\n", mysql_error(mysql1));\r\n        return;\r\n    }\r\n\r\n    \/\/Connect to the database\r\n    if(mysql_real_connect(mysql1, "localhost", DATABASE_USERNAME, DATABASE_PASSWORD, DATABASE_NAME, 0, NULL, 0) == NULL)\r\n    {\r\n    \tfprintf(stderr, "%s\\n", mysql_error(mysql1));\r\n    }\r\n    else\r\n    {\r\n        printf("Database connection successful.\\n");\r\n    }\r\n}\r\n\r\n\r\n\r\n\/\/**********************************************\r\n\/\/**********************************************\r\n\/\/********** DISCONNECT FROM DATABASE **********\r\n\/\/**********************************************\r\n\/\/**********************************************\r\nvoid mysql_disconnect (void)\r\n{\r\n    mysql_close(mysql1);\r\n    printf( "Disconnected from database.\\n");\r\n}\r\n\r\n<\/code><\/pre>\n
\nWriting The Database (safe parameters based query)
\n<\/h4>\n
\r\n\r\n\tMYSQL_STMT *sql_statement1;\r\n\tMYSQL_BIND sql_bind_parameters1[3];\r\n\tbool sql_error = false;\r\n\t\r\n\r\n\t\/\/Setup to create query\r\n\tsql_statement1= mysql_stmt_init(mysql1);\r\n\tif (!sql_statement1)\r\n\t\tsql_error = 1;\r\n\r\n\t\r\n\t\/\/----- SET THE QUERY TEXT -----\r\n\t#define SQL_QUERY_TEXT_1 "INSERT INTO \\\r\n\t\t\t\t\t\t\t\ttest_table(some_int_column, some_string_column, some_smallint_column) \\\r\n\t\t\t\t\t\t\t\tVALUES(?,?,?)"\r\n\tif (mysql_stmt_prepare(sql_statement1, SQL_QUERY_TEXT_1, strlen(SQL_QUERY_TEXT_1)))\r\n\t\t\tsql_error = 1;\r\n\r\n\t\/\/----- SET THE QUERY PARAMETER VALUES -----\r\n\t\/\/If you want to know how many parameters are expected\r\n\t\/\/int param_count = mysql_stmt_param_count(sql_statement1);\r\n\t\r\n\t\/\/Set the parameter values\r\n\tmemset(sql_bind_parameters1, 0, sizeof(sql_bind_parameters1));\t\t\/\/Reset the parameters memory to null\r\n\r\n\t\/\/Integer\r\n\tint int_data = 10;\r\n\tsql_bind_parameters1[0].buffer_type = MYSQL_TYPE_LONG;\r\n\tsql_bind_parameters1[0].buffer = (char*)&int_data;\t\t\/\/<Note: this is a pointer!\r\n\tsql_bind_parameters1[0].is_null = 0;\r\n\tsql_bind_parameters1[0].length = 0;\r\n\r\n\t\/\/string\r\n\tchar str_data[50] = "Hello";\r\n\tunsigned long str_length = strlen(str_data);\r\n\tsql_bind_parameters1[1].buffer_type = MYSQL_TYPE_STRING;\r\n\tsql_bind_parameters1[1].buffer = (char*)str_data;\r\n\tsql_bind_parameters1[1].buffer_length = sizeof(str_data);\r\n\tsql_bind_parameters1[1].is_null = 0;\r\n\tsql_bind_parameters1[1].length = &str_length;\t\t\t\/\/<Note: this is a pointer!\r\n\r\n\t\/\/smallint\r\n\tshort small_data;\r\n\tmy_bool is_null = 1;\t\t\/\/We'll store this as null in this example\r\n\tsql_bind_parameters1[2].buffer_type = MYSQL_TYPE_SHORT;\r\n\tsql_bind_parameters1[2].buffer = (char*)&small_data;\t\/\/<Note: this is a pointer!\r\n\tsql_bind_parameters1[2].is_null = &is_null;\t\t\t\t\/\/<Note: this is a pointer!\r\n\tsql_bind_parameters1[2].length = 0;\r\n\t\r\n\t\/\/Pointers are used in the bind parameters so that if you are say adding multiple rows you can use the same query setup with new values for each execute of it.\r\n\t\r\n\t\/\/Bind the buffers\r\n\tif (mysql_stmt_bind_param(sql_statement1, sql_bind_parameters1))\r\n\t\tsql_error = 1;\r\n\r\n\t\/\/----- EXECUTE THE QUERY ------\r\n\tif (!sql_error)\r\n\t{\r\n\t\tif (mysql_stmt_execute(sql_statement1))\r\n\t\t\tsql_error = 1;\r\n\t}\r\n\r\n\t\/\/If you want to get the number of affected rows\r\n\t\/\/my_ulonglong affected_rows = mysql_stmt_affected_rows(sql_statement1);\r\n\t\/\/if (affected_rows != 1)\r\n\t\/\/{\r\n\t\/\/\tdo something\r\n\t\/\/}\r\n\r\n\t\/\/IF YOU WANT TO GET THE VALUE GENERATED FOR AN AUTO_INCREMENT COLUMN IN THE PREVIOUS INSERT\/UPDATE STATEMENT\r\n\t\/\/my_ulonglong sql_insert_id = mysql_stmt_insert_id(sql_statement1);\r\n\r\n\t\/\/If you want to do the query again then change any values you want to change and call mysql_stmt_execute(sql_statement1) again\r\n\r\n\t\/\/Close the statement\r\n\tif (sql_statement1)\r\n\t{\r\n\t\tif (mysql_stmt_close(sql_statement1))\r\n\t\t\tsql_error = 1;\r\n\t}\r\n<\/code><\/pre>\n
\nReading From The Database (safe parameters based query)
\n<\/h4>\n
\r\n\r\n\tMYSQL_STMT *sql_statement1;\r\n\tMYSQL_BIND sql_bind_parameters1[3];\r\n\tMYSQL_BIND sql_bind_results1[3];\r\n\tbool sql_error = false;\r\n\r\n\t\r\n\r\n\t\/\/Setup to create query\r\n\tsql_statement1= mysql_stmt_init(mysql1);\r\n\tif (!sql_statement1)\r\n\t\tsql_error = 1;\r\n\t\r\n\t\/\/----- SET THE QUERY TEXT -----\r\n\t#define SQL_QUERY_TEXT_1 "SELECT some_string_column FROM test_table WHERE some_int_column = ?"\r\n\tif (mysql_stmt_prepare(sql_statement1, SQL_QUERY_TEXT_1, strlen(SQL_QUERY_TEXT_1)))\r\n\t\tsql_error = 1;\r\n\r\n\t\/\/----- SET THE QUERY PARAMETER VALUES -----\r\n\t\/\/If you want to know how many parameters are expected\r\n\t\/\/int param_count = mysql_stmt_param_count(sql_statement1);\r\n\r\n\tmemset(sql_bind_parameters1, 0, sizeof(sql_bind_parameters1));\t\t\/\/Reset the parameters memory to null\r\n\r\n\t\/\/Integer\r\n\tint int_data = 10;\r\n\tsql_bind_parameters1[0].buffer_type = MYSQL_TYPE_LONG;\r\n\tsql_bind_parameters1[0].buffer = (char*)&int_data;\t\t\/\/<Note: this is a pointer!\r\n\tsql_bind_parameters1[0].length = 0;\r\n\t\r\n\t\/\/Pointers are used in the bind parameters so that if you are say adding multiple rows you can use the same query setup with new values for each execute of it.\r\n\r\n\t\/\/Bind the buffers\r\n\tif (mysql_stmt_bind_param(sql_statement1, sql_bind_parameters1))\r\n\t\tsql_error = 1;\r\n\r\n\t\/\/----- SETUP THE RESULT BUFERS -----\r\n\t\/\/Integer column\r\n\t\/\/int int_data;\r\n\t\/\/unsigned long int_length;\r\n\t\/\/my_bool int_is_null;\r\n\t\/\/my_bool int_error;\r\n\t\/\/sql_bind_results1[0].buffer_type= MYSQL_TYPE_LONG;\r\n\t\/\/sql_bind_results1[0].buffer = (char *)&int_data;\r\n\t\/\/sql_bind_results1[0].is_null= &int_is_null;\t\t\t\t\/\/<This is filled by mysql_stmt_fetch, not specified by us\r\n\t\/\/sql_bind_results1[0].length= &int_length;\t\t\t\t\/\/<This is filled by mysql_stmt_fetch, not specified by us\r\n\t\/\/sql_bind_results1[0].error= &int_error;\t\t\t\t\t\/\/<This is filled by mysql_stmt_fetch, not specified by us\r\n\r\n\t\/\/String column\r\n\tchar str_data[50] = "";\r\n\tunsigned long str_length;\r\n\tmy_bool str_is_null;\r\n\tmy_bool str_error;\r\n\tsql_bind_results1[0].buffer_type= MYSQL_TYPE_STRING;\r\n\tsql_bind_results1[0].buffer= (char *)str_data;\r\n\tsql_bind_results1[0].buffer_length= sizeof(str_data);\r\n\tsql_bind_results1[0].is_null= &str_is_null;\r\n\tsql_bind_results1[0].length= &str_length;\r\n\tsql_bind_results1[0].error= &str_error;\r\n\r\n\t\/\/Smallint column\r\n\t\/\/short small_data;\r\n\t\/\/unsigned long short_length;\r\n\t\/\/my_bool short_is_null;\r\n\t\/\/my_bool short_error;\r\n\t\/\/sql_bind_results1[0].buffer_type= MYSQL_TYPE_SHORT;\r\n\t\/\/sql_bind_results1[0].buffer= (char *)&small_data;\r\n\t\/\/sql_bind_results1[0].is_null= &short_is_null;\r\n\t\/\/sql_bind_results1[0].length= &short_length;\r\n\t\/\/sql_bind_results1[0].error= &short_error;\r\n\r\n\t\/\/Timestamp column\r\n\t\/\/MYSQL_TIME ts;\r\n\t\/\/unsigned long ts_length;\r\n\t\/\/my_bool ts_is_null;\r\n\t\/\/my_bool ts_error;\r\n\t\/\/sql_bind_results1[0].buffer_type= MYSQL_TYPE_TIMESTAMP;\r\n\t\/\/sql_bind_results1[0].buffer= (char *)&ts;\r\n\t\/\/sql_bind_results1[0].is_null= &ts_is_null;\r\n\t\/\/sql_bind_results1[0].length= &ts_length;\r\n\t\/\/sql_bind_results1[0].error= &ts_error;\r\n\t\r\n\tif (mysql_stmt_bind_result(sql_statement1, sql_bind_results1))\r\n\t\tsql_error = 1;\r\n\t\t\r\n\t\/\/----- EXECUTE THE QUERY ------\r\n\tif (!sql_error)\r\n\t{\r\n\t\tif (mysql_stmt_execute(sql_statement1))\r\n\t\t\tsql_error = 1;\r\n\t}\r\n    \r\n    if (!sql_error)\r\n\t{\r\n\t\t\/\/OPTIONAL - If you want to fetch all results in 1 operation include this, to get each row from the server one by one comment out\/discard:\r\n\t\t\/\/if (mysql_stmt_store_result(sql_statement1))\r\n\t\t\/\/{\r\n\t\t\/\/\tsql_error = 1;\r\n\t\t\/\/\tstd::cout << "Database error: " << mysql_errno(mysql1) << " - " << mysql_error(mysql1) << std::endl;\r\n\t\t\/\/}\r\n\t\twhile(!mysql_stmt_fetch(sql_statement1))\r\n\t\t{\r\n\t\t\t\/\/Get next result\r\n\t\t\tstring mystring = str_data; \r\n\t\t}\r\n\t}\r\n\r\n\t\/\/Close the statement\r\n\tif (sql_statement1)\r\n\t{\r\n\t\tif (mysql_stmt_close(sql_statement1))\r\n\t\t\tsql_error = 1;\r\n\t}\r\n<\/code><\/pre>\n
\nWriting The Database (Without parameters – the old simpler but not so safe method)
\n<\/h4>\n
\r\n\r\n\r\nvoid mysql_write_something (void)\r\n{\r\n   \/\/vector times;   \/\/a vector of alarm times\r\n\r\n    if(mysql1 != NULL)\r\n    {\r\n        \/\/Retrieve all data from alarm_times\r\n        if (mysql_query(mysql1, "INSERT INTO settings (   \\\r\n\t\t\t\t\tid,   \\\r\n\t\t\t\t\tvalue_string   \\\r\n\t\t\t\t) VALUES (   \\\r\n\t\t\t\t\t99,   \\\r\n\t\t\t\t\t'Hello'   \\\r\n\t\t\t\t) \\\r\n\t\t\t\tON DUPLICATE KEY UPDATE   \\\r\n\t\t\t\t\tid = 99,   \\\r\n\t\t\t\t\tvalue_string = 'Hellow'   \\\r\n\t\t\t\t"))\r\n\r\n        {\r\n            fprintf(stderr, "%s\\n", mysql_error(mysql1));\r\n            return;\r\n        }\r\n    }\r\n}\r\n\r\n<\/code><\/pre>\n
\n \n<\/p>\n
\nReading From The Database (Without parameters – the old simpler but not so safe method)
\n<\/h4>\n
\r\n\r\n    mysql_connect();\r\n\r\n    if (mysql1 != NULL)\r\n    {\r\n        if (!mysql_query(mysql1, "SELECT value_int, value_string FROM settings WHERE id = 8"))\r\n        {\r\n        \tMYSQL_RES *result = mysql_store_result(mysql1);\r\n        \tif (result != NULL)\r\n        \t{\r\n        \t\t\/\/Get the number of columns\r\n        \t\tint num_rows = mysql_num_rows(result);\r\n        \t\tint num_fields = mysql_num_fields(result);\r\n\r\n        \t\tMYSQL_ROW row;\t\t\t\/\/An array of strings\r\n        \t\twhile( (row = mysql_fetch_row(result)) )\r\n        \t\t{\r\n        \t\t\tif(num_fields >= 2)\r\n        \t\t\t{\r\n        \t\t\t\tchar *value_int = row[0];\r\n        \t\t\t\tchar *value_string = row[1];\r\n\r\n        \t\t\t\tprintf( "Got value %s\\n", value_string);\r\n        \t        }\r\n        \t\t}\r\n   \t            mysql_free_result(result);\r\n        \t}\r\n        }\r\n\r\n    }\r\n\r\n    mysql_disconnect();\r\n<\/code><\/pre>\n
\n \n<\/p>\n
\n <\/p>\n","protected":false},"excerpt":{"rendered":"
Connecting Global Things #define DATABASE_NAME "YOUR_DATABASE_NAME" #define DATABASE_USERNAME "YOUR_DATABASE_USERNAME" #define DATABASE_PASSWORD "YOUR_DATABASE_PASSWORD" MYSQL *mysql1;   Connect and Disconnect #include <mysql\/mysql.h> \/\/***************************************** \/\/***************************************** \/\/********** CONNECT TO DATABASE ********** \/\/***************************************** \/\/***************************************** void mysql_connect (void) { \/\/initialize MYSQL object for connections mysql1 = mysql_init(NULL); if(mysql1 == NULL) { fprintf(stderr, "%s\\n", mysql_error(mysql1)); return; } \/\/Connect to the database if(mysql_real_connect(mysql1, […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[73],"tags":[],"class_list":["post-1119","post","type-post","status-publish","format-standard","hentry","category-mysql"],"_links":{"self":[{"href":"https:\/\/raspberry-projects.com\/pi\/wp-json\/wp\/v2\/posts\/1119","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/raspberry-projects.com\/pi\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/raspberry-projects.com\/pi\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/raspberry-projects.com\/pi\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/raspberry-projects.com\/pi\/wp-json\/wp\/v2\/comments?post=1119"}],"version-history":[{"count":21,"href":"https:\/\/raspberry-projects.com\/pi\/wp-json\/wp\/v2\/posts\/1119\/revisions"}],"predecessor-version":[{"id":2051,"href":"https:\/\/raspberry-projects.com\/pi\/wp-json\/wp\/v2\/posts\/1119\/revisions\/2051"}],"wp:attachment":[{"href":"https:\/\/raspberry-projects.com\/pi\/wp-json\/wp\/v2\/media?parent=1119"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/raspberry-projects.com\/pi\/wp-json\/wp\/v2\/categories?post=1119"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/raspberry-projects.com\/pi\/wp-json\/wp\/v2\/tags?post=1119"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}