DBService/StoredProcedure/Databases.cpp

#include "Databases.h"
#include "thread.h"
#include "debug.h"

#ifdef _WIN32
	#pragma comment(lib,"libmysql.lib")
#else
	#include <string.h>
#endif

CSema * g_pSema = NULL;

Databases::Databases()
{
	m_nfieldNum = 0;
	m_record = NULL;
	m_result = NULL;
	InitMysql();

    g_pSema = new CSema(NULL, 1);
}
Databases::~Databases()
{
    if( g_pSema ){
        delete g_pSema;
        g_pSema = NULL;
    }

	mysql_free_result(m_result);
	mysql_close(&m_mysql);
}

int Databases::ConnectDatabases(char *IP, char *user, char *pass ,char *db)
{
    m_srvIP = IP;
    m_userName=user;
    m_password = pass;
    m_dbName=db;

    if (!mysql_real_connect(&m_mysql,
       						m_srvIP.c_str(), m_userName.c_str(), m_password.c_str(), m_dbName.c_str(),
                            0, 0, CLIENT_MULTI_STATEMENTS)){

        DebugPrint( "SQL CONNECT error %d : %s\n", mysql_errno(&m_mysql), mysql_error(&m_mysql) );
        return -1;
    }

    return 0;
}

int Databases::InitMysql()
{
    if(!mysql_init(&m_mysql)){
        DebugPrint( "SQL INIT error %d : %s\n", mysql_errno(&m_mysql), mysql_error(&m_mysql) );
        return -1;
    }

    char value = 1;
    mysql_options(&m_mysql, MYSQL_OPT_RECONNECT, (char *)&value);
	mysql_options(&m_mysql,MYSQL_SET_CHARSET_NAME, "utf8");

    //禁用SSL
	mysql_ssl_mode sslmode = SSL_MODE_DISABLED;
	mysql_options(&m_mysql, MYSQL_OPT_SSL_MODE, (void*)&sslmode);

    return 0;
}
int Databases::FreeResult()
{
    //mysql_free_result(m_result);
    return 0;
}

void Databases::CloseMysql()
{
    //mysql_free_result(m_result);
    mysql_close(&m_mysql);
    m_nfieldNum = 0;
    m_record = NULL;
    m_result = NULL;
}

int	Databases::Query( char *query )
{
	FreeResult();

	if(mysql_ping(&m_mysql))
	{
		DebugPrint( "SQL PING error %d : %s\n", mysql_errno(&m_mysql), mysql_error(&m_mysql) );
		printf("尝试重新连接……!\n");

		CloseMysql();		

		if( InitMysql() == -1)
			return -1;


		if (!mysql_real_connect(&m_mysql, m_srvIP.c_str(), m_userName.c_str(), m_password.c_str(),
        						m_dbName.c_str(), 0, 0, CLIENT_MULTI_STATEMENTS)){
			DebugPrint( "SQL CONNECT error %d : %s\n", mysql_errno(&m_mysql), mysql_error(&m_mysql) );
			return -1;
		}
	}

	if(mysql_query(&m_mysql, query))
	{
		DebugPrint( "SQL QUERY error %d : %s\n", mysql_errno(&m_mysql), mysql_error(&m_mysql) );
		printf("sql: %s\n ",query);
		return -1;
	}	

	m_result = mysql_store_result(&m_mysql);
	
	if( m_result ){
		m_record = mysql_fetch_row(m_result);
		m_nfieldNum = mysql_num_fields(m_result);
	}

	return 0;
}

int Databases::CallPro(const char *call, char *select, MYSQL_ROW* row)
{
    if( g_pSema->ActP(1000) ){
        return -1;
    }

	int ret = 0;

	FreeResult();

	if(mysql_ping(&m_mysql))
	{
		DebugPrint( "SQL PING error %d : %s\n", mysql_errno(&m_mysql), mysql_error(&m_mysql) );
		printf("尝试重新连接……!\n");

		CloseMysql();		

		if( InitMysql() == -1){
			ret = -1;
		}
		else if (!mysql_real_connect(&m_mysql, m_srvIP.c_str(), m_userName.c_str(), m_password.c_str(),
        						m_dbName.c_str(), 0, 0, CLIENT_MULTI_STATEMENTS)){
			DebugPrint( "SQL CONNECT error %d : %s\n", mysql_errno(&m_mysql), mysql_error(&m_mysql) );
		}
	}

	if( !ret && mysql_real_query(&m_mysql,call,(unsigned int)strlen(call)))
	{			
		DebugPrint( "SQL QUERY error %d : %s\n", mysql_errno(&m_mysql), mysql_error(&m_mysql) );
		printf("proc: %s\n ",call);

		printf("尝试重新连接……!\n");	
		CloseMysql();
		if( InitMysql()
        		|| !mysql_real_connect(&m_mysql, m_srvIP.c_str(), m_userName.c_str(), m_password.c_str(),
                			m_dbName.c_str(), 0, 0, CLIENT_MULTI_STATEMENTS)
                || mysql_real_query(&m_mysql,call,(unsigned int)strlen(call)) ){
			printf( "recover failed.\n" );
			ret = -1;
		}
		else{
			printf( "recovered.\n" );
		}
	}		
	
	if( !ret && select && strlen(select)>0 ){
		if(mysql_query(&m_mysql, select))
		{	
			DebugPrint( "SQL QUERY error %d : %s\n", mysql_errno(&m_mysql), mysql_error(&m_mysql) );
			printf("sql: %s\n ",select);
			ret = -1;
		}	
		else{
			int i, maxLen = 0;			
			
			MYSQL_RES* result = mysql_store_result(&m_mysql);
			if( result ){
				int nfieldNum = mysql_num_fields(result);
				if( nfieldNum>0 && row ){
					MYSQL_ROW record = mysql_fetch_row(result);
					if( record && record[0] ){
						*row = new char*[nfieldNum];
						unsigned long* lengths = mysql_fetch_lengths(result);

						for(i = 0; i < nfieldNum; i++){
							if( lengths[i] ){
								(*row)[i] = new char[lengths[i]+1];
								memcpy( (*row)[i], record[i], lengths[i] );
								(*row)[i][lengths[i]] = '\0';
							}
							else{
								(*row)[i] = NULL;
							}
						}
						
						ret = nfieldNum;
					}
				}
				
				mysql_free_result(result);
			}
			else{
				ret = -1;
			}
/*
			m_nfieldNum = mysql_num_fields(m_result);	
			if( m_nfieldNum>0 ){
				unsigned long* length = mysql_fetch_lengths(m_result);	
				if( length[0]>0 ){
					m_record = mysql_fetch_row(m_result);
					
					*record = (char**)new char[length[0]];
					memcpy( *record, m_record, length[0] );
				}
			}
*/
		}
	}
	
    g_pSema->ActV();

	return ret;
}

int Databases::GetFieldData(int i, char *chData)
{

	if( i < m_nfieldNum )		
		strcpy(chData,m_record[i]);	
	else
		return -1;
		
	return 0;
}

MYSQL_ROW Databases::GetRecord()
{
		return m_record;
}

my_ulonglong Databases::GetInsertID()
{
	return mysql_insert_id(&m_mysql);
}