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

[Waldemar Lima]

hello everyone !

I am experiencing this error when updating the table again ...

Waiting for debugger to connect...
Program started.
---------- NorthWind Database (MySQL) ----------

Error occurred on line: 635 (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:156)
    at b4j.example.b4xtable._createtable(b4xtable.java:2035)
    at b4j.example.b4xtable$ResumableSub_SetData.resume(b4xtable.java:473)
    at b4j.example.b4xtable._setdata(b4xtable.java:412)
    at b4j.example.main$ResumableSub_MySQL_Ready.resume(main.java:1272)
    at b4j.example.main._mysql_ready(main.java:854)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    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 sun.reflect.GeneratedMethodAccessor4.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at anywheresoftware.b4a.BA.raiseEvent2(BA.java:91)
    at anywheresoftware.b4a.shell.ShellBA.raiseEvent2(ShellBA.java:98)
    at anywheresoftware.b4a.BA$3.run(BA.java:247)
    at com.sun.javafx.application.PlatformImpl.lambda$null$172(PlatformImpl.java:295)
    at java.security.AccessController.doPrivileged(Native Method)
    at com.sun.javafx.application.PlatformImpl.lambda$runLater$173(PlatformImpl.java:294)
    at com.sun.glass.ui.InvokeLaterDispatcher$Future.run(InvokeLaterDispatcher.java:95)
    at com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
    at com.sun.glass.ui.win.WinApplication.lambda$null$147(WinApplication.java:177)
    at java.lang.Thread.run(Thread.java:748)

b4j code >

Dim ApostasData As List
            ApostasData.Initialize
            '
            
            Dim RS As ResultSet = SQL.ExecQuery($"select a.id, CONCAT(b.equipeCasa,' x ',b.equipeFora) , b.campeonato , CONCAT(b.dataP,' ás ', b.horarioP) , c.nome , a.apostadorNome , a.apostado , a.tipo
            from
            apostas a
            inner join
            cambistas c
            on a.cambistaId = c.id
            inner join
            partidas b
            on a.partidaId = b.id"$)
            'WHERE dataA='"&$Date{DateTime.Now}&"';"$)
            
            'Dim RS As ResultSet = SQL.ExecQuery("SELECT * FROM apostas WHERE dataA='"&DateTime.Date(DateTime.Now)&"';")
            Log(DateTime.Date(DateTime.Now))
                Do While RS.NextRow

                    Dim row(8) As Object
                    row(0) = RS.GetString2(0)
                    row(1) = RS.GetString2(1)
                    row(2) = RS.GetString2(2)
                    row(3) = RS.GetString2(3)
                    row(4) = RS.GetString2(4)
                    row(5) = RS.GetString2(5)
                    row(6) = RS.GetString2(6)
                    row(7) = RS.GetString2(7)
                    ApostasData.Add(row)
              
                Loop
                
                RS.Close
            Log("Chega aqui !")
            B4XTable1.SetData(ApostasData)
            HideLoading
            MenuBar1.Enabled = True

 

[Erel]

Have you added 8 columns?

 

[rraswisak]

java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (near ")": syntax error)

CONCAT function is not supported in SQLite, use || instead, so your sql should look like this:

CONCAT(b.equipeCasa,' x ',b.equipeFora) change to: (b.equipeCasa || ' x ' || b.equipeFora)

 

[Waldemar Lima]

Have you added 8 columns?

how can i clear just items from B4XTable without clear columns ?

 

[Waldemar Lima]

CONCAT function is not supported in SQLite, use || instead, so your sql should look like this:

CONCAT(b.equipeCasa,' x ',b.equipeFora) change to: (b.equipeCasa || ' x ' || b.equipeFora)

changed, thanks

 

[Erel]

Call SetData without calling Clear.

 

[Waldemar Lima]

thanks @Erel