C语言操作MySql

最新推荐文章于 2024-06-20 16:46:29 发布

sauphy

最新推荐文章于 2024-06-20 16:46:29 发布

阅读量423

点赞数

分类专栏： C

本文链接：https://blog.youkuaiyun.com/sauphy/article/details/46125329

版权

C 专栏收录该内容

13 篇文章

订阅专栏

typedef.h

/*
 * @file typedef.h - type definition header file.
 */
#ifndef _TYPE_DEF_H
#define _TYPE_DEF_H

#include <stdint.h>
#include <memory.h>
#include <string.h>


#define TRUE       1
#define FALSE      0

typedef void VOID;

typedef void* PVOID;

typedef long long INT64;

typedef unsigned long long UINT64;

typedef unsigned long UINT32;

typedef signed long INT32;

typedef unsigned short UINT16;

typedef signed short INT16;

typedef unsigned char UINT8;

typedef signed char INT8;

typedef char CHAR;

typedef unsigned char UCHAR;

typedef char* PCHAR;

typedef char** PPCHAR;

typedef unsigned char BOOL;

#define MAX_STRING_LEN  128
typedef char STRING[MAX_STRING_LEN];

#define MAC_ADDR_LENGTH	6
typedef unsigned char MAC_ADDRESS[MAC_ADDR_LENGTH];

#define IP_ADDR_LENGTH	4
typedef unsigned char IP_ADDRESS[IP_ADDR_LENGTH];


// operations for string
#define STRING_COPY(dstStr, srcStr) \
do { \
	strncpy(dstStr, srcStr, MAX_STRING_LEN); \
	dstStr[MAX_STRING_LEN-1] = '\0'; \
}while(0)
	
#define STRING_COMPARE(str1, str2) \
	strcmp(str1, str2)

#define STRING_TO_MAC_ADDRESS(str, mac) \
	sscanf(str, "%02x:%02x:%02x:%02x:%02x:%02x", \
	       &mac[0], &mac[1], &mac[2], &mac[3], &mac[4], &mac[5])

#define STRING_TO_IP_ADDRESS(str, ip) \
	sscanf(str, "%u.%u.%u.%u", &ip[0], &ip[1], &ip[2], &ip[3])
	

// operations for mac address
#define MAC_ADDRESS_COMPARE(mac1, mac2) \
	memcmp((mac1),(mac2),MAC_ADDR_LENGTH)

#define MAC_ADDRESS_COPY(dstMac, srcMac) \
	memcpy(dstMac,srcMac,MAC_ADDR_LENGTH)

#define MAC_ADDRESS_TO_STRING(mac, str) \
	snprintf(str, MAX_STRING_LEN, "%02x:%02x:%02x:%02x:%02x:%02x", \
	         mac[0], mac[1], mac[2], mac[3], mac[4], mac[5])
	         
// operations for ip address
#define IP_ADDRESS_COMPARE(ip1, ip2) \
	memcmp((ip1),(ip2),IP_ADDR_LENGTH)

#define IP_ADDRESS_COPY(dstIp, srcIp) \
	memcpy(dstIp,srcIp,IP_ADDR_LENGTH)

#define IP_ADDRESS_TO_STRING(ip, str) \
	snprintf(str, MAX_STRING_LEN, "%u.%u.%u.%u", \
	         ip[0], ip[1], ip[2], ip[3])



/***************************************************************  
   API return code.
****************************************************************/
typedef enum {
	RC_SUCCESS   		= 0,
	RC_ERROR     		= 1, 
	RC_ERR_TIMEOUT      = 2,
	RC_ERR_OUTOFMEM  	= 3,
	RC_ERR_PARAMETER 	= 4, 
	RC_ERR_SOCKET  	    = 5,
	RC_ERR_MESSAGE  	= 6,
	RC_ERR_EXIST		= 7,
	RC_ERR_NONEXIST     = 8,
	RC_ERR_BUSY         = 9,
} RET_CODE;

#endif

mysqldb.h

<pre name="code" class="cpp">/*
 * @file mysqldb.h - my sql database API
 */
#if !defined(_DONICA_MYSQLDB_H)
#define _DONICA_MYSQLDB_H

#include "typedef.h"


#ifdef __cplusplus
extern "C" {
#endif

typedef PVOID MSQL_INST;
typedef PVOID MSQL_RES;
typedef PVOID MSQL_ROW;


/*******************************************************************************
   Function Name: MSQL_INIT 
   Description:  Create MySQL instance
   Parameters: None
   Return: MSQL_INST if success, 0 if failed.
******************************************************************************/
MSQL_INST MSQL_CREATE();


/*******************************************************************************
   Function Name: MSQL_DESTROY 
   Description:  Destroy MySQL instance.
   Parameters: (IN)MSQL_INST inst - mysql instance 
   Return: None
******************************************************************************/
VOID MSQL_DESTROY(MSQL_INST inst);


/*******************************************************************************
   Function Name: MSQL_CONNECT 
   Description:  Connect to MySQL server.
   Parameters: (IN)MSQL_INST inst - mysql instance 
                       (IN)PCHAR host - mysql server ip address.
                       (IN)PCHAR usr - user name 
                       (IN)PCHAR pwd - password
                       (IN)PCHAR db - database name
   Return: RC_SUCCESS if success.
******************************************************************************/
RET_CODE MSQL_CONNECT(MSQL_INST inst, PCHAR host, PCHAR usr, PCHAR pwd, PCHAR db);

/*******************************************************************************
   Function Name: MSQL_CHECK_CONNECT 
   Description:  Check connection to MySQL server.
   Parameters: (IN)MSQL_INST inst - mysql instance 
   Return: RC_SUCCESS if connection is valid.
******************************************************************************/
RET_CODE MSQL_PING(MSQL_INST inst);

/*******************************************************************************
   Function Name: MSQL_EXECUTE 
   Description:  Execute SQL like INSERT or UPDATE. 
   Parameters: (IN)MSQL_INST inst -mysql instance 
                       (IN)PCHAR sql - insert or update sql string.
                       (OUT)UINT32 *rows -return affected rows. can be NULL.
                       (OUT)UINT32 *insertId - return auto increment id. can be NULL.
   Return: RC_SUCCESS if success.
******************************************************************************/
RET_CODE MSQL_EXECUTE(MSQL_INST inst, PCHAR sql, UINT32 *rows, UINT32 *insertId);


/*******************************************************************************
   Function Name: MSQL_QUERY 
   Description:  Execute SQL like SELECT.
   Parameters: (IN)MSQL_INST inst -mysql instance 
                       (IN)PCHAR sql - select sql string.
                       (OUT)MSQL_RES *res -return query result.
   Return: RC_SUCCESS if success.
******************************************************************************/
RET_CODE MSQL_QUERY(MSQL_INST inst, PCHAR sql, MSQL_RES *res);


/*******************************************************************************
   Function Name: MSQL_FREE_RESULT 
   Description:  Free query result.
   Parameters:  (IN)MSQL_RES *res -query result.
   Return: None
******************************************************************************/
VOID MSQL_FREE_RESULT(MSQL_RES res);

/*******************************************************************************
   Function Name: MSQL_RES_NUM_ROWS 
   Description:  Return row number of query result.
   Parameters:  (IN)MSQL_RES *res -query result.
   Return: Return filed number of query result.
******************************************************************************/
UINT32 MSQL_RES_NUM_ROWS(MSQL_RES res);

/*******************************************************************************
   Function Name: MSQL_RES_NUM_FIELDS 
   Description:  Return filed number of query result.
   Parameters:  (IN)MSQL_RES *res -query result.
   Return: Return filed number of query result.
******************************************************************************/
UINT32 MSQL_RES_NUM_FIELDS(MSQL_RES res);


/*******************************************************************************
   Function Name: MSQL_RES_FETCH_ROW 
   Description:  Fetch first or next row object from query result
   Parameters:  (IN)MSQL_RES *res -query result.
   Return: Return row object.
******************************************************************************/
MSQL_ROW MSQL_RES_FETCH_ROW(MSQL_RES res);


/*******************************************************************************
   Function Name: MSQL_RES_FIELD_VALUE 
   Description:  Get field value of current row.
   Parameters:  (IN)MSQL_RES *res -query result.
                       (IN)MSQL_ROW row -row object.
                       (IN)UINT16 fieldId -field id.
                       (OUT)UINT32 *len - return field len.
   Return: Return field data pointer.
******************************************************************************/
PCHAR MSQL_RES_FIELD_VALUE(MSQL_RES res, MSQL_ROW row, UINT16 fieldId, UINT32 *len);


/*******************************************************************************
   Function Name: MSQL_RES_PRINT 
   Description:  Print query result table.
   Parameters:  (IN)MSQL_RES *res -query result.
   Return: None
******************************************************************************/
VOID MSQL_RES_PRINT (MSQL_RES res);


#ifdef __cplusplus
}
#endif

#endif /* _DONICA_MYSQLDB_H */

mysqldb.c

<pre name="code" class="cpp">#include <stdio.h>
#include <stdlib.h>
#include <pthread.h>

#include "mysqldb.h"

#include <mysql.h>

#define MOD_NAME "MSQL"
#ifdef COMMLIB_DEBUG
#define MSQL_LOG(info, ...) printf("[%s]"info"\r\n", MOD_NAME, ##__VA_ARGS__)
#else
#define MSQL_LOG(info, ...)
#endif

static pthread_mutex_t glock = PTHREAD_MUTEX_INITIALIZER;

#define MSQL_LOCK \
	pthread_mutex_lock(&glock)
	
#define MSQL_UNLOCK \
	pthread_mutex_unlock (&glock)
	


MSQL_INST MSQL_CREATE()
{
	MYSQL *pMySQL = NULL;
	my_bool isReconn = 1;
	
	pMySQL = mysql_init (NULL);
	if (!pMySQL)
	{
		MSQL_LOG ("MSQL_INIT failed!!!");
		return NULL;
	}

	// reconnect automatically.
	mysql_options (pMySQL, MYSQL_OPT_RECONNECT, &isReconn);
	
	MSQL_LOG ("MSQL_INIT(0x%x) success~~~~", pMySQL);
	return pMySQL;
}

VOID MSQL_DESTROY(MSQL_INST inst)
{
	MSQL_LOG ("MSQL_DESTROY(0x%x)", inst);
	if (!inst)
		return;

	mysql_close (inst);
	return;
}

RET_CODE MSQL_CONNECT(MSQL_INST inst, PCHAR host, PCHAR usr, PCHAR pwd, PCHAR db)
{
	if (!inst || !host || !usr || !pwd)
		return RC_ERR_PARAMETER;

	MSQL_LOG ("MSQL_CONNECT (inst=0x%x, host=%s,usr=%s,pwd=%s,db=%s)", inst, host, usr, pwd, db);
	
	if (!mysql_real_connect(inst, host, usr, pwd, db, 0, NULL, 0))
	{
		MSQL_LOG ("mysql_real_connect failed!!! Error:%s", mysql_error(inst));
		return RC_ERROR;
	}

	if (mysql_set_character_set (inst, "gbk"))
	{
		MSQL_LOG ("mysql_set_character_set failed!!!");
	}

	MSQL_LOG ("MSQL_CONNECT success~~~~");
	return RC_SUCCESS;
}

RET_CODE MSQL_PING(MSQL_INST inst)
{
	if (!inst)
		return RC_ERROR;
	
	if (!mysql_ping(inst))
	{
		MSQL_LOG ("MSQL_PING success~~~~");
		return RC_SUCCESS;
	}
	else
	{
		MSQL_LOG ("MSQL_PING failed!!! Error:%s", mysql_error(inst));
		return RC_ERROR;
	}
}

RET_CODE MSQL_EXECUTE(MSQL_INST inst, PCHAR sql, UINT32 *rows, UINT32 *insertId)
{
	MYSQL_RES *result = NULL;
	UINT32 affectRow = 0;
	
	if (!inst || !sql )
		return RC_ERR_PARAMETER;

	if (rows) *rows = 0;
	if (insertId) *insertId = 0;

	MSQL_LOCK; // multithread reentry.
	if (mysql_real_query(inst, sql, strlen(sql)))
	{
		MSQL_LOG ("mysql_real_query failed!!! Error:%s", mysql_error(inst));
		MSQL_UNLOCK;
		return RC_ERROR;
	}

	result = mysql_store_result (inst);
	MSQL_UNLOCK;
		
	if (result || mysql_field_count(inst) != 0) // it's a SELECT without result.
	{
		MSQL_LOG ("it's a SELECT sql(result = %x, field_count=%d).",result, mysql_field_count(inst));
		if (result)	mysql_free_result(result);
		return RC_ERROR;
	}
		
	// get affected rows
	affectRow = mysql_affected_rows(inst);
	MSQL_LOG ("affected rows = %d", affectRow);
	if (rows) *rows = affectRow;
		
	
	// get auto increment insert id
	if (insertId)
	{
		*insertId = mysql_insert_id(inst);
		MSQL_LOG ("insert id = %d", *insertId);
	}
	
	if (result)	mysql_free_result(result);
	
	return RC_SUCCESS;
}



RET_CODE MSQL_QUERY(MSQL_INST inst, PCHAR sql, MSQL_RES *res)
{
	MYSQL_RES *result;

	*res = NULL;
	
	if (!inst || !sql)
		return RC_ERR_PARAMETER;

	MSQL_LOCK; // multithread reentry.
	if (mysql_real_query(inst, sql, strlen(sql)))
	{
		MSQL_LOG ("mysql_real_query failed!!! Error:%s", mysql_error(inst));
		MSQL_UNLOCK;
		return RC_ERROR;
	}
	
	result = mysql_store_result (inst);
	MSQL_UNLOCK;
	
	if (result) // there are rows
	{	
		*res = result;
		return RC_SUCCESS;
	}
	else
	{
		if (mysql_errno(inst))
		{
			MSQL_LOG ("mysql_store_result failed!!! Error:%s", mysql_error(inst));
		}
		else if (mysql_field_count(inst) == 0)
		{
			MSQL_LOG ("it's not a SELECT query.");
		}
		return RC_ERROR;
	}
}


VOID MSQL_FREE_RESULT(MSQL_RES res)
{
	mysql_free_result(res);
	return;
}

UINT32 MSQL_RES_NUM_ROWS(MSQL_RES res)
{
	if (!res) return 0;
	
	return mysql_num_rows(res);
}

UINT32 MSQL_RES_NUM_FIELDS(MSQL_RES res)
{
	if (!res) return 0;
	
	return mysql_num_fields(res);
}

MSQL_ROW MSQL_RES_FETCH_ROW(MSQL_RES res)
{
	if (!res)
return NULL;
	return mysql_fetch_row(res);
}

PCHAR MSQL_RES_FIELD_VALUE(MSQL_RES res, MSQL_ROW row, UINT16 col, UINT32 *len)
{
	unsigned long *lengths;
	
	if (!row) return NULL;

	// get length of each field for current row.
	lengths = mysql_fetch_lengths(res);
	
	*len = lengths[col];
	
	return ((MYSQL_ROW)row)[col];
}

VOID MSQL_RES_PRINT (MSQL_RES res)
{
	unsigned int cols, i, len;
	MSQL_ROW row;
	PCHAR pData;
	
	cols = MSQL_RES_NUM_FIELDS(res);
	printf ("\r\ntable columns number= %d\r\n", cols);

	printf ("\n-------------------------------------------------\n");
	while (row = MSQL_RES_FETCH_ROW(res))
	{
		for (i = 0; i < cols; i++)
		{
			pData = MSQL_RES_FIELD_VALUE (res, row, i, &len);
			printf ("%-.*s", len, pData?pData:"NULL");
			printf ("\t");
		}
		printf ("\n");
	}
	printf ("---------------------------------------------------\n");
	return;
}

main.c

<pre name="code" class="cpp">#include<stdio.h>
#include"mysqldb.h"

int main(){
	// create one datebase
	MSQL_INST psql = MSQL_CREATE();

	// connect to server
	PCHAR host = "192.168.3.47";
	PCHAR usr = "root";
	PCHAR pwd = "";
	PCHAR db = "cmt";
	RET_CODE ret = MSQL_CONNECT(psql,host,usr,pwd,db);
	
	if (RC_SUCCESS != ret)
	{
		printf("connected fails %d\n",ret);
		return 0;
	}
	else{
		printf("connected OK");
	}
	return 0;
}