NOTE:
All session related information in MySQL is stored in a table called "SESSION_STATUS". Therefore, we will be querying this table for MySQL counter values.
For demonstration purpose, the script below will query the values for LAST_QUERY_COST, OPENED_TABLES, QCACHE_HITS, SELECT_FULL_JOIN and SELECT_SCAN counters.
Global declaration
#include "C:\\Program Files\\MySQL\\oldfiles\\include\\mysql.h" //mySQL.h path is included /*MySQL structure defined*/ MYSQL *mySQL; MYSQL_ROW row; MYSQL_RES *result; int MyRC; char *MySQLserver = "localhost"; // Location where server is running char *MySQLuser = "root"; // User name used to connect to the database char *MySQLpassword = ""; // Not a good idea to leave password empty char *MySQLdatabase = "information_schema"; //Database name int MySQLport = 3306; // Database port
Vvuser_int
//libmysql.dll file loaded using lr_load_dll function
MyRC= lr_load_dll("C:\\Program Files\\MySQL\\MySQL Connector C 6.0.2\\lib\\opt\\libmysql.dll");
//Initialise mySQL connection handler
mySQL= mysql_init(NULL);
// Connect to the database
mysql_real_connect(mySQL,MySQLserver, MySQLuser, MySQLpassword, MySQLdatabase, MySQLport,NULL,0);
return 0;
Query Function
double atof(const char *string); // Explicit declaration
Query()
{
int i=0;
float VarValue[5]; //Float variable array
/*Save SQL statement into variable into sqlQuery.
This query returns values for the variable name ={LAST_QUERY_COST,OPENED_TABLES,QCACHE_HITS,SELECT_FULL_JOIN,SELECT_SCAN}*/
lr_param_sprintf("sqlQuery","select variable_value from session_status where variable_name IN"
"('LAST_QUERY_COST','OPENED_TABLES','QCACHE_HITS','SELECT_FULL_JOIN','SELECT_SCAN')");
mysql_query(mySQL, lr_eval_string ("{sqlQuery}")); //Execute SQL statement
result = mysql_store_result(mySQL); //Result of the sql statement is placed into MYSQL_RES result structure
row=mysql_fetch_row(result);
while(row!=NULL) //Iterate through to last row
{
VarValue[i]=atof(row[0]); //Save float row value to VarValue
row=mysql_fetch_row(result); //Retrive next row of the fetched result
i++;
}
/*Use lr_user_data_point function to generate the graph*/
lr_user_data_point("LAST_QUERY_COST", VarValue[0]);
lr_user_data_point("OPEN_TABLES", VarValue[1]);
lr_user_data_point("QCACHE_HITS", VarValue[2]);
lr_user_data_point("SELECT_FULL_JOIN", VarValue[3]);
lr_user_data_point("SELECT_SCAN", VarValue[4]);
return 0;
}
vuser_end
/*Free the memory allocated to result structure and close the database connection*/ mysql_free_result(result); mysql_close(mySQL); return 0;
NOTE: Make sure you are explicitly declaring the atof function before using it or else you will get totally different values. See the screenshot of the values received for the above counters when atof was not declared explicitly before using it.
