New firebird db - can't use sql functions

[jjz]

Using LO Version: 6.2.8.2 (x64)
With fresh install of JVM

Created a firebird db

Imported a csv using the Copy from calc - Paste to a new table method. All columns (except ID) automatically created as VARCHAR 255.

Can successfully use a simple select with simple where clause. Such as:
SELECT * FROM Table1 WHERE OtherInfo = 'Tel ’

When attempting this select
SELECT * FROM Table1 WHERE SUBSTRING(OtherInfo, 1, 4) = 'Tel ’

I get this error:

---

Error code: 1

firebird_sdbc error:
*Dynamic SQL Error
*SQL error code = -104
*Token unknown - line 1, column 53
*, caused by 'isc_dsql_prepare'

---

I have searched for hours and cannot find a solution. I tried running the macro suggested in LO Bug link text

It appears that the use of the SUBSTRING is calling a parsing function that is not working.
Any idea what is wrong?

thanks

[Ratslinger]

Hello,

It appears you are using SQL from HSQLDB. Firebird may be different in some cases and other databases as well. For some of these differences see this post: Firebird Migration Expectations. Also the Firebird documentation is located here: Firebird RDBMS. Firebird 2.5 Language Reference (English) is the latest documentation.

For your statement you should use:

SELECT * FROM "Table1" WHERE SUBSTRING("OtherInfo" from 1 for 4) = 'Tel '

[jjz]

Thanks, the suggested syntax did function. It does make me wonder if I should start over with an HSQLDB database instead of firebird. I choose firebird as the bit I read said it was a better database. My goal is a self contained system (single file approach) instead of a more advanced separate MySQL with LO as a front end. It is a “lightweight” solution. Am I better off with Firebird or HSQLDB?

[Ratslinger]

Hello,

That is a matter of personal choice. The direction of LO is Firebird. HSQLDB is very old and with embedded has some possibilities of losing data not yet seen in Firebird. As an example see this post → Base+HSQLDB: Closing a table, LO crashed.. Now understand Firebird is not fully implemented and there are some problems (most answered on this site already) which can be worked around. If you are willing to do this Firebird certainly has more capabilities and appears to be more reliable. Have been using for over a year now and find SQL alone worth the effort.

[jjz]

Thank you - I’ll go with firebird as it is the planned direction. With people like you helping to find solutions to any difficulties, I should be OK. Thank you again!

[Ratslinger]

@jjz,

If you have a situation try searching the site with keyword Firebird. As noted there are many answers already. If still having problems, ask as a new question. Will be happy to try and help.