int main(int argc, char **argv) {
MYSQL *conn;
MYSQL_RES *res;
MYSQL_ROW row;
MYSQL_RES *result;
int num_fields;
int i;
char *server = "localhost";
char *user = "******";
char *password = "******"; /* set me first */
char *database = "first";
conn = mysql_init(NULL);
printf("MySQL client version: %s\n", mysql_get_client_info());
conn = mysql_init(NULL);
if (conn == NULL) {
printf("Error %u: %s\n", mysql_errno(conn), mysql_error(conn));
exit(1);
}
// connect to database
if (!mysql_real_connect(conn, server, user, password, database, 0, NULL, 0)) {
fprintf(stderr, "%s\n", mysql_error(conn));
exit(1);
}
// send SQL query
if (mysql_query(conn, "show tables")) {
fprintf(stderr, "%s\n", mysql_error(conn));
exit(1);
}
res = mysql_use_result(conn);
// output table name
printf("MySQL Tables in mysql database:\n");
while ((row = mysql_fetch_row(res)) != NULL)
printf("%s \n", row[0]);
mysql_query(conn, "SELECT * FROM authors");
result = mysql_store_result(conn);
num_fields = mysql_num_fields(result);
while ((row = mysql_fetch_row(result))) {
for (i = 0; i < num_fields; i++) {
printf("%s ", row[i] ? row[i] : "NULL");
}
printf("\n");
}
// close connection
mysql_free_result(res);
mysql_close(conn);
return 0;
}
//仅按照时间顺序和用户名获取一定数量的开锁记录,当lock为全0，openuser不为null时候
OLInfo *getOLInfoByTimeUser(uint64 starttime,uint8 num,char *username)
{
if((starttime == (uint64)0) || (num == (uint8)0) || (username == NULL))
return NULL;
MYSQL *conn = getIdleConn();
MYSQL_RES *res; //查询的result
MYSQL_ROW row;
char *sql_str = NULL; //sql语句
//设置字符编码为utf8
mysql_setUTF8(conn);
//设置插入语句
sql_str = (char *)malloc(sizeof(char) * 200);
memset(sql_str,0,200);
sprintf(sql_str,"select OpenLockInfo.lockid,open_time,close_time from OpenLockInfo,UserLockInfo where open_time > %llu and userlock_id = id and username='******' order by open_time limit %1u",starttime,username,num);
//执行查询
if(mysql_query(conn,sql_str))
{
debug("getOLInfoByID error\n");
recycleConn(conn);
free(sql_str);
return NULL;
}
//获取查询结果
res = mysql_use_result(conn);
OLInfo *info = NULL, *preInfo = NULL,*curInfo = NULL;
//如果查询结果不为空
if((row = mysql_fetch_row(res)) != NULL)
{
curInfo = (OLInfo *)malloc(sizeof(OLInfo));
memset(curInfo,0,sizeof(OLInfo));
curInfo->lock = (uint8)atoi(row[0]);
curInfo->open_time = (uint64)atoll(row[1]);
if(row[2] != NULL)
curInfo->close_time = (uint64)atoll(row[2]);
else
curInfo->close_time = 0;
info = curInfo;
preInfo = curInfo;
while((row = mysql_fetch_row(res)) != NULL)
{
curInfo = (OLInfo *)malloc(sizeof(OLInfo));
memset(curInfo,0,sizeof(OLInfo));
curInfo->lock = (uint8)atoi(row[0]);
curInfo->open_time = (uint64)atoll(row[1]);
if(row[2] != NULL)
curInfo->close_time = (uint64)atoll(row[2]);
else
curInfo->close_time = 0;
preInfo->next = curInfo;
preInfo = preInfo->next;
}
}
//释放资源
mysql_free_result(res);
recycleConn(conn);
free(sql_str);
return info;
}
/* use_result() */
static VALUE use_result(VALUE obj)
{
MYSQL* m = GetHandler(obj);
MYSQL_RES* res = mysql_use_result(m);
if (res == NULL)
mysql_raise(m);
return mysqlres2obj(res);
}
void loadbotservdb()
{
char nick[NICKLEN+1];
char ident[NICKLEN+1];
char host[HOSTLEN+1];
char chan[CHANLEN+1];
MYSQL_RES *result;
MYSQL_ROW row;
if (!reconnect_to_db()) {
fprintf(stderr,"Cannot connect to db\n");
operlog("Cannot connect to db");
return;
}
mysql_query(&mysql,"SELECT * FROM child_botserv_bots");
if ((result = mysql_use_result(&mysql)) == NULL) {
fprintf(stderr,"CRITICAL: Cannot load botserv bots table: mysql_use_result returned NULL\n");
return;
}
while ((row = mysql_fetch_row(result))) {
strncpy(nick,row[0],NICKLEN);
strncpy(ident,row[1],NICKLEN);
strncpy(host,row[2],HOSTLEN);
if (find_bot(nick)) continue;
addBot(nick,ident,host);
if (vv) printf("Bot %s added (%s@%s)\n",nick,ident,host);
}
mysql_query(&mysql,"SELECT * FROM child_botserv_chans");
if ((result = mysql_use_result(&mysql)) == NULL) {
fprintf(stderr,"CRITICAL: Cannot load botserv chans table: mysql_use_result returned NULL\n");
return;
}
while ((row = mysql_fetch_row(result))) {
strncpy(chan,row[0],CHANLEN);
strncpy(nick,row[1],NICKLEN);
if (find_chanbot(chan)) continue;
if (!find_bot(nick)) continue;
if (!find_channel(chan)) continue;
addChanbot(chan,nick);
if (vv) printf("Chanbot %s added with bot %s\n",chan,nick);
}
mysql_close(&mysql);
}
void dbMySQLPrepareForQuery(void)
{
MYSQL_RES *mysql_res;
mysql_res = mysql_use_result(mysql);
if (mysql_res) {
mysql_free_result(mysql_res);
}
}
std::auto_ptr<CUseResult> CConnection::useResult()
{
H_AUTO(CConnection_useResult);
MYSQL_RES *res = mysql_use_result(_MysqlContext);
std::auto_ptr<CUseResult> sr = std::auto_ptr<CUseResult>(new CUseResult(res));
return sr;
}
/* Get an artist id for an artist title. Add it to the db if not found,
* otherwise return the existing id. Return 0 for any error.
*/
static int get_artist_id(char *artist) {
MYSQL_RES *res_ptr;
MYSQL_ROW mysqlrow;
int res;
char qs[250];
char is[250];
char es[250];
int artist_id = -1;
// look up the artist to see if they exist already.
// use es for escaped name, qs for the constructed querystring
// if there are multiple matches, use the first (shouldn't happen)
mysql_escape_string(es, artist, strlen(artist));
sprintf(qs, "SELECT id FROM artist WHERE name = '%s'", es);
res = mysql_query(&my_connection, qs);
if (res) {
fprintf(stderr, "SELECT error: %s\n", mysql_error(&my_connection));
} else {
// use store_result, not use_result, so we can then use mysql_num_rows.
// we expect the result to be small, so ram isn't an issue.
res_ptr = mysql_store_result(&my_connection);
if (res_ptr) {
if (mysql_num_rows(res_ptr) > 0) {
if (mysqlrow = mysql_fetch_row(res_ptr)) {
sscanf(mysqlrow[0], "%d", &artist_id);
}
}
mysql_free_result(res_ptr); // remember to free result!
}
}
if (artist_id != -1) return artist_id;
// if we get here, no artist was found. So add a new one
sprintf(is, "INSERT INTO artist(name) VALUES('%s')", es);
res = mysql_query(&my_connection, is);
if (res) {
fprintf(stderr, "Insert error %d: %s\n",
mysql_errno(&my_connection), mysql_error(&my_connection));
return 0;
}
// and we need to return the id, so get the auto-incremented value
res = mysql_query(&my_connection, "SELECT LAST_INSERT_ID()");
if (res) {
printf("SELECT error: %s\n", mysql_error(&my_connection));
return 0;
} else {
res_ptr = mysql_use_result(&my_connection);
if (res_ptr) {
if ((mysqlrow = mysql_fetch_row(res_ptr))) {
sscanf(mysqlrow[0], "%d", &artist_id);
}
mysql_free_result(res_ptr); // remember to free result!
}
}
return artist_id;
}
int MySqlConnector::query(const std::string& sql)
{
int retcode = mysql_query(&m_mysql, sql.c_str());
if(retcode == 0)
{
m_result = mysql_use_result(&m_mysql);
}
return retcode;
}
int MySqlConnector::query(const char* sql, unsigned long size)
{
int retcode = mysql_real_query(&m_mysql, sql, size);
if(retcode == 0)
{
m_result = mysql_use_result(&m_mysql);
}
return retcode;
}
bool CMYSQL::MysqlUseResult()
{
pmysqlRes = mysql_use_result(pmysqlConn);
if(!pmysqlRes)
{
return false;
}
return true;
}
UseQueryResult Query::use()
{
executeImpl();
MYSQL_RES * res = mysql_use_result(conn->getDriver());
if (!res)
onError(conn->getDriver());
return UseQueryResult(res, conn, this);
}
int main(int argc, char *argv[]){
MYSQL *conn;
MYSQL_RES *res;
MYSQL_ROW row;
char *server ="localhost";
char *user ="******";
char *password ="******";/* set me first */
char *database ="lsf";
conn = mysql_init(NULL);
/* Connect to database */
if (!mysql_real_connect(conn,
server,
user,
password,
database,
0,
NULL,
0)){
fprintf(stderr,"%s\n",mysql_error(conn));
exit(1);
}
char *qstr;
qstr = (char *)malloc(200*sizeof(char));
strcpy(qstr,"select user_internal_id from user where user_name = '");
strcat(qstr,argv[1]);
strcat(qstr,"'");
printf("Query is %s\n",qstr);
/* send SQL query */
if (mysql_query(conn,qstr)){
fprintf(stderr,"%s\n",mysql_error(conn));
exit(1);
}
res = mysql_use_result(conn);
/* output table name */
while((row = mysql_fetch_row(res))!= NULL) {
printf("User %s\n",row[0]);
}
/* close connection */
mysql_free_result(res);
mysql_close(conn);
return 1;
}
int Query::ExecSql(string sql)
{
int ret = mysql_query(&mysql, sql.c_str());
if(ret)
return -1;
res = mysql_use_result(&mysql);
if(!res)
return -1;
rowcount = mysql_field_count(&mysql);
}
int ConnectSQL::queryMYSQL(const char *query) {
if (mysql_query(_mysql, query)) {
std::cerr << mysql_error(_mysql) << std::endl;
mysql_close(_mysql);
return 1;
}
_res = mysql_use_result(_mysql);
return 0;
}
int Team5_DB::GetUserInfo(char *Name, UserInfo &RetInfo)
{
sprintf(buffer, "select ID, Password, Score from UserData where Name = '%s'", Name);
if (mysql_query(mysql, buffer))
{
printf("User is not exist...\n");
return -1;
}
result = mysql_use_result(mysql);
if (!result)
{
printf("GetUserInfo Error\n");
return -1;
}
row = mysql_fetch_row(result) ;
if (!row)
{
printf("GetUserInfo Error\n");
return -1;
}
RetInfo.ID = atoi(row[0]);
if (RetInfo.ID < 0)
{
printf("GetUserInfo Error\n");
return -1;
}
strcpy(RetInfo.Password, row[1]);
RetInfo.Score = atoi(row[2]);
if (RetInfo.Score < 0)
{
printf("GetUserInfo Error\n");
return -1;
}
mysql_free_result(result);
RetInfo.Rank = GetUserRank(Name);
if (RetInfo.Rank < 0)
{
printf("GetUserInfo Error\n");
return -1;
}
if (Name != RetInfo.Name)
{
strcpy(RetInfo.Name, Name);
}
return 0;
}
/*-------------------------------------------*/
static int get_artist_id(char *artist)
{
MYSQL_RES *res_ptr;
MYSQL_ROW mysqlrow;
int res;
char qs[100];
char is[100];
char es[100];
int artist_id = -1;
/* does it already exist? */
mysql_escape_string(es, artist, strlen(artist));
sprintf(qs, "SELECT id FROM artist WHERE name = '%s'", es);
res = mysql_query(&my_connection, qs);
if(res)
fprintf(stderr, "SELECT error: %s\n", mysql_error(&my_connection));
else {
res_ptr = mysql_store_result(&my_connection);
if(res_ptr) {
if(mysql_num_rows(res_ptr) > 0)
if(mysqlrow = mysql_fetch_row(res_ptr))
sscanf(mysqlrow[0], "%d", &artist_id);
mysql_free_result(res_ptr);
}
}
if(artist_id != -1) return artist_id;
sprintf(is, "INSERT INTO artist(name) VALUES ('%s')", es);
res = mysql_query(&my_connection, is);
if(res) {
fprintf(stderr, "Insert error %d : %s\n", mysql_errno(&my_connection),
mysql_error(&my_connection));
return 0;
}
res = mysql_query(&my_connection, "SELECT LAST_INSERT_ID()");
if(res) {
fprintf(stderr, "SELECT error %d: %s\n", mysql_errno(&my_connection),
mysql_error(&my_connection));
return 0;
}
else {
res_ptr = mysql_use_result(&my_connection);
if(res_ptr) {
if((mysqlrow = mysql_fetch_row(res_ptr)))
sscanf(mysqlrow[0], "%d", &artist_id);
mysql_free_result(res_ptr);
}
}
return artist_id;
}
main() {
MYSQL *conn;
MYSQL_RES *res;
MYSQL_RES *res2;
MYSQL_ROW row;
char *server = "127.0.0.1";
char *user = "******";
char *password = "******";
char *database = "test";
unsigned int port = 1;
conn = mysql_init(NULL);
/* Connect to database */
if (!mysql_real_connect(conn, server,
user, password, database, 3306, NULL, 0)) {
fprintf(stderr, "%s\n", mysql_error(conn));
exit(1);
}
/* send SQL query */
if (mysql_query(conn, "show tables")) {
fprintf(stderr, "%s\n", mysql_error(conn));
//exit(1);
}
res = mysql_use_result(conn);
/* output table name */
printf("MySQL Tables in mysql database:\n");
while ((row = mysql_fetch_row(res)) != NULL)
printf("%s \n", row[0]);
/* close connection */
if (mysql_query(conn, "SELECT * FROM col2;")) {
fprintf(stderr, "%s\n", mysql_error(conn));
//exit(1);
}
res2 = mysql_use_result(conn);
while ((row = mysql_fetch_row(res2)) != NULL)
printf("%s, %s \n", row[0], row[1]);
mysql_free_result(res);
mysql_close(conn);
}
//获取第一级温度信息
LTInfo *getTempFirstInfo(uint64 starttime,uint8 drum,uint8 num)
{
if((starttime == (uint64)0) || (drum == (uint8)0) || (num == (uint8)0))
return NULL;
MYSQL *conn = getIdleConn();
MYSQL_RES *res; //查询的result
MYSQL_ROW row;
char *sql_str = NULL; //sql语句
//设置字符编码为utf8
mysql_setUTF8(conn);
//设置插入语句
sql_str = (char *)malloc(sizeof(char) * 200);
memset(sql_str,0,200);
sprintf(sql_str,"select * from TempFirstInfo where time > %llu and drum = %1u order by time limit %1u",starttime,drum,num);
//执行查询
if(mysql_query(conn,sql_str))
{
debug("get temp first info error\n");
recycleConn(conn);
free(sql_str);
return NULL;
}
//获取查询结果
res = mysql_use_result(conn);
LTInfo *info = NULL, *preInfo = NULL,*curInfo = NULL;
//如果查询结果不为空
if((row = mysql_fetch_row(res)) != NULL)
{
curInfo = (LTInfo *)malloc(sizeof(LTInfo));
memset(curInfo,0,sizeof(LTInfo));
curInfo->time = atoll(row[0]);
curInfo->drum = (uint8)atoi(row[1]);
curInfo->lt.temp = atof(row[2]);
info = curInfo;
preInfo = curInfo;
while((row = mysql_fetch_row(res)) != NULL)
{
curInfo = (LTInfo *)malloc(sizeof(LTInfo));
memset(curInfo,0,sizeof(LTInfo));
curInfo->time = atoll(row[0]);
curInfo->drum = (uint8)atoi(row[1]);
curInfo->lt.temp = atof(row[2]);
preInfo->next = curInfo;
preInfo = preInfo->next;
}
}
//释放资源
mysql_free_result(res);
recycleConn(conn);
free(sql_str);
return info;
}
void loaduserdb()
{
char accname[50];
int acclevel;
int seen,options,timeout;
char md5_pass[35];
User *uptr;
MYSQL_RES *result;
MYSQL_ROW row;
if (!reconnect_to_db()) {
fprintf(stderr,"Cannot connect to db\n");
operlog("Cannot connect to db");
return;
}
mysql_query(&mysql,"SELECT * FROM child_users");
if ((result = mysql_use_result(&mysql)) == NULL) {
printf("CRITICAL: Cannot load user table: mysql_use_result returned NULL\n");
return;
}
while ((row = mysql_fetch_row(result))) {
strncpy(accname,row[0],50);
acclevel = strtol(row[1],NULL,10);
seen = strtol(row[2],NULL,10);
strncpy(md5_pass,row[4],35);
options = strtol(row[5],NULL,10);
timeout = strtol(row[6],NULL,10);
if (!timeout) timeout = TIMEOUT_DFLT;
if (vv) printf("Adding user %s with level %d and pass %s and vhost %s\n",accname,acclevel,md5_pass,row[3]);
if (find_user(accname))
continue;
uptr = AddUser(accname,acclevel);
if (seen) uptr->lastseen = seen; else uptr->lastseen = time(NULL);
uptr->options = options;
uptr->timeout = timeout;
if (vv) printf("User %s added (%p)\n",accname,uptr);
strncpy(uptr->vhost,row[3],HOSTLEN);
strncpy(uptr->email,row[7],EMAILLEN);
uptr->regtime = strtol(row[8], NULL, 10);
memset(uptr->md5_pass,'\0',34);
strncpy(uptr->md5_pass,md5_pass,32);
}
mysql_close(&mysql);
}
MYSQL_RES* mysql_perform_query(MYSQL *connection, char *sql_query)
{
// send the query to the database
if (mysql_query(connection, sql_query))
{
printf("MySQL query error : %s\n", mysql_error(connection));
exit(1);
}
return mysql_use_result(connection);
}
extern unsigned int db_d2char_loaddata(char const * db_table,char const * account,char const * charname,char const * realmname,unsigned char * chardata, unsigned int chardata_size)
{
unsigned long * fieldlengths;
unsigned int size;
/* This shall allow us to reconnect, after a disconnected MySQL link */
if (!db_connect(&loaddata.mysql)) return 0;
if (!(loaddata.sql = malloc(strlen("SELECT chardata FROM WHERE accname=\'\' AND charname=\'\' AND realm=\'\' AND recage=0")+strlen(db_table)+strlen(account)+strlen(charname)+strlen(realmname)+1)))
{
eventlog(eventlog_level_error, __FUNCTION__, "unable to allocate memory for query");
free(loaddata.sql);
return 0;
}
sprintf(loaddata.sql, "SELECT chardata FROM %s WHERE accname=\'%s\' AND charname=\'%s\' AND realm=\'%s\' AND recage=0",db_table,account,charname,realmname);
if(mysql_query(&loaddata.mysql,loaddata.sql))
{
eventlog(eventlog_level_error, __FUNCTION__, "error selecting character \"%s(*%s)@%s\" (%d:%s)",charname,account,realmname,mysql_errno(&loaddata.mysql),mysql_error(&loaddata.mysql));
free(loaddata.sql);
return 0;
}
free(loaddata.sql);
if(!(loaddata.res = mysql_use_result(&loaddata.mysql)))
{
eventlog(eventlog_level_error, __FUNCTION__, "result error in character \"%s(*%s)@%s\" (%d:%s)",charname,account,realmname,mysql_errno(&loaddata.mysql),mysql_error(&loaddata.mysql));
return 0;
}
if(!(loaddata.row = mysql_fetch_row(loaddata.res)))
{
eventlog(eventlog_level_error, __FUNCTION__, "record not found for character \"%s(*%s)@%s\"",charname,account,realmname);
return 0;
}
fieldlengths = mysql_fetch_lengths(loaddata.res);
if(!fieldlengths[0])
{
eventlog(eventlog_level_error, __FUNCTION__, "read zero chardata size for character \"%s(*%s)@%s\"",charname,account,realmname);
return 0;
}
if(fieldlengths[0] > chardata_size)
{
eventlog(eventlog_level_error, __FUNCTION__, "chardata record is larger than buffer size (%lu > %u) for character \"%s(*%s)@%s\"",fieldlengths[0],chardata_size,charname,account,realmname);
return 0;
}
memcpy(chardata, loaddata.row[0], (unsigned long) fieldlengths[0]);
size=(unsigned int) fieldlengths[0];
mysql_free_result(loaddata.res);
eventlog(eventlog_level_trace, __FUNCTION__, "successfully read data record \"%s(*%s)@%s\"",charname,account,realmname);
return size;
}
//获取用户单个信息,获取的信息空间需要显式释放,如果获取的是int，则需要自己调用atoi函数转换
char *getUserDetail(char *username,enum UserDetail type)
{
if(username == NULL)
return NULL;
MYSQL *conn = getIdleConn();
MYSQL_RES *res; //查询的result
MYSQL_ROW row;
char *sql_str = NULL; //sql语句
//设置字符编码为utf8
mysql_setUTF8(conn);
//设置插入语句
sql_str = (char *)malloc(sizeof(char) * 200);
memset(sql_str,0,200);
switch(type)
{
case USER_PASSWORD: sprintf(sql_str,"select password from UserDetail where username = '******'",username); break;
case USER_PHONE: sprintf(sql_str,"select cellphone from UserDetail where username = '******'",username); break;
case USER_RNAME: sprintf(sql_str,"select real_name from UserDetail where username = '******'",username); break;
default :
debug("get user detail unrecognized cmd error\n");
recycleConn(conn);
free(sql_str);
return NULL;
}
//执行查询
if(mysql_query(conn,sql_str))
{
debug("get user detail error\n");
recycleConn(conn);
free(sql_str);
return NULL;
}
//获取查询结果
res = mysql_use_result(conn);
//如果查询结果不为空
if((row = mysql_fetch_row(res)) != NULL)
{
int len = strlen(row[0]);
char *info = (char *)malloc(len + 1); //加上结束位
memcpy(info,row[0],len);
info[len] = '\0'; //加上结束位
//释放资源
mysql_free_result(res);
recycleConn(conn);
free(sql_str);
return info;
}
//未查到数据
mysql_free_result(res);
recycleConn(conn);
free(sql_str);
return NULL;
}
const void*
MySQLDriver::execute(const char* query)
{
int error;
if ((error = mysql_query(m_mysql.get(), query))) {
return NULL;
}
return mysql_use_result(m_mysql.get());
}
int ConnectSQL::queryMYSQL(const char *query) {
//std::cout << "Query : " << query << std::endl;
if (mysql_query(mysql, query)) {
std::cout << "Query : " << query << std::endl;
std::cerr << mysql_error(mysql) << std::endl;
mysql_close(mysql);
return 1;
}
result = mysql_use_result(mysql);
return 0;
}
//--------------------------------------------------------------------------
ResUse Connection::use(const string &str, bool throw_excptns)
{
Success = false;
if (lock())
if (throw_excptns) throw BadQuery(error());
else return ResUse();
Success = !mysql_query(&mysql, str.c_str());
if (!Success)
if (throw_excptns) throw BadQuery(error());
else return ResUse();
return ResUse(mysql_use_result(&mysql), this);
}
int login_user(char *buffer, int connfd)
{
MYSQL *conn;
MYSQL_RES *res;
MYSQL_ROW row;
int n;
char *server = "localhost";
char *user = "******";
char *password = "******";
char *database = "socket";
char username[1024], passwd[1024], query[1024];
conn = mysql_init(NULL);
/* Connect to database */
if (!mysql_real_connect(conn, server,
user, password, database, 0, NULL, 0)) {
fprintf(stderr, "%s\n", mysql_error(conn));
exit(0);
}
n = query_check(buffer, username, 2);
n = query_check(buffer, passwd, 3);
snprintf(query, sizeof query, "SELECT * FROM clients WHERE username = '******' AND password = '******'", username, passwd);
//printf("query = %s\n", query);
if (mysql_query(conn,query)) {
finish_with_error(conn);
}
res = mysql_use_result(conn);
if ((row = mysql_fetch_row(res)) != NULL)
{
logged_in = 1;
strcpy(curr_user, username);
}
mysql_free_result(res);
if(logged_in == 1)
{
snprintf(query, sizeof query, "UPDATE clients SET active = 1, ip = '%s', port = %s WHERE username = '******'",ip,port,username);
if(mysql_query(conn,query)){
finish_with_error(conn);
}
}
else
logged_in = 0;
mysql_close(conn);
return logged_in;
}
//通过锁编号和用户名(锁用户编号)获取锁记录id,user既可能为username也可能为userlabel
uint16 getIDbyUserLock(uint8 lock,char *user,enum UserLock type)
{
if((lock == (uint8)0) || (user == NULL))
return (uint16)0;
MYSQL *conn = getIdleConn();
MYSQL_RES *res; //查询的result
MYSQL_ROW row;
char *sql_str = NULL; //sql语句
//设置字符编码为utf8
mysql_setUTF8(conn);
//设置插入语句
sql_str = (char *)malloc(sizeof(char) * 200);
memset(sql_str,0,200);
switch(type)
{
case LOCK_USERLABEL: sprintf(sql_str,"select id from UserLockInfo where lockid = %1u and user_label = '%s'", \
lock,user);break;
case LOCK_USERNAME: sprintf(sql_str,"select id from UserLockInfo where lockid = %1u and username = '******'", \
lock,user);break;
default:
debug("getIDbyUserLock type error\n");
recycleConn(conn);
free(sql_str);
return (uint16)0;
}
//执行查询
if(mysql_query(conn,sql_str))
{
debug("getIDbyUserLock error\n");
recycleConn(conn);
free(sql_str);
return (uint16)0;
}
//获取查询结果
res = mysql_use_result(conn);
//如果查询结果不为空
if((row = mysql_fetch_row(res)) != NULL)
{
uint16 id = (uint16)atoi(row[0]);
//释放资源
mysql_free_result(res);
recycleConn(conn);
free(sql_str);
return id;
}
//未查到数据
mysql_free_result(res);
recycleConn(conn);
free(sql_str);
return 0;
}
int MySQL::Login(char* id, char* pw)
{
char buffer[200];
sprintf(buffer,"SELECT usr_id FROM users WHERE usr_name = \'%s\' AND usr_pw = \'%s\'",id,pw);
mysql_query(connection,buffer);
MYSQL_RES *res = mysql_use_result(connection);
MYSQL_ROW result = mysql_fetch_row(res);
if(!result)return 0;
int x = atoi(result[0]);
mysql_free_result(res);
return x;
}
int last_insert_id (void) {
if (mysql_query (connection, "select last_insert_id()") != 0) return -1;
MYSQL_RES * result = mysql_use_result (connection);
if (result == NULL) return -1;
if (mysql_num_fields (result) < 1) {mysql_free_result (result); return -1;}
MYSQL_ROW row;
if ((row = mysql_fetch_row (result)) == 0) {mysql_free_result (result); return -1;}
int ret = -1;
if (row [0] != NULL) ret = atoi (row [0]);
mysql_free_result (result);
return ret;
}
int main() {
MYSQL *conn;
MYSQL_RES *res;
MYSQL_ROW row;
char *server = "localhost";
char *user = "******";
char *password = ""; /* set the server passwor */
char *database = "sensorDB";
conn = mysql_init(NULL);
/* Connect to database */
if (!mysql_real_connect(conn, server,
user, password, database, 0, NULL, 0)) {
fprintf(stderr, "%s\n", mysql_error(conn));
exit(1);
}
/* send SQL query */
if (mysql_query(conn, "select at.application_url from application_table at, device_app_assoc d where d.device_id=1 and d.application_id = at.application_id")) {
fprintf(stderr, "%s\n", mysql_error(conn));
exit(1);
}
res = mysql_use_result(conn);
/* output table name */
printf("Application urls for the device are:\n");
while ((row = mysql_fetch_row(res)) != NULL)
printf("%s \n", row[0]);
/* send SQL query */
if (mysql_query(conn, "select flag from device_app_assoc where device_id=1")) {
fprintf(stderr, "%s\n", mysql_error(conn));
exit(1);
}
res = mysql_use_result(conn);
/* output table name */
printf("Flag of url:\n");
while ((row = mysql_fetch_row(res)) != NULL)
printf("%s \n", row[0]);
/* close connection */
mysql_free_result(res);
mysql_close(conn);
return 0;
}
