Android Question Query in SQLite

Sergey_New · Feb 13, 2023

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

RichardN · Feb 14, 2023

@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 =...

DarkoT · Feb 13, 2023

Do you mean - how to get result from SqlLite table? Simple... you need to use sql select and recordset will be send to resultset ...

B4X:

Private Sub Example
    Dim sqlSelect As String = "select field1, field2, field3 from mytable"
    Dim rs As ResultSet = SqlLite.ExecQuery(sqlSelect)
    
    Do While rs.NextRow
        Log(rs.GetString("field1"))
        Log(rs.getstirng("field2"))
    Loop
    rs.Close
End Sub

Maybe I was not really understund the question...

Sergey_New · Feb 13, 2023

DarkoT said:
Maybe I was not really understund the question...

You misunderstood the question.
The query is already in the SQLite database file itself.

DarkoT · Feb 13, 2023

Sergey_New said:
You misunderstood the question.
The query is already in the SQLite database file itself.

I will go with 2 step logic...
First step will return a query as result from some table and you can store it into some string; after this you can execute "clasic" select with query from stored variable...

RichardN · Feb 13, 2023

I believe you are referring to the SQLite VIEW object. A VIEW object is the result of a pre-constructed query in the database that can return a complex recordset of multiple other data objects. It is read-only and is created in a familiar syntax:

CREATE [IF NOT EXISTS] MyView AS SELECT a,b,c FROM MyTable [ORDER BY b,c,a]

The query MyView is saved to the database as one of any number of pre-tested queries ready for later use. To retrieve the dataset MyView....

SELECT * FROM MyView

It is particularly useful If you are working with a dataset that needs to be viewed from many angles later. I use SQLite Expert Pro at database design time and it facilitates much faster debugging of queries. When the query is perfected you save it as a database VIEW with a familiar name thereby keeping SQL complexity out of the B4X IDE workflow.

aeric · Feb 14, 2023

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

B4X:

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

Sergey_New · Feb 14, 2023

Thank you RichardN and aeric!
The task is just that.
The database my_tags.db has a table named tags and a query named lds.

Code:

sql1.InitializeSQLite(my_path, "my_tags.db", False)
Dim rs As ResultSet = sql1.ExecQuery("SELECT * FROM tags")
'    rs = sql1.ExecQuery("call select lds")

I can't get data from a query only.
my_tags.db attached.

aeric · Feb 14, 2023

Sergey_New said:
Thank you RichardN and aeric!
The task is just that.
The database my_tags.db has a table named tags and a query named lds.

Code:

sql1.InitializeSQLite(my_path, "my_tags.db", False) Dim rs As ResultSet = sql1.ExecQuery("SELECT * FROM tags") ' rs = sql1.ExecQuery("call select lds")

I can't get data from a query only.
my_tags.db attached.

Have you solved the problem?

B4X:

sql1.InitializeSQLite(my_path, "my_tags.db", False)

This code is for B4J.

For B4A, you should use:

B4X:

sql1.Initialize(my_path, "my_tags.db", False)

Sergey_New · Feb 14, 2023

aeric said:
Have you solved the problem?

No, I haven't decided.

aeric said:
This code is for B4J.

I'm just testing in B4J

aeric · Feb 14, 2023

There are many possibilities without knowing exactly what you are doing.
It is always a good idea to upload a small project to help you get your answer quickly.

Sergey_New · Feb 14, 2023

aeric said:
It is always a good idea to upload a small project

Database and almost the entire project in post #7.
I am attaching the project B4J.

aeric · Feb 14, 2023

Can you run this?

Sergey_New · Feb 14, 2023

aeric said:
Can you run this?

Launched. Works with the table, with request - is not present. An exception occurs.

aeric · Feb 14, 2023

Sergey_New said:
Launched. Works with the table, with request - is not present. An exception occurs.

I don't understand what you are trying to say, may be due to lost of translation. Can you post what is the error message?

Sergey_New · Feb 14, 2023

The error occurs when:

B4X:

'rs = sql1.ExecQuery("SELECT * FROM tags")
rs = sql1.ExecQuery("SELECT * FROM lds")

Error occurred on line: 22 (Main)
java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (no such table: lds)
at org.sqlite.DB.newSQLException(DB.java:383)
at org.sqlite.DB.newSQLException(DB.java:387)
at org.sqlite.DB.throwex(DB.java:374)
at org.sqlite.NativeDB.prepare(Native Method)
at org.sqlite.DB.prepare(DB.java:123)
at org.sqlite.PrepStmt.<init>(PrepStmt.java:42)
at org.sqlite.Conn.prepareStatement(Conn.java:404)
at org.sqlite.Conn.prepareStatement(Conn.java:399)
at org.sqlite.Conn.prepareStatement(Conn.java:383)
at anywheresoftware.b4j.objects.SQL.ExecQuery2(SQL.java:365)
at anywheresoftware.b4j.objects.SQL.ExecQuery(SQL.java:353)
at b4j.example.main._appstart(main.java:91)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:629)
at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:237)
at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:167)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:111)
at anywheresoftware.b4a.shell.ShellBA.raiseEvent2(ShellBA.java:100)
at anywheresoftware.b4a.BA.raiseEvent(BA.java:98)
at b4j.example.main.start(main.java:38)
at com.sun.javafx.application.LauncherImpl.lambda$launchApplication1$8(LauncherImpl.java:863)
at com.sun.javafx.application.PlatformImpl.lambda$runAndWait$7(PlatformImpl.java:326)
at com.sun.javafx.application.PlatformImpl.lambda$null$5(PlatformImpl.java:295)
at java.security.AccessController.doPrivileged(Native Method)
at com.sun.javafx.application.PlatformImpl.lambda$runLater$6(PlatformImpl.java:294)
at com.sun.glass.ui.InvokeLaterDispatcher$Future.run(InvokeLaterDispatcher.java:95)
at com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
at com.sun.glass.ui.win.WinApplication.lambda$null$4(WinApplication.java:186)
at java.lang.Thread.run(Thread.java:748)

aeric · Feb 14, 2023

Sergey_New said:
The error occurs when:

B4X:

'rs = sql1.ExecQuery("SELECT * FROM tags") rs = sql1.ExecQuery("SELECT * FROM lds")

Error occurred on line: 22 (Main)
java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (no such table: lds)
at org.sqlite.DB.newSQLException(DB.java:383)
at org.sqlite.DB.newSQLException(DB.java:387)
at org.sqlite.DB.throwex(DB.java:374)
at org.sqlite.NativeDB.prepare(Native Method)
at org.sqlite.DB.prepare(DB.java:123)
at org.sqlite.PrepStmt.<init>(PrepStmt.java:42)
at org.sqlite.Conn.prepareStatement(Conn.java:404)
at org.sqlite.Conn.prepareStatement(Conn.java:399)
at org.sqlite.Conn.prepareStatement(Conn.java:383)
at anywheresoftware.b4j.objects.SQL.ExecQuery2(SQL.java:365)
at anywheresoftware.b4j.objects.SQL.ExecQuery(SQL.java:353)
at b4j.example.main._appstart(main.java:91)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:629)
at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:237)
at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:167)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:111)
at anywheresoftware.b4a.shell.ShellBA.raiseEvent2(ShellBA.java:100)
at anywheresoftware.b4a.BA.raiseEvent(BA.java:98)
at b4j.example.main.start(main.java:38)
at com.sun.javafx.application.LauncherImpl.lambda$launchApplication1$8(LauncherImpl.java:863)
at com.sun.javafx.application.PlatformImpl.lambda$runAndWait$7(PlatformImpl.java:326)
at com.sun.javafx.application.PlatformImpl.lambda$null$5(PlatformImpl.java:295)
at java.security.AccessController.doPrivileged(Native Method)
at com.sun.javafx.application.PlatformImpl.lambda$runLater$6(PlatformImpl.java:294)
at com.sun.glass.ui.InvokeLaterDispatcher$Future.run(InvokeLaterDispatcher.java:95)
at com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
at com.sun.glass.ui.win.WinApplication.lambda$null$4(WinApplication.java:186)
at java.lang.Thread.run(Thread.java:748)

Your database file only contains 1 table name tags. lds is a column in table tags.

Do you want to select column lds ?

B4X:

Dim rs As ResultSet = sql1.ExecQuery("SELECT lds FROM tags")

RichardN · Feb 14, 2023

@Sergey_New

In your database 'tags' is a Table, and 'lds' is the name of a field within that table.

The error message: java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (no such table: lds) tells you all you need to know. You are asking SQL to look for an object that is not there.... a table called 'lds' which is a field name not a table.

Try this:

SELECT * FROM tags alternatively,
SELECT key, family, lds FROM tags where you only need certain fields (it keeps the memory use to a minimum).

Executing SQL from within an Android program or even within B4J is a very slow way and painful way to learn SQL. I suggest you try an SQL management program as I suggested at post #5

Sergey_New · Feb 14, 2023

aeric said:
Your database file only contains 1 table name tags.

In my database, in addition to the table, there is a query:

RichardN · Feb 14, 2023

I am not a Navicat user but the program layout seems to suggest that these query view is part of the database. It is not. You are looking at a query builder/wizard that allows you to build and test queries. When you are happy with the result you can cut/paste the query into your B4X code or save it within the database as a new VIEW.

I would strongly suggest you improve your knowledge of SQLite fundamentals before attempting to embed SQL in B4X code.

RB Smissaert · Feb 14, 2023

RichardN said:
@Sergey_New

In your database 'tags' is a Table, and 'lds' is the name of a field within that table.

The error message: java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (no such table: lds) tells you all you need to know. You are asking SQL to look for an object that is not there.... a table called 'lds' which is a field name not a table.

Try this:

SELECT * FROM tags alternatively,
SELECT key, family, lds FROM tags where you only need certain fields (it keeps the memory use to a minimum).

Executing SQL from within an Android program or even within B4J is a very slow way and painful way to learn SQL. I suggest you try an SQL management program as I suggested at post #5

> Executing SQL from within an Android program or even within B4J is a very slow way and painful way to learn SQL

I don't think it is slow and it doesn't have to be painful, but I am sure it can be if the app is not suitable for that purpose.

RBS

Android Question Query in SQLite

Well-Known Member

Active Member

Well-Known Member

Active Member

Well-Known Member

Expert

Well-Known Member

Attachments

Expert

Well-Known Member

Expert

Well-Known Member

Attachments

Expert

Attachments

Well-Known Member

Expert

Well-Known Member

Expert

Well-Known Member

Well-Known Member

Well-Known Member

Well-Known Member

Similar Threads

Privacy & Transparency

Privacy & Transparency