B4J Question [SOLVED] SQLite Built-in Window Functions?

Jorge M A

Well-Known Member
Licensed User
Why is it not possible to run a query against SQLite that contains functions like row_number() or rank() ?

e.g.
B4X:
Dim RS As ResultSet=Sql1.ExecQuery($"SELECT ROW_NUMBER () OVER ( ORDER BY cid  ) RowNum, ostatus, cid, itemcount FROM orders;"$)

Error in Log:
java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (near "(": syntax error)
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:360)
at anywheresoftware.b4j.objects.SQL.ExecQuery(SQL.java:348)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:566)
at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:632)
at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:234)
at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:167)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:566)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:91)
at anywheresoftware.b4a.shell.ShellBA.raiseEvent2(ShellBA.java:98)
at anywheresoftware.b4a.debug.Debug.delegate(Debug.java:64)
at b4j.example.dbservice._getcustomers(dbservice.java:78)

DBeavear and DB Browser for SQLite do this kind of queries without any problem.

I already tried it with the internal sqlite-jdbc, as well as with the latest version of the driver.

Reference: https://www.sqlite.org/windowfunctions.html#built_in_window_functions
 

OliverA

Expert
Licensed User
Longtime User
Try taking the space out between ROW_NUMBER and ()
 
Upvote 0

LucaMs

Expert
Licensed User
Longtime User
From Stackoverflow:
"SQLite supports window functions since version 3.25".

version 3.25 date: 2018/9/15

https://bitbucket.org/xerial/sqlite-jdbc/downloads/
upload_2019-9-28_9-42-26.png
 
Upvote 0
Top