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

MichalK73

Well-Known Member
Licensed User
Longtime User
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:
B4X: 
'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:
B4X: 
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?
 
Sort by date Sort by votes
B

Brian Dean

Well-Known Member
Licensed User
Longtime User
I am not an SQL expert by any means and I don't generally use smart strings, but two things look odd to me in your query statement. In the last line ...

B4X: 
      FROM program where stop >"${czas}") subquery WHERE row_num <= 5;"$

... surely the "$ before {czas} terminates the smart string. And I am surprised by the semi-colon at the end - not normal in SQL. Hope that this is sensible.
 
Upvote 0

MichalK73

Well-Known Member
Licensed User
Longtime User
deleting the semicolon does not bring anything new , still the error
 
Upvote 0

josejad

Expert
Licensed User
Longtime User
Upvote 0

MichalK73

Well-Known Member
Licensed User
Longtime User
Are you talking about such a record ?
B4X: 
    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"$
It also gives the same error
 
Upvote 0

josejad

Expert
Licensed User
Longtime User
What do you get if you Log(query)? Try to copy and paste in SQLiteStudio
 
Upvote 0

josejad

Expert
Licensed User
Longtime User
Sorry, I meant the ", but you did it too
 
Upvote 0

MichalK73

Well-Known Member
Licensed User
Longtime User
B4X: 
    Dim epg0 As List= DB_EPG.SQLSelect("SELECT * FROM (Select program.channel,    program.title,    program.stop,    program.start,    ROW_NUMBER() OVER (PARTITION BY program.channel ORDER BY program.channel) As row_num    FROM program    WHERE program.stop > ?    )    subquery    WHERE row_num <= 5", Array as String(czas))

Error:
B4X: 
2023-09-05 13:36:08 -> (SQLException) java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (near "(": syntax error)

 
Upvote 0

OliverA

Expert
Licensed User
Longtime User
Maybe something wrong with your DB connection? Something strange contained within the table? Running your query against an empty table produces no errors.
 

Attachments

  • sqlitetest.zip
    1,012 bytes · Views: 150
Upvote 0

MichalK73

Well-Known Member
Licensed User
Longtime User
OliverA said:
Maybe something wrong with your DB connection? Something strange contained within the table? Running your query against an empty table produces no errors.
Click to expand...
database connection is standard as always. other queries on this database and table execute fine. just this one with a more complex query displays an error. I can get the result by multiple queries for each column value but I wanted to save time on generating the result.
 
Upvote 0

OliverA

Expert
Licensed User
Longtime User
Can you create a sample program that recreates the issue? With data that is ok for us to play with? As of now, on an empty table, there is no issue (see my sample application attached).
 
Upvote 0
M

Mahares

Expert
Licensed User
Longtime User
MichalK73 said:
However, this query passes in SQLiteStudio editor without any problem.
Click to expand...
Here is a complete working project that seem to work for me unless you have another objective. that I am not aware of. I used data similar to yours in the insert. I used 3.40 jar as I do not have 3.43 jar.
B4X: 
'Non-UI application (console / server application)
#Region Project Attributes 
    #CommandLineArgs:
    #MergeLibraries: True
'    #AdditionalJar: sqlite-jdbc-3.43.0.0.jar
    #AdditionalJar: sqlite-jdbc-3.40.0.0.jar
#End Region

Sub Process_Globals
    Dim SQL As SQL
End Sub

Sub AppStart (Args() As String)
    SQL.InitializeSQLite(File.DirApp, "mydb", True)
'    Dim czas As String = "20230905115500 +0200"  'works
    Dim czas As String = "20230905125000 +0200"   'works
'    Dim czas As String = "20230905120000 +0200"   'works
    
    Dim strQuery As String
    strQuery="DROP TABLE IF EXISTS program"   'You don't have to drop the table every time. This is just for testing
    SQL.ExecNonQuery(strQuery)
    
    strQuery = $"
    CREATE TABLE IF NOT EXISTS program (
    ID INTEGER PRIMARY KEY,
    channel TEXT,
    title TEXT,
    stop TEXT,
    start TEXT)"$
    SQL.ExecNonQuery(strQuery)
    
    InsertRecords

    strQuery = $"SELECT * FROM (SELECT channel, title, stop, start,
    ROW_NUMBER() OVER (PARTITION BY channel ORDER BY channel) AS row_num
    FROM program where stop > ?) subquery WHERE row_num <= ?"$
    Dim rs As ResultSet = SQL.ExecQuery2(strQuery, Array As Object(czas, 5))
    Do While rs.NextRow
        Log($"${rs.GetString("stop")}  ${rs.GetString("start")}   ${rs.getlong("row_num")}"$)
    Loop
    rs.close
End Sub

Sub InsertRecords
    Dim strQuery As String="INSERT OR IGNORE INTO program VALUES(?, ?, ?, ?, ?)"
    
    SQL.ExecNonQuery2(strQuery, Array As Object (Null, "TravelXP eu", "Bliss", "20230905120000 +0200", "20230905113000 +0200"))
    SQL.ExecNonQuery2(strQuery, Array As Object (Null, "TravelXP eu", "Best", "20230905124500 +0200", "20230905120000 +0200"))    
    SQL.ExecNonQuery2(strQuery, Array As Object (Null, "TravelXP eu", "Frodicted", "20230905124500 +0200", "20230905120000 +0200"))
    SQL.ExecNonQuery2(strQuery, Array As Object (Null, "1+1 International", "Svit", "20230905140000 +0200", "20230905105000 +0200"))
    SQL.ExecNonQuery2(strQuery, Array As Object (Null, "1+1 International", "Odnogo", "20230905143500 +0200", "20230905141000 +0200"))
    
    strQuery = $"SELECT Count(*) AS cnt FROM program"$
    Dim rs As ResultSet = SQL.ExecQuery(strQuery)
    Log($"Records inserted in table: ${rs.GetLong("cnt"}"$))
    rs.Close
End Sub
 

Attachments

  • MichalKProjectByMahares090523.zip
    1.4 KB · Views: 129
Upvote 0

MichalK73

Well-Known Member
Licensed User
Longtime User
Hello.

I have looked at your project. I have added my EPG database. I have added my code because I am relying on the module for the database. I did as I have in the program. Your code works, my code works correctly. It gives the results I want. Now I completely don't know what could be the cause in my pragram. A question comes to my mind because in the program I use at once 2 connections to 2 different SQLite from separate modules, can this have any effect?

Enclosed I send your package modified with my code, module and database for 5 days ahead from now.
All other simple queries on the database are without error. Only this one does not want to go through and a simple project with the database itself works.

Test projekt with DB

B4X: 
http://localhost/api/plusztv
2023-09-06 08:07:41  Komand: plusztv
Error occurred on line: 49 (DB_EPG)
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:365)
    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:629)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:234)
    at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:167)
    at jdk.internal.reflect.GeneratedMethodAccessor1.invoke(Unknown Source)
    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:111)
    at anywheresoftware.b4a.shell.ShellBA.raiseEvent2(ShellBA.java:100)
    at anywheresoftware.b4a.debug.Debug.delegate(Debug.java:64)
    at com.misiek.api._plusztv(api.java:1079)
    at com.misiek.api._handle(api.java:698)
    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:629)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:237)
    at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:167)
    at jdk.internal.reflect.GeneratedMethodAccessor1.invoke(Unknown Source)
    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:111)
    at anywheresoftware.b4a.shell.ShellBA.raiseEvent2(ShellBA.java:100)
    at anywheresoftware.b4a.BA.raiseEvent(BA.java:98)
    at anywheresoftware.b4j.object.JServlet$Handle.run(JServlet.java:146)
    at anywheresoftware.b4j.object.JServlet.Handle(JServlet.java:126)
    at anywheresoftware.b4j.object.JServlet.doGet(JServlet.java:100)
    at jakarta.servlet.http.HttpServlet.service(HttpServlet.java:500)
    at jakarta.servlet.http.HttpServlet.service(HttpServlet.java:587)
    at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:764)
    at org.eclipse.jetty.servlet.ServletHandler$ChainEnd.doFilter(ServletHandler.java:1630)
    at anywheresoftware.b4j.object.JServlet$Handle.run(JServlet.java:151)
    at anywheresoftware.b4a.keywords.SimpleMessageLoop.runMessageLoop(SimpleMessageLoop.java:47)
    at anywheresoftware.b4a.StandardBA.startMessageLoop(StandardBA.java:43)
    at anywheresoftware.b4a.shell.ShellBA.startMessageLoop(ShellBA.java:121)
    at anywheresoftware.b4a.keywords.Common.StartMessageLoop(Common.java:180)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:309)
    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:111)
    at anywheresoftware.b4a.shell.ShellBA.raiseEvent2(ShellBA.java:100)
    at anywheresoftware.b4a.BA.raiseEvent(BA.java:98)
    at anywheresoftware.b4j.object.JServlet.createInstance(JServlet.java:81)
    at anywheresoftware.b4j.object.BackgroundWorkersManager$1.run(BackgroundWorkersManager.java:40)
    at anywheresoftware.b4a.keywords.SimpleMessageLoop.runMessageLoop(SimpleMessageLoop.java:47)
    at anywheresoftware.b4a.StandardBA.startMessageLoop(StandardBA.java:43)
    at anywheresoftware.b4a.shell.ShellBA.startMessageLoop(ShellBA.java:121)
    at anywheresoftware.b4a.keywords.Common.StartMessageLoop(Common.java:180)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:309)
    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:111)
    at anywheresoftware.b4a.shell.ShellBA.raiseEvent2(ShellBA.java:100)
    at anywheresoftware.b4a.BA.raiseEvent(BA.java:98)
    at com.misiek.main.main(main.java:29)
2023-09-06 08:07:44 -> (Exception) java.lang.Exception:  java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (near "(": syntax error)
java.lang.InterruptedException
    at java.base/java.util.concurrent.locks.AbstractQueuedSynchronizer.doAcquireSharedInterruptibly(AbstractQueuedSynchronizer.java:1040)
    at java.base/java.util.concurrent.locks.AbstractQueuedSynchronizer.acquireSharedInterruptibly(AbstractQueuedSynchronizer.java:1345)
    at java.base/java.util.concurrent.CountDownLatch.await(CountDownLatch.java:232)
    at anywheresoftware.b4j.object.JServlet.Handle(JServlet.java:119)
    at anywheresoftware.b4j.object.JServlet.doFilter(JServlet.java:88)
    at org.eclipse.jetty.servlet.FilterHolder.doFilter(FilterHolder.java:202)
    at org.eclipse.jetty.servlet.ServletHandler$Chain.doFilter(ServletHandler.java:1600)
    at org.eclipse.jetty.servlets.DoSFilter.doFilterChain(DoSFilter.java:483)
    at org.eclipse.jetty.servlets.DoSFilter.doFilter(DoSFilter.java:332)
    at org.eclipse.jetty.servlets.DoSFilter.doFilter(DoSFilter.java:297)
    at org.eclipse.jetty.servlet.FilterHolder.doFilter(FilterHolder.java:202)
    at org.eclipse.jetty.servlet.ServletHandler$Chain.doFilter(ServletHandler.java:1600)
    at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:506)
    at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:221)
    at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1571)
    at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:221)
    at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1375)
    at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:176)
    at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:463)
    at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1544)
    at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:174)
    at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1297)
    at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:129)
    at org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.java:141)
    at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122)
    at org.eclipse.jetty.server.Server.handle(Server.java:562)
    at org.eclipse.jetty.server.HttpChannel.lambda$handle$0(HttpChannel.java:505)
    at org.eclipse.jetty.server.HttpChannel.dispatch(HttpChannel.java:762)
    at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:497)
    at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:282)
    at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:319)
    at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:100)
    at org.eclipse.jetty.io.SelectableChannelEndPoint$1.run(SelectableChannelEndPoint.java:53)
    at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:894)
    at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.run(QueuedThreadPool.java:1038)
    at java.base/java.lang.Thread.run(Thread.java:834)
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar Threads

  • Question
B4J Question Issue with SQL errors and xui
Replies
3
Views
572
Erel
K
  • Question
B4J Question Error message, SQL (ucanaccess-5.0.0)
Replies
6
Views
3K
kohle
K
  • Question
B4J Question B4J - ERRORE ) io non lo vedo)
Replies
6
Views
777
Erel
  • Question
B4J Question [B4X Table] java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (near ")"
Replies
6
Views
2K
Waldemar Lima
P
  • Question
B4J Question SQLite error or missing database
Replies
1
Views
1K
EnriqueGonzalez
Menu
Log in
Register
Cookies are required to use this site. You must accept them to continue using the site. Learn more…