Libsqlite3: Difference between revisions

Revision as of 09:18, 25 February 2015

Overview

Sqlite3 프로그래밍.

Sqlite3 프로그래밍 관련 함수 전체 레퍼런스는 이곳<ref>https://www.sqlite.org/c3ref/funclist.html</ref> 애서 확인할 수 있다.

Basic functions

exec

Interface

<source lang=c> int sqlite3_exec(

 sqlite3*,                                  /* An open database */
 const char *sql,                           /* SQL to be evaluated */
 int (*callback)(void*,int,char**,char**),  /* Callback function */
 void *,                                    /* 1st argument to callback */
 char **errmsg                              /* Error msg written here */

); </source>

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, 실패시 에러코드를 리턴한다.

sqlite3_step

Interface

<source lang=c> int sqlite3_step(sqlite3_stmt*); </source>

Return 값으로 다음의 값들이 올 수 있다.

SQLITE_BUSY

현재 데이터베이스에 lock 이 걸려 있음을 뜻한다. 다시 재시도를 하거나 롤백 후, 다시 시도할 수 있다.

SQLITE_DONE

해당 쿼리 구문이 성공적으로 수행(종료)되었음을 의미한다. sqlite3_reset() 호출 후, sqlite3_step()를 수행하여야 한다.

SQLITE_ROW

수행 결과에 데이터가 있음을 나타낸다. select 구문과 같은 경우를 생각해볼 수 있는데, 이후 column_access 함수들을 사용할 수 있다.

SQLITE_ERROR

오류가 발생했음을 나타낸다.

SQLITE_MISUSE

잘못된 사용법 오류.

column name

Interface

<source lang=c> const char *sqlite3_column_name(sqlite3_stmt*, int N); const void *sqlite3_column_name16(sqlite3_stmt*, int N); </source>

column patch

<source lang=c> const void *sqlite3_column_blob(sqlite3_stmt*, int iCol); int sqlite3_column_bytes(sqlite3_stmt*, int iCol); int sqlite3_column_bytes16(sqlite3_stmt*, int iCol); double sqlite3_column_double(sqlite3_stmt*, int iCol); int sqlite3_column_int(sqlite3_stmt*, int iCol); sqlite3_int64 sqlite3_column_int64(sqlite3_stmt*, int iCol); const unsigned char *sqlite3_column_text(sqlite3_stmt*, int iCol); const void *sqlite3_column_text16(sqlite3_stmt*, int iCol); int sqlite3_column_type(sqlite3_stmt*, int iCol); sqlite3_value *sqlite3_column_value(sqlite3_stmt*, int iCol); </source>

Locking

SQLite3 에서 Locking 은 가장 큰 특징이기도 하면서도 가장 큰 단점이기도 하다. 기본적으로 SQLite 에서의 DB 접근은 한번에 하나의 접근만 허용한다. 만약 높은 수준의 동시성을 지원해야 한다면, 다른 DB 를 찾아보는 것이 좋다.

Samples

Connecting to database

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

Code

include <stdio.h>
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

include <stdio.h>
include <stdlib.h>
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

include <stdio.h>
include <stdlib.h>
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

include <stdio.h>
include <stdlib.h>
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

include <stdio.h>
include <stdlib.h>
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

include <stdio.h>
include <stdlib.h>
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

References

@@ Line 576: / Line 576: @@
 == External links ==
 * <nowiki>http://www.tutorialspoint.com/sqlite/sqlite_c_cpp.htm</nowiki>
+== References ==
+<references />
 [[category:linux]]

Libsqlite3: Difference between revisions

Revision as of 09:18, 25 February 2015

Contents

Overview

Basic functions

exec

SQL statement

Prepare statement

sqlite3_step

column name

column patch

Locking

Samples

Connecting to database

Create a table

Insert operation

Select operation

Update operation

Delete operation

External links

References

Navigation menu

Libsqlite3: Difference between revisions

Revision as of 09:18, 25 February 2015

Overview

Basic functions

exec

SQL statement

Prepare statement

sqlite3_step

column name

column patch

Locking

Samples

Connecting to database

Create a table

Insert operation

Select operation

Update operation

Delete operation

External links

References

Navigation menu

Search