Libsqlite3

From 탱이의 잡동사니
Jump to navigation Jump to search

Overview

Sqlite3 프로그래밍.

SQL statement

Prepare statement

실행된 SQL의 결과를 바로 리턴하는 것이 아닌, statement 형태의 구조로 가지고 있으면서 결과값을 하나씩 하나씩 row 단위로 사용할 수 있도록 하는 형태.

  • Interface

<source lang=c> typedef struct sqlite3_stmt sqlite3_stmt; </source>

"Prepared statement" 혹은 "Compiled SQL statement" 혹은 그냥 "statement"라고도 불린다. 보통은 다음의 패턴으로 사용된다. 

- sqlite3_prepare_v2() 혹은 그와 비슷한 함수로 object를 생성한다.
- sqlite3_bind_*() 함수로 SQL 결과값을 host_parameter 로 바인딩한다.
- sqlite3_step() 함수로 SQL 문을 하나씩 실행시킨다.
- sqlite3_reset() 함수로 statement 를 리셋한다. 
- sqlite3_finalize() 함수로 object를 종료한다.
  • Interface

<source lang=c> int sqlite3_prepare( 

 sqlite3 *db,            /* Database handle */
 const char *zSql,       /* SQL statement, UTF-8 encoded */
 int nByte,              /* Maximum length of zSql in bytes. */
 sqlite3_stmt **ppStmt,  /* OUT: Statement handle */
 const char **pzTail     /* OUT: Pointer to unused portion of zSql */

); int sqlite3_prepare_v2( 

 sqlite3 *db,            /* Database handle */
 const char *zSql,       /* SQL statement, UTF-8 encoded */
 int nByte,              /* Maximum length of zSql in bytes. */
 sqlite3_stmt **ppStmt,  /* OUT: Statement handle */
 const char **pzTail     /* OUT: Pointer to unused portion of zSql */

); int sqlite3_prepare16( 

 sqlite3 *db,            /* Database handle */
 const void *zSql,       /* SQL statement, UTF-16 encoded */
 int nByte,              /* Maximum length of zSql in bytes. */
 sqlite3_stmt **ppStmt,  /* OUT: Statement handle */
 const void **pzTail     /* OUT: Pointer to unused portion of zSql */

); int sqlite3_prepare16_v2( 

 sqlite3 *db,            /* Database handle */
 const void *zSql,       /* SQL statement, UTF-16 encoded */
 int nByte,              /* Maximum length of zSql in bytes. */
 sqlite3_stmt **ppStmt,  /* OUT: Statement handle */
 const void **pzTail     /* OUT: Pointer to unused portion of zSql */

); </source> 인자값 "db"는 데이터 베이스 커넥션을 의미한다.

인자값 "zSql"은 실행될 SQL 쿼리를 의미한다.(UTF-8/UTF-16 으로 인코딩된다.) sqlite3_prepare() 와 sqlite3_prepare_v2() 는 UTF-8 를 사용하고, sqlite3_prepare16()과 sqlite3_prepare16_v2()는 UTF-16을 사용한다.

인자값 "nByte"는 SQL 쿼리 최대 길이를 의미한다. 문자열 종료 문자 '\000' 이나, '\u0000'이 올때까지 읽거나, 지정된 바이트 길이까지 읽어들인다. 음수 값으로 지정시, 문자열 종료문자가 올때까지 읽어들인다.

인자값 "pzTail"는 입려된 zSql 에서 nByte 설정으로 인해 실행되지 못한 다음번 문자열의 첫 번째 바이트를 가리키는 역할은 한다.

인자값 "ppStmt"는 prepared statement 가리킨다. 에러 발생시, NULL 로 입력된다.

성공시 SQLITE_OK, 실패시 에러코드를 리턴한다.

Samples

Connecting to database

데이터 베이스 생성 후 접속하기.

  • Code

<source lang=c>

  1. include <stdio.h>
  2. include <sqlite3.h>

int main(int argc, char* argv[]) { 

   sqlite3 *db;
   char *zErrMsg = 0;
   int rc;
   
   rc = sqlite3_open("test.db", &db);
   
   if(rc)
   {
       fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
       exit(0);
   }
   else
   {
       fprintf(stderr, "Opened database successfully\n");
   }
   sqlite3_close(db);

}

</source>

  • compile

<source lang=bash> $ gcc test.c -l sqlite3 $ ./a.out Opened database successfully </source>

Create a table

데이터 베이스 생성 후, 테이블 생성하기.

  • Code

<source lang=c>

  1. include <stdio.h>
  2. include <stdlib.h>
  3. include <sqlite3.h>

static int callback(void *NotUsed, int argc, char **argv, char **azColName) { 

   int i;
   for(i = 0; i < argc; i++)
   {
       printf("%s = %s\n", azColName[i], argv[1] ? argv[i] : "NULL");
   }
   printf("\n");
   
   return 0;

}

int main(int argc, char* argv[]) { 

   sqlite3 *db;
   char *zErrMsg = 0;
   int rc;
   char *sql;
   
   /* Open database */
   rc = sqlite3_open("test.db", &db);
   if(rc)
   {
       fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
       exit(0);
   }
   else
   {
       fprintf(stdout, "Opened database successfully\n");
   }
   
   /* Create SQL statement */
   sql = "CREATE TABLE COMPANY(" \
       "ID INT PRIMARY KEY     NOT NULL," \
       "NAME           TEXT    NOT NULL," \
       "AGE            INT     NOT NULL," \
       "ADDRESS        CHAR(50)," \
       "SALARY         REAL);";
   
   /* Execute SQL statement */
   rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);
   if(rc != SQLITE_OK)
   {
       fprintf(stderr, "SQL error: %s\n", zErrMsg);
       sqlite3_free(zErrMsg);
   }
   else
   {
       fprintf(stdout, "Table created successfully\n");
   }
   sqlite3_close(db);
   return 0;

} </source>

Insert operation

  • Code

<source lang=c>

  1. include <stdio.h>
  2. include <stdlib.h>
  3. include <sqlite3.h>

static int callback(void *NotUsed, int argc, char **argv, char **azColName) { 

   int i;
   for(i = 0; i < argc; i++)
   {
       printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
   }
   printf("\n");
   return 0;

}

int main(int argc, char* argv[]) { 

   sqlite3 *db;
   char *zErrMsg = 0;
   int rc;
   char *sql;
   
   /* Open database */
   rc = sqlite3_open("test.db", &db);
   if(rc)
   {
       fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
       exit(0);
   }
   else
   {
       fprintf(stderr, "Opened database successfully\n");
   }
   
   /* Create SQL statement */
   sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "  \
       "VALUES (1, 'Paul', 32, 'California', 20000.00 ); " \
       "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "  \
       "VALUES (2, 'Allen', 25, 'Texas', 15000.00 ); "     \
       "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)" \
       "VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );" \
       "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)" \
       "VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );";
   
   /* Execute SQL statement */
   rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);
   if(rc != SQLITE_OK)
   {
       fprintf(stderr, "SQL error: %s\n", zErrMsg);
       sqlite3_free(zErrMsg);
   }
   else
   {
       fprintf(stdout, "Records created successfully\n");
   }
   sqlite3_close(db);
   return 0;

} </source>

  • Result

<source lang=bash> $ ./main Opened database successfully Records created successfully </source>

Select operation

  • Callback prototype

<source lang=c> 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 */

); </source>

  • code

<source lang=c>

  1. include <stdio.h>
  2. include <stdlib.h>
  3. include <sqlite3.h>

static int callback(void *data, int argc, char **argv, char **azColName) { 

   int i;
   
   fprintf(stderr, "%s: ", (const char*)data);
   
   for(i = 0; i < argc; i++)
   {
       printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
   }
   printf("\n");
   return 0;

}

int main(int argc, char* argv[]) { 

   sqlite3 *db;
   char *zErrMsg = 0;
   int rc;
   char *sql;
   const char* data = "Callback function called";
   
   /* Open database */
   rc = sqlite3_open("test.db", &db);
   if(rc)
   {
       fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
       exit(0);
   }
   else
   {
       fprintf(stderr, "Opened database successfully\n");
   }
   
   /* Create SQL statement */
   sql = "SELECT * from COMPANY";
   
   /* Execute SQL statement */
   rc = sqlite3_exec(db, sql, callback, (void*)data, &zErrMsg);
   if(rc != SQLITE_OK)
   {
       fprintf(stderr, "SQL error: %s\n", zErrMsg);
       sqlite3_free(zErrMsg);
   }
   else
   {
       fprintf(stdout, "Operation done successfully\n");
   }
   sqlite3_close(db);
   return 0;

} </source>

  • Result
Opened database successfully
Callback function called: ID = 1
NAME = Paul
AGE = 32
ADDRESS = California
SALARY = 20000.0

Callback function called: ID = 2
NAME = Allen
AGE = 25
ADDRESS = Texas
SALARY = 15000.0

Callback function called: ID = 3
NAME = Teddy
AGE = 23
ADDRESS = Norway
SALARY = 20000.0

Callback function called: ID = 4
NAME = Mark
AGE = 25
ADDRESS = Rich-Mond
SALARY = 65000.0

Operation done successfully

Update operation

  • Code

<source lang=c>

  1. include <stdio.h>
  2. include <stdlib.h>
  3. include <sqlite3.h>

static int callback(void *data, int argc, char **argv, char **azColName) { 

   int i;
   fprintf(stderr, "%s: ", (const char*)data);
   for(i = 0; i < argc; i++)
   {
       printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
   }
   printf("\n");
   return 0;

}

int main(int argc, char* argv[]) { 

   sqlite3 *db;
   char *zErrMsg = 0;
   int rc;
   char *sql;
   const char* data = "Callback function called";
   
   /* Open database */
   rc = sqlite3_open("test.db", &db);
   if(rc)
   {
       fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
       exit(0);
   }
   else
   {
       fprintf(stderr, "Opened database successfully\n");
   }
   
   /* Create merged SQL statement */
   sql = "UPDATE COMPANY set SALARY = 25000.00 where ID=1; " \
       "SELECT * from COMPANY";
   
   /* Execute SQL statement */
   rc = sqlite3_exec(db, sql, callback, (void*)data, &zErrMsg);
   if(rc != SQLITE_OK)
   {
       fprintf(stderr, "SQL error: %s\n", zErrMsg);
       sqlite3_free(zErrMsg);
   }
   else
   {
       fprintf(stdout, "Operation done successfully\n");
   }
   sqlite3_close(db);
   return 0;

} </source>

  • Result

<source lang=bash> Opened database successfully Callback function called: ID = 1 NAME = Paul AGE = 32 ADDRESS = California SALARY = 25000.0

Callback function called: ID = 2 NAME = Allen AGE = 25 ADDRESS = Texas SALARY = 15000.0

Callback function called: ID = 3 NAME = Teddy AGE = 23 ADDRESS = Norway SALARY = 20000.0

Callback function called: ID = 4 NAME = Mark AGE = 25 ADDRESS = Rich-Mond SALARY = 65000.0

Operation done successfully </source>

Delete operation

  • Code

<source lang=c>

  1. include <stdio.h>
  2. include <stdlib.h>
  3. include <sqlite3.h>

static int callback(void *data, int argc, char **argv, char **azColName) { 

   int i;
   fprintf(stderr, "%s: ", (const char*)data);
   
   for(i = 0; i < argc; i++)
   {
       printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
   }
   printf("\n");
   
   return 0;

}

int main(int argc, char* argv[]) { 

   sqlite3 *db;
   char *zErrMsg = 0;
   int rc;
   char *sql;
   const char* data = "Callback function called";
   
   /* Open database */
   rc = sqlite3_open("test.db", &db);
   if(rc)
   {
       fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
       exit(0);
   }
   else
   {
       fprintf(stderr, "Opened database successfully\n");
   }
   
   /* Create merged SQL statement */
   sql = "DELETE from COMPANY where ID = 2; " \
       "SELECT * from COMPANY";
   
   /* Execute SQL statement */
   rc = sqlite3_exec(db, sql, callback, (void*)data, &zErrMsg);
   if(rc != SQLITE_OK)
   {
       fprintf(stderr, "SQL error: %s\n", zErrMsg);
       sqlite3_free(zErrMsg);
   }
   else
   {
       fprintf(stdout, "Operation done successfully\n");
   }
   sqlite3_close(db);
   return 0;

} </source>

  • Result

<source lang=bash> Opened database successfully Callback function called: ID = 1 NAME = Paul AGE = 32 ADDRESS = California SALARY = 25000.0

Callback function called: ID = 3 NAME = Teddy AGE = 23 ADDRESS = Norway SALARY = 20000.0

Callback function called: ID = 4 NAME = Mark AGE = 25 ADDRESS = Rich-Mond SALARY = 65000.0

Operation done successfully </source>

External links

  • http://www.tutorialspoint.com/sqlite/sqlite_c_cpp.htm
Retrieved from "http://wiki.pchero21.com/index.php?title=Libsqlite3&oldid=209"