Android Question Query in SQLite

S

Sergey_New

Well-Known Member
Licensed User
Longtime User
Can you tell me how to get the result of a query created in the SQLite file itself?
 
Solution
R
@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 =...
Sort by date Sort by votes
DarkoT

DarkoT

Active Member
Licensed User
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...
 
Upvote 0
DarkoT

DarkoT

Active Member
Licensed User
Sergey_New said:
You misunderstood the question.
The query is already in the SQLite database file itself.
Click to expand...
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...
 
Upvote 0
R

RichardN

Well-Known Member
Licensed User
Longtime User
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.
 
Upvote 0
S

Sergey_New

Well-Known Member
Licensed User
Longtime User
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.
 

Attachments

  • my_tags.zip
    1.4 KB · Views: 133
Last edited:
Upvote 0
aeric

aeric

Expert
Licensed User
Longtime User
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.
Click to expand...
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)
 
Upvote 0
S

Sergey_New

Well-Known Member
Licensed User
Longtime User
aeric said:
It is always a good idea to upload a small project
Click to expand...
Database and almost the entire project in post #7.
I am attaching the project B4J.
 

Attachments

  • example.zip
    2 KB · Views: 134
Upvote 0
S

Sergey_New

Well-Known Member
Licensed User
Longtime User
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)
 
Upvote 0
aeric

aeric

Expert
Licensed User
Longtime User
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)
Click to expand...

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

1676367976059.png


Do you want to select column lds ?
B4X: 
Dim rs As ResultSet = sql1.ExecQuery("SELECT lds FROM tags")
 
Upvote 0
R

RichardN

Well-Known Member
Licensed User
Longtime User
@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
 
Upvote 0
R

RichardN

Well-Known Member
Licensed User
Longtime User
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.
 
Upvote 0
R

RB Smissaert

Well-Known Member
Licensed User
Longtime User
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
Click to expand...
> 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
 
Upvote 0
You must log in or register to reply here.

Similar Threads

D
  • Locked
  • Question
Android Question MSSQL Query to MSSQL DB
Replies
10
Views
2K
Erel
Erel
K
  • Question
Android Question MySQL to sqlite
Replies
2
Views
1K
kdp
K
V
  • Question
Android Question Database Query
Replies
10
Views
764
DonManfred
DonManfred
Magma
  • Question
Android Question [SOLVED] MSMariaDB/MSMySQL query/bind to CustomView
Replies
4
Views
2K
Magma
Magma
SNOUHyhQs2
  • Question
Android Question How to Extend SQL library?
Replies
1
Views
669
DonManfred
DonManfred
Top