B4J Question [Solved] B4XTable java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (near ")"

[DaleA]

I saw this previous question but the resolution shown isn't relevant to my issue. java.sql.SQLException...

I have a Public B4XTable (named "theList") set up with the following code:

Sub doListInit
    theList.AddColumn("Name", theList.COLUMN_TYPE_TEXT)
    theList.AddColumn("Web Site", theList.COLUMN_TYPE_TEXT)
    theList.AddColumn("UserID", theList.COLUMN_TYPE_TEXT)
    theList.AddColumn("Password", theList.COLUMN_TYPE_TEXT)
'    theList.RowHeight = 26  ' default = 40
End Sub

and it displays correctly.

Later when I try to populate the table from the SQLite database using this code:

Sub UpdateTheList
    Dim Data As List
    Data.Initialize
    Dim rs As ResultSet = theDatabase.theDB.ExecQuery("SELECT pk, SiteName, URL, UserID, Password FROM Main")

    Do While rs.NextRow
        Dim row(4) As Object
        row(0) = rs.GetString("SiteName")
        row(1) = rs.GetString("URL")
        row(2) = rs.GetString("UserID")
        row(3) = rs.GetString("Password")
    
        Data.Add(row)
    Loop
    
    rs.Close
    
    theList.SetData(Data)
End Sub

I get the following error:

Waiting for debugger to connect...
Program started.
DB Path: D:\Documents\B4J Projects\Passwds\Files
DB File: Passwds.db
Error occurred on line: 705 (B4XTable)
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.Stmt.execute(Stmt.java:113)
    at anywheresoftware.b4j.objects.SQL.ExecNonQuery(SQL.java:161)
    at b4j.example.b4xtable._createtable(b4xtable.java:2252)
    at b4j.example.b4xtable$ResumableSub_SetData.resume(b4xtable.java:287)
    at b4j.example.b4xtable._setdata(b4xtable.java:220)
    at b4j.example.main._updatethelist(main.java:287)
    at b4j.example.main._openupshop(main.java:167)
    at b4j.example.main._appstart(main.java:111)
    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:632)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:237)
    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:91)
    at anywheresoftware.b4a.shell.ShellBA.raiseEvent2(ShellBA.java:98)
    at anywheresoftware.b4a.BA.raiseEvent(BA.java:78)
    at b4j.example.main.start(main.java:38)
    at javafx.graphics/com.sun.javafx.application.LauncherImpl.lambda$launchApplication1$9(LauncherImpl.java:846)
    at javafx.graphics/com.sun.javafx.application.PlatformImpl.lambda$runAndWait$12(PlatformImpl.java:455)
    at javafx.graphics/com.sun.javafx.application.PlatformImpl.lambda$runLater$10(PlatformImpl.java:428)
    at java.base/java.security.AccessController.doPrivileged(Native Method)
    at javafx.graphics/com.sun.javafx.application.PlatformImpl.lambda$runLater$11(PlatformImpl.java:427)
    at javafx.graphics/com.sun.glass.ui.InvokeLaterDispatcher$Future.run(InvokeLaterDispatcher.java:96)
    at javafx.graphics/com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
    at javafx.graphics/com.sun.glass.ui.win.WinApplication.lambda$runLoop$3(WinApplication.java:174)
    at java.base/java.lang.Thread.run(Thread.java:834)

Note that putting a breakpoint on the SetData line and inspecting the ResultSet and the Data list reveals that they both have the correct data. This is all in the Main module while the database itself, named 'theDB', is declared and created in a separate code module named 'theDatabase'.

I've been a programmer since the mid '70s but only trying B4X for about a week or so. Still learning the basics.

 

[Erel]

The code looks correct. The problem is somewhere else. Are you sure that you are not adding the columns multiple times?

 

[DaleA]

Solved! It took rewriting the entire collection of initialization subs but I finally determined that I had two statements addressing the table data that were out of order. Both are necessary but the way I had it originally, the second stepped on the first and, apparently, caused the situation that later made the updating of the B4XTable fail.

Like I said, I'm still learning the basics.