Ask Your Question
0

New firebird db - can't use sql functions

asked 2019-11-26 16:49:07 +0200

jjz gravatar image

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

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2019-11-26 17:10:22 +0200

Ratslinger gravatar image

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 '
edit flag offensive delete link more

Comments

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?

jjz gravatar imagejjz ( 2019-11-26 17:29:23 +0200 )edit

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.

Ratslinger gravatar imageRatslinger ( 2019-11-26 17:54:21 +0200 )edit

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!

jjz gravatar imagejjz ( 2019-11-26 18:07:15 +0200 )edit

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

Ratslinger gravatar imageRatslinger ( 2019-11-26 18:38:44 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-11-26 16:49:07 +0200

Seen: 126 times

Last updated: Nov 26 '19