B4J Question jRDC2 Server - Rejects some valid select queries.

rgarnett1955 · Jan 29, 2025

Hi

I am using jRDC2 as my server for an sqlite db.

I composed a query:

SQL:

sql.getDailyData5min= 
SELECT 
   max(UTC_DataTimeStamp)         OVER (ORDER BY UTC_DataTimeStamp ROWS BETWEEN 5 PRECEDING AND CURRENT ROW) AS UTC_DataTimeStampfir, 
   max(InsideTempDegC)             OVER (ORDER BY UTC_DataTimeStamp ROWS BETWEEN 5 PRECEDING AND CURRENT ROW) AS InsideTempDegCfir, 
   max(OutsideTemperatureDegC)     OVER (ORDER BY UTC_DataTimeStamp ROWS BETWEEN 5 PRECEDING AND CURRENT ROW) AS OutsideTempDegCfir, 
    avg(BatteryVolts)             OVER (ORDER BY UTC_DataTimeStamp ROWS BETWEEN 5 PRECEDING AND CURRENT ROW) AS BatteryVoltsfir 
 FROM  tblProcessAnalog 
 ORDER BY UTC_DataTimeStamp DESC 
 LIMIT 288;

Basically the query runs a moving average over the data. Because the query doesn't work I had to bring over all the rows and do the moving average on the Android. Not very efficient.

The error I get is:

(SQLException) java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (near "(": syntax error)

The query runs fine using using an API connection, but fails when submitted to jRDC. All my simpler queries are running fine with jRDC2.

Does anyone know why this is and if it can be worked around?

Who are the developers of jRDC and do they take questions?

Regards
Rob

Erel · Jan 29, 2025

1. These are not valid SQLite queries.
2. jRDC2 only passes the queries to the configured SQL engine.

aeric · Jan 29, 2025

rgarnett1955 said:
(SQLException) java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (near "(": syntax error)

Have you tried with a newer version of SQLite library?

I encountered similar problem before

Update sqlite-jdbc-3.7.2.jar dependency in B4XTable

sqlite-jdbc-3.7.2.jar is the default SQLite JDBC library comes with B4X IDEs installation but it is quite old. The good thing about this version is it is very small (3.05MB only). The bad thing is, it is a dependency for B4XTable for B4J. Recently, I tried to execute a SQLite query in B4J...

www.b4x.com

MicroDrie · Jan 29, 2025

I use the following SQLite query is:

SELECT * FROM your_table_name WHERE id < ? LIMIT 5;

and the ? is the id from where the previous 5 records must be showed.

rgarnett1955 · Feb 3, 2025

Erel said:
1. These are not valid SQLite queries.
2. jRDC2 only passes the queries to the configured SQL engine.

I can run these queries in sqLite studio, sqlite Browser and sqLite Expert. They return the correct values. So how can they be invalid? The OVER keyword is specified in sqLite query language and represents a "window" function. See:

sqLite Window Functions

I use them a lot for data processing. They are really useful and surprisingly fast.

I got onto Gi Hub and raised the issue with them :

sqLite Window Query

They advised the following:

Github Unit Test:

I tried your query with your DB in a unit test and it works fine. You must have some problem with your iBatis configuration.

@Test
  public void test() throws SQLException {
    Connection c = DriverManager.getConnection("jdbc:sqlite:/Downloads/Greenhouse_db/Greenhouse_db.sqlite3");
    Statement s = c.createStatement();

    ResultSet rs = s.executeQuery("SELECT \n" +
        "    max(UTC_DataTimeStamp) OVER (ORDER BY UTC_DataTimeStamp ROWS BETWEEN 5 PRECEDING AND CURRENT ROW) AS UTC_DataTimeStampfir,\n" +
        "    max(InsideTempDegC) OVER (ORDER BY UTC_DataTimeStamp ROWS BETWEEN 5 PRECEDING AND CURRENT ROW) AS InsideTempDegCfir, \n" +
        "    max(OutsideTemperatureDegC) OVER (ORDER BY UTC_DataTimeStamp ROWS BETWEEN 5 PRECEDING AND CURRENT ROW) AS OutsideTempDegCfir, \n" +
        "    avg(BatteryVolts) OVER (ORDER BY UTC_DataTimeStamp ROWS BETWEEN 5 PRECEDING AND CURRENT ROW) AS BatteryVoltsfir\n" +
        "FROM tblProcessAnalog\n" +
        "ORDER BY UTC_DataTimeStamp DESC LIMIT 288;");

    while (rs.next()) {
      System.out.println("---");
      System.out.println(rs.getString(1));
      System.out.println(rs.getString(2));
      System.out.println(rs.getString(3));
      System.out.println(rs.getString(4));
    }

'Sample output
---
1738117034
28.8565120697021
25.5016574859619
13.1748598416646
---
1738116976
28.8565120697021
25.5016574859619
13.178431669871
---
1738116919
28.8565120697021
25.5016574859619
13.1957268714905

...
  }

Chris2 · Feb 4, 2025

rgarnett1955 said:
but fails when submitted to jRDC

Can you explain what you mean by 'submitted to jRDC'?
My understanding is that jRDC2 picks up queries from a file on the server (config.properties in the example), are you passing queries to the server some other way?

It might be a daft question, but the query as written in the first post doesn't look like valid syntax for the jRDC2 config.properties file.

Ignore this. I see from your github post that you are indeed using the config.properties file.

teddybear · Feb 4, 2025

rgarnett1955 said:
(SQLException) java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (near "(": syntax error)
The query runs fine using using an API connection, but fails when submitted to jRDC. All my simpler queries are running fine with jRDC2.

1.As Erel said jRDC2 only passes the queries to the configured SQL engine.
2.aeric has provided the solution to you, upgrade jdbc to sqlite-jdbc-3.39.2.0.jar, it works

rgarnett1955 · Feb 5, 2025

Hi,

Thanks for that I will give it a try. I was using sqlite-jdbc-3.7.2.

teddybear said:
1.As Erel said jRDC2 only passes the queries to the configured SQL engine.
2.aeric has provided the solution to you, upgrade jdbc to sqlite-jdbc-3.39.2.0.jar, it works

In fact I have changed to mySQL as I have had occasional problems with windows locking the sqlite db files; .sqlite3 and .wal which prevents inserts.

I think it happens if the pc is rebooted without shutting down all of the apps using the database.

The query works fine with mySQL albeit with the changes neccessary for the mySQL dialect.

I will post it in my creations as it might be useful for others.

I didn't quite understand what is meant by the configuration of the sqlite engine. I know sqlite has a lot of options to makes it more compact for small computers,
does it have options that prevent more complex queries from running?

Chris2 · Feb 5, 2025

rgarnett1955 said:
I was using sqlite-jdbc-3.7.2

At the bottom of the page on sqLite Window Functions:

Window function support was first added to SQLite with release version 3.25.0 (2018-09-15). The SQLite developers used the PostgreSQL window function documentation as their primary reference for how window functions ought to behave. Many test cases have been run against PostgreSQL to ensure that window functions operate the same way in both SQLite and PostgreSQL.

In SQLite version 3.28.0 (2019-04-16), windows function support was extended to include the EXCLUDE clause, GROUPS frame types, window chaining, and support for "<expr> PRECEDING" and "<expr> FOLLOWING" boundaries in RANGE frames.

B4J Question jRDC2 Server - Rejects some valid select queries.

rgarnett1955

Active Member

Erel

B4X founder

aeric

Expert

Update sqlite-jdbc-3.7.2.jar dependency in B4XTable

MicroDrie

Well-Known Member

rgarnett1955

Active Member

Chris2

Active Member

teddybear

Well-Known Member

rgarnett1955

Active Member

Chris2

Active Member

Similar Threads

B4J Question jRDC2 Server - Rejects some valid select queries.

Active Member

B4X founder

Expert

Well-Known Member

Active Member

Active Member

Well-Known Member

​

Active Member

​

Active Member

Similar Threads

Privacy & Transparency

Privacy & Transparency