Android Code Snippet How To Use SQRT and POWER in a SQLite Query

Name: Using SQRT and POWER functions in SQLite:

Description:

Since SQRT and POWER are not functions directly supported by SQLite, my workaround was to extract the fields in questions from a query, create a variable to store the sqrt of one field (POPULATION) and another variable to store the power for the next field (ID). I create a list to load it with the query columns and calculated fields. There are no external extensions used. It is all through SQLite.The result set is printed to the logs. Also all returned rows populate a list, which in turn is saved to a text file using textwriter in the device’s database folder. The text file can be opened on the device or opened on a PC using Excel, Notepad ++ or any other program that is capable of handling a text file. @imbault may have a different code to arrive at using these functions. If his procedure is different or more efficient, I welcome his input. The more variety of methods become available, the better.

1. I create a table with the following schema:CREATE TABLE IF NOT EXISTS tblOriginal (ID INTEGER PRIMARY KEY,COUNTRY TEXT, POPULATION TEXT)
2. I insert 6 records in the table for the sake of the demonstration and use the following code to calculate the SQRT of the population and the POWER 3 of the ID.
3. Code to generate the proper query and result set:

Dim MyTextwriter As TextWriter
  MyTextwriter.Initialize(File.OpenOutput(DBFilePath,"sqrt_power.txt",False))
  Dim MyList As List
  MyList.Initialize
  MyList.Add("COUNTRY, POPULATION, SQRT_POP, ID, POWER_ID")
  Log("COUNTRY, POPULATION, SQRT_POP, ID, POWER_ID")

  'Query table:
  txt="SELECT COUNTRY, POPULATION, ID FROM tblOriginal"
  Cursor1=SQL1.ExecQuery(txt)

  For i=0 To Cursor1.RowCount-1
     Cursor1.Position=i

     'Calculate SQRT and POWER:
     Dim POWER_ID As Double = Power(Cursor1.GetInt("ID"),3)
     Dim SQRT_POP As Int = Round(Sqrt(Cursor1.GetString("POPULATION")))

     'Display result in logs:
     Log(Cursor1.GetString("COUNTRY") & "," & Cursor1.GetString("POPULATION") & _
     "," & SQRT_POP & "," & Cursor1.GetInt("ID") & "," & POWER_ID)
    
     'Populate a list:
     MyList.Add( (Cursor1.GetString("COUNTRY") & "," & Cursor1.GetString("POPULATION") & _
     "," & SQRT_POP & "," & Cursor1.GetInt("ID") & "," & POWER_ID) )
  Next

  'Save result set to a text file:
  MyTextwriter.WriteList(MyList)
  MyTextwriter.Close

Tags: SQLite, Sqrt, Power, Textwriter

Dependency: SQL lib

Data printed in the logs:
COUNTRY, POPULATION, SQRT_POP, ID, POWER_ID
China,1330044000,36470,1,1
India,1173108018,34251,4,64
USA,310232863,17613,6,216
BELGIUM,10403000,3225,7,343
BOLIVIA,9947412,3154,8,512
IRAQ,29671605,5447,9,729

As shown in Excel: