LOCATE on firebird generates unknown function

[Przemo]

Edit: WARNING: The title was edited, it was about HSQLDB previously

System info:
linux fedora 33
LO 7.0.4.2

Table t_test with 2 columns, one is ID [INTEGER], the other is number TEXT [VARCHAR]
The name field contains values like:

1/2012
2/2012
1/2013

I’m trying to use query:

SELECT "number", LOCATE( '/', "number" ) FROM "t_test"

but I get error:

The data content could not be loaded. /builddir/build/BUILD/libreoffice-7.0.4.2/connectivity/source/commontools/dbtools.cxx:751

Error code 

firebird_sdbc error:
*Dynamic SQL Error
*SQL error code = -804
*Function unknown
*LOCATE
caused by
'isc_dsql_prepare'
 /builddir/build/BUILD/libreoffice-7.0.4.2/connectivity/source/drivers/firebird/Util.cxx:68

I tried to use " ’ and [] as delimiters, but none works. What I’m doing wrong?

I used this [1] as reference describing formatting of LOCATE

[1] Chapter 4. Built In Functions

[anon73440385]

firebird_sdbc error:

You don’t seem to use a hsqldb but a firebird database and you may want to check http://firebirdsql.org/ for functions supported by firebird.

[Przemo]

@anon73440385 Thank you! - that’s another lesson for me. I use the same ods file on 2 systems: windows 10 and linux fedora and I wrongly assumed that if I see HSQLDB on windows it’s the same on linux…

[anon73440385]

Edit: WARNING: The title is misleading! I don’t use HSQLSB, but I can’t edit the topic title.

You can edit the title:

[Przemo]

I think it depends on the karma. I’ve fixed the title.

[anon73440385]

I think it depends on the karma

No - it doesn’t

[Przemo]

OK, anyway it works now.

[Ratslinger]

Hello,

Just as the version of a database is important to refer to documentation, the actual database type is even more important. In Base this can be seen on the bottom line of the main Base screen. Most databases follow standards but also have additional processes not in the standard. This is true for most databases you will come across.

In Firebird there is no Locate but rather Position() (see → this post).

So the statement previously given for HSQLDB just needs a simple modification to work with Firebird:

SELECT "DATEID", "text_date", LEFT( "text_date", POSITION( '/', "text_date" ) - 1 ) "data_left", RIGHT( "text_date", 4 ) "data_right" FROM "DATETEST"