mycommon.cpp

连接MYSQL数据库

//author : Jarry
//E-mail : [e-mail]
#include "stdafx.h"

//Show records in the special list
//arguments:
//          res is the recordset
//          lstRecords is the list where the data to be showed
//Note:     it's your matter to free the res after calling
int ShowRecords(MYSQL_RES *pRes,CListCtrl& lstRecords,bool bShowNULL)
{
	int nShowRows = -1;
	if (pRes != NULL)
	{
		MYSQL_FIELD	*pFds;
		int nFields,nCol;

		lstRecords.DeleteAllItems();  //Clear old data
		for (nCol = 0 ; lstRecords.DeleteColumn(0) ; nCol++);

		nFields = mysql_num_fields(pRes);
		pFds = mysql_fetch_fields(pRes);
		for (nCol = 0 ; nCol < nFields ; nCol++)
			if (IS_NUM(pFds[nCol].type))
				lstRecords.InsertColumn(nCol,pFds[nCol].name,LVCFMT_RIGHT,pFds[nCol].length > 30 ? pFds[nCol].length * 2 : 60);
			else
				lstRecords.InsertColumn(nCol,pFds[nCol].name,LVCFMT_LEFT,pFds[nCol].length > 30 ? pFds[nCol].length * 2 : 60);
		MYSQL_ROW row;
		int nRow = 0;
		while (row = mysql_fetch_row(pRes))
		{
			int j = 0;
			if (bShowNULL && ((row[j] == NULL) || (strlen(row[j]) <= 0)))
				lstRecords.InsertItem(nRow,"<NULL>");
//          At this time C API IS_BLOB is incorrect , it can't identify char and blob
//			else if (IS_BLOB(pFds[j].type))
//				lstRecords.InsertItem(nRow,"<BLOB>");
			else
				lstRecords.InsertItem(nRow,row[j]);
			for (j = 1 ; j < nCol ; j++)
				if (bShowNULL && ((row[j] == NULL) || (strlen(row[j]) <= 0)))
					lstRecords.SetItemText(nRow,j,"<NULL>");
//				else if (IS_BLOB(pFds[j].type))
//					lstRecords.SetItemText(nRow,j,"<BLOB>");
				else
					lstRecords.SetItemText(nRow,j,row[j]);
			nRow++;
		}
		nShowRows = lstRecords.GetItemCount();
	}
	return nShowRows;
}

void GenerateTableSQL(LPSTR pszSQL,LPSTR pszTableName,CPtrList& lstField)
{
	CHAR szTemp[2048],szKey[2048];
	FieldInfo *pFieldInfo;
	POSITION pos;

	ASSERT(pszSQL != NULL);
	ASSERT(pszTableName != NULL);
	ASSERT(lstField.GetCount() > 0);

	strcpy(szKey,"");
	sprintf(szTemp,"CREATE TABLE %s (",pszTableName);
	strcpy(pszSQL,szTemp);
	pos = lstField.GetHeadPosition();
	int nField = 1,nKey = 1;
	while (pos != NULL)
	{
		CString strNULL,strDefault;
		pFieldInfo = (FieldInfo*)lstField.GetNext(pos);
		if (nField <= 1) 
			strcat(pszSQL,"\r\n");
		else
			strcat(pszSQL,",\r\n");
		if (pFieldInfo->bNull)
			strNULL = " NULL ";
		else
			strNULL = " NOT NULL ";
		strDefault = pFieldInfo->strDefault;
		strDefault.TrimLeft();
		strDefault.TrimRight();
		if (strDefault.GetLength() > 0)
			if (pFieldInfo->bNum)
				strDefault = " DEFAULT " + strDefault;
			else
				strDefault = " DEFAULT \'" + strDefault + "\'";
		if (pFieldInfo->bNum)
		{
			if (pFieldInfo->strFieldLength.GetLength() > 0)
			{
				if (pFieldInfo->strFieldPrecision.GetLength() > 0)
					sprintf(szTemp,"%s %s(%s,%s) %s %s",
					        pFieldInfo->strFieldName,
							pFieldInfo->strFieldType,
							pFieldInfo->strFieldLength,
							pFieldInfo->strFieldPrecision,
							strNULL,strDefault);
				else
					sprintf(szTemp,"%s %s(%s) %s %s",
					        pFieldInfo->strFieldName,
							pFieldInfo->strFieldType,
							pFieldInfo->strFieldLength,
							strNULL,strDefault);
			}
			else
			{
				sprintf(szTemp,"%s %s %s %s",
					        pFieldInfo->strFieldName,
							pFieldInfo->strFieldType,
							strNULL,strDefault);
			}
		}
		else
		{
			if (pFieldInfo->strFieldLength.GetLength() > 0)
				sprintf(szTemp,"%s %s(%s) %s %s",
								pFieldInfo->strFieldName,
								pFieldInfo->strFieldType,
								pFieldInfo->strFieldLength,
								strNULL,strDefault);
			else
				sprintf(szTemp,"%s %s %s %s",
								pFieldInfo->strFieldName,
								pFieldInfo->strFieldType,
								strNULL,strDefault);
		}
		strcat(pszSQL,szTemp);
		if (pFieldInfo->bKey)
		{
			if (nKey <= 1)
			{
				strcat(szKey,pFieldInfo->strFieldName);
			}
			else
			{
				strcat(szKey,",");
				strcat(szKey,pFieldInfo->strFieldName);
			}
			nKey++;
		}
		nField++;
	}
	if (strlen(szKey) > 0)
	{
		strcat(pszSQL,",\r\nPRIMARY KEY (");
		strcat(pszSQL,szKey);
		strcat(pszSQL,")\r\n");
	}
	strcat(pszSQL,")\r\n");
}