B4J Question [SOLVED] SQLite does not understand SQL syntax

MichalK73

Well-Known Member
Licensed User
Longtime User
Welcome.

I have a strange case. The syntax of the query:
SQL:
    Dim g As List = DBM.SQLSelect($"select grupa, dateend from film  where SUBSTRING (grupa, 1, 1) <> "." group by grupa order by grupa asc"$, Null)

A query in SQLManager on the same file passes nicely and shows the results as it wants. Select with omitting the 'group' column if the first character is not a '.'

Unfortunately it does not work in B4J, I get an error
B4X:
2023-03-02 1124 -> (SQLException) java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (no such function: SUBSTRING)

Do the drivers used for SQLite in B4J have limitations?
Has anyone encountered a similar problem?
 

DonManfred

Expert
Licensed User
Longtime User
For SQLite use substr šŸ˜‚

PS: It also works in MySQL ;-)
 
Last edited:
Upvote 0

MichalK73

Well-Known Member
Licensed User
Longtime User
Thank you for your reply.

According to your link the substring function is in SQLite, but in my case it says there is no such function.
Do you have a link to another version of the sqlite driver for b4j ??
 
Upvote 0

MichalK73

Well-Known Member
Licensed User
Longtime User
I already know the difference.
In standard SQL, there is a SUBSTRING function.
In SQLite, it is SUBSTR.
This is why it worked in SQLStudio and not in B4J SQLite.


Problem solved.
 
Upvote 0

Mahares

Expert
Licensed User
Longtime User
The commandname in SQLite is SUBSTR, not SUBSTRING.
n standard SQL, there is a SUBSTRING function.
In SQLite, it is SUBSTR.

The substr() SQLite function can also be replaced with "substring()" if you use SQLite version 34.0.0 or higher. Before that vesrion, you can only use Substr.
You can download a more recent version of sqlite-jdbc instead of: #AdditionalJar: sqlite-jdbc-3.7.2. You will be good to go.
 
Upvote 0
Top