B4J Question Unusual SQLite error. Works in editor and not in B4J.

[MichalK73]

Hello.

The query extracts 5 consecutive programs from the specified time for each channel in the database.
I have a SQL query in B4J to SQLite:

'Main.bas
....
'    #AdditionalJar: sqlite-jdbc-3.7.2.jar        
    #AdditionalJar: sqlite-jdbc-3.43.0.0.jar    
...

'Module Code API
query = $"SELECT * FROM (SELECT channel, title, stop, start,
    ROW_NUMBER() OVER (PARTITION BY channel ORDER BY channel) AS row_num
  FROM program where stop >"${czas}") subquery WHERE row_num <= 5;"$
    Dim epg0 As List= DB_EPG.SQLSelect(query,Null)

B4J is giving me an error:

2023-09-05 11:58:09 -> (Exception) java.lang.Exception:  java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (near "(": syntax error)

However, this query passes in SQLiteStudio editor without any problem.

What could be the cause?

 

[josejad]

Tested here your program, and I think is working fine.

I've dowloaded sqlite-jdbc-3.43.0.0.jar from here

Spoiler: Log

Waiting for debugger to connect...
Program started.
init sqlite EPG
Connect SqLite EPG
20230906090000 +0200 20230906093000 +0200 TravelXP.eu Unbound
20230906080000 +0200 20230906100000 +0200 EVENT4 Brak zrodła. EPG Dostarcza Serwis http://kodiwpigulce.pl. Pokaz poranny II
20230906100000 +0200 20230906120000 +0200 EVENT4 Brak zrodła. EPG Dostarcza Serwis http://kodiwpigulce.pl. Pokaz poranny III
20230906120000 +0200 20230906140000 +0200 EVENT4 Brak zrodła. EPG Dostarcza Serwis http://kodiwpigulce.pl. Pokaz popołudniowy I
20230906140000 +0200 20230906160000 +0200 EVENT4 Brak zrodła. EPG Dostarcza Serwis http://kodiwpigulce.pl. Pokaz popołudniowy II
20230906160000 +0200 20230906180000 +0200 EVENT4 Brak zrodła. EPG Dostarcza Serwis http://kodiwpigulce.pl. Pokaz popołudniowy III
20230906090000 +0200 20230906100000 +0200 Eleven Sports 1 LaLiga
20230906100000 +0200 20230906120000 +0200 Eleven Sports 1 Piłka nożna: Liga francuska - mecz: FC Nantes - Olympique Marsylia
20230906120000 +0200 20230906140000 +0200 Eleven Sports 1 Piłka nożna: Liga włoska - mecz: Inter Mediolan - ACF Fiorentina
20230906140000 +0200 20230906160000 +0200 Eleven Sports 1 Piłka nożna: Liga francuska - mecz: AS Monaco - RC Lens
20230906160000 +0200 20230906164000 +0200 Eleven Sports 1 Full Impact Serie A
20230906080000 +0200 20230906100000 +0200 Eleven Sports 2 Piłka nożna: Liga hiszpańska - mecz: Atletico Madryt - Sevilla FC
20230906100000 +0200 20230906120000 +0200 Eleven Sports 2 Piłka nożna: Liga włoska - mecz: SSC Napoli - SS Lazio
20230906120000 +0200 20230906130000 +0200 Eleven Sports 2 LaLiga
20230906130000 +0200 20230906140000 +0200 Eleven Sports 2 Ligue1
20230906140000 +0200 20230906160000 +0200 Eleven Sports 2 Piłka nożna: Liga hiszpańska - mecz: Real Madryt CF - Getafe CF
20230906080000 +0200 20230906100000 +0200 Eleven Sports 3 Piłka nożna: Liga francuska - mecz: OGC Nice - RC Strasbourg
20230906100000 +0200 20230906110000 +0200 Eleven Sports 3 Ligue1
20230906110000 +0200 20230906120000 +0200 Eleven Sports 3 Serie A
20230906120000 +0200 20230906140000 +0200 Eleven Sports 3 Piłka nożna: Liga hiszpańska - mecz: Real Betis Balompie - Rayo Vallecano
20230906140000 +0200 20230906160000 +0200 Eleven Sports 3 Piłka nożna: Liga włoska - mecz: Torino FC - Genoa CFC
20230906080000 +0200 20230906100000 +0200 Eleven Sports 4 Piłka nożna: Liga włoska - mecz: Udinese Calcio - Frosinone Calcio
20230906100000 +0200 20230906120000 +0200 Eleven Sports 4 Piłka nożna: Liga hiszpańska - mecz: CD Alaves - Valencia CF
20230906120000 +0200 20230906140000 +0200 Eleven Sports 4 Piłka nożna: Liga francuska - mecz: Toulouse FC - Clermont Foot 63
20230906140000 +0200 20230906150000 +0200 Eleven Sports 4 LaLiga
20230906150000 +0200 20230906160000 +0200 Eleven Sports 4 LaLiga Hypermotion
20230906090000 +0200 20230906100000 +0200 Eleven Sports 4K LaLiga
20230906100000 +0200 20230906120000 +0200 Eleven Sports 4K Piłka nożna: Liga francuska - mecz: FC Nantes - Olympique Marsylia
20230906120000 +0200 20230906140000 +0200 Eleven Sports 4K Piłka nożna: Liga włoska - mecz: Inter Mediolan - ACF Fiorentina
20230906140000 +0200 20230906160000 +0200 Eleven Sports 4K Piłka nożna: Liga francuska - mecz: AS Monaco - RC Lens
20230906160000 +0200 20230906164000 +0200 Eleven Sports 4K Full Impact Serie A
20230906092000 +0200 20230906093500 +0200 English Club TV HD Let's talk
20230906093500 +0200 20230906094500 +0200 English Club TV HD English in focus
20230906094500 +0200 20230906095000 +0200 English Club TV HD What did they say
20230906095000 +0200 20230906095500 +0200 English Club TV HD What did they say

 

[MichalK73]

I know it works.
However, the same code doesn't work in my program. Although I am sure that the SQL query works in B4J. The fault must lie elsewhere. I'm wondering because the query is a query within a query on a database quite large for SQLite, it may cause more load in my application and eventually cause an error. I was calmly looking for a solution.
Thanks everyone for the pointers. I'm looking for where now

 

[Mahares]

hanks everyone for the pointers

Looking at your project after you modified mine to accomodate your database, a couple of recommendations:
1. You need a PRIMARY KEY col and also an INDEX with a large database like you have.
2. Your col channel has a big mix of lower and upper. Data will be better sorted if you ORDER BY channel COLLATE NOCASE
3. You do not need that lengthy Sub buildingTime . To calculate czas, all you need to do is 2 lines:

DateTime.DateFormat ="yyyyMMddHHmm"
 czas = $"${DateTime.Date(DateTime.Now)}00 +200"$

 

[MichalK73]

Looking at your project after you modified mine to accomodate your database, a couple of recommendations:
1. You need a PRIMARY KEY col and also an INDEX with a large database like you have.
2. Your col channel has a big mix of lower and upper. Data will be better sorted if you ORDER BY channel COLLATE NOCASE
3. You do not need that lengthy Sub buildingTime . To calculate czas, all you need to do is 2 lines:

DateTime.DateFormat ="yyyyMMddHHmm"
 czas = $"${DateTime.Date(DateTime.Now)}00 +200"$

Great. I will take them into consideration. It's so easy with the date

 

[Mahares]

It's so easy with the date

If you are adding the extension +200 to each value, you can do it like this too:

DateTime.DateFormat ="yyyyMMddHHmm00 +200"
 czas = DateTime.Date(DateTime.Now)

 

[MichalK73]

Yes, I forgot to use date and time formatting for my case

 

[MichalK73]

What do you get if you Log(query)? Try to copy and paste in SQLiteStudio

I often write more complicated queries first in SQLiteStudio and when they work I move them to B4X. So moving log(query) from b4x gives the same thing I did early on which is it works.