B4J Question [SOLVED] UCanAccess - error on a simple query with a date-time field

LucaMs

Expert
Licensed User
Longtime User
This query:

SELECT * FROM Table1 WHERE OneDate = #31/08/2020#

works perfectly in Access sw, raises an error in b4j using ucanaccess library.
 

Attachments

  • UCanAccessTest.zip
    16 KB · Views: 243
Last edited:

Jorge M A

Well-Known Member
Licensed User
Please try changing the query with cDate function, (without pound signs)
B4X:
Dim Query As String = $"SELECT * FROM Table1 WHERE OneDate = cDate('${SomeDate}')"$
(assuming the column is a TIMESTAMP type)
 
Upvote 0

OliverA

Expert
Licensed User
Longtime User
1) What was the error message? Mine was
net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::4.x.x unknown token: in statement [SELECT * FROM Table1 WHERE OneDate = #31/08/2020#]
2) I changed the date to good ol US thinking 08/31/2020 and it worked
3) To get your sample going I had to block out this code block in InitDB
B4X:
    If Result Then
        Try
            If Not(File.Exists(File.DirApp, accDBName)) Then
                File.Copy(File.DirAssets, accDBName, File.DirApp, accDBName)
            End If
            AccDB.InitializeSQLite(File.DirApp, accDBName, False)
        Catch
            Log(LastException)
            Result = False
        End Try
    End If
 
Upvote 0

LucaMs

Expert
Licensed User
Longtime User
1) What was the error message? Mine was
In my real project I get the same error message as you; in the test project I attached, I get a different one; this:
java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (near "#31": syntax error)

2) I changed the date to good ol US thinking 08/31/2020 and it worked
I had already tried to change the format of the past date as a parameter, but having the doubt of having tried in the real project, I tried again in the attached project and I get the same error:
java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (near "#08": syntax error)

To get your sample going I had to block out this code block in InitDB
šŸ˜¦
 
Upvote 0

OliverA

Expert
Licensed User
Longtime User
Here is your project, working for me, with the block mentioned above blocked out. The test is executed. I did not repair the DB, I did nothing else but block out the code and change the date format.
 

Attachments

  • UCanAccessTest_OliverA.zip
    16 KB · Views: 199
Upvote 0

OliverA

Expert
Licensed User
Longtime User
Trying the last version of UCanAccess (5.0)...
That was going to be my question: Why are you using 4.0.4? If you are trying to be cross platform with B4A (4.0.4 is the last version working in Android) I would understand, otherwise, why not use the newest?
 
Upvote 0

LucaMs

Expert
Licensed User
Longtime User
I did it and updated the project attached.

Trying your project...
... and your project works.
Also, I found the error...

Creating the test project, I used the real project, removing what not needed but keeping and badly changing part of it:

AccDB.Initialize("net.ucanaccess.jdbc.UcanaccessDriver", "jdbc:ucanaccess://" & AccDBPath)

but after that...

AccDB.InitializeSQLite(File.DirApp, accDBName, False)


(the real project imports data from Access to SQLite).
 
Upvote 0

OliverA

Expert
Licensed User
Longtime User
... and your project works.
I just updated to use UCanAccess 5.0.0 and even it wants US collation on the date. Go figure... (Note: I'm not a UCanAccess expert)
 
Upvote 0

Jorge M A

Well-Known Member
Licensed User
Just for the record: with cDate function the date format does not matter, as long as it is valid, the conversion from string to date is done internally , and you remove the ugly #'s.
B4X:
@FunctionType(functionName = "CDATE", argumentTypes = { AccessType.MEMO }, returnType = AccessType.DATETIME)
    public static Timestamp cdate(String dt) {
        return dateValue(dt);
    }
 
Upvote 0

OliverA

Expert
Licensed User
Longtime User
Just tested #2020-08-31# and #2020-8-31# and it works (with 4.0.4 and 5.0.0). So using a / it expects US collation and with - it wants yyyy-mm-dd format (note: this is not a language specific string format specification, it just means 4 digit year, one or two digit month and one or two digit date)
 
Upvote 0
Top