I tried to do what you and Igor said, but I found out that it does not really
work that way. I can make a user-defined function, but it stays active only
while the current connection is open. It is not persisted in the "database".
It does not work even if I try it from the same application. Here is a short
snippet of code to demonstrate the test:
static void MyFunc(sqlite3_context *context, int argc, sqlite3_value **argv)
{
if (argc < 1)
return;
char sqlCommand[512];
sprintf(sqlCommand, (char*)sqlite3_value_text(argv[0]));
sqlite3* db = (sqlite3*) sqlite3_user_data(context);
sqlite3_exec(db, sqlCommand, NULL, NULL, NULL);
}
void TestSQLiteOpen()
{
sqlite3* db;
int rc;
//The first attempt
rc = sqlite3_open("C:\\SQLite\\Local\\test.db3", &db);
if (sqlite3_create_function(db, "MyFunc", 1, SQLITE_UTF8, db, &MyFunc,
NULL, NULL) != 0)
{
cout << "Problem with MyFunc" << endl;
}
sqlite3_exec(db, "UPDATE PARENT2 SET COLUMN3 = 1.1", NULL, NULL, NULL);
sqlite3_close(db);
//The second attempt
rc = sqlite3_open("C:\\SQLite\\Local\\test.db3", &db);
sqlite3_exec(db, "UPDATE PARENT2 SET COLUMN3 = 2.2", NULL, NULL, NULL);
sqlite3_close(db);
//The third attempt
rc = sqlite3_open("C:\\SQLite\\Local\\test.db3", &db);
if (sqlite3_create_function(db, "MyFunc", 1, SQLITE_UTF8, db, &MyFunc,
NULL,NULL) != 0)
{
cout << "Problem with MyFunc" << endl;
}
sqlite3_exec(db, "UPDATE PARENT2 SET COLUMN3 = 3.3", NULL, NULL, NULL);
sqlite3_close(db);
}
MyFunc is called from the trigger on PARENT2 table by executing
select MyFunc("INSERT INTO TEST_TABLE VALUES ('TEST')") from PARENT2
On the first attempt, the command is successfully executed, the value in the
Column3 is set to 1.1 and the trigger fires, inserting a row in the
TEST_TABLE.
Then, I close the connection, reopen it without creating the function and
repeat the test. The value in the Column3 is not changed and the trigger
does not fire with the error message: "MyFunc does not exist".
Then, I repeat the test by reopening the connection (actually reaquiring the
handle to the .db3 file) to the database, recreating the function and
reexecuting the command. The value in the Column3 is set to 3.3 and the
trigger fires, inserting another row in the TEST_TABLE.
From this test, I can just deduce that I have to define the user-defined
function each time I open any connection, which does not look like a good
solution.
Please, correct me if I am wrong, because this is causing me a lot of
headaches :)
Vladimir Stokic
Scott Hess wrote:
Triggers won't help, either, because triggers run in the sqlite3
handle which, um, triggers. So if app A makes a change, the trigger
runs in app A, but not in app B.
You could have a custom function which the trigger invokes to send an
IPC over to app B.
-scott
