MariaDB C/C++ Connector 를 이용해 prepareStatement 쿼리 사용하기

C/C++ 를 이용해 MariaDB 에 접속하여 쿼리를 실행해야 하는 경우 Maria DB 에서 제공하는 C/C++ Connector 를 이용하여 소스코드를 작성하게 된다.

단순 쿼리는 크게 문제가 없지만, prepareStament 를 이용하여 Result나 Param을 하나씩 설정해주어야 하는 경우 번거로움이 발생한다.

마침, MSSQL에서 MariaDB로 마이그레이션 하는 도중이라 MSSQL에서 사용하는 OleDB의 COM Interface를 참고하여 랩핑클래스를 작성해보았다.

기본적인 prepareStatement 내용은 아래 링크를 참고 하였다.
https://dev.mysql.com/doc/refman/8.0/en/mysql-stmt-execute.html




MySQLConnection.h

#pragma once
#include <mysql.h>

#define MYSQL_SUCCESS         0
#define MYSQL_ERROR_OCCURRED 1

class CMySQLConnection
{
public:
CMySQLConnection();
~CMySQLConnection();

bool Connect(char *host, int port, char *user, char *passwd, char *db);
void Disconnect();
bool IsConnected(bool ping=false);

MYSQL_STMT* Init(const char *query, MYSQL_BIND *param, int paramCnt);
int Execute(MYSQL_STMT *stmt, MYSQL_BIND *column, int columnCnt);
int Fetch(MYSQL_STMT *stmt, int *rowCnt=NULL);
int AffectedRow(MYSQL_STMT *stmt);
void Close(MYSQL_STMT *stmt);

private:
#define MYSQL_LOCK() EnterCriticalSection(&m_csMySQL)
#define MYSQL_UNLOCK() LeaveCriticalSection(&m_csMySQL)

public:
private:
CRITICAL_SECTION m_csMySQL;
MYSQL *m_connection;
};

class CMySQLPrepareStatement
{
public:
CMySQLPrepareStatement(CMySQLConnection *conn);
~CMySQLPrepareStatement();

public:
int excute();
int fetch();
int affectedrow();

protected:
void db_column(int idx, enum_field_types type, char* buffer, unsigned long buffer_length = 0);
void db_column_cnt(unsigned int cnt);
void db_param(int idx, enum_field_types type, char* buffer, unsigned long buffer_length = 0);
void db_param_cnt(unsigned int cnt);

protected:
MYSQL_BIND *column;
my_bool *column_null;
unsigned long *column_length;
my_bool *column_error;
unsigned int column_cnt;

MYSQL_BIND *param;
my_bool *param_null;
unsigned long *param_length;
my_bool *param_error;
unsigned int param_cnt;

MYSQL_STMT *stmt;
CMySQLConnection *mysql;
};

MySQLConnection.cpp

#include "stdafx.h"
#include "MySQLConnection.h"

CMySQLConnection::CMySQLConnection()
{
InitializeCriticalSection(&m_csMySQL);
m_connection = NULL;
}


CMySQLConnection::~CMySQLConnection()
{
DeleteCriticalSection(&m_csMySQL);
}

bool CMySQLConnection::Connect(char *host, int port, char *user, char *passwd, char *db)
{
int ret = MYSQL_ERROR_OCCURRED;

MYSQL_LOCK();

if (m_connection)
Disconnect();

do
{
m_connection = mysql_init(NULL);
if (m_connection == NULL)
{
// mysql_init fail
break;
}

if (mysql_real_connect(m_connection, host, user, passwd, db, port, NULL, 0) == NULL)
{
// mysql_real_connect fail
break;
}

ret = true;
} while (0);

MYSQL_UNLOCK();

return ret;

}

void CMySQLConnection::Disconnect()
{
if (m_connection)
{
mysql_close(m_connection);
m_connection = NULL;
}
}

bool CMySQLConnection::IsConnected(bool ping)
{
if (m_connection)
{
if (ping)
{
int res = mysql_ping(m_connection);
if (res != 0)
{
return false;
}
}

return true;
}

return false;
}

MYSQL_STMT* CMySQLConnection::Init(const char *query, MYSQL_BIND *param, int paramCnt)
{
MYSQL_STMT *stmt = NULL;

if (IsConnected() == false)
return stmt;

do
{
stmt = mysql_stmt_init(m_connection);
if (stmt == NULL)
{
// mysql_stmt_init fail
break;
}

if (mysql_stmt_prepare(stmt, query, strlen(query)))
{
// mysql_stmt_prepare fail
break;
}

if (paramCnt)
{
if (mysql_stmt_param_count(stmt) != paramCnt)
{
// check param count
mysql_stmt_close(stmt);
stmt = NULL;
break;
}

if (mysql_stmt_bind_param(stmt, param))
{
// mysql_stmt_bind_param fail
mysql_stmt_close(stmt);
stmt = NULL;
break;
}
}
} while (0);

return stmt;
}


int CMySQLConnection::Execute(MYSQL_STMT *stmt, MYSQL_BIND *column, int columnCnt)
{
int ret = MYSQL_ERROR_OCCURRED;

if ( IsConnected() == false || stmt == NULL)
return ret;

do
{
if (columnCnt)
{
MYSQL_RES *meta = NULL;
int metaColumnCnt = 0;
meta = mysql_stmt_result_metadata(stmt);
if (meta)
{
metaColumnCnt = mysql_num_fields(meta);
mysql_free_result(meta);
}

if (metaColumnCnt != columnCnt)
{
// check column count
break;
}
}

if (ret = mysql_stmt_execute(stmt))
{
// mysql_stmt_execute fail
break;
}

if (columnCnt)
{
if (ret = mysql_stmt_bind_result(stmt, column))
{
// mysql_stmt_bind_result fail
break;
}

if (ret = mysql_stmt_store_result(stmt))
{
// mysql_stmt_store_result fail
break;
}
}

ret = true;

} while (0);

return ret;
}

int CMySQLConnection::Fetch(MYSQL_STMT* stmt, int *rowCnt)
{
int ret = MYSQL_ERROR_OCCURRED;

if (IsConnected() == false || stmt == NULL)
return ret;

ret = mysql_stmt_fetch(stmt);

return ret;
}

int CMySQLConnection::AffectedRow(MYSQL_STMT* stmt)
{
int ret = 0;

if (IsConnected() == false || stmt == NULL)
return ret;

ret = mysql_stmt_affected_rows(stmt);

return ret;
}

void CMySQLConnection::Close(MYSQL_STMT *stmt)
{
if (stmt)
{
mysql_stmt_close(stmt);
stmt = NULL;
}
}


CMySQLPrepareStatement::CMySQLPrepareStatement(CMySQLConnection *conn) :mysql(conn)
{
column = NULL;
column_null = NULL;
column_length = NULL;
column_error = NULL;
column_cnt = 0;
param = NULL;
param_null = NULL;
param_length = NULL;
param_error = NULL;
param_cnt = 0;
stmt = NULL;
}

CMySQLPrepareStatement::~CMySQLPrepareStatement()
{
if (column)
delete[] column;
if (column_null)
delete[] column_null;
if (column_length)
delete[] column_length;
if (column_error)
delete[] column_error;
if (param)
delete[] param;
if (param_null)
delete[] param_null;
if (param_length)
delete[] param_length;
if (param_error)
delete[] param_error;
if (stmt)
mysql_stmt_close(stmt);
}

void CMySQLPrepareStatement::db_column(int idx, enum_field_types type, char *buffer, unsigned long buffer_length)
{
idx--;

if (idx >= 0 && idx < column_cnt)
{
column[idx].buffer_type = type;
column[idx].buffer = buffer;

if (type == MYSQL_TYPE_STRING)
column[idx].buffer_length = buffer_length;

column[idx].is_null = &column_null[idx];
column[idx].length = &column_length[idx];
column[idx].error = &column_error[idx];
}
}

void CMySQLPrepareStatement::db_column_cnt(unsigned int cnt)
{
if (column)
delete[] column;
if (column_null)
delete[] column_null;
if (column_length)
delete[] column_length;
if (column_error)
delete[] column_error;

column_cnt = cnt;

if (column_cnt)
{
column = new MYSQL_BIND[column_cnt];
memset(column, 0, sizeof(MYSQL_BIND)*column_cnt);

column_null = new my_bool[column_cnt];
memset(column_null, 0, sizeof(my_bool)*column_cnt);

column_length = new unsigned long[column_cnt];
memset(column_length, 0, sizeof(unsigned long)*column_cnt);

column_error = new my_bool[column_cnt];
memset(column_error, 0, sizeof(my_bool)*column_cnt);
}
}

void CMySQLPrepareStatement::db_param(int idx, enum_field_types type, char * buffer, unsigned long buffer_length)
{
idx--;

if (idx >= 0 && idx < param_cnt)
{
param[idx].buffer_type = type;
param[idx].buffer = buffer;

if (type == MYSQL_TYPE_STRING)
param[idx].buffer_length = buffer_length;

param[idx].is_null = &param_null[idx];
param[idx].length = &param_length[idx];
param[idx].error = &param_error[idx];
}
}

void CMySQLPrepareStatement::db_param_cnt(unsigned int cnt)
{
if (param)
delete[] param;
if (param_null)
delete[] param_null;
if (param_length)
delete[] param_length;
if (param_error)
delete[] param_error;


param_cnt = cnt;

if (param_cnt)
{
param = new MYSQL_BIND[param_cnt];
memset(param, 0, sizeof(MYSQL_BIND)*param_cnt);

param_null = new my_bool[param_cnt];
memset(param_null, 0, sizeof(my_bool)*param_cnt);

param_length = new unsigned long[param_cnt];
memset(param_length, 0, sizeof(unsigned long)*param_cnt);

param_error = new my_bool[param_cnt];
memset(param_error, 0, sizeof(my_bool)*param_cnt);

}
}

int CMySQLPrepareStatement::excute()
{
for (int i = 0; i < param_cnt; i++)
{
if (param[i].buffer_type == MYSQL_TYPE_STRING)
param_length[i] = strlen((char *)param[i].buffer);
}

return mysql->Execute(stmt, column, column_cnt);
}

int CMySQLPrepareStatement::fetch()
{
return mysql->Fetch(stmt);
}

int CMySQLPrepareStatement::affectedrow()
{
return mysql->AffectedRow(stmt);
}



CMySQLConnection 객체를 생성하여 서버에 접속하고, 핸들을 관리한다.
쿼리를 할때에는 CMySQLPrepareStatement 를 상속 받는 객체를 정의해서 사용해야한다.

class CQueryTest : public CMySQLPrepareStatement
{
const char *query = "select `column1`, `column2` from `test_table` where `id`=?";

public:
CQueryTest(CMySQLConnection* conn): CMySQLPrepareStatement(conn)
{
db_column_cnt(2);
db_column(1, MYSQL_TYPE_STRING, (char *)&column1, 128);
db_column(2, MYSQL_TYPE_LONG, (char *)&column);

db_param_cnt(1);
db_param(1, MYSQL_TYPE_LONG, (char *)&param);

stmt = mysql->Init(query, param, param_cnt);
}

char column1[128] ;
long column2;

        long param;
};

 test_table 에서 특정 id 의 column1, column2 를 가져오기 위한 쿼리 객체는 위와 같이 정의 된다.

CQueryTest 객체를 선언할때 CMySQLConnection 객체의 포인터를 전달하고, CQueryTest 의 생성자에서는 query 문장을 prepare 와 column과 param이 전달하기 위한 동적 할당 작업을 한다.

CQueryTest test(conn);
test.param = 13;

test.excute();

while (1)
{
int status = test.fetch();
if (status == MYSQL_NO_DATA || status == MYSQL_DATA_TRUNCATED)
break;
column1 = test.column1;
column2 = test.column2;
}

뭐 이런식으로 사용 하면 되겠다.
라벨: ,