Android Question Create View in SQLite?

[kohjb]

I am hoping that the answer is that Views can be created in SQLite in B4A, but I suspect it can't. Can someone help me confirm?

When I use SQLite Database Browser, I am able to CREATE VIEW Summary AS SELECT...etc. to succesfully create a View that can be stored in the database. However, I don't seem to be able to do so from within b4a.

If the answer is indeed that it can't be done in b4a, then what is the best way to create a table (or view) that is a join of several tables and whose fields contain calculations of fields of tables?

Thank you!

 

[rboeck]

Because you can use all sqlite statements, why should it be impossible to create views. B4A only creates the string, which is transfered to sqlite. I use currently views in B4a, but i have designed with an external tool. It not a question of ability, but of comfort.
I know, there are same kind of problematic joins in sqlite, but currently i have no source for this problem. In your situation i would try my databasedesign first on an desktop computer and then transfer the running logic and database(s) to the mobile device.

 

[Mahares]

Here some code that can get you started. It works. Suppose you have a table: tblChemicals with the following columns: Code INTEGER Chemical TEXT Price REAL

'Below code to create the view:
txt="CREATE VIEW vChemicals AS SELECT Code, Chemical, (Price* 100) Cost " _
& "FROM tblChemicals ORDER BY Cost Desc"
SQL1.ExecNonQuery(txt)

'Below code if you want to delete the view
txt="DROP VIEW if exists vChemicals"
SQL1.ExecNonQuery(txt)

 

[kohjb]

Thanks so much for the replies! It helped me track down what I was doing wrong.

Basically I didn't realise that I had to position the "cursor" that is returned from a ExecQuery, so because your replies indicated that Views were supported, I wrote a simple test program to demonstrate that it indeed works!

Sub DoIt
    'Initialize the file, copy from Assets if it doesn't exist
    SQL1.Initialize(File.DirDefaultExternal, "SQLite Views.db", True)
   
    'Erase table1 if it exists, and recreate it to have 2 columns
    SQL1.ExecNonQuery("DROP TABLE IF EXISTS table1")
    SQL1.ExecNonQuery("CREATE TABLE table1 (col1 INTEGER, col2 INTEGER)")
   
    'Fill table1 with simple numbers (3 rows)
    SQL1.ExecNonQuery("INSERT INTO table1 VALUES (2, 3)")
    SQL1.ExecNonQuery("INSERT INTO table1 VALUES (4, 5)")
    SQL1.ExecNonQuery("INSERT INTO table1 VALUES (6, 7)")
   
    'Create a View that has the two columns, and their product in a 3rd column
    SQL1.ExecNonQuery("CREATE VIEW view1 AS SELECT col1, col2, (col1 * col2) col3 FROM table1")
   
    'Navigate the View and extract the data
    Dim c As Cursor
    c = SQL1.ExecQuery("SELECT * FROM view1")
   
    Log("Rows = " & c.RowCount & " and Columns = " & c.ColumnCount)
    For x = 0 To c.ColumnCount-1
        Log("Column " & x & " is : " & c.GetColumnName(x))
    Next
    For x = 0 To c.RowCount-1
        c.Position = x        'Position the Cursor to Row x
        For y = 0 To c.ColumnCount-1
            Log("Row " & x & " Col " & y & " is " & c.GetInt2(y))
        Next
    Next
End Sub