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); }