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.

Answer 2

You can also create an SQLite user-defined function from python. Based on the example at docs.python.org: sqlite3.Connection.create_function

Create a python function:

def sqlite_power(x,n):
    return int(x)**n
print(sqlite_power(2,3))
# 8    

Create a SQLite user-defined function based on the python function:

con = sqlite3.connect(":memory:")
con.create_function("power", 2, sqlite_power)

Use it:

cur = con.cursor()
cur.execute("select power(?,?)", (2,3))
print cur.fetchone()[0]
# 8

Answer 3

I was strugginling with this too, but if all you need is powers of 2 (or multiples, etc) there is a simpler way:

Use the shift operator, e.g

SELECT 1 << mytable.value 

SELECT 1 << (table.x + etc..)

Answer 4

https://www.cafe-encounter.net/p3244/installing-and-using-sqlite-extensions-on-macos-and-maybe-windows-linux-too

Step was to build the Math extensions library that some wonderful person named Liam Healy wrote:

Enter following command in terminal :

Step 1) Download/ Open link http://sqlite.org/contrib/download/extension-functions.c?get=25

Step 2) Go to location where extension-functions.c is downloaded. Run command "gcc -fno-common -dynamiclib extension-functions.c -o libsqlitefunctions.dylib". This will create file libsqlitefunctions.dylib at same place then you can use that in your ios application from xcode.

Now in your cocoa app you can add:

“SELECT load_extension(’libsqlitefunctions.dylib’);”

and then you have access to all kinds of glorious methods like COS, SQRT, etc! You can use them in your app like this:

//Activate database loading
sqlite3_enable_load_extension(database, 1);
sqlite3_load_extension(database,”libsqlitefunctions.dylib”,0,0);

Answer 5

If you are using SQLite NuGet package in a .NET project, you can write an extension method and bind it at runtime;

[SQLiteFunction("pow", 2, FunctionType.Scalar)]
public class SQLitePowerExtension : SQLiteFunction
{
    public override object Invoke(object[] args)
    {
        double num = (double)args[0];
        double exp = (double)args[1];

        return Math.Pow(num, exp);
    }
}

And then use it like this;

using (var conn = new SQLiteConnection("Data Source=:memory:"))
{
    conn.Open();
    conn.BindFunction(typeof(SQLitePowerExtension).GetCustomAttribute<SQLiteFunctionAttribute>(), new SQLitePowerExtension());

    var comm = new SQLiteCommand("CREATE TABLE test (num REAL, exp REAL, result REAL)", conn);
    comm.ExecuteNonQuery();

    // Populate with some data - not shown

    comm = new SQLiteCommand($"UPDATE test SET result = pow(num, exp))", conn);
    comm.ExecuteNonQuery();
}

Answer 6

This could be solved with SQL. This works for integer exponents:

Drop Table if Exists args ;
Create Table args as Select 2.5 as Base, 4 as Exponent ;

WITH RECURSIVE pow(exponent, exponent_remainder, base, result) as (
    --FIRST EXPRESSION
    SELECT exponent,exponent -1 , base,base
    FROM args
    
    union all 
    --SECOND EXPRESSION
    select Args.exponent,pow.exponent_remainder -1, pow.base,pow.result * pow.base
    from args
    join pow on args.exponent = pow.exponent
    where pow.exponent_remainder >= 0
)
select pow.result
from pow
where pow.exponent_remainder = 0;

Answer 7

Actually sqlite does have pow/power as a built-in mathematical function, but you need to enable it with DSQLITE_ENABLE_MATH_FUNCTIONS.

From https://www.sqlite.org/lang_mathfunc.html#pow:

The math functions shown below are part of the SQLite amalgamation source file but are only active if the amalgamation is compiled using the -DSQLITE_ENABLE_MATH_FUNCTIONS compile-time option.

Answer 8

SQLite doesn't provide a power function or operator. You'll have to implement it yourself via sqlite3_create_function….