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

[Jorge M A]

Why is it not possible to run a query against SQLite that contains functions like row_number() or rank() ?

e.g.

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

Error in Log:

Spoiler: Log Error

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]

Try taking the space out between ROW_NUMBER and ()

 

[LucaMs]

From Stackoverflow:
"SQLite supports window functions since version 3.25".

version 3.25 date: 2018/9/15

https://bitbucket.org/xerial/sqlite-jdbc/downloads/

 

[LucaMs]

Downloaded the last version, used:

#AdditionalJar: sqlite-jdbc-3.27.2.1.jar

and Row_Number works.

 

[Jorge M A]

#AdditionalJar: sqlite-jdbc-3.27.2.1.jar

It's working!

Thank You Teacher!