int main()
{
MYSQL mysql;
char record[1000];
MYSQL_RES *result;
MYSQL_ROW row;
unsigned int num_fields;
unsigned int i;
char query_def[1000];
mysql_init(&mysql);
mysql_options(&mysql,MYSQL_READ_DEFAULT_GROUP,"blah");
if (!mysql_real_connect(&mysql,"host","username","password","database",0,NULL,0))
{
} else {
strmov(query_def,"SELECT * from bb_ib_forums");
if(mysql_exec_sql(&mysql,query_def)==0)
{
result = mysql_store_result(&mysql);
} else {
printf( "Error" );
}
print_table_data_xml(result,"forums_get_all");
}
}
/*
* return = 0; OK
*/
int mysql_fetch_string(MYSQL *mysql,const char *create_definition,
char* result_store)
{
MYSQL_RES *result;
MYSQL_ROW row;
if(mysql_exec_sql(mysql, create_definition) != 0)
{
st_d_print("exec sql failed:%s\n", create_definition);
st_d_print(mysql_error(mysql));
return -1;
}
result = mysql_store_result(mysql);
if (!result)
{
st_d_print("mysql_store_result failed!\n");
st_d_print(mysql_error(mysql));
return -1;
}
if( (row = mysql_fetch_row(result)) )
strcpy(result_store, row[0]);
mysql_free_result(result);
return 0;
}
// return 0 if not exist
int mysql_check_exist(MYSQL *mysql,const char *create_definition)
{
MYSQL_RES *result;
int ret = 0;
if(mysql_exec_sql(mysql, create_definition) != 0)
{
st_d_print("exec sql failed:%s\n", create_definition);
st_d_print(mysql_error(mysql));
return 0;
}
result = mysql_store_result(mysql);
if (!result)
{
st_d_print("mysql_store_result failed!\n");
st_d_print(mysql_error(mysql));
return 0;
}
if(mysql_affected_rows(mysql) != 0)
ret = 1;
mysql_free_result(result);
return ret;
}
int putSensorValue(MYSQL *mysql, TABLE *table, int sensorNo) {
char query_def[2048];
char db_type;
unsigned long *lengths;
unsigned int num_fields;
MYSQL_RES *result = NULL;
MYSQL_ROW row;
/*
* Check which database scenario we are dealing with,
* and select appropriate query.
*/
if(!table->key_field) {
/* This is the case when the timestamp is the primary key */
db_type = 'B';
} else if(!table->key_field2) {
/* This is the case when the primray key is a single attribute */
db_type = 'A';
} else {
/* This is the case when the primary key is two separate attributes */
db_type = 'C';
}
switch( db_type )
{
case 'A' :
sprintf(query_def,"select %s, %s from %s where %s = \"%s\" and %s > \"%s\" order by %s DESC limit 1",
table->time_field, table->sensor[sensorNo].value_field, table->name,
table->key_field, table->sensor[sensorNo].database_key, table->time_field,
table->sensor[sensorNo].lasttime, table->time_field);
break;
case 'B' :
sprintf(query_def,"select %s, %s from %s where %s > \"%s\" order by %s DESC limit 1",
table->time_field, table->sensor[sensorNo].value_field, table->name,
table->time_field, table->sensor[sensorNo].lasttime, table->time_field);
break;
case 'C' :
sprintf(query_def,"select %s, %s from %s where %s = \"%s\" and %s = \"%s\" and %s > \"%s\" order by %s DESC limit 1",
table->time_field, table->sensor[sensorNo].value_field, table->name,
table->key_field, table->sensor[sensorNo].database_key, table->key_field2, table->sensor[sensorNo].database_key2,
table->time_field, table->sensor[sensorNo].lasttime, table->time_field);
break;
}
if (VERBOSE)
printf("query_def: %s\n", query_def);
/* Execute the query */
if(mysql_exec_sql(mysql,query_def)==0)/*success*/
{
result = mysql_store_result(mysql);
if (VERBOSE)
printf( "%ld Record Found\n",(long) mysql_affected_rows(mysql));
/* If there are results... */
if (result && mysql_affected_rows(mysql) > 0) {
num_fields = mysql_num_fields(result);
if (num_fields < 2)
return (-1);
while ((row = mysql_fetch_row(result))) {
lengths = mysql_fetch_lengths(result);
/* update last timestamp */
if (table->sensor[sensorNo].lasttime)
free(table->sensor[sensorNo].lasttime);
table->sensor[sensorNo].lasttime = calloc((int) lengths[0], sizeof(char));
sprintf(table->sensor[sensorNo].lasttime, "%.*s", (int) lengths[0], row[0]);
/* Add sensor value */
if (table->sensor[sensorNo].value)
free(table->sensor[sensorNo].value);
table->sensor[sensorNo].value = calloc((int) lengths[1], sizeof(char));
sprintf(table->sensor[sensorNo].value, "%.*s", (int) lengths[1], row[1] ? row[1] : NULL);
}
mysql_free_result(result);
}
else { // mysql_store_result() returned nothing
if(mysql_field_count(mysql) > 0) // mysql_store_result() should have returned data
printf( "Error getting records: %s\n", mysql_error(mysql));
}
}
else {
printf( "Failed to find any records and caused an error: %s\n", mysql_error(mysql));
}
return (EXIT_SUCCESS);
}
int putMaxTimestamp(MYSQL *mysql, TABLE *table, int sensorNo) {
char query_def[2048];
char db_type;
unsigned long *lengths;
MYSQL_RES *result = NULL;
MYSQL_ROW row;
/*
* Check which database scenario we are dealing with,
* and select appropriate query.
*/
if(!table->key_field) {
/* This is the case when the timestamp is the primary key */
db_type = 'B';
} else if(!table->key_field2) {
/* This is the case when the primray key is a single attribute */
db_type = 'A';
} else {
/* This is the case when the primary key is two separate attributes */
db_type = 'C';
}
switch( db_type )
{
case 'A' :
sprintf(query_def,"select max(%s) from %s where %s = \"%s\"", table->time_field, table->name, table->key_field, table->sensor[sensorNo].database_key );
break;
case 'B' :
sprintf(query_def, "select max(%s) from %s", table->time_field, table->name);
break;
case 'C' :
sprintf(query_def, "select max(%s) from %s where %s = \"%s\" and %s = \"%s\"", table->time_field, table->name, table->key_field, table->sensor[sensorNo].database_key, table->key_field2, table->sensor[sensorNo].database_key2);
break;
}
if (VERBOSE) {
printf("query_def: %s\n", query_def);
printf("table->key_field = %s\n", table->key_field);
printf("table->key_field2 = %s\ndb _type %c\n", table->key_field2, db_type);
}
if(mysql_exec_sql(mysql,query_def)==0)/*success*/
{
result = mysql_store_result(mysql);
if (VERBOSE)
printf("Result successful\n");
if (result)
{
row = mysql_fetch_row(result);
lengths = mysql_fetch_lengths(result);
if (table->sensor[sensorNo].lasttime)
free(table->sensor[sensorNo].lasttime);
table->sensor[sensorNo].lasttime = calloc((int) lengths[0], sizeof(char));
sprintf(table->sensor[sensorNo].lasttime, "%.*s", (int) lengths[0], row[0]);
}
mysql_free_result(result);
}
return (EXIT_SUCCESS);
}
