database - How to find power of a number in SQLite

Question

I want to update the Interest field in my database. My SQL query is like as per below

Update Table_Name set Interest = Principal * Power(( 1 + (rate / 100),year)

This query works fine in MySQL but don't work with SQLite.

The error says that No Power funcation found

Does anyone know how to resolve this problem as I have to do this using query to update more than 3000 records at a time.

Answer 1

SQLite doesn't have a lot of functions available. But the good news is that is easy enough to add your own.

Here's how to do it using the C API (which also works from Objective-C code).

First write a power function:

void sqlite_power(sqlite3_context *context, int argc, sqlite3_value **argv) {
    double num = sqlite3_value_double(argv[0]); // get the first arg to the function
    double exp = sqlite3_value_double(argv[1]); // get the second arg
    double res = pow(num, exp);                 // calculate the result
    sqlite3_result_double(context, res);        // save the result
}

Then you need to register the function:

int res = sqlite3_create_function(dbRef, "POWER", 2, SQLITE_UTF8, NULL, &sqlite_power, NULL, NULL);

The 2 is the number of arguments for the function. dbRef is of course the sqlite3 * database reference.