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.
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:
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.
- I create a table with the following schema:CREATE TABLE IF NOT EXISTS tblOriginal (ID INTEGER PRIMARY KEY,COUNTRY TEXT, POPULATION TEXT)
- 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.
- Code to generate the proper query and result set:
B4X:
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.CloseTags: 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:
Last edited:
