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

MichalK73 · Sep 5, 2023

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?

Brian Dean · Sep 5, 2023

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.

MichalK73 · Sep 5, 2023

Brian Dean said:
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.

deleting the semicolon does not bring anything new , still the error

josejad · Sep 5, 2023

MichalK73 said:
deleting the semicolon does not bring anything new , still the error

Have you try to delete the $ Bryan have said?

MichalK73 · Sep 5, 2023

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

josejad · Sep 5, 2023

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

MichalK73 · Sep 5, 2023

I just provided this in 1 post and it works there

MichalK73 · Sep 5, 2023

José J. Aguilar said:
Have you try to delete the $ Bryan have said?

Can't delete the $ because it's a data substitution tag for the query.

josejad · Sep 5, 2023

Sorry, I meant the ", but you did it too

MichalK73 · Sep 5, 2023

SQLite library version for B4J may have a problem?

MichalK73 · Sep 5, 2023

what do you have in mind?

aeric · Sep 5, 2023

Why don't use parameterized query?

MichalK73 · Sep 5, 2023

aeric said:
Why don't use parameterized query?

In my opinion it doesn't matter but will try

MichalK73 · Sep 5, 2023

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)

OliverA · Sep 5, 2023

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

MichalK73 · Sep 5, 2023

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.

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.

OliverA · Sep 5, 2023

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).

MichalK73 · Sep 5, 2023

sure, no problem. Tomorrow I will try to prepare a version with a database.

Mahares · Sep 6, 2023

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

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

MichalK73 · Sep 6, 2023

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)

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

Well-Known Member

Well-Known Member

Well-Known Member

Expert

Well-Known Member

Expert

Well-Known Member

Well-Known Member

Expert

Well-Known Member

Well-Known Member

Expert

Well-Known Member

Well-Known Member

Expert

Attachments

Well-Known Member

Expert

Well-Known Member

Expert

Attachments

Well-Known Member

Similar Threads