B4J Question jRDC2 Server - Rejects some valid select queries.

rgarnett1955

Active Member
Licensed User
Longtime User
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
 

aeric

Expert
Licensed User
Longtime User
(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
 
Upvote 0

MicroDrie

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

rgarnett1955

Active Member
Licensed User
Longtime User
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

...
  }
 
Last edited:
Upvote 0

Chris2

Active Member
Licensed User
Longtime User
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.
 
Last edited:
Upvote 0

teddybear

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

 
Upvote 0

rgarnett1955

Active Member
Licensed User
Longtime User
Hi,

Thanks for that I will give it a try. I was using sqlite-jdbc-3.7.2.
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?
 
Upvote 0

Chris2

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