B4J Question When using variable with sqlite, error unrecognized token: ":"

[Sifu]

Hi,

Can't figure out why this works:

Dim RS3 As ResultSet = Main.SQL2.ExecQuery("SELECT Fullpath, Filename, Size, Date, Time FROM DblFiles WHERE Fullpath = 'C:\temp\{C}\'")

But this does not, while the variable beholds the same string:

Dim RS3 As ResultSet = Main.SQL2.ExecQuery("SELECT Fullpath, Filename, Size, Date, Time FROM DblFiles WHERE Fullpath = "&FilePath2)

it gives this error:
java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (unrecognized token: ":")

I have tried several possibilities following the B4X SQlite booklet, searching forum and web.
I tried this:

Dim RS3 As ResultSet = Main.SQL2.ExecQuery("SELECT Fullpath, Filename, Size, Date, Time FROM DblFiles WHERE Fullpath = '"& FilePath2 &"'")

same error

Dim RS3 As ResultSet = Main.SQL2.ExecQuery2("SELECT * FROM DblFiles WHERE Fullpath = ? ", Array As String (FilePath2))

then I only get, there is an error, without further hint

What must I do to get the string as variable accepted?
Thanks for any suggestion.

 

[Erel]

Only the last one is correct.
Post the error message and post the output of Log(FilePath2)

 

[Sifu]

Thanks, these where the errors I got, but with the code at the end of this post it seems to work without error.

The Log is as follows:

Log("filepath2: " & FilePath2)

(note that the DB is still empty)

filepath2: C:\temp\{C}\
Error occurred on line: 381 (B4XMainPage)
java.sql.SQLException: ResultSet closed
    at org.sqlite.RS.checkOpen(RS.java:63)
    at org.sqlite.RS.findColumn(RS.java:108)
    at org.sqlite.RS.getString(RS.java:317)
    at anywheresoftware.b4j.objects.SQL$ResultSetWrapper.GetString(SQL.java:552)
    at b4j.example.b4xmainpage._comparedata2(b4xmainpage.java:711)
    at b4j.example.b4xmainpage$ResumableSub_Button1Search_MouseClicked.resume(b4xmainpage.java:206)
    at anywheresoftware.b4a.shell.DebugResumableSub$DelegatableResumableSub.resumeAsUserSub(DebugResumableSub.java:47)
    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:564)
    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.GeneratedMethodAccessor2.invoke(Unknown Source)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:564)
    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.b4a.shell.DebugResumableSub$DelegatableResumableSub.resume(DebugResumableSub.java:42)
    at anywheresoftware.b4a.keywords.Common$2$1.run(Common.java:1051)
    at javafx.graphics/com.sun.javafx.application.PlatformImpl.lambda$runLater$10(PlatformImpl.java:428)
    at java.base/java.security.AccessController.doPrivileged(AccessController.java:391)
    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:832)

Perhaps for the the error without data in the DB I need to add a check if there is data, but on the other hand I expected this would not cause an error.If that might be a cause.

(with data in DB)

filepath2: C:\temp\{C}\
Error occurred on line: 379 (B4XMainPage)
java.sql.SQLException: statement is not executing
    at org.sqlite.Stmt.checkOpen(Stmt.java:49)
    at org.sqlite.PrepStmt.batch(PrepStmt.java:170)
    at org.sqlite.PrepStmt.setObject(PrepStmt.java:238)
    at anywheresoftware.b4j.objects.SQL.ExecQuery2(SQL.java:368)
    at b4j.example.b4xmainpage._comparedata2(b4xmainpage.java:705)
    at b4j.example.b4xmainpage$ResumableSub_Button1Search_MouseClicked.resume(b4xmainpage.java:206)
    at anywheresoftware.b4a.shell.DebugResumableSub$DelegatableResumableSub.resumeAsUserSub(DebugResumableSub.java:47)
    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:564)
    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.GeneratedMethodAccessor2.invoke(Unknown Source)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:564)
    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.b4a.shell.DebugResumableSub$DelegatableResumableSub.resume(DebugResumableSub.java:42)
    at anywheresoftware.b4a.keywords.Common$2$1.run(Common.java:1051)
    at javafx.graphics/com.sun.javafx.application.PlatformImpl.lambda$runLater$10(PlatformImpl.java:428)
    at java.base/java.security.AccessController.doPrivileged(AccessController.java:391)
    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:832)

Now this seems to work today:

Dim RS3 As ResultSet = Main.SQL2.ExecQuery2("SELECT * FROM DblFiles WHERE Fullpath = ? ", Array As String (FilePath2)) 
               Do While RS3.NextRow
                    FilePath3 = RS3.GetString("Fullpath") ----this is line 381
                    Log("FilePath3 : " & FilePath3)
                Loop
             RS3.Close

Not that I understand why, because I've tried this before.
Maybe its because I said to my PC I'm gonna tell Erel if you don't work, I think that did it

Thanks for the heads up.