MichalK73 Well-Known Member Licensed User Longtime User Mar 2, 2023 #1 Welcome. I have a strange case. The syntax of the query: SQL: Copy 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: Copy 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?
Welcome. I have a strange case. The syntax of the query: SQL: Copy 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: Copy 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?
Solution DonManfred Mar 2, 2023 MichalK73 said: According to your link the substring function is in SQLite, but in my case it says there is no such function. Click to expand... The commandname in SQLite is SUBSTR, not SUBSTRING.
MichalK73 said: According to your link the substring function is in SQLite, but in my case it says there is no such function. Click to expand... The commandname in SQLite is SUBSTR, not SUBSTRING.
DonManfred Expert Licensed User Longtime User Mar 2, 2023 #2 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 ;-) Last edited: Mar 2, 2023 Upvote 0
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 Well-Known Member Licensed User Longtime User Mar 2, 2023 #3 DonManfred said: 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 Click to expand... 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
DonManfred said: 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 Click to expand... 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 Well-Known Member Licensed User Longtime User Mar 2, 2023 #4 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
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 Expert Licensed User Longtime User Mar 2, 2023 #5 MichalK73 said: According to your link the substring function is in SQLite, but in my case it says there is no such function. Click to expand... The commandname in SQLite is SUBSTR, not SUBSTRING. Upvote 0 Solution
MichalK73 said: According to your link the substring function is in SQLite, but in my case it says there is no such function. Click to expand... The commandname in SQLite is SUBSTR, not SUBSTRING.
M Mahares Expert Licensed User Longtime User Mar 2, 2023 #6 DonManfred said: The commandname in SQLite is SUBSTR, not SUBSTRING. Click to expand... MichalK73 said: n standard SQL, there is a SUBSTRING function. In SQLite, it is SUBSTR. Click to expand... 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
DonManfred said: The commandname in SQLite is SUBSTR, not SUBSTRING. Click to expand... MichalK73 said: n standard SQL, there is a SUBSTRING function. In SQLite, it is SUBSTR. Click to expand... 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.
