B4J Question [SOLVED] SQLite does not understand SQL syntax

[MichalK73]

Welcome.

I have a strange case. The syntax of the query:

    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

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]

For SQLite use substr ?

SQLite substr Function

The SQLite substr function returns a substring from a string starting at a specified position with a predefined length.

www.sqlitetutorial.net

PS: It also works in MySQL ;-)

 

[MichalK73]

SQLite substr Function

The SQLite substr function returns a substring from a string starting at a specified position with a predefined length.

www.sqlitetutorial.net

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 ??

 

[MichalK73]

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.

 

[DonManfred]

According to your link the substring function is in SQLite, but in my case it says there is no such function.

The commandname in SQLite is SUBSTR, not SUBSTRING.

 

[Mahares]

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.