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

[LucaMs]

This query:

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

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

 

[Philip Chatzigeorgiadis]

Although practically empty (one table, one row, one field) the file has a size of 139MB!

It might be helpful to try "Compact and Repair Database".

 

[LucaMs]

It might be helpful to try "Compact and Repair Database".

Yes, done right now (few minutes ago).

Thank you.

 

[Jorge M A]

Please try changing the query with cDate function, (without pound signs)

Dim Query As String = $"SELECT * FROM Table1 WHERE OneDate = cDate('${SomeDate}')"$

(assuming the column is a TIMESTAMP type)

 

[LucaMs]

(assuming the column is a TIMESTAMP type)

It is DateTime, then cDate is not needed (I tried anyway) and the error remains.

 

[OliverA]

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

    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

 

[LucaMs]

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

 

[LucaMs]

3) To get your sample going I had to block out this code block in InitDB

I did it too, of course with leaving there the copy line, and now it works (even with date 31/08/2020).
Evidently the db file was corrupt.

Thank you all

 

[LucaMs]

I did it too, of course with leaving there the copy line, and now it works (even with date 31/08/2020).
Evidently the db file was corrupt.

Thank you all

Forget it; deleting that part, the test is not executed, this is why I get no error.

 

[LucaMs]

Now I get:

net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::4.0.4 unknown token:

even in the test project

 

[OliverA]

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.

 

[LucaMs]

Trying the last version of UCanAccess (5.0)...

 

[LucaMs]

I did not repair the DB,

I did it and updated the project attached.

Trying your project...

 

[OliverA]

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?

 

[LucaMs]

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

 

[LucaMs]

why not use the newest?

Because you need to know that you are not using the latest version (I rarely used ucanaccess).

 

[OliverA]

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

 

[Jorge M A]

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.

@FunctionType(functionName = "CDATE", argumentTypes = { AccessType.MEMO }, returnType = AccessType.DATETIME)
    public static Timestamp cdate(String dt) {
        return dateValue(dt);
    }

 

[OliverA]

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)

 

[LucaMs]

Just tested #2020-08-31# and #2020-8-31#