SQLite - C/C++

C/C++에서 SQLite를 사용해 보자.

 

C source code와 Precompiled Binaries for Windows를 다운로드 한다.

sqlite-amalgamation-XXX.zip과 sqlite-dll-win32-x86-XXX.zip를 다운로드하고 압축을 풀어 준다.

 

Visual Studio - Tools - Command Line - Developer Command Prompt를 실행 한다.

 

sqlite3.lib 파일을 생성 한다.

sqlite3.def, sqlite3.dll 파일이 있는 sqlite-dll-win32-x86-XXX 폴더에서 아래 명령어를 실행하면 sqlite3.lib 파일이 생성된다.

lib /def:sqlite3.def /machine:x86

 

sqlite-amalgamation-XXX 폴더에는 sqlite3.h 파일이 있다.

 

sqlite3.h, sqlite3.lib, sqlite3.dll 파일들을 프로젝트 폴더로 복사 한다.

 

 

SQLite 라이브러리 버전을 표시하는 간단한 프로그램을 작성하고 빌드해 보자.

 

SQLite 라이브러리 버전이 표시 된다.

 

#include <iostream>
#include "sqlite3.h"
 
#pragma comment(lib, "sqlite3.lib")
 
using namespace std;
 
int main()
{
    sqlite3* db;
    char* ErrMsg = 0;
    int rc;
 
    rc = sqlite3_open("test.db", &db);
    if (rc == SQLITE_OK) {
        cout << "Database opened successfully." << endl;
    }
    else {
        cout << "Can't open database: " << sqlite3_errmsg(db) << endl;
        return -1;
    }
        
    const char* sql1 = "CREATE TABLE COMPANY(" \
        "ID INT PRIMARY KEY NOT NULL, " \
        "NAME TEXT NOT NULL, " \
        "AGE INT NOT NULL, " \
        "ADDRESS CHAR(50), " \
        "SALARY REAL);";
 
    rc = sqlite3_exec(db, sql1, NULL, NULL, &ErrMsg);
    if (rc != SQLITE_OK) {
        cout << "SQL error: " << ErrMsg << endl;
        sqlite3_free(ErrMsg);
    }
    else {
        cout << "Table created successfully." << endl;
    }
    
    const char* sql2 = "INSERT INTO COMPANY VALUES(1, 'Paul', 32, 'California', 20000.00);" \
        "INSERT INTO COMPANY VALUES(2, 'Allen', 25, 'Texas', 15000.00);" \
        "INSERT INTO COMPANY VALUES(3, 'Teddy', 23, 'Norway', 20000.00);" \
        "INSERT INTO COMPANY VALUES(4, 'Mark', 25, 'Rich-Mond ', 65000.00);";
        
    rc = sqlite3_exec(db, sql2, NULL, NULL, &ErrMsg);
    if (rc != SQLITE_OK) {
        cout << "SQL error: " << ErrMsg << endl;
        sqlite3_free(ErrMsg);
    }
    else {
        cout << "Records created successfully." << endl;
    }
    
    sqlite3_close(db);
 
    return 0;
}

 

데이터베이스 생성, 테이블 생성, 레코드 삽입.

 

test.db 파일이 생성 된다.

 

 

#include <iostream>
#include "sqlite3.h"
 
#pragma comment(lib, "sqlite3.lib")
 
using namespace std;
 
/*
typedef int (*sqlite3_callback)(
    void*,    // Data provided in the 4th argument of sqlite3_exec()
    int,    // The number of columns in row
    char**,    // An array of strings representing fields in the row
    char**    // An array of strings representing column names
    );
*/
static int callback(void* data, int argc, char** argv, char** azColName) {
    int i;
    cout << (const char*)data << ":" << endl;
 
    for (i = 0; i < argc; i++) {
        cout << azColName[i] << " = " << (argv[i] ? argv[i] : "NULL") << endl;
    }
    cout << endl;
 
    return 0;
}
 
int main()
{
    sqlite3* db;
    char* ErrMsg = 0;
    int rc;
 
    rc = sqlite3_open("test.db", &db);
    if (rc == SQLITE_OK) {
        cout << "Database opened successfully." << endl;
    }
    else {
        cout << "Can't open database: " << sqlite3_errmsg(db) << endl;
        return -1;
    }
    
    const char* sql = "SELECT * FROM COMPANY;";
    const char* data = "Callback function called";
    
    rc = sqlite3_exec(db, sql, callback, (void*)data, &ErrMsg);
    if (rc != SQLITE_OK) {
        cout << "SQL error: " << ErrMsg << endl;
        sqlite3_free(ErrMsg);
    }
    else {
        cout << "Operation done successfully." << endl;
    }
    
    sqlite3_close(db);
 
    return 0;
}

 

데이터 읽기.

 

#include <iostream>
#include "sqlite3.h"
 
#pragma comment(lib, "sqlite3.lib")
 
using namespace std;
 
/*
typedef int (*sqlite3_callback)(
    void*,    // Data provided in the 4th argument of sqlite3_exec()
    int,    // The number of columns in row
    char**,    // An array of strings representing fields in the row
    char**    // An array of strings representing column names
    );
*/
static int callback(void* data, int argc, char** argv, char** azColName) {
    int i;
    cout << (const char*)data << ":" << endl;
 
    for (i = 0; i < argc; i++) {
        cout << azColName[i] << " = " << (argv[i] ? argv[i] : "NULL") << endl;
    }
    cout << endl;
 
    return 0;
}
 
int main()
{
    sqlite3* db;
    char* ErrMsg = 0;
    int rc;
 
    rc = sqlite3_open("test.db", &db);
    if (rc == SQLITE_OK) {
        cout << "Database opened successfully." << endl;
    }
    else {
        cout << "Can't open database: " << sqlite3_errmsg(db) << endl;
        return -1;
    }
    
    const char* sql = "UPDATE COMPANY SET SALARY = 25000.00 WHERE ID = 1; " \
        "DELETE FROM COMPANY WHERE ID = 2; " \
        "SELECT * FROM COMPANY;";
    const char* data = "Callback function called";
    
    rc = sqlite3_exec(db, sql, callback, (void*)data, &ErrMsg);
    if (rc != SQLITE_OK) {
        cout << "SQL error: " << ErrMsg << endl;
        sqlite3_free(ErrMsg);
    }
    else {
        cout << "Operation done successfully." << endl;
    }
    
    sqlite3_close(db);
 
    return 0;
}

 

데이터 수정 및 삭제.