[ C++Builder ] TSQLConnection 을 이용한 MySQL 접속 및 쿼리문사용

대략 1년만에 Builder로 작업하려니 ...

MySQL 접속및 쿼리 실행함수를 Class로 만들어 필요 할 때마다 가져다 쓰자! 

_databaseMySql.cpp, _databaseMySql.hpp

libmySQL.dll 파일이 같이 있어야 한다는점 주의! (파일 첨부했음) ^^

_databaseMySql.cpp

/*
################################################################################
모듈이름	MySql Database모듈
작성일자	2013. 09.12
작성자명	HwangKyuseok
################################################################################
*/
//---------------------------------------------------------------------------
#include <vcl.h>	//vcl함수를 사용하기위해 사용자 추가
#pragma hdrstop

#include "_databaseMySql.h"
//---------------------------------------------------------------------------
#pragma package(smart_init)
//------------------------------------------------------------------------------
// 생성자 함수
//------------------------------------------------------------------------------
__fastcall databaseMySql::databaseMySql(void)
{
	errView = true;// 접속시 오류발생시 메시지창 띄움
	SQLConnection1 = new TSQLConnection(NULL);
	SQLQuery1 = new TSQLQuery(NULL);

	SQLQuery1->SQLConnection = SQLConnection1;
}
//------------------------------------------------------------------------------
// 소멸자 함수
//------------------------------------------------------------------------------
__fastcall databaseMySql::~databaseMySql(void)
{
	delete SQLConnection1;
	delete SQLQuery1;
}
//------------------------------------------------------------------------------
// 기능: DB연결
// 인수:
// 반환: true:정상, false:접속실패 및 메시지출력
//------------------------------------------------------------------------------
bool __fastcall databaseMySql::fn_Open(int port,String ip,String name,String id,String pass)
{
	int bret = false;
	try {
		if(SQLConnection1->Connected == false) {
			SQLConnection1->DriverName = "MySql";
			SQLConnection1->ConnectionName = "MySQLConnection";
			SQLConnection1->GetDriverFunc = "getSQLDriverMYSQL";
			//SQLConnection1->LibraryName = "dbxmys.dll";
			SQLConnection1->VendorLib = "libmysql.dll";

			SQLConnection1->Params->Clear();
			SQLConnection1->Params->Values["Database"] = name;
			SQLConnection1->Params->Values["Port"] = port;
			SQLConnection1->Params->Values["HostName"] = ip;
			SQLConnection1->Params->Values["User_Name"] = id;
			SQLConnection1->Params->Values["Password"] = pass;
			SQLConnection1->Open();
			bret = true;
		}
	} catch (Exception *e) {
		if(errView) ShowMessage(((TDBXError *)e)->Message);
		bret = false;
	}
	return bret;
}
//------------------------------------------------------------------------------
// DB연결 끊기
//------------------------------------------------------------------------------
void __fastcall databaseMySql::fn_Close(void)
{
	if(SQLConnection1->Connected) SQLConnection1->Close();
}
//------------------------------------------------------------------------------
// 기능: Select 쿼리를 위한 함수
// 반환: 0:정상, -1:접속불량, -2:쿼리문오류
//------------------------------------------------------------------------------
int __fastcall databaseMySql::fn_Select(String strSql)
{
	int iret= -1;
	if(SQLConnection1->Connected) {
		try {
			SQLQuery1->Close();
			SQLQuery1->SQL->Clear();
			SQLQuery1->SQL->Add(strSql);
			SQLQuery1->Open();
		} catch (Exception &e) {
			///ShowMessage(e.Message);
			return -2;
		}
		return 0;
	}
	else 	return -1;
}
//------------------------------------------------------------------------------
// 함수기능: Insert, Update, Delete를 쿼리하기 위한 함수
// 반환: 0:정상, -1:접속불량, -2:쿼리문오류
//------------------------------------------------------------------------------
int __fastcall databaseMySql::fn_Exec(String strSql)
{
	int iret= -1;
	if(SQLConnection1->Connected) {
		try {
			SQLQuery1->Close();
			SQLQuery1->SQL->Clear();
			SQLQuery1->SQL->Add(strSql);
			SQLQuery1->ExecSQL();
		} catch (Exception &e) {
			///ShowMessage(e.Message);
			return -2;
		}
		return 0;
	}
	else 	return -1;
}
//------------------------------------------------------------------------------
// 인수: (default)true:에러메시지창 뛰움, false:에러메시지창 띄우지 않음
//------------------------------------------------------------------------------
void __fastcall databaseMySql::fn_setErrMsg(bool errView)
{
	this->errView = errView;

}

 

_databaseMySql.hpp

//---------------------------------------------------------------------------
#ifndef _databaseMySqlH
#define _databaseMySqlH
//---------------------------------------------------------------------------
#include <Classes.hpp>	//추가
#include <DB.hpp>       //추가
#include <DBXMySql.hpp> //추가
#include <SqlExpr.hpp>  //추가
class databaseMySql {

public:
	TSQLConnection *SQLConnection1;
	TSQLQuery *SQLQuery1;
	__fastcall databaseMySql::databaseMySql(void);
	__fastcall databaseMySql::~databaseMySql(void);

	bool	errView;//연결시 에러메시지창 사용여부
	bool __fastcall databaseMySql::fn_Open(int port,String ip,String name,String id,String pass);
	void __fastcall databaseMySql::fn_Close(void);

	int __fastcall databaseMySql::fn_Select(String strSql);
	int __fastcall databaseMySql::fn_Exec(String strSql);

	void __fastcall databaseMySql::fn_setErrMsg(bool errView);

};
#endif

 

사용예: 이거저거 다 빼고 위에서 작성한 클래스를 사용해 쿼리하고 결과 읽어오는 방법만

//... 생략
//------------------------------------------------------------------------------
__fastcall TfmMain::TfmMain(TComponent* Owner)
	: TForm(Owner)
{
		//databaseMySql *DB;  헤더에 선언되어있음
		DB = new databaseMySql();
}
//------------------------------------------------------------------------------
//DB 열기
// return : true:성공, false:실패
//------------------------------------------------------------------------------
bool __fastcall TfmMain::fn_DBopen()
{
	return DB->fn_Open(3306,"localhost","testdb","root","1234");
}
//------------------------------------------------------------------------------
//DB 닫기
//------------------------------------------------------------------------------
void __fastcall TfmMain::fn_DBclose()
{
	DB->fn_Close();
	delete DB;
}
//------------------------------------------------------------------------------
//DB 배열에 담기(동적메모리할당) - SELECT , UPDATE 쿼리문 ....
//------------------------------------------------------------------------------
void __fastcall TfmMain::fn_DBtoArray()
{
	//... 생략
	String strSql;
	DB->fn_Select("SELECT * FROM tbl_sb_sbsp WHERE I_cnt_type = 1");
	iCNT = DB->SQLQuery1->RecordCount;
	if(iCNT > 0) {
		//~~~~~~~~~~~~~~~~~~~~~~~~~~
		DB->SQLQuery1->First();
		//데이터 옮김
		for(i=0;i < iCNT;i++) {
			vcSBSP[i][1] = DB->SQLQuery1->FieldByName("C_gigino")->AsString;
			vcSBSP[i][2] = DB->SQLQuery1->FieldByName("C_spcode")->AsString;
			
			//... 생략

			DB->SQLQuery1->Next();
		}
		//~~~~~~~~~~~~~~~~~~~~~~~~~~
		for(i=0;i < iCNT;i++) {
			if(vcSBSP[i][0] == "1") {
				strSql =	"UPDATE tbl_sb_sbsp SET ";
				strSql +=	"I_runtime_na = " + vcSBSP[i][3]  + ",";
				strSql +=	"I_waram_st = " + vcSBSP[i][4] + ",";
				strSql +=	"I_daram_st = " + vcSBSP[i][5] + " ";
				strSql +=	"WHERE C_gigino = '" + vcSBSP[i][1] + "' AND C_spcode = '" + vcSBSP[i][2] + "'";
				DB->fn_Exec(strSql);
			}
		}
	}
}
//------------------------------------------------------------------------------
// (호출예)
//------------------------------------------------------------------------------
void __fastcall TfmMain::Button4Click(TObject *Sender)
{
	bool dbopen;			//DB연결상태
	Ndate = Now();//현재시간
	dbopen = fn_DBopen();
	if(dbopen) {
		labMsg1->Caption = Ndate.DateTimeString() + "  [DB 접속, 작업시작!]";
		fn_DBtoArray();
		fn_DBclose();
		Ndate = Now();
		labMsg3->Caption = Ndate.DateTimeString() + "  [작업완료, DB 차단!]";
	}
	else	labMsg1->Caption = Ndate.DateTimeString() + "  [DB 접속실패!]";
}
//... 생략