SQLite使ってみる その2
SQLite使ってみる その1 - forestexor’s blog(仮)
で書いたコードを一つのプログラムとしてまとめた物。
※追記
オープンに失敗した場合でも「sqlite3*」はインスタンスが作成されるのでクローズしないとメモリリークする事が判明、申し訳ありませんでした。
・ソースコード
#include <stdio.h> #include "sqlite3.h" #pragma comment( lib, "sqlite3-x86.dll" ) int main(){ char* strDBName = "test.db"; char* strErrMsg = nullptr; sqlite3* pDB = nullptr; //--------------------------------------------------------------------------------------------- // 読み取り書き込みモードで開く 読み取り専用の場合は「SQLITE_OPEN_READONLY」 int iErr = ::sqlite3_open_v2( strDBName, &pDB, SQLITE_OPEN_READWRITE, nullptr ); if( SQLITE_OK != iErr && SQLITE_CANTOPEN != iErr ){ // オープン失敗 ::printf_s( "DB open failed. 1\n%s\n", ::sqlite3_errmsg(pDB) ); // 追記部分↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓ iErr = ::sqlite3_close( pDB ); if( SQLITE_OK != iErr ){ ::printf_s( "DB close failed.\n%s\n", ::sqlite3_errmsg(pDB) ); ::getchar(); return -1; } //↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑ ::getchar(); return -1; // .dbファイルが存在しない場合 }else if( SQLITE_CANTOPEN == iErr ){ //----------------------------------------------------------------------------------------- // .dbファイル新規作成で開く iErr = ::sqlite3_open_v2( strDBName, &pDB, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, nullptr ); if( SQLITE_OK != iErr ){ ::printf_s( "DB open failed. 2\n%s\n", ::sqlite3_errmsg(pDB) ); ::printf_s( "DB open failed. 1\n%s\n", ::sqlite3_errmsg(pDB) ); // 追記部分↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓ iErr = ::sqlite3_close( pDB ); if( SQLITE_OK != iErr ){ ::printf_s( "DB close failed.\n%s\n", ::sqlite3_errmsg(pDB) ); ::getchar(); return -1; } //↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑ ::getchar(); return -1; }else{ ::printf_s( ".db file created. DB open success.\n" ); } //----------------------------------------------------------------------------------------- // テーブル作成 iErr = ::sqlite3_exec( pDB, "create table hoge(id integer, time date)", nullptr, nullptr, &strErrMsg ); if( SQLITE_OK != iErr ){ // テーブル作成失敗 ::printf_s( "create table failed.\n" ); ::printf_s( "%s\n", strErrMsg ); ::sqlite3_free( strErrMsg ); // DBを閉じる iErr = ::sqlite3_close( pDB ); if( SQLITE_OK != iErr ){ ::printf_s( "DB close failed.\n%s\n", ::sqlite3_errmsg(pDB) ); ::getchar(); return -1; } ::printf_s( "DB close success.\n" ); ::getchar(); return -1; } // テーブル作成成功 ::printf_s( "create table success.\n" ); //----------------------------------------------------------------------------------------- // レコード挿入 iErr = ::sqlite3_exec( pDB, "insert into hoge(id, time) values(1, CURRENT_TIME)", nullptr, nullptr, &strErrMsg ); if( SQLITE_OK != iErr ){ // 挿入失敗 ::printf_s( "%s\n", strErrMsg ); sqlite3_free( strErrMsg ); // DBを閉じる iErr = ::sqlite3_close( pDB ); if( SQLITE_OK != iErr ){ ::printf_s( "DB close failed.\n%s\n", ::sqlite3_errmsg(pDB) ); ::getchar(); return -1; } ::printf_s( "DB close success.\n" ); ::getchar(); return -1; } // 挿入成功 ::printf_s( "insert success. 1\n" ); // レコード挿入その2 iErr = ::sqlite3_exec( pDB, "insert into hoge(id, time) values(2, CURRENT_TIME)", nullptr, nullptr, &strErrMsg ); if( SQLITE_OK != iErr ){ ::printf_s( "%s\n", strErrMsg ); sqlite3_free( strErrMsg ); iErr = ::sqlite3_close( pDB ); if( SQLITE_OK != iErr ){ ::printf_s( "DB close failed.\n%s\n", ::sqlite3_errmsg(pDB) ); ::getchar(); return -1; } ::printf_s( "DB close success.\n" ); ::getchar(); return -1; } ::printf_s( "insert success. 2\n" ); }else{ // 既存の.dbファイルの読み込みに成功 ::printf_s( "DB open success.\n" ); //----------------------------------------------------------------------------------------- // レコード更新 iErr = sqlite3_exec( pDB, "update hoge set time = CURRENT_TIME where id = 2", nullptr, nullptr, &strErrMsg ); if( SQLITE_OK != iErr ){ // 更新失敗 ::printf_s( "%s\n", strErrMsg ); sqlite3_free( strErrMsg ); // DBを閉じる iErr = ::sqlite3_close( pDB ); if( SQLITE_OK != iErr ){ ::printf_s( "DB close failed.\n%s\n", ::sqlite3_errmsg(pDB) ); ::getchar(); return -1; } ::printf_s( "DB close success.\n" ); ::getchar(); return -1; } // 更新成功 ::printf_s( "update success. 1\n" ); } //--------------------------------------------------------------------------------------------- // レコードの抽出と表示 sqlite3_stmt* stmt = nullptr; // ステートメント // iErr = ::sqlite3_prepare_v2( pDB, "select * from hoge where id = 1", -1, &stmt, nullptr ); iErr = ::sqlite3_prepare_v2( pDB, "select * from hoge", -1, &stmt, nullptr ); if( SQLITE_OK != iErr ){ // 抽出失敗 ::printf_s( "%s\n", ::sqlite3_errmsg(pDB) ); ::sqlite3_reset( stmt ); ::sqlite3_finalize( stmt ); iErr = ::sqlite3_close( pDB ); if( SQLITE_OK != iErr ){ ::printf_s( "DB close failed.\n%s\n", ::sqlite3_errmsg(pDB) ); ::getchar(); return -1; } ::printf_s( "DB close success.\n" ); ::getchar(); return -1; }else{ // 抽出成功 ::printf_s( "select success.\n" ); for( ; SQLITE_ROW == ::sqlite3_step(stmt) ; ){ // step1回で1レコード取得 ::printf_s("+--------+-----------------+\n| "); // レコードの列数取得 int iColMax = ::sqlite3_column_count(stmt); for( int i(0) ; i < iColMax ; i++ ){ // 列名取得 const char* name = sqlite3_column_name( stmt, i ); // 列のデータ型取得 int iColType = sqlite3_column_type( stmt, i ); switch( iColType ){ // INTEGER(int) case SQLITE_INTEGER: ::printf_s( "%s = %d", name, sqlite3_column_int( stmt, i ) ); break; // REAL(double) case SQLITE_FLOAT: ::printf_s( "%s = %f", name, (float)sqlite3_column_double( stmt, i ) ); break; // TEXT(string) case SQLITE_TEXT: ::printf_s( "%s = %s", name, sqlite3_column_text( stmt, i ) ); break; // NULL(nullptr) case SQLITE_NULL: break; // BLOB(不明) case SQLITE_BLOB: break; } ::printf_s(" | "); }// for( int i(0) ; i < iColMax ; i++ ) ::printf_s("\n"); }// for( ; SQLITE_ROW == ::sqlite3_step(stmt) ; ) ::printf_s("+--------+-----------------+\n"); } // stmtの後始末 ::sqlite3_reset( stmt ); ::sqlite3_finalize( stmt ); //--------------------------------------------------------------------------------------------- // レコードの削除 // 削除用に1つレコードを追加 iErr = ::sqlite3_exec( pDB, "insert into hoge(id, time) values(3, CURRENT_TIME)", nullptr, nullptr, &strErrMsg ); if( SQLITE_OK != iErr ){ ::printf_s( "%s\n", strErrMsg ); sqlite3_free( strErrMsg ); iErr = ::sqlite3_close( pDB ); if( SQLITE_OK != iErr ){ ::printf_s( "DB close failed.\n%s\n", ::sqlite3_errmsg(pDB) ); ::getchar(); return -1; } ::printf_s( "DB close success.\n" ); ::getchar(); return -1; } ::printf_s( "insert success. 3\n" ); // 追加確認の表示 iErr = ::sqlite3_prepare_v2( pDB, "select * from hoge", -1, &stmt, nullptr ); if( SQLITE_OK != iErr ){ // 抽出失敗 ::printf_s( "%s\n", ::sqlite3_errmsg(pDB) ); ::sqlite3_reset( stmt ); ::sqlite3_finalize( stmt ); iErr = ::sqlite3_close( pDB ); if( SQLITE_OK != iErr ){ ::printf_s( "DB close failed.\n%s\n", ::sqlite3_errmsg(pDB) ); ::getchar(); return -1; } ::printf_s( "DB close success.\n" ); ::getchar(); return -1; }else{ // 抽出成功 ::printf_s( "select success.\n" ); for( ; SQLITE_ROW == ::sqlite3_step(stmt) ; ){ // step1回で1レコード取得 ::printf_s("+--------+-----------------+\n| "); // レコードの列数取得 int iColMax = ::sqlite3_column_count(stmt); for( int i(0) ; i < iColMax ; i++ ){ // 列名取得 const char* name = sqlite3_column_name( stmt, i ); // 列のデータ型取得 int iColType = sqlite3_column_type( stmt, i ); switch( iColType ){ // INTEGER(int) case SQLITE_INTEGER: ::printf_s( "%s = %d", name, sqlite3_column_int( stmt, i ) ); break; // REAL(double) case SQLITE_FLOAT: ::printf_s( "%s = %f", name, (float)sqlite3_column_double( stmt, i ) ); break; // TEXT(string) case SQLITE_TEXT: ::printf_s( "%s = %s", name, sqlite3_column_text( stmt, i ) ); break; // NULL(nullptr) case SQLITE_NULL: break; // BLOB(不明) case SQLITE_BLOB: break; } ::printf_s(" | "); }// for( int i(0) ; i < iColMax ; i++ ) ::printf_s("\n"); }// for( ; SQLITE_ROW == ::sqlite3_step(stmt) ; ) ::printf_s("+--------+-----------------+\n"); } ::sqlite3_reset( stmt ); ::sqlite3_finalize( stmt ); // 削除 iErr = sqlite3_exec( pDB, "delete from hoge where id = 3", nullptr, nullptr, &strErrMsg ); if( SQLITE_OK != iErr ){ // 削除失敗 ::printf_s( "%s\n", strErrMsg ); sqlite3_free( strErrMsg ); iErr = ::sqlite3_close( pDB ); if( SQLITE_OK != iErr ){ ::printf_s( "DB close failed.\n%s\n", ::sqlite3_errmsg(pDB) ); ::getchar(); return -1; } ::printf_s( "DB close success.\n" ); ::getchar(); return -1; }else{ // 削除成功 ::printf_s( "delete success.\n" ); } // 削除確認の表示 iErr = ::sqlite3_prepare_v2( pDB, "select * from hoge", -1, &stmt, nullptr ); if( SQLITE_OK != iErr ){ // 抽出失敗 ::printf_s( "%s\n", ::sqlite3_errmsg(pDB) ); ::sqlite3_reset( stmt ); ::sqlite3_finalize( stmt ); iErr = ::sqlite3_close( pDB ); if( SQLITE_OK != iErr ){ ::printf_s( "DB close failed.\n%s\n", ::sqlite3_errmsg(pDB) ); ::getchar(); return -1; } ::printf_s( "DB close success.\n" ); ::getchar(); return -1; }else{ // 抽出成功 ::printf_s( "select success.\n" ); for( ; SQLITE_ROW == ::sqlite3_step(stmt) ; ){ // step1回で1レコード取得 ::printf_s("+--------+-----------------+\n| "); // レコードの列数取得 int iColMax = ::sqlite3_column_count(stmt); for( int i(0) ; i < iColMax ; i++ ){ // 列名取得 const char* name = sqlite3_column_name( stmt, i ); // 列のデータ型取得 int iColType = sqlite3_column_type( stmt, i ); switch( iColType ){ // INTEGER(int) case SQLITE_INTEGER: ::printf_s( "%s = %d", name, sqlite3_column_int( stmt, i ) ); break; // REAL(double) case SQLITE_FLOAT: ::printf_s( "%s = %f", name, (float)sqlite3_column_double( stmt, i ) ); break; // TEXT(string) case SQLITE_TEXT: ::printf_s( "%s = %s", name, sqlite3_column_text( stmt, i ) ); break; // NULL(nullptr) case SQLITE_NULL: break; // BLOB(不明) case SQLITE_BLOB: break; } ::printf_s(" | "); }// for( int i(0) ; i < iColMax ; i++ ) ::printf_s("\n"); }// for( ; SQLITE_ROW == ::sqlite3_step(stmt) ; ) ::printf_s("+--------+-----------------+\n"); } ::sqlite3_reset( stmt ); ::sqlite3_finalize( stmt ); //--------------------------------------------------------------------------------------------- // おまけ1 テーブル数の取得 iErr = sqlite3_prepare_v2( pDB, "select count(*) as TableNum from sqlite_master where type='table'", -1, &stmt, nullptr ); if( SQLITE_OK != iErr ){ ::printf_s( "%s\n", ::sqlite3_errmsg(pDB) ); ::sqlite3_reset( stmt ); ::sqlite3_finalize( stmt ); iErr = ::sqlite3_close( pDB ); if( SQLITE_OK != iErr ){ ::printf_s( "DB close failed.\n%s\n", ::sqlite3_errmsg(pDB) ); ::getchar(); return -1; } ::printf_s( "DB close success.\n" ); ::getchar(); return -1; }else{ // 表示 ::printf_s( "select success\n" ); while( SQLITE_ROW == sqlite3_step(stmt) ){ int iMax = sqlite3_column_count(stmt); for( int i(0) ; i < iMax ; i++ ){ const char* name = sqlite3_column_name( stmt, i ); int iColType = sqlite3_column_type( stmt, i ); if( SQLITE_TEXT == iColType ){ ::printf_s( "%s = %s\n", name, sqlite3_column_text( stmt, i ) ); }else if( SQLITE_INTEGER == iColType ){ ::printf_s( "%s = %d\n", name, sqlite3_column_int( stmt, i ) ); } } } } ::sqlite3_reset( stmt ); ::sqlite3_finalize( stmt ); //--------------------------------------------------------------------------------------------- // おまけ2 テーブル名の取得 iErr = sqlite3_prepare_v2( pDB, "select tbl_name from sqlite_master where type='table'", -1, &stmt, nullptr ); if( SQLITE_OK != iErr ){ ::printf_s( "%s\n", ::sqlite3_errmsg(pDB) ); ::sqlite3_reset( stmt ); ::sqlite3_finalize( stmt ); iErr = ::sqlite3_close( pDB ); if( SQLITE_OK != iErr ){ ::printf_s( "DB close failed.\n%s\n", ::sqlite3_errmsg(pDB) ); ::getchar(); return -1; } ::printf_s( "DB close success.\n" ); ::getchar(); return -1; }else{ // 表示 ::printf_s( "select success\n" ); while( SQLITE_ROW == sqlite3_step(stmt) ){ int iMax = sqlite3_column_count(stmt); for( int i(0) ; i < iMax ; i++ ){ const char* name = sqlite3_column_name( stmt, i ); int iColType = sqlite3_column_type( stmt, i ); if( SQLITE_TEXT == iColType ){ ::printf_s( "%s = %s\n", name, sqlite3_column_text( stmt, i ) ); }else if( SQLITE_INTEGER == iColType ){ ::printf_s( "%s = %d\n", name, sqlite3_column_int( stmt, i ) ); } } } } ::sqlite3_reset( stmt ); ::sqlite3_finalize( stmt ); //--------------------------------------------------------------------------------------------- // 後始末 iErr = ::sqlite3_close( pDB ); if( SQLITE_OK != iErr ){ ::printf_s( "DB close failed.\n%s\n", ::sqlite3_errmsg(pDB) ); ::getchar(); return -1; } ::printf_s( "DB close success.\n" ); ::getchar(); return 0; }
