프로그램/C++Builder
[ C++Builder ] TSQLConnection 을 이용한 MySQL 접속 및 쿼리문사용
구서기
2014. 12. 19. 12:30
대략 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 접속실패!]";
}
//... 생략