Android Question Query in SQLite

[Sergey_New]

Can you tell me how to get the result of a query created in the SQLite file itself?

 

[Sergey_New]

I would strongly suggest you improve your knowledge of SQLite fundamentals

It's clear.
In B4A, is it possible to execute a query that includes the SQLite EXISTS operator?

 

[RB Smissaert]

It's clear.
In B4A, is it possible to execute a query that includes the SQLite EXISTS operator?

Yes.

RBS

 

[Sergey_New]

Yes.

Thank you!

 

[aeric]

If you mean stored procedure, then use "call" keyword.

Dim rs As ResultSet = SQL1.ExecQuery2("call select_animal(?)", Array As String("cat"))

Edit: SQLite doesn't support Stored Procedures or "embeded" Queries. You may want to create a table to store the SQL commands and call them by using normal SELECT.

 

[Sergey_New]

SQLite doesn't support Stored Procedures or "embeded" Queries.

I didn't know that.
Too bad it doesn't support.

 

[aeric]

You may want to create a table to store the SQL commands and call them by using normal SELECT.

CREATE TABLE "queries" (
    "name"    TEXT,
    "script"    TEXT
);

INSERT INTO "queries" (name, script) VALUES
('lds', 'SELECT `key` FROM `tags` WHERE `lds` = ''Y''');

Dim my_path As String = File.DirApp
sql1.InitializeSQLite(my_path, "my_tags.db", False)
Dim rs1 As ResultSet = sql1.ExecQuery2("SELECT script FROM queries WHERE name = ?", Array As String("lds"))
Do While rs1.NextRow
    Dim query As String = rs1.GetString("script")
    Dim rs2 As ResultSet = sql1.ExecQuery(query)
    Do While rs2.NextRow
        Log(rs2.GetString("key"))
    Loop
    rs2.Close
Loop
rs1.Close

 

[aeric]

@aeric Sorry to disagree but embedded queries are just what SQLite VIEWs are. They are designed to save you from having to write code for a 'database of database queries' as you have described above.

@Sergey_New See the attached project. I have made a couple of modifications to both the code and the database to demonstrate.

Firstly don't use 'key' as a field name in SQLite because it is a reserved word. I have changed it to 'keyx'

Within the code you will see an object called 'MyDatabaseView'. It is not a TABLE but a VIEW object the detail of which you can only see if you load the database into your SQLite Browser. It actually represents the more complicated query:

SELECT keyx, lds FROM tags WHERE lds = 'Y' ORDER BY keyx DESC

Having defined the query in the database you call it with a simple SELECT command. You can define as many VIEW queries as you like but they remain read-only.

Ya, I forgotten about VIEW.

CREATE VIEW "lds" AS
SELECT `key` FROM `tags` WHERE `lds` = 'Y';

 

[RichardN]

@aeric Sorry to disagree but embedded queries are just what SQLite VIEWs are. They are designed to save you from having to write code for a 'database of database queries' as you have described above.

@Sergey_New See the attached project. I have made a couple of modifications to both the code and the database to demonstrate.

Firstly don't use 'key' as a field name in SQLite because it is a reserved word. I have changed it to 'keyx'

Within the code you will see an object called 'MyDatabaseView'. It is not a TABLE but a VIEW object the detail of which you can only see if you load the database into your SQLite Browser. It actually represents the more complicated query:

SELECT keyx, lds FROM tags WHERE lds = 'Y' ORDER BY keyx DESC

Having defined the query in the database you call it with a simple SELECT command. You can define as many VIEW queries as you like but they remain read-only.

 

[Sergey_New]

See the attached project.

RichardN, thank you!
This is exactly what I wanted to get!

 

[Mahares]

Since this is the Android forum and this thread was exclusivey dealing with B4J, I thought it is appropriate for me to post a complete B4X project code using Views in SQLite. Please note that, when you create a View and you use CREATE VIEW, the view you create is a permanent view.. It stays with the database. However, if you want the view to last only during the db connection, the view will be temporary and is deleted at the end of the session. The code for creating a temporary view is: CREATE TEMP VIEW or CREATE TEMPORARY VIEW:

Sub Class_Globals
    Private Root As B4XView
    Private xui As XUI
    Private SQL As SQL
    Private Const Filename As String = "my_tags.db"
End Sub

Public Sub Initialize
    xui.SetDataFolder("sql example") 'required in B4J
    If File.Exists(xui.DefaultFolder, Filename) = False Then
        File.Copy(File.DirAssets, Filename, xui.DefaultFolder, Filename)
    End If
    #if B4J
    'B4J SQL object can access many types of databases (same as B4A JdbcSQL).
    SQL.InitializeSQLite(xui.DefaultFolder, Filename, True)
    #else
    SQL.Initialize(xui.DefaultFolder, Filename, True)
    #End If
End Sub

Sub CreateView
'    Dim strQuery As String = $"DROP VIEW IF EXISTS MyView"$
'    SQL.ExecNonQuery(strQuery)
    
    Dim strQuery As String = $"CREATE VIEW IF NOT EXISTS MyView AS
    SELECT keyx, lds FROM tags"$
    SQL.ExecNonQuery(strQuery)
End Sub

Private Sub B4XPage_Created (Root1 As B4XView)
    Root = Root1
'    Root.LoadLayout("MainPage")
    CreateView
    DisplayViewData    
End Sub

Sub DisplayViewData
    Dim rs As ResultSet
    rs = SQL.ExecQuery2($"SELECT * FROM MyView WHERE lds = ? ORDER BY keyx COLLATE NOCASE"$, Array As String ("Y"))    
    Do While rs.NextRow
        Log($"${rs.GetString("keyx")} ${TAB} ${rs.GetString("lds")}"$)
    Loop
    rs.Close
End Sub

 

[Sergey_New]

to post a complete B4X project code using Views in SQLite

If possible, please make an example for B4A.

 

[Mahares]

If possible, please make an example for B4A.

Of course, yes. Here is attched a B4X project that takes care of both B4A and B4J. All you have to do is unzip and run.

 

[Sergey_New]

Of course, yes. Here is attched a B4X project

Mahares, thank you!