2009年8月6日木曜日

sqlite3 で自作関数


/*
// to compile with vc
CL /EHsc /GR /MD /c sqli.cpp
link /DLL sqli.obj
mt -manifest sqli.dll.manifest -outputresource:sqli.dll;2
*/

/*
sqlite3 test.db
> create table test ( id integer primary key, name text );
> select load_extension('sqli.dll');
> select test_insert( 'hoge' );
> select * from test;
*/

#include <sqlite3ext.h>

extern "C" {

SQLITE_EXTENSION_INIT1

static void test_insert_func( sqlite3_context* context, int argc, sqlite3_value** argv ) {
const char sql[] = "insert or replace into test (name) values (?)";
sqlite3* db = (sqlite3*)sqlite3_user_data( context );
sqlite3_stmt* statement;
int err = sqlite3_prepare_v2( db, sql, -1, &statement, 0 );
const char* p = (const char*)sqlite3_value_text( argv[0] );
size_t len = sqlite3_value_bytes( argv[0] );
sqlite3_bind_text( statement, 1, p, len, SQLITE_STATIC );
while( err = sqlite3_step( statement ), err == SQLITE_BUSY ) {}
sqlite3_finalize( statement );
}

__declspec(dllexport) int sqlite3_extension_init(sqlite3 *db, char **errmsg, const sqlite3_api_routines *api) {
SQLITE_EXTENSION_INIT2(api);
sqlite3_create_function(db, "test_insert", 1, SQLITE_UTF8, (void*)db, test_insert_func, 0, 0);
return 0;
}


}



という訳で、sqlite3 でも、ストアド・プロシジャからトリガまで自由自在やね。やったね!

0 件のコメント: